locked
Can an SQL Query have an equivalent to a "IF" "then" statement? If so, do I use it ? RRS feed

  • Question

  • Hello,

    I am trying to learn SQL query, and I have asked to come up with a collation of data from other tables, but one of the columns might not have data in it, but if it does, I need to find a matching vale and place in another column, ie

    if group_id >0 then find the matching IDs description and put in the group_description column, else leave both blank.

    my code (amended for brevity) looks like 

    INSERT @RESresults
    SELECT OtherVariables,a.group_id,'',MoreVariables
    FROM resTable a , @findInfo b , andOTHERtables
    WHERE a.guest_id = b.guest_id
    and b.country_id = a.country_id
    MORE "ands"
    GROUP BY re_id], etc 

    What I cannot work out is how, and where (and if) to put the comparison.

    I can do a "and" like I have with other matchs, but if the group_id = 0, then I loose this information from the table.

    I need to keep all the rows, but add the corresponding IDs description  =if the group_id >0. Ie


    Could someone explain to me how I can do this please ?

    Many Thanks in advance 

    Thursday, March 5, 2020 2:11 PM

All replies

  • DECLARE @YourTable TABLE (
    	group_id int,
    	group_description varchar(20)
    )
    INSERT INTO @YourTable VALUES
    (0, ''), (0, ''), (1727625, ''), (1727625, ''), (0, ''), (0, '');
    
    WITH CTE AS (
    	SELECT group_id, COUNT(group_id) AS id_count
    	FROM @YourTable
    	WHERE group_id > 0
    	GROUP BY group_id
    )
    
    UPDATE t
    SET t.group_description = 'Mums Oarty'
    FROM @YourTable AS t
    INNER JOIN CTE AS c ON t.group_id = c.group_id;
    
    SELECT * FROM @YourTable;


    A Fan of SSIS, SSRS and SSAS

    Thursday, March 5, 2020 3:05 PM
  • It is time to learn to write code using current best practices. That means you specify the column list in your insert statement. It means you use the much preferred join syntax rather than the ancient and error-prone join-via-where clause. 

    It seems an outer join should work here. Ignoring the insert portion and focusing on the select portion, it would look like: 

    select <columns>, 
        grp.description -- replacing a.group_id
        <other columns>
    from dbo.resTable as res 
    inner join @findInfo as fnd on res.guest_id = res.guest_id
    left join <your group descriptions table> as grp on res.group_id = grp.group_id
    inner join <your other tables in similar fashion> 
    where ...
    group by ... 
    ;

    Notice the terminator - get into the habit of using one as it will eventually be required and is now required in certain situations. And one other note - you specifically mention "group_id > 0". Either this is a mistaken assumption or you don't have an actual foreign key constraint. Either way, that is a concerning situation. 

    Thursday, March 5, 2020 3:10 PM
  • Thank you both for your help, I got round the problem by inserting an extra value into my temporary @findInfo table

    INSERT into @findIfo 
    VALUES(0,'')

    and then doing a normal "and" compassion when building the bigger tablr.

    I will look into what you have suggested though.

    many thanks again 

    Friday, March 6, 2020 8:44 AM