SQL INNER JOIN tables with different row names

Question

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 want a UNION--rows from either of 2 tables--returning a row when it is in one OR it is in the other. JOIN returns a row pair when it has a subrow in one AND it has a subrow in the other. Your description of desired rows does not require that a returned row has columns (possibly its entirety) in both tables, but in either. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Jun 08 '19 at 07:34
Why didn't you try this code? Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Jun 08 '19 at 07:35
Possible duplicate of [What is the difference between JOIN and UNION?](https://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union) — philipxy, Jun 08 '19 at 07:40

score 0 · Accepted Answer · answered Jun 08 '19 at 10:26

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:

A 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).
The ? is for a parameter. If you are calling this from an application, use parameters!
You can include additional columns for more information in the result set.

This may not be the exact result format you want. If not, ask another question with sample data and desired results.

SQL INNER JOIN tables with different row names

1 Answers1