asked on

MySQL Server 5.0.37, left join syntax

I have two tables as shown in Section A of code details

I need to return a result set that looks like Section B of code details where sh_date=1218420600

The closest I've been able to get is the query in Section C, which returns the result in Section D.

///////////////
// SECTION A //
///////////////
TABLE COMPANY
sc_id
1
2
3
 
TABLE HISTORY
sh_id  sh_company  sh_value  sh_date
1      1           10        1218420600
2      2           12        1218420600
3      1           8         1234567990
4      2           4         1234567990
5      3           3         1234567990
6      1           9         1234568090
7      2           12        1234568090
8      3           6         1234568090
 
///////////////
// SECTION B //
///////////////
sh_company   sh_value
1            10
2            12
3            0
 
///////////////
// SECTION C //
///////////////
select sh_company, sh_value
from history left join company
on history.sh_company = company.sc_id
where sh_date=1218420600
 
 
///////////////
// SECTION D //
///////////////
sh_company  sh_value
1  	    10
2 	    12

Open in new window

Terry Woods

Hmmm... I think this is what you want:

select sh_company, sh_value
from company left join history
on history.sh_company = company.sc_id
where sh_date=1218420600 or sh_date is null

Open in new window

Terry Woods

Actually, you may need to make that distinct:

select distinct sh_company, sh_value
from company left join history
on history.sh_company = company.sc_id
where sh_date=1218420600 or sh_date is null

Open in new window

techport13

ASKER

That returns the same two rows in opposite order.

techport13

ASKER

No dice on option 2 either, returns the same data as option 1.

Terry Woods

This should do it:

select distinct sc_id, sh_value
from company left join history
on history.sh_company = company.sc_id
where sh_date=1218420600 or sh_date is null

Open in new window

techport13

ASKER

Terry's suggestion only brings back 2 rows too. It's still not showing me that I have another company that doesn't have matching data in the history table for the specified date.

Terry Woods

Are you sure you ran the right query? I ran an equivalent query on my MySQL db (Server version: 5.0.18) and got an equivalent of your desired result.

SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

techport13

ASKER

Terry, I just ran it again to double check, still no difference.

Angel:
Your query as is returns all rows in the stock history table.

ASKER CERTIFIED SOLUTION

Terry Woods

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Terry Woods

I just created those tables with the data you've provided, and my last query gave the right result.

techport13

ASKER

That did it. Thanks a bunch!

Guy Hengel [angelIII / a3]

oups... I forgot indeed to reverse the table "order" :/

as compensation, I will post this, although I never use it myself:

select sh_company, coalesce(sh_value,0) sh_value
from history right join company
on history.sh_company = company.sc_id AND sh_date=1218420600

Open in new window