Displaying Two Tables 2

[eatwork]

Hi, does anyone know how to display all the rows in a query from two tables.
Eg. I have join_tbl_LoadCommodity_OptionType and join_tbl_DispatchHist_OptionType. They both have a jobId and loadId field.
table 1 has 321 records, and table2 has 16 records
if I do the following, I get 5000+ records
SELECT join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM join_tbl_LoadCommodity_OptionType, join_tbl_DispatchHist_OptionType;

if I add the load table and set conditions for the job and loadId based on each table:
SELECT join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*, join_tbl_DispatchHist_OptionType.jobId, join_tbl_DispatchHist_OptionType.loadId, join_tbl_LoadCommodity_OptionType.jobId, join_tbl_LoadCommodity_OptionType.loadId
FROM (join_tbl_LoadCommodity_OptionType RIGHT JOIN tbl_Load ON (join_tbl_LoadCommodity_OptionType.loadId = tbl_Load.loadId) AND (join_tbl_LoadCommodity_OptionType.jobId = tbl_Load.jobId)) LEFT JOIN join_tbl_DispatchHist_OptionType ON (tbl_Load.loadId = join_tbl_DispatchHist_OptionType.loadId) AND (tbl_Load.jobId = join_tbl_DispatchHist_OptionType.jobId)
WHERE (((join_tbl_DispatchHist_OptionType.jobId) Is Not Null) AND ((join_tbl_DispatchHist_OptionType.loadId) Is Not Null)) OR (((join_tbl_LoadCommodity_OptionType.jobId) Is Not Null) AND ((join_tbl_LoadCommodity_OptionType.loadId) Is Not Null));

I get 335 records, but there should be 337.
There are 2 records that share a job and loadId and the query is placing all of the data into one row instead of splitting them up into two rows. Can anyone help please? thank you

 

[lespaul]

table 1 has 321 records, and table2 has 16 records
if I do the following, I get 5000+ records
SELECT join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM join_tbl_LoadCommodity_OptionType, join_tbl_DispatchHist_OptionType;

that's because by using:
FROM join_tbl_LoadCommodity_OptionType, join_tbl_DispatchHist_OptionType

you are creating a cartesian join which produces a result set of 321*16 = 5136 records

I'm not sure how to fix the missing 2 records though. Can you post some data and expected results? are join_tbl_LoadCommodity_OptionType and join_tbl_DispatchHist_OptionType queries?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

I guess you need an UNION query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eatwork]

Hi lespaul, thank you for your reply,
1) both join_tbl_LoadCommodity_OptionType and join_tbl_DispatchHist_OptionType are both tables
2)its a little hard to show the data because there are a lot of fields, but expected results could be
join_tbl_LoadCommodity_OptionType = tbl1
join_tbl_DispatchHist_OptionType = tbl2

current results:
tbl.jobId tbl1.loadId tbl2.jobId tbl2.loadId
6001 131
6001 131
3064 01
3064 03
6098 02 6098 02
6099 01
6100 01
6102 01
6102 01 6102 01

what I would like:
tbl.jobId tbl1.loadId tbl2.jobId tbl2.loadId
6001 131
6001 131
3064 01
3064 03
6098 02
6098 02
6099 01
6100 01
6102 01
6102 01
6102 01
Any ideas? thank you

 

[eatwork]

Hello PHV,
Thank you for your post, I looked into the union syntax you suggested, but it does not work because the # of columns are different in the two tables. THank you

 

[PHV]

SELECT tbl1.*, tbl2.*
FROM (tbl1 RIGHT JOIN tbl0 ON ValidJoincondition))
LEFT JOIN tbl2 ON InvalidJoincondition)
UNION SELECT tbl1.*, tbl2.*
FROM (tbl1 RIGHT JOIN tbl0 ON InvalidJoincondition))
LEFT JOIN tbl2 ON ValidJoincondition

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eatwork]

Hello PHV,
thanks for your post, the query works, but desired results seem to be further from what I need. I get 335 records, the two records mentioned above seem to be doing the same, they are being combined in one record. maybe I am doing something wrong?
the statement I was able to draw from your example is:

