[RESOLVED] Selecting data from multiple tables and joining into one result set

larsr

Hi all,

having trouble selecting from multiple tables and getting unique multiple values from them combined.

I have three tables, call them "Products", "Users" and "Files":

#1 Products:
Fields: ID (key), make, model, price, seller (reference to "Users" ID), photo1 (reference to "Files" ID), photo2 (-"-), photo3 (-"-)

#2 Users:
Fields: ID (key), username, fullname, email, telephone, address, city, zip

#3 Files:
Fields: ID (key), productID (reference to "Products" ID), filename, mimetype, width, height

I'm trying to select data from the "Users" and "Files" tables which would match with one unique row from the "Products" table. One Product can have up to three product-related photos so I would like to match one product with possibly up to those three available files. I've truncated the query a bit to display on the relevant fields.

My query is as follows:

SELECT  m.id AS mid,
        p.make,
        p.model,
        p.photo1,
        p.photo2,
        p.photo3,
        u.id AS uid,
        u.username,
        f.id AS fid,
        f.productid,
        f.filename,
FROM    products AS p
        users AS u
        files AS f
WHERE   m.seller = u.id
AND     f.productid = m.id
AND     m.id=1234;

This query, given that the product 1234 has three files, returns three rows which contains all the unique files related to the product, but all other columns are identical - ie. I'm getting the same product three times over.

+-----+-------+-------+--------+--------+--------+--------+-----+----------+-----+-------------------+-----------+
| mid | make  | model | photo1 | photo2 | photo3 | seller | uid | username | fid | filename          | productid |
+-----+-------+-------+--------+--------+--------+--------+-----+----------+-----+-------------------+-----------+
| 244 | mfct1 | chair |    125 |    126 |    127 |     12 |  43 | user123  | 125 | 244-43_photo1.jpg |       244 |
| 244 | mfct2 | chair |    125 |    126 |    127 |     12 |  43 | user123  | 126 | 244-43_photo2.jpg |       244 |
| 244 | mfct2 | chair |    125 |    126 |    127 |     12 |  43 | user123  | 127 | 244-43_photo3.jpg |       244 |
+-----+-------+-------+--------+--------+--------+--------+-----+----------+-----+-------------------+-----------+

I'm not that proficient in MySQL - can I get a result set with one row containing the product but three filenames (probably need to be aliased?). Can I do it with a JOIN or UNION select ? Ie I would like to get something which has the columns "filename1", "filename2" and "filename3" which all would be related to one unique product.

Any pointers would be more than welcome! Thanks!

Krik

LEFT JOIN will do the trick

SELECT *
FROM `Products`AS P
LEFT JOIN User AS U ON U.id = P.seller
LEFT JOIN Files AS F1 ON F1.id = P.photo1
LEFT JOIN Files AS F2 ON F2.id = P.photo2
LEFT JOIN Files AS F3 ON F3.id = P.photo3
WHERE P.id = '244';

Hopefully that gets you started

larsr

Thanks - that's about what I want, but I'm still bewildered as I'm getting results in one row, but all filename columns are still titled "filename" so I can't separate them into unique columns, ie. "filename1", "filename2" and so on??

Krik

For myself I have never had to do this but there is three ways to go about it.

The MySQL way

SELECT *, F1.filename AS file1, F2.filename AS file2, F3.filename AS file3,
FROM `Products`AS P
LEFT JOIN User AS U ON U.id = P.seller
LEFT JOIN Files AS F1 ON F1.id = P.photo1
LEFT JOIN Files AS F2 ON F2.id = P.photo2
LEFT JOIN Files AS F3 ON F3.id = P.photo3
WHERE P.id = '244';

This actually adds three columns to the query results. I am not entirely sure that is the best way, but it will work. May be someone else can pipe in on that. I would think this the best of the three as you don't actually have to list every column.

The second way would be to do about the same as you had in your first post listing each column, in SELECT, just changing the name of those three like I did above

The third way is via PHP

while ($row = mysql_fetch_array($result)) {
    list($pid, $make, $model, $price, $seller, $photo1, $photo2, $photo3, $uid, $username, $fullname, $email, $telephone, $address, $city, zip, $fid, $productid1, $filename1, $mimetype1, $width1, $height1, $fid2, $productid2, $filename2, $mimetype2, $width2, $height2, $fid3, $productid3, $filename3, $mimetype3, $width3, $height2 ) = $row;
    // use the results
}

Here you just use "list" to rename all the values in the $row array. I would think this the most cumber some. But it would work.

larsr

Thank you Krik!

Your first suggestion did the trick - I was so puzzled I didn't come to think that I could have selected the filenames by aliasing them 😛 blush...

Thanks again! Cheers!