Question about using one table to get data from two tables.

[nelsonm]

Hi all,

I'm looking to find a way to create one mysql select statement that uses one table (a workorder table) to get records from two sets of like tables (open workorder: [customer table and billing address table] and closed workorder: [customer table and billing address table]) for display in a single screen grid.

I have a workorder table that contains records that are tagged open and closed via a workorder status field. I currently use two sets of customer and billing address tables that point to the workorder table via the workorder id foreign key “WOID”. The first set of customer and billing address tables hold the live customer and billing address records for open workorders. The data in those tables can change while workorders are “open”. However, when a workorder status “WSID” is changed to “closed”, I copy the live customer and associated billing address record from their respective live tables to the snapshot (archive) customer and billing address tables.

For display purposes, I want to show both open and closed workorder records on the same screen grid that include customer and billing address data. For open workorders, I need to show the associated customer and bill address data from the live tables. For closed workorders, I need to show the associated customer and bill address data from the snapshot tables.

For the life of me I can't seem to figure out how to construct a single mysql statement that can output a result set containing the data I need.

Here's a conceptual example of what I'm trying to do...

SELECT
workorder-fields,
if wo.WSID = 'open' (SELECT customer-fields, billing-address-fields FROM live-customer-table , live-billing-address-table )
else if wo.WSID = 'closed' (SELECT customer-fields, billing-address-fields FROM snapshot-customer-table, snapshot-billing-address-table)
FROM workorder-table AS wo

Any help would be greatly appreciated.

[nelsonm]

Wait...

I think i misread the section in the mysql online manual related to conditions. I think this is the way to construct the statement...

I'll use more descriptive names then i did in the previous post to make it easier to understand.

SELECT
workorder-fields,

CASE
WHEN workorder-table.Status = 'open' THEN
SELECT customer-fields, billing-fields FROM live-customer-table , live-billing-table 
WHERE live-customer-table.customerID = workorder-table.customerID AND live-billing-table.billingID = workorder-table.billingID

WHEN workorder-table.Status = 'closed' THEN
SELECT customer-fields, billing-fields FROM snapshot-customer-table, snapshot-billing-table 
WHERE snapshot-customer-table.workorderID = workorder-table.workorderID AND snapshot-billing-table.workorderID = workorder-table.workorderID
END

FROM workorder-table

Does this look right?

[arms]

Construct as a union statement

SELECTworkorder-fields, customer-fields, billing-fields
FROMworkorder-table
INNER JOINlive-customer-table
ONlive-customer-table.customerID = workorder-table.customerID
INNER JOINlive-billing-table
ONlive-billing-table.billingID = workorder-table.billingID
WHEREworkorder-table.Status = 'open'
UNION ALL
SELECTworkorder-fields, customer-fields, billing-fields
FROMworkorder-table
INNER JOINsnapshot-customer-table
ONsnapshot-customer-table.customerID = workorder-table.customerID
INNER JOINsnapshot-billing-table
ONsnapshot-billing-table.billingID = workorder-table.billingID
WHEREworkorder-table.Status = 'open'
ORDER BY(workorderid)

[nelsonm]

I think that last WHERE should be: "WHERE workorder-table.Status = 'closed'" correct?

Also, just for my info,

Q1. why is your method better than mine solution?
Q2. Wouldn't my solution work?


thanks.

[arms]

yes you are correct the last where should be "WHERE workorder-table.Status = 'closed'" (sorry, copy and paste error)

Your query is horrible, you would be executing a new query for every row of workorder which would create a massive overhead and would probably kill your server if workorder was more than a couple of hundred rows. This uses one query only

You could use your way, I think you would have to have a case statement for each column of live/snapshot data you require (which would compound the problem)

Try and stay away from subqueries in the select part of your statements for execution effiency

[nelsonm]

Thanks for being so blunt - i like that.

So the following two SELECT sub queries is horrible too? (i removed most of the fields to make it easier to read)

$sql = 'SELECT
wo.WOID,
.
.

SELECT SUM(wi.UnitPrice * wi.QTY) FROM '.$crudTable['wo-items-wi'].' AS wi WHERE wi.WOID = wo.WOID) AS SubTotal,
.
.
wo.WSID,
.
.

(SELECT COUNT(*) FROM '.$crudTable['wo-items-wi'].' AS wi WHERE wo.WOID = wi.WOID) AS ChildRecords,

st.TaxRate';

FROM '.$crudTable['workorder-wo'].' AS wo';
INNER JOIN '.$crudTable['user-ur'].' AS ur ON wo.URID = ur.URID';
LEFT JOIN '.$crudTable['member-me'].' AS me ON ur.MEID = me.MEID';
LEFT JOIN '.$crudTable['customer-cu'].' AS cu ON wo.WOID = cu.WOID';
LEFT JOIN '.$crudTable['cu-status-cs'].' AS cs ON cu.CSID = cs.CSID';
LEFT JOIN '.$crudTable['cu-billing-address-ba'].' AS ba ON wo.WOID = ba.WOID';
LEFT JOIN '.$crudTable['state-st'].' AS st ON cu.State = st.StateAbbrev';
LEFT JOIN '.$crudTable['wo-service-wv'].' AS wv ON wo.SVID = wv.SVID';
LEFT JOIN '.$crudTable['wo-lead-source-ls'].' AS ls ON wo.LSID = ls.LSID';
LEFT JOIN '.$crudTable['wo-timeblock-tb'].' AS tb ON wo.TBID = tb.TBID';
LEFT JOIN '.$crudTable['wo-status-ws'].' AS ws ON wo.WSID = ws.WSID';
LEFT JOIN '.$crudTable['franchise-fr'].' AS fr ON wo.FRID = fr.FRID';
WHERE wo.FRID LIKE "'.$_SESSION['FRID'].'"';