SELECT  join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM (join_tbl_LoadCommodity_OptionType RIGHT JOIN tbl_load ON  join_tbl_LoadCommodity_OptionType.jobId=tbl_load.jobId)
LEFT JOIN join_tbl_DispatchHist_OptionType ON  join_tbl_DispatchHist_OptionType.jobId=tbl_load.jobId
UNION SELECT  join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM (join_tbl_LoadCommodity_OptionType RIGHT JOIN tbl_load ON join_tbl_LoadCommodity_OptionType.jobId=tbl_load.jobId)
LEFT JOIN join_tbl_DispatchHist_OptionType ON join_tbl_DispatchHist_OptionType.jobId=tbl_load.jobId

 

[eatwork]

this code below shows the sql statement that comes closest to the desired result of 337 records:

SELECT join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*, join_tbl_LoadCommodity_OptionType.jobId, join_tbl_LoadCommodity_OptionType.loadId, join_tbl_DispatchHist_OptionType.jobId, join_tbl_DispatchHist_OptionType.loadId
FROM ((join_tbl_LoadCommodity_OptionType RIGHT JOIN tbl_Load ON (join_tbl_LoadCommodity_OptionType.jobId = tbl_Load.jobId) AND (join_tbl_LoadCommodity_OptionType.loadId = tbl_Load.loadId)) LEFT JOIN tbl_LoadHist ON (tbl_Load.jobId = tbl_LoadHist.jobId) AND (tbl_Load.loadId = tbl_LoadHist.loadId)) LEFT JOIN (tbl_Dispatch LEFT JOIN (tbl_DispatchHist LEFT JOIN join_tbl_DispatchHist_OptionType ON (tbl_DispatchHist.loadHistId = join_tbl_DispatchHist_OptionType.loadHistId) AND (tbl_DispatchHist.jobId = join_tbl_DispatchHist_OptionType.jobId) AND (tbl_DispatchHist.loadId = join_tbl_DispatchHist_OptionType.loadId) AND (tbl_DispatchHist.dispatchId = join_tbl_DispatchHist_OptionType.dispatchId) AND (tbl_DispatchHist.dispatchHistId = join_tbl_DispatchHist_OptionType.dispatchHistId)) ON (tbl_Dispatch.jobId = tbl_DispatchHist.jobId) AND (tbl_Dispatch.loadId = tbl_DispatchHist.loadId) AND (tbl_Dispatch.loadHistId = tbl_DispatchHist.loadHistId) AND (tbl_Dispatch.dispatchId = tbl_DispatchHist.dispatchId)) ON (tbl_LoadHist.jobId = tbl_Dispatch.jobId) AND (tbl_LoadHist.loadId = tbl_Dispatch.loadId) AND (tbl_LoadHist.loadHistId = tbl_Dispatch.loadHistId)
WHERE (((join_tbl_LoadCommodity_OptionType.jobId) Is Not Null) AND ((join_tbl_LoadCommodity_OptionType.loadId) Is Not Null)) OR (((join_tbl_DispatchHist_OptionType.jobId) Is Not Null) AND ((join_tbl_DispatchHist_OptionType.loadId) Is Not Null));

 

[PHV]

And what about this ?
SELECT join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM (join_tbl_LoadCommodity_OptionType
RIGHT JOIN tbl_load ON join_tbl_LoadCommodity_OptionType.jobId=tbl_load.jobId)
LEFT JOIN join_tbl_DispatchHist_OptionType ON join_tbl_DispatchHist_OptionType.jobId=[highlight]-[/highlight]tbl_load.jobId
UNION [highlight]ALL[/highlight] SELECT join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM (join_tbl_LoadCommodity_OptionType
RIGHT JOIN tbl_load ON join_tbl_LoadCommodity_OptionType.jobId=[highlight]-[/highlight]tbl_load.jobId)
LEFT JOIN join_tbl_DispatchHist_OptionType ON join_tbl_DispatchHist_OptionType.jobId=tbl_load.jobId


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eatwork]

