mysql – fetch related rows from multiple tables without all combinations

Let’s say I have a primary table record, and it has 2 related tables fields and comments.

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.

Answer

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

    SELECT * 
    FROM record
    LEFT JOIN fields ON (fields.record_id = record.id) /* maybe INNER JOIN ? */
    LEFT JOIN comments ON (1=0)
UNION ALL
    SELECT * 
    FROM record
    LEFT JOIN fields ON (1=0)
    LEFT JOIN comments ON (comments.record_id = record.id) /* maybe INNER JOIN ? */
-- ORDER BY record.id