Select Using Multiple Joins 1

[Halliarse]

Good Afternoon

I'm having an issue getting my head around a select statement using multiple joins! I've built my query in stages and tested as I've gone and it works fine until the third / fourth join is created so I need some advice!

I have and POP_Header table, an POP_Detail table, a PL_Accounts table and a STK_Stock table

The POP_Header contains a field SUCODE which links to the POH_Account field on PL_Accounts.

The POP_Header contains a field POH_Reference which links to the POD_Reference field on POP_Detail.

The POP_Detail contains a field POD_Stock_Code which links to the STKCODE field on STK_Stock.

The select should return one line per record on the POP_Detail table

Please can someone advise what the Select statement should look like?

Many thanks

Steve

 

[TamarGranor]

Looks like you want something like this:

[pre]
FROM POP_Header
JOIN PL_Accounts
ON POP_Header.SUCODE = PL_Accounts.POH_Account
JOIN POP_Detail
ON POP_Header.POH_Reference = POP_Detail.POD_Reference
JOIN STK_Stock
ON POP_Detail.POD_Stock_Code = STK_Stock.STKCODE
[/pre]

Whether that will give you exactly one record for every row in POP_Detail depends on your data. Most importantly, if any POP_Detail rows don't have a match in STK_Stock, they'll be omitted from the result. If that's possible and you want to see them anyway, change the last JOIN to LEFT JOIN.

Similarly, if there are any records in POP_Header with no match in PL_Accounts, you want see any of the matching POP_Detail lines for that POP_Header. Same solution.

Tamar

 

[Andrzejek]

You may find this info helpful about Visual Representation of SQL Joins


---- Andy

There is a great need for a sarcasm font.

 

[Halliarse]

Thanks for the response Tamar, unfortunately this yielded far too many records!

I know my target record set is only 355 records, however this has returned over 800k!

 

[Olaf Doschke]

Well, you can't get what you want, if there are multiplicators in the form of multiple details for a head record.

Let's say you want to total order price, which is the sum of all order items (details) of an order (head), then you can't simply

Select * from orders join orderitems on orderitems.orderid=orders.id

Well, it doesn't error, but it gives you as many records as there are order items and that even of all orders, as I didn't filter [tt]WHERE order.id=42[/tt] (for example).

It's totally unclear from just knowing your table names and matching columns, what exactly you need to get the wanted result.

What doesn't exist is a type of query only joining the first match of detail tables. So just the number of header records determines the result size.
What can be done in case of order is a simple thing:

Select orders.id, SUM(orderitems.qty*orderitems.price) as OrderTotal FROM orders JOIN orderitems on orderitems.orderid=orders.id [highlight #FCE94F]GROUP BY orders.id[/highlight]

But you already see you cannot get details about ordered products AND the total prize at the same time, you can aggregate or not aggregate, but not both. Also, order items will not join all to the right and create more and more columns, the order item columns are added to the order columns, but each order item causes a new row repeating order data in the left part of the resulting table before I group by.

If you want an overall result giving you some aggregated information for each POP_Header, then you likely want to GROUP BY some identifier in POP_Header, but what else you want from other tables remains your secret. We would need to know much more information about your data and it's meanings and what you want to retrieve and aggregate or not aggregate.

There are simple joins, which only fill in a detail, those are the joins in the direction from detail to header, for example, an order is from a customer. That customer is higher in the data hierarchy, therefore starting from an order joining up to customers you only get one customer, that can extend the information of the order by customer name and address and payment info and you'd never expect an order to join two customers, simply because orders.customerid can only store one customer id as reference. But see the other direction again and one customer doesn't need to have many orders, but that's at least possible. So not just the join type (inner, outer (left, right)) but mostly the hierarchy levels of data determine whether a join is a multiplicator of rows or not.

Maybe that helps figure things out, but your table names POP_Header and POP_Detail alone point out you'll get more result records than POP_Header has if the average number of details is more than 1.

Bye, Olaf.