Mysql query with multiple left joins from tables that are subsets of table "from" does not work as expected
Create a database for incoming calls with data for each call type. I have defined a database with a "records" table, a "sales" table and an "account_receivable" table. The sales and account_receivable tables are essentially subsets of the records table. Meaning: The "records" table has a corresponding row for each row of both the "sales" and "account_receivable" tables (exclusive).
My "records" table has values:
id (BIGINT)
timestamp (TIMESTAMP)
rep_id (INT)
notes (VARCHAR)
The Sales table has the following meanings:
local_record_id (BIGINT)
record_id (BIGINT)
amount (VARCHAR)
bottles_sold (VARCHAR)
record_type (VARCHAR) default 'sales'
The "account_receivable" table has the following meanings:
local_record_id (BIGINT)
record_id (BIGINT)
amount (VARCHAR)
bottles_sold (VARCHAR)
record_type (VARCHAR) default 'ar'
I am trying to pull all records in the entire database with applicable data for each record. For this I thought that a LEFT JOIN starting with the "records" table would work fine, but for some reason it doesn't. This is my request:
SELECT *
FROM $table_name_records
LEFT JOIN $table_name_sales ON ($table_name_records.id = $table_name_sales.record_id)
LEFT JOIN $table_name_ar ON ($table_name_records.id = $table_name_ar.record_id)
This returns a result set in which data from subset tables with similar column names is lost.
Sample output (which corresponds to a row / record in the "sales" table in my database):
Array (
[id] => 1
[timestamp] => 2014-12-17 13:11:07
[rep_id] => 37
[notes] => Some notes for you.
[local_record_id] =>
[record_id] =>
[amount] =>
[bottles_sold] =>
[record_type] =>
)
I have verified that there is data in every column of each of the tables in a subset of my database, so I don’t understand why the values are returned as empty. If I only ask for one left connection:
SELECT *
FROM $table_name_records
LEFT JOIN $table_name_sales ON ($table_name_records.id = $table_name_sales.record_id)
I get what I expect to see:
Array (
[id] => 1
[timestamp] => 2014-12-17 13:11:07
[rep_id] => 37
[notes] => Some notes for you.
[local_record_id] => 14
[record_id] => 1
[amount] => 45.45
[bottles_sold] => Multiple
[record_type] => sales
)
Works the same as I expect if I leave the join with only the "account" table:
SELECT *
FROM $table_name_records
LEFT JOIN $table_name_ar ON ($table_name_records.id = $table_name_ar.record_id)
Array (
[id] => 1
[timestamp] => 2014-12-17 13:12:16
[rep_id] => 37
[notes] => Some notes.
[local_record_id] => 6
[record_id] => 2
[amount] => 50.89
[bottles_sold] => Single
[record_type] => ar
)
I looked at awesome graphical SQL-attached doc MySQL joins and LEFT JOIN looks exactly like this, I want: every thing from table A (records) with data from table B (sales) and then also from second table B (accounts_receivable) attached to the corresponding lines (according to the ON instruction).
What am I doing wrong that this is not responding as I expect?
My queries, where I only want sales records, are usually structured in a different direction. Thus:
SELECT *
FROM $table_name_sales
LEFT JOIN $table_name_records ON ($table_name_records.id = $table_name_sales.record_id)
So, I tried to structure my request from this direction and then just join them via:
SELECT *
FROM $table_name_sales
LEFT JOIN $table_name_records ON ($table_name_records.id = $table_name_sales.record_id)
UNION
SELECT *
FROM $table_name_ar
LEFT JOIN $table_name_records ON ($table_name_records.id = $table_name_ar.record_id)
This query returns exactly what I expect:
Array (
[id] => 1
[timestamp] => 2014-12-17 13:11:07
[rep_id] => 37
[notes] => Some notes for you.
[local_record_id] => 14
[record_id] => 1
[amount] => 45.45
[bottles_sold] => Multiple
[record_type] => sales
)
But it seems to me that structuring a query in this way may become expensive in the future when I have many records and more tables (corresponding to different types of incoming calls - for example: request for information, problem with order, etc.). I don't have much experience with MySQL.
source to share
It looks like your tables sales
and accounts_receivable
have parallel structures, and the rows in each of those tables are related to your table records
, but not to each other.
@MichaelBerkowski pointed out the dangers of trying to interpret the result set from SELECT *
.
It seems to me that you want the following query.
SELECT r.id AS record_id, r.timestamp, r.rep_id, r.notes AS record_notes,
d.record_type, d.amount, d.bottles_sold
FROM records AS r
LEFT JOIN (
SELECT record_id, record_type, amount, bottles_sold FROM sales
UNION ALL
SELECT record_id, record_type, amount, bottles_sold FROM accounts_receivable
) AS d ON d.record_id = r.id
ORDER BY r.rep_id, r.id, r.timestamp, d.record_type DESC
This will represent each rep action in order, alternating between the "sales" and "ar" entries.
If you separately join the sales and accounts tables, you get an unwanted combinatorial explosion. Doing UNION ALL prevents this from happening.
Please note that you can use one physical table for your sales and data.
source to share