Stuck in a join - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Stuck in a join

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-09 : 10:57:05

Mandar writes "Hi Experts,

I have 3 master tables :

PR_Mast : PR_NO, PR_DESC
Type_Mast: Type_No, Type_Desc
COB_Mast: COB_No, COB_Desc

and a relational table :

PR_COB_REL : PR_NO, Type_No, COB_No

the relationship within all 3 is many to many.

i have designed a query to select specific COB_No as follows

select COB_No from PR_COB_REL where PR_NO='P1' and Type_No='T1'

The output is suppose:
C1
C2
C3
C6

now i want to design a query where i want to select the COB_Nos other than the above mentioned output from the table COB_Mast.

how should i make the join? Plz explain.

Regards"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-09 : 12:23:17

If I understand correctly,

select a.COB_No, c.COB_Desc from PR_COB_REL a join COB_Mast c
on a.COB_No = c.COB_No
where not exists (select COB_No from PR_COB_REL b
where a.COB_No = b.COB_No and PR_NO='P1' and Type_No='T1')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources