MySQL returns 1 image for each product
This is very similar to Limit the results of a concatenated table to one row , but I am struggling to get it to work exactly the way I need it ...
The table structure is very similar:
CREATE TABLE tblProducts (
intProductID int(11) NULL AUTO_INCREMENT,
strProductName varchar(255) NULL,
PRIMARY KEY (intProductID)
);
CREATE TABLE tblProductImages (
intImageID int(11) NULL AUTO_INCREMENT,
intProductID int(11) NULL,
strImageName varchar(255) NULL,
intOrder int(11) NULL,
PRIMARY KEY (intImageID)
);
Fill in the tables as follows:
INSERT INTO tblProducts (strProductName)
VALUES
('Product #1'), ('Product #2'), ('Product #3');
INSERT INTO tblProductImages (intProductID, strImageName, intOrder)
VALUES
(1, 'image_for_1.jpg', 1),
(2, '1st_image_for_2.jpg', 1),
(2, '2nd_image_for_2.jpg', 2);
I want to do a SELECT that returns all 3 products with matching images. Similar to:
intProductID, strProductName, strImageName
1, Product #1, image_for_1.jpg
2, Product #2, 1st_image_for_2.jpg
3, Product #3, NULL
A simple solution:
SELECT intProductID, strProductName,
(SELECT strImageName
FROM tblProductImages
WHERE tblProductImages.intProductID = tblProducts.intProductID
ORDER BY intOrder
LIMIT 1)
FROM tblProducts
But it fails if I want to add and return another field from tblProductImages (like AltText)
I have installed the script at http://sqlfiddle.com/#!2/883c5/1
thank
source to share
This one will work but is pretty ugly
select p.intProductId, p.strProductName, pi.strImageName
from tblProducts p
inner join tblProductImages pi on pi.intProductID = p.intProductId
inner JOIN (select min(intOrder) minOrder, intProductID
FROM tblProductImages
GROUP BY intProductID) s
on s.intProductID = p.intProductID and s.minOrder = pi.intOrder
union
select p.intProductId, p.strProductName, pi.strImageName
from tblProducts p
left join tblProductImages pi on pi.intProductID = p.intProductId
where pi.intProductID is null;
alternative:
select p.intProductId, p.strProductName, pi.strImageName
from tblProducts p
left join tblProductImages pi on pi.intProductID = p.intProductId
where pi.intProductId is null or pi.IntOrder = (select min(intOrder)
from tblProductImages
where intProductId = pi.intProductId);
source to share
You need to use aggregation (in particular GROUP BY
). You want the GROUP BY
name of the product and use LEFT JOIN
so that the image doesn't need to exist.
SELECT strProductName, strImageName
FROM tblProducts LEFT JOIN tblProductImages USING (intProductID)
GROUP BY strProductName
Please note that the selected image in this case is random (although it will usually be fine intImageID
). This assumes that the actual image captured is not important as long as it is associated with the product in question.
source to share
For this exact output, you may need to modify the insert statement for the tblProductImages table as:
INSERT INTO tblProductImages (intProductID, strImageName, intOrder) VALUES
(1, 'image_for_1.jpg', 1), (2, '1st_image_for_2.jpg', 1), (3, '2nd_image_for_3.jpg', 2);
And use the below Select statement:
SELECT tP.intProductID, tp.strProductName, tPI.strImageName
FROM tblProducts AS tP
INNER JOIN tblProductImages AS tPI ON tP.intProductID = tPI.intProductID;
source to share