Mutliple Table Left Join

[evaaseow]

Hi,

I'm having a problem with a query and was hoping someone could help me out.

I have 3 tables each with the same key.

Table 1 - List of Customers (500 rows)
Table 2 - Usage (500 rows)
Table 3 - Status (400 rows)

I attempted to do this:

Select a.*
from customers a left join usage b on a.id = b.id
left join status c on b.id = c.id

My goal is to end up with 500 rows but i'm ending up with 400, so i'm thinking i'm not understanding the concept of left join with multiple tables. Could someone please help?

Thanks!

 

[RiverGuy]

Instead of joining Status to Usage, join Status to Customers:

Select a.*
from customers a left join usage b on a.id = b.id
                 left join status c on [COLOR=red][b]a[/b][/color].id = c.id

 

[Qik3Coder]

@evaaseow

Remember that the Join syntax specifies how the system links the tables together, RiverGuy suggests that you change the link from B to A. this is so that if there is a missing row in B, you will still get the data for table C.

You can use joins to either expand or contract your result set, based on your needs.

Start with the base table, check your row count.
After you do your Left join, you should have at least that many rows. It may not be as high as you are anticipating if you missing rows, from either B, or C.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[pmegan]

Do you have 500 rows in your Customers table? If not, the only way you'll hit 500 is of there are multiple matching rows in one or both of the other tables.


Out of curiosity, what are you trying to get out of the query?

The results are Customers.* with outer joins to the usage and status tables. The results will always be the same as
SELECT * FROM Customers.

 

[Qik3Coder]

@Pmegan. Not true, he might get dupes if there are multiple ties in the other tables, if there are say two rows in B, with an ID in A.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[bborissov]

If you have 500 records in table [A] you'll get at least 500 records as a result set. You can have MORE if there are duplicates in other tables, but not less.
So you don't have 500 records in table [A] (or you have WHERE clause that filters rest 100)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.