Hamza Javed Hamza Javed - 3 months ago 8
SQL Question

How to get Multiple images against ID (FK) from two tables

I have two tables, i-e

SELECT `HID`, `HName`, `HDescription`, `ParentID`, `Latitude`, `Longitude`, `CatagoryType`, `Date` FROM `hazara_tbl`


and

SELECT HPhoto_ID, HID, Image, Date FROM hazara_tbl_photos


i want to get all images against HID. I tried this, it returns me two rows, but i want single row with multiple image.

SELECT h.HID, h.HName, hp.Image from hazara_tbl h join hazara_tbl_photos hp on hp.HID = h.HID WHERE hp.HID = 49

Query result

i want something like this

Query 2

Answer

You need aggregation. In MySQL, the typical function to use is group_concat():

select h.HID, h.HName, group_concat(hp.Image) as images
from hazara_tbl h join
     hazara_tbl_photos hp
     on hp.HID = h.HID 
where h.HID = 49
group by h.HID, h.HName;

EDIT:

The above assumed that hp.image is a native SQL type. If it is a BLOB, then it is better for you to combine the results in the application layer. MySQL does not readily support arrays of BLOBs.

Comments