Using 'Where' OR Having for Searching select in SQL-CodePudding

I am trying to fetch records in SQL using a simple used submitted field. More precisely, the user inputs a keyword (name,m_name, or field_region) and the server should return matched rows.

Here my code :

Select user_sg.id as id,user_sg.name as name,id_channel,master_channel.code,master_channel.name as m_name,user_sg.id_relation,
            STUFF((SELECT ', '   region_code
                       FROM user_sg_region AS T3 
                       WHERE T3.id_sg = user_sg.id 
                      FOR XML PATH('')), 1, 2, '') as field_region
    FROM user_sg 
    INNER JOIN master_channel ON user_sg.id_channel=master_channel.id 
where user_sg.name like '%search%' OR master_channel.name like '%search%'
    GROUP BY user_sg.id,user_sg.name,id_channel,master_channel.code,master_channel.name,user_sg.id_relation
ORDER BY user_sg.Id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

That works well for now, but that (obviously) won't work when a user trying to search field_region. Is there a way to add a OR between the whole 'WHERE type conditions' and the 'HAVING type conditions'?

what should i change where it can be work like what i need ? (I need i can search for field_region)

Here my table :

Here My sample data :

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=62409eec4ca0eef171d2d862e99c98d4

CodePudding user response：

If you need FOR XML to aggregate then you need to add an EXISTS clause to check the child table.

DECLARE @search varchar(255) = '%BSI%';

Select user_sg.id as id,user_sg.name as name,id_channel,master_channel.code,master_channel.name as m_name,user_sg.id_relation,
            STUFF((SELECT ', '   region_code
                       FROM user_sg_region AS T3 
                       WHERE T3.id_sg = user_sg.id 
                      FOR XML PATH('')), 1, 2, '') as field_region
FROM user_sg 
INNER JOIN master_channel ON user_sg.id_channel=master_channel.id 
WHERE user_sg.name like @search
   OR master_channel.name like @search
   OR EXISTS (SELECT 1
    FROM user_sg_region AS T3 
    WHERE T3.id_sg = user_sg.id
      AND T3.region_code LIKE @search)
ORDER BY user_sg.Id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

In newer versions of SQL Server you can use STRING_AGG inside an APPLY, and combine it with a conditional COUNT.

DECLARE @search varchar(255) = '%BSI%';

Select user_sg.id as id,user_sg.name as name,id_channel,master_channel.code,master_channel.name as m_name,user_sg.id_relation,
  T3.field_region
FROM user_sg 
INNER JOIN master_channel ON user_sg.id_channel=master_channel.id
CROSS APPLY (
    SELECT
      STRING_AGG(T3.region_code, ', ') AS field_region,
      COUNT(CASE WHEN T3.region_code LIKE @search THEN 1 END) AS match
    FROM user_sg_region AS T3 
    WHERE T3.id_sg = user_sg.id 
) T3
WHERE user_sg.name like @search
   OR master_channel.name like @search
   OR T3.match > 0
ORDER BY user_sg.Id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

db<>fiddle

I see no need for the GROUP BY as there is no aggregation in the outer scope.
In both versions, the aggregation is done inside a subquery, which does not affect the outer SELECT