Multi table select query 1

[gaperry]

I have 6 tables that I need to join. Each table has an identifier that exists in all other tables. I can join the first 3 tables using DISTINCT and I get the results I am looking for. But when I try to add any of the remaining three tables I get only a few records. I could create 2 separate queries for 3 tables / 3 tables and then run an update query to update the main or first three tables from the second set of tables. I suspect that I need subquery of some kind and don't know much about them but it seems that I should be able to join all six tables via a subquery ?

Here are the first three tables joined.

SELECT DISTINCT dbo.tbl_CMD_ORDERITM.OIORDR, dbo.tbl_CMD_ORDHDR.OHORDR, dbo.tbl_CMD_BILLMAST.BMNAME, dbo.tbl_CMD_ORDERITM.OIPART, dbo.tbl_CMD_ORDERITM.OITQTY, dbo.tbl_CMD_ORDERITM.OIPRCE, dbo.tbl_CMD_ORDERITM.OIPLIN
FROM dbo.tbl_CMD_ORDERITM INNER JOIN dbo.tbl_CMD_ORDHDR ON dbo.tbl_CMD_ORDERITM.OIORDR = dbo.tbl_CMD_ORDHDR.OHORDR INNER JOIN dbo.tbl_CMD_BILLMAST ON dbo.tbl_CMD_ORDERITM.OICUST = dbo.tbl_CMD_BILLMAST.BMCST



Here are the second three tables.

SELECT DISTINCT dbo.tbl_CMD_ORDERSCH.SCSHYR AS Expr4, dbo.tbl_CMD_ORDERSCH.SCSHMN, dbo.tbl_CMD_ORDERSCH.SCSHDY,
dbo.tbl_CMD_ORDERPRF.PSPFYR, dbo.tbl_CMD_ORDERPRF.PSPFMN, dbo.tbl_CMD_ORDERPRF.PSPFDY, dbo.tbl_CMD_ORDERCRQ.ORORDR, dbo.tbl_CMD_ORDERSCH.SCORDR, dbo.tbl_CMD_ORDERPRF.PSORDR, dbo.tbl_CMD_ORDERCRQ.ORDAY,dbo.tbl_CMD_ORDERCRQ.ORMNTH, dbo.tbl_CMD_ORDERCRQ.ORYEAR

FROM dbo.tbl_CMD_ORDERCRQ INNER JOIN dbo.tbl_CMD_ORDERSCH ON dbo.tbl_CMD_ORDERCRQ.ORORDR = bo.tbl_CMD_ORDERSCH.SCORDR INNER JOIN dbo.tbl_CMD_ORDERPRF ON dbo.tbl_CMD_ORDERSCH.SCORDR = dbo.tbl_CMD_ORDERPRF.PSORDR


Is this something that can be done with a select and subquery or should I be looking elsewhere ?

Thanks

 

[bperry]

Hi there,
You may be quite aware of this already, but I'll just mention this business about joins in case it helps a little bit:

When doing Inner Joins among multiple tables like this, and you get fewer records than you expect, it's almost always because some of the rows don't match in all the tables. i.e. Inner Joins only return records that match across every table involved in the join(s). So, could it be in this case that when you join to those other three tables, some rows that do match across the first three tables don't match across all six tables, and therefore fall out of the result set?

If so, then perhaps what you want is a combination of Inner Joins and Left Outer Joins. It is okay to mix these, as long as you put the tables that MUST match early in the syntax, and the optional matches later. Something like this, perhaps:

Select blah, blah
From MustTable1 t1
Inner Join MustTable2 t2
On t1.Column = t2.Column
Inner Join MustTable3 t3
On t3.Column = t3.Column
Left Join OptionalTable4 t4
On t3.Column = t4.Column
Left Join OptionalTable5 t5
On t4.Column = t5.Column
Left Join OptionalTable6 t6
On t5.Column = t6.Column

So, this example would return rows that match across t1, t2, and t3, even if those rows don't happen to exist in either t4, t5, t6.

I'm probably explaining too much here; sorry about that, but wasn't quite sure how far to go with it. Anyway, do you think that might be at least part of your problem? i.e. doing Inner Joins (required) on some tables when perhaps they should be Left Joins (optional)?

 

[gaperry]

bperry:

Thanks for the help here. My world is a better place today. Your write-up was most helpful. As it turns out, I should have been looking closer at the data. This data is coming from an AS400 and they have the date fields split out with a field for year then month then day. The reason the data did not look right is because they were putting a 66 in a month field to be used as a flag for something so I was seeing screwy data. The write-up you did made me look closer at the real problem instead of analysing my code to death. Now I just have to concantenate the 3 fields and make a real date field out of them. Thanks again.

George