Which Join to use, if any?

[laina222]

I'm trying to link two tables with a join. I'm terrible with joins, so my usual method is to just keep trying each one until I get the results I want.

When I do a right join, I get all blank records for one table, and the records populated for the other. When I do a left join, the opposite happens.

Any advice is appreciated. Thanks in advance!

 

[mp9]

A little info regarding the structure of the tables (i.e. fieldnames and what/why/how you want to join them) would help us to help you.

 

[r937]

When I do a right join, I get all blank records for one table, and the records populated for the other. When I do a left join, the opposite happens.

have you tried an inner join?

if you are not joining on the correct columns, the inner join should return no rows at all!




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[BNPMike]

And when you do an upside-down join, all the records fall out.

 

[r937]

the upside-down join is actually called a FULL OUTER JOIN and access doesn't support that, you have to simulate it with a UNION of a LEFT OUTER and a RIGHT OUTER



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[laina222]

One table is data based on a customer phone number. For the other table I took the phone number and pulled more data from our AS400 based on the phone numbers. So now I want to take the data from both tables and merge it based on the phone number. I currently have the phone numbers stored as text fields - maybe it would help if they were numeric? Anyway, here is the hypothetical SQL I have:
SELECT hw1.*, [qry400].[Claim], [qry400].[Sfx], [qry400].[AOS], [qry400].[PhoneNum]
FROM hw1 SOME KIND OF JOIN qry400 ON [hw1].[phnumber]=[qry400].[PhoneNum];

 

[r937]

select hw1.*
     , qry400.Claim
     , qry400.Sfx
     , qry400.AOS
     , qry400.PhoneNum
  from hw1 
[b]inner[/b]
  join qry400 
    on hw1.phnumber = qry400.PhoneNum

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[BNPMike]

the upside-down join is actually called a FULL OUTER JOIN

No it's the fall outa join you're thinking of...

You can put all the records back in again in any order as long as they are all the same way round. I think that's what Ted Codd said...

 

[lespaul]

Understanding SQL Joins is a great article for understanding the basics of Joins.

HTH

leslie