CODEWITHSUNDEEP
mysql
Mohan
Mohan

Reputation: 4829

Query to fetch multiple records from table in same row

I have 2 tables in my database like this :

porfolio

id  | title  | content
----+--------+-------------------
1   |  hello | aksjdhaksd..
2   |  hi    | asdasd..
----+--------+-------------------

portfolio_image

id  | portfolio_id  | image
----+---------------+-------------------
1   |  1            | a.jpg
2   |  1            | b.jpg
3   |  2            | c.png
4   |  2            | d.png
----+---------------+-------------------

I want to fetch data so that i get all data from portfolio table and their respective images from second table in the same row :

I mean something like this :

id  | title | content       | image1 | image2
----+-------+---------------+--------+-------------
1   | hello | aksjdhaksd..  | a.jpg  | b.jpg
2   |  hi   | asdasd..      | c.png  | d.png
----+-------+---------------+------------------------------

Please help Me

Upvotes: 0

Views: 934

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If you have only two images per portfolio, then you can do:

select p.id, p.title, p.content,
       min(pi.image) as image1,
       (case when max(pi.image) <> min(pi.image) then max(pi.iamge) as image2
from portfolio p join
     portfolio_image pi
     on p.id = pi.portfolio_id
group by p.id, p.title, p.content;

If you have multiple images, the easiest approach is to put them into a single column, using group_concat():

select p.id, p.title, p.content, group_concat(pi.image) as images
from portfolio p join
     portfolio_image pi
     on p.id = pi.portfolio_id
group by p.id, p.title, p.content;

If you have multiple images, don't know how many, and want each in a separate column. Then you need a dynamic pivot. I would suggest that you google "mysql dynamic pivot" to find some examples.

Upvotes: 1

Related Questions