Simple SQL Question on Joins

mountainyman · 06-11-2008 05:51PM #1

How do I join 2 Tables on JOINID where
Table 1 has 5 records and
Table 2 has 500 records and I want to get 500 records

I want the fields from Table1 to be null in the Query but a right Outer Join is giving me 2500 records and an Inner Join is giving me 5 records !!!

Left Outer Join Gives 5 records
Join Gives 5 records

What do I do?

brianmc · 06-11-2008 05:52PM

mountainyman wrote: »

How do I join 2 Tables on JOINID where
Table 1 has 5 records and
Table 2 has 500 records and I want to get 500 records

I want the fields from Table1 to be null in the Query but a right Outer Join is giving me 2500 records and an Inner Join is giving me 5 records !!!

Left Outer Join Gives 5 records
Join Gives 5 records

What do I do?

Reverse the tables and LEFT OUTER to Table 1?

Silent Rain · 06-11-2008 06:04PM

Shouldnt have to.
The right outer join should work...
Are you sure you arent accidently typing the name of the larger table twice in the join ?
i.e. joining on itself ?

mountainyman · 06-11-2008 06:06PM

Thanks

Tried that but it didn't work.

Swapping the bit after the 'ON' didnt work

(what's that called by the way?

(Players p Right Outer JOIN Teams te ON p.team = te.PK)

Silent Rain · 06-11-2008 06:16PM

SELECT *
FROM Player
SELECT *
FROM Team
SELECT *
FROM Team T
RIGHT OUTER JOIN Player P
ON T.TeamID = P.TeamID

PlayerID Name TeamID
1 Dave 1
2 John 3
3 Mary 2
4 Susan 5
5 Sandra 4
6 Nick 3
7 Mick 2
8 James 1
9 John 1
(9 row(s) affected)

TeamID TeamName
1 Tigers
2 Panthers
3 Seahawks
(3 row(s) affected)

TeamID TeamName PlayerID Name TeamID
1 Tigers 1 Dave 1
3 Seahawks 2 John 3
2 Panthers 3 Mary 2
4 Susan 5
5 Sandra 4
3 Seahawks 6 Nick 3
2 Panthers 7 Mick 2
1 Tigers 8 James 1
1 Tigers 9 John 1
(9 row(s) affected)

Sorry about the poor formatting.... but is this not what you are going for ?

marco_polo · 06-11-2008 09:01PM

Looks like a full outer join (5*500)is being done by the query

. What is the exact query you are using and ON which DB?

Simple SQL Question on Joins

Comments