Hello PHV,
Thank you for your post again, but this post ended up giving me 11000+ records.

 

[PHV]

OOps, remove the highlighted ALL :~/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eatwork]

Thank you PHV,
That code almost does it, just has one extra row with all null values? any ideas? thank you for your patience and expertise. would you be able to explain your logic behind your much simpler sql statement as well? thank you

 

[PHV]

one extra row with all null values
You may try to replace the 2 RIGHT JOIN with INNER JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eatwork]

Hi PHV, the replacement of RIGHT JOIN with the INNER JOIN, does not work, but thats okay, I use another query to modify my results. I was wondering if you know how to use the UNION syntax suggested to join 4 tables. I used the code you provided and modified another union but now it is giving me a column number problem again. Any ideas? thank you

SELECT  join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM (join_tbl_LoadCommodity_OptionType
RIGHT JOIN tbl_load ON  join_tbl_LoadCommodity_OptionType.jobId=tbl_load.jobId)
LEFT JOIN join_tbl_DispatchHist_OptionType ON  join_tbl_DispatchHist_OptionType.jobId=-tbl_load.jobId
UNION 
SELECT  join_tbl_LoadCommodity_OptionType.*, join_tbl_DispatchHist_OptionType.*
FROM (join_tbl_LoadCommodity_OptionType
RIGHT JOIN tbl_load ON join_tbl_LoadCommodity_OptionType.jobId=-tbl_load.jobId)
LEFT JOIN join_tbl_DispatchHist_OptionType ON join_tbl_DispatchHist_OptionType.jobId=tbl_load.jobId
UNION
SELECT tbl_loadHist.*, tbl_dispatchHist.*
FROM (tbl_LoadHist 
RIGHT JOIN tbl_Dispatch ON (tbl_LoadHist.jobId = tbl_Dispatch.jobId) AND (tbl_LoadHist.loadId = tbl_Dispatch.loadId) AND (tbl_LoadHist.loadHistId = tbl_Dispatch.loadHistId)) 
LEFT JOIN tbl_DispatchHist ON (tbl_Dispatch.jobId = -tbl_DispatchHist.jobId) AND (tbl_Dispatch.loadId = tbl_DispatchHist.loadId) AND (tbl_Dispatch.loadHistId = tbl_DispatchHist.loadHistId) AND (tbl_Dispatch.dispatchId = tbl_DispatchHist.dispatchId)
WHERE (((tbl_LoadHist.loadHistIsExtra)=True))
UNION SELECT tbl_loadHist.*, tbl_dispatchHist.*
FROM (tbl_loadHist
RIGHT JOIN tbl_Dispatch ON (tbl_LoadHist.jobId = -tbl_Dispatch.jobId) AND (tbl_LoadHist.loadId = tbl_Dispatch.loadId) AND (tbl_LoadHist.loadHistId = tbl_Dispatch.loadHistId)) 
LEFT JOIN tbl_DispatchHist ON (tbl_Dispatch.jobId = tbl_DispatchHist.jobId) AND (tbl_Dispatch.loadId = tbl_DispatchHist.loadId) AND (tbl_Dispatch.loadHistId = tbl_DispatchHist.loadHistId) AND (tbl_Dispatch.dispatchId = tbl_DispatchHist.dispatchId)
WHERE (((tbl_DispatchHist.dispatchHistIsExtra)=True));

 

[lespaul]

when you use a union query there have to be the same number of fields in the select statement. If Table1 and Table2 have the same number of fields then

SELECT * FROM Table1
UNION
SELECT * FROM Table2

will work, if however, Table1 has two more fields, then you will have to list each field individually and add "place holders" in the select of Table2

SELECT Field1, Field2, Field3, Field4, Field5 FROM Table1
UNION
SELECT Field1, Field2, Field3, "", "" FROM Table2

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[eatwork]

Hi lespaul,
thank you for your response, that is something I will keep in mind when I create my other union queries.