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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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