Syntax Probelem

[dhulbert]

I'm sure this should work, just can't get the syntax right.

I want to join 2 tables and depending on a flag in 1 of the tables vary the data that gets selected in the result, so if the select flag is set to 1 then only select where the appointment flag is 1 when the select flag is set to 0 then select regardless of the appointment flag

Code below gives an idea of what I'm looking for.

Thanks.

select *
from    Table1
		inner join
        Table2
        on
        Table1.CALLID = Table2.JOBID
        
where  Table2.APPOINTMENT IN (CASE WHEN Table1.Flag = 1 THEN 1 ELSE Table1.Flag ="Anything" END)

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[drlex]

Would this satisfy?

SELECT *
FROM    Table1
        INNER JOIN        Table2
        ON
        Table1.CALLID = Table2.JOBID
        
WHERE  Table1.Flag = 0 OR (Table1.Flag = 1 and Table2.APPOINTMENT =1)

soi là, soi carré

 

[dhulbert]

Not quite I wanted to select if table1.flag =1 then sect where table2.appointment flag = 1 if table1.flag <> 1 then select where table2.appointment flag is any value.

Have now worked out the syntax myself though.

Where  Table2.Appointment like (CASE WHEN Table1.flag = 1 THEN '1' ELSE '%' END)

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[drlex]

Ah, OK - I'd seen Table1.Flag as a bit/logical field. In which case

...WHERE  Table1.Flag <> 1 OR (Table1.Flag = 1 and Table2.APPOINTMENT =1)

soi là, soi carré