subselect or outer 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

General SQL Server Forums

New to SQL Server Programming

subselect or outer join??

Author

Topic

tjbarr
Starting Member

8 Posts

Posted - 2009-03-18 : 11:45:35

Original Queries

1.

SELECT PROPOSAL_FULL.PROPID, PROPOSAL_FULL.PROPDIV, DIVISION.TABLE_VAL
FROM PROPOSAL_FULL, DIVISION

WHERE (PROPOSAL_FULL.PROPDIV=DIVISION.TABLE_CODE)

2.

SELECT Relation_full.relid FROM Relation_FUll WHERE Relation_FULL.RELWHOSE='pm'

I want to combine these where relation_full.relid = proposal_full.propid - to show all records from Proposal_full. The problem is there are a few in proposal full that have more than one type of Relation_full.relwhose in the relation_full_table but one of them isn't 'pm' so I am losing those.

A left join doesn't work because then if there are 3 types of RelWHose then you get this:

Company RELWHOSE
ABC mm
ABC jj
ABC pm

An inner join only gives me

ABC pm

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-03-18 : 12:12:11

Try the ANSI92 outer join syntax and put the RelWhose filter in the ON condition:

SELECT *
FROM Proposal_Full P
    JOIN Division D
        ON P.PropDiv=D.Table_Code
    LEFT JOIN Relation_Full R
        ON P.PropId = R.RelId
            AND R.RelWhose = 'pm'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 11:11:12

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources