Need help with multiple table join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with multiple table join

Author

Topic

djsjbjcj
Starting Member

4 Posts

Posted - 2007-09-22 : 18:12:50

I'm trying to join 5 tables to get a result set, but I'm not getting the results I want.

I have an inventory table with product ID's (iv00101.itemnmbr)
I have a sales header table with sales order information (SO Number, date, type, etc..)
I have a sales detail table with line item details (SO Number, itemnmbr, qty, etc...)
I have a purchasing header table with PO information (PO Number, date, type, etc...)
I have a purchasing detail table with PO details (PO Number, itemnmbr, qty, etc..)

I want to produce a table that shows all itemnmbr, soldQty, soldDate, purchaseQty, purchaseDate

If I take the PO data out, I get what I want. (See example below).

--------------------------------------------------------------------------------------------

select
rtrim(i.itmshnam) as style,
case isnumeric(i.uscatvls_1)
when 1 then cast(i.uscatvls_1 as decimal(3,1))
else 0
end as Size,
i.uscatvls_2 as Width,
sum(isnull(cast (case sh.soptype
when '3' then isnull(sd.quantity,0)
when '4' then isnull(sd.quantity,0) * -1
end as int),0)) as Sold,
convert(varchar(6),sh.docdate,112) as SoldDate

from
iv00101 i
left join
SOP30300 sd
on i.itemnmbr = sd.itemnmbr
left join
SOP30200 sh
on sd.SOPNUMBE = sh.SOPNUMBE
where
i.itmshnam = 'MG1275B'
and i.uscatvls_2 = '2E'

group by
i.itmshnam,
i.uscatvls_1,
i.uscatvls_2,
sh.docdate

order by
style,
width,
size,
sh.docdate

Produces this result. Sales by product, grouped by month.

style Size Width Sold SoldDate
--------------- ---------------------------------------
MG1275B 7.0 2E 0 NULL
MG1275B 7.5 2E 1 200602
MG1275B 7.5 2E 1 200610
MG1275B 7.5 2E 1 200704
MG1275B 7.5 2E 1 200708
....

I would like to join the PO table and get this:
style Size Width Sold SoldDate Odered OrderDate
--------------- --------------------------------------- -------- ----------------
MG1275B 7.0 2E 0 NULL 0 NULL
MG1275B 7.5 2E 1 200602 1 200602
MG1275B 7.5 2E 1 NULL 1 200604
MG1275B 7.5 2E 1 200610 4 200610
MG1275B 7.5 2E 1 200704 0 NULL
MG1275B 7.5 2E 1 200708 0 NULL
....

I've tried to join the other two tables many ways. I though this would work, but I get duplicates.
left join
POP10110 pod
on i.itemnmbr = pod.itemnmbr
left join
POP10100 poh
on poh.ponumber = pod.ponumber

Can someone provide a solution? I'm thinking I might consolidate the table, and make a qty, date, and type field and then I could probably union the tables and get out what I want. (Ultimately the table will be pivoted to display what I want.)

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:06:02

A FULL OUTER JOIN might get what you want - you will get a row from both Sales and Purchase orders if they have same Product / Date, but rows with only date for either Sales or Purchase when there is no matching data from the other table.

Kristen

djsjbjcj
Starting Member

4 Posts

Posted - 2007-09-23 : 02:12:50

quote:
Originally posted by Kristen

A FULL OUTER JOIN might get what you want - you will get a row from both Sales and Purchase orders if they have same Product / Date, but rows with only date for either Sales or Purchase when there is no matching data from the other table.

Kristen

So is it a LEFT join to the Item table, and a full outer join between the sales and PO table?

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:52:02

Yes, that would be my first experiement!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources