selecting multiples from multiply tables

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

selecting multiples from multiply tables

Author

Topic

Neon
Starting Member

13 Posts

Posted - 2011-12-06 : 23:22:38

hi looked around a while trying to do this, however i'm even mor confused than when i started and someone please tell me how i can do this, or direct me to somewhere i can find out thanks

i have data as follows

PTtable
PID, PTID
1, 1
1, 4
2, 2
2, 4
3, 7
4, 1
4, 4

Ptable
PID, Name
1, Pump 1
2, Pump 2
3, Pump 3
4, Pump 4

i wish to select pumps from Ptable only when a single pump (PID), has both PTID of 1 and 4
so result would be

PID Name
1 Pump 1
4 Pump 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:33:39

[code]
SELECT p.*
FROM Ptable p
INNER JOIN (SELECT PID
FROM PTtable
WHERE PTID IN (1,4)
GROUP BY PID
HAVING COUNT(DISTINCT PTID)=2
)q
ON q.PID = p.PID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Neon
Starting Member

13 Posts

Posted - 2011-12-06 : 23:41:20

Wow fast thanks heaps will go play around with it now

again thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:46:21

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources