SQL Join Help? - 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)

SQL Join Help?

Author

Topic

Malby
Starting Member

10 Posts

Posted - 2002-08-28 : 22:03:48

Here are my table structures;

lGrades

ID | Name
--------------------------------
1 | AA
2 | A
3 | AB
4 | B
5 | C
6 | D
7 | E
8 | XX

tblGrades
ClientID | GradeID | TopupMax
1 | 1 | 100000
1 | 2 | 10000
1 | 3 | 1000
1 | 4 | 100
1 | 5 | 10
2 | 1 | 10000000
2 | 2 | 1000000
2 | 3 | 100000
2 | 4 | 10000
2 | 5 | 1000
2 | 6 | 100
2 | 7 | 10
2 | 8 | 1

Here's an example query;

SELECT g.ID, g.Name, (SELECT TopupMax FROM tblGrades cg WHERE cg.GradeID = g.ID AND cg.ClientID = 1) AS TopupMax
FROM lGrades g

Here is the output:

ID Name TopupMax
----- ---------- -----------
1 AA 100000
2 AB 10000
3 A 1000
4 B 100
5 C 10
6 D NULL
7 E NULL
8 XX NULL

Now my question is... How can I do that WITHOUT using the subquery?

I tried a left outer join, however, it only returns:

ID Name TopupMax
----- ---------- -----------
1 AA 100000
2 AB 10000
3 A 1000
4 B 100
5 C 10

Not the null records. I need the null records.

Help?

----------------------
"An eye for an eye will leave the whole world blind" - Ghandi

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-08-28 : 22:45:43

All you need to do is this:

SELECT g.ID, g.Name, cg.TopupMax 
FROM lGrades g LEFT JOIN tblGrades cg 
      ON cg.GradeID = g.ID AND cg.ClientID = 1

if you put the ClientID check in the WHERE clause, it will filter the whole result set. Putting the check in the JOIN clause is a way of getting around this.

Hope it helps.

Tim

Malby
Starting Member

10 Posts

Posted - 2002-08-28 : 22:49:45

Note, I empted tblGrades completely, because I want the following query to rewturn all Nulls for TopupMax...

SELECT g.ID, g.Name, c.TopupMax
FROM lGrades g
LEFT JOIN tblGrades c ON g.ID = c.GradeID
WHERE c.ClientID = 1

ID Name TopupMax
----- ----- -----------

(0 row(s) affected)

----------------------
"An eye for an eye will leave the whole world blind" - Ghandi

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-08-29 : 02:59:50

Malby

I saw this over at Whirlpool, but prefer to post here

Try Timmy's query. He has put the client filter in the join clause which will only join client 1. If you filter in the where clause it will require client id to be 1 which eliminates the point of a left join.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources