Conditional Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conditional Joins

Author

Topic

SuperSQLNewb
Starting Member

9 Posts

Posted - 2009-03-13 : 16:52:16

I have a query like
SELECT * FROM Apps WHERE ID = 6
One of the fields in table Apps is Status
I want to join table Holds if status = Hold So I can SELECT NotificationTime FROM HoldList WHERE AppID = 6

How can I do this?

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 16:54:43

Post some sample data and expected output.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-13 : 16:56:15

I dont fully understand your question...but I think this is what you need.

SELECT a.col1,a.col2,b.NotificationTime
FROM Apps a INNER JOIN Holds b
ON a.ID = b.AppID AND a.Status = 'Hold'

SuperSQLNewb
Starting Member

9 Posts

Posted - 2009-03-13 : 17:07:28

Table Apps
ID Status
1 Open
2 Closed
6 Hold
7 Hold

Table Holds
ID AppID NotificationTime
23 6 2009:03:27:00:00:00
27 7 2009:03:29:00:00:00

Table Holds will not have AppID 1 and 2 in them since they do not have a status of Hold

Expected output if Query is: SELECT * FROM Apps WHERE ID = 6
ID Status NotificationTime
6 Hold 2009:03:27:00:00:00

Expected output if Query is: SELECT * FROM Apps WHERE ID = 1
ID Status
1 Open

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-14 : 03:15:50

i think is this ur requirement
SELECT a.*,th.NotificationTime FROM Apps a inner join TableHolds th on a.id = th.appid WHERE a.ID = 6

SELECT * FROM Apps a WHERE ID = 1 AND NOT EXISTS (select * from TableHolds WHERE appid = a.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources