Simple SQL Question on Joins

mountainyman

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

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

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

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

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

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?