mySQL multiple results into one result row

Question

I have a table that stores image URLs by product id and am trying to write a query that will pull all image results onto one line. The table looks something like: ``` photoFlag photoName objectID ``` where photoName holds the relative path to the image and photoFlag identifies the type of image (full, thumb, etc). I started off by writing a quick query to get two images (with different flags) to pull up. This is what I have: ``` select t2.pic, t4.pic, t2.id from (select p2.photoName as pic, p2.objectID as id from ds_photos as p2 where p2.photoFlag = 2) as t2, (select p4.photoName as pic, p4.objectID as id from ds_photos as p4 where p4.photoFlag = 4) as t4 where t2.id=t4.id ``` which seems to be a correct query but when I execute via phpMyAdmin it never returns a result and shows up indefinitely (well, at least for about 40 min) in the "SHOW PROCESS" list. Is there something that I have wrong that I'm not seeing in here that is causing an endless loop? Ideally I would like grab only pi…

Accepted Answer

Use: `GROUP_CONCAT`:

```
SELECT id, GROUP_CONCAT(photoName ) As pic 
FROM ds_photos 
WHERE (photoFlag = 2 or photoFlag = 4) and id = ?;
GROUP BY id
```

replace the `?` with the ID you want.

mySQL multiple results into one result row

Solution

Problem