Let’s say I have a primary table
record, and it has 2 related tables
CREATE TABLE record (id int primary key, name varchar(20)) CREATE TABLE fields (record_id int, name varchar(20), val int) CREATE TABLE comments (record_id int, who varchar(20), comment text)
I would like to run one query that fetches a set of records AND fetches all the related fields for that record, AND fetches all the comments related to that record.
If I do left joins to ensure I get the records, I would use:
select * from record left join fields on (fields.record_id = record.id) left join comments on (comments.record_id = record.id) order by record.id
The problem is, I get back n * m rows for each record, where n is the number of fields, and m is the number of comments. I want to get back n + m rows (what makes sense is that the fields columns are all null while returning the comments, and the comments columns are all null while returning the fields). Is there a way to make this work aside from inserting a dummy comment and dummy field to join with? I would very much prefer not to have to perform an extra query for each record.
I suppose this is not mysql specific, but that’s what I’m using for my application.