PHP MYSQL Query Multiple Tables Where the Second Table returns multiple rows

I'm trying to query 2 tables where the first table will return 1 row and the second table will return multiple rows. So basically the first table with return text on a page and the second table will return a list that will go within the page. Both tables have a reference row which is what both tables are queried on. (See Below)

SELECT shop_rigs.*, shop_rigs_images.*, shop_rigs_parts.*
    FROM shop_rigs
    LEFT JOIN shop_rigs_images 
        ON shop_rigs.shoprigs_ref = shop_rigs_images.shoprigsimg_ref 
    LEFT JOIN shop_rigs_parts
        ON shop_rigs.shoprigs_ref = shop_rigs_parts.shoprigsparts_ref 
    WHERE shoprigs_enabled='1' AND shoprigs_ref='$rig_se开发者_StackOverflow社区lect'
    ORDER BY shoprigs_order ASC

Is it better to just do 2 queries?

Thanks,

• dane

I would do this in two queries. The problem isn't efficiency or the size of the respective tables, the problem is that you're create a Cartesian product between shop_rigs_images and shop_rigs_parts.

Meaning that if a given row of shop_rigs has three images and four parts, you'll get back 3x4 = 12 rows for that single shop_rig.

So here's how I'd write it:

SELECT ...
FROM shop_rigs
INNER JOIN shop_rigs_images 
    ON shop_rigs.shoprigs_ref = shop_rigs_images.shoprigsimg_ref 
WHERE shoprigs_enabled='1' AND shoprigs_ref='$rig_select'
ORDER BY shoprigs_order ASC

SELECT ...
FROM shop_rigs
INNER JOIN shop_rigs_parts
    ON shop_rigs.shoprigs_ref = shop_rigs_parts.shoprigsparts_ref 
WHERE shoprigs_enabled='1' AND shoprigs_ref='$rig_select'
ORDER BY shoprigs_order ASC

I left the select-list of columns out, because I agree with @Doug Kress that you should select only the columns you need from a given query, not all columns with *.

If you're pulling a large amount of data from the first table, then it would be better to do two queries.

Also, for efficiency, it would be better to specify each column that you actually need, instead of all columns - that way, less data will be fetched and retrieved.

Joins are usually more efficient than running 2 queries, as long as you are joining on indexes, but then it depends on your data and indexes. You may want to run a "explain SELECT ....." for both options and compare "possible keys" and "rows" from your results.