I have a table in which I store large (big) and medium (medium) size photos:
|----------------|-------------------|
| articulo_id | foto |
|----------------|-------------------|
| 1 | fot1-medium.jpg |
| 1 | fot1-big.jpg |
| 1 | fot2-medium.jpg |
| 1 | fot2-big.jpg |
| 3 | foto-medium.jpg |
| 4 | foto-big.jpg |
| 3 | foto-big.jpg |
| ... | ... |
When displaying photos on the page, I need a large and small photo in the same loop, so I want to get something like this from the table:
|----------------|-------------------|-------------------|
| articulo_id | foto_medium | foto_big |
|----------------|-------------------|-------------------|
| 1 | fot2-medium.jpg | fot2-big.jpg |
| 1 | fot1-medium.jpg | fot1-big.jpg |
I currently have this query:
SELECT a.foto as medium, b.foto as big
FROM photos a
JOIN photos b ON a.articulo_id = b.articulo_id
WHERE 1
AND a.foto LIKE '%medium.jpg'
AND b.foto LIKE '%big.jpg'
AND a.service_id = 1;
Which returns duplicate rows like this:
|----------------|-------------------|-------------------|
| articulo_id | foto_medium | foto_big |
|----------------|-------------------|-------------------|
| 1 | fot2-medium.jpg | fot2-big.jpg |
| 1 | fot2-medium.jpg | fot2-medium.jpg |
| 1 | fot1-medium.jpg | fot1-big.jpg |
| 1 | fot1-medium.jpg | fot1-medium.jpg |
How to pass values from one column to two different columns in the same MySql table without the values not being repeated?
Try using subqueries. Something like this should work for you:
Although what I would do is create a field in the table for each size, so when consulting it is much easier.