Thank you for taking the time to read this, it is probably a very basic question. Most search queries I did seemed a bit more in depth to the INNER JOIN operator.
Basically my question is this: I have a shipping and receiving table with dates on when the item was either shipped or received. In the shipping table (tbl_shipping) the date row is labeled as trans_out_date and for the receiving table (tbl_receiving) the date row is labeled as trans_in_date.
I can view transactions set on either table from a user entered form but I want to populate a table with information pulled from both tables where the criteria meets. ie. If the receiving table has 10 transactions done in April and 5 in June and the shipping table has 15 transactions in April and 10 in June... when the user wants to see all transactions in June, it will populate the 15 transactions that occurred in June.
As of right now, I can pull only from 1 table with
SELECT *
FROM tbl_shipping
WHERE trans_out_date >= ‘from_date’
AND trans_out_date <= ‘to_date’
Would this be the appropriate syntax for what I am looking to achieve?
SELECT *
FROM tbl_shipping
INNER JOIN tbl_receiving ON tbl_shipping.trans_out_date = tbl_receiving.trans_in_date
WHERE
tbl_shipping.trans_out_date >= ‘from_date’
AND tbl_shipping.trans_out_date <= ‘to_date’
Thank you again in advance for reading this.
You appear to want union all rather than a join:
SELECT s.item, s.trans_out_date as dte, 'shipped' as which
FROM tbl_shipping S
WHERE s.trans_out_date >= ? AND
s.trans_out_date <= ?
UNION ALL
SELECT r.item, NULL, r.trans_in_date as dte, 'received'
FROM tbl_receiving r
WHERE r.trans_out_date >= ? AND
r.trans_out_date <= ?
ORDER BY dte;
Notes:
JOIN can cause problems due to data that goes missing (because dates don't line up) or data that gets duplicated (because there are multiple dates).? is for a parameter. If you are calling this from an application, use parameters!This may not be the exact result format you want. If not, ask another question with sample data and desired results.