MySQL Error to selecting data

I have two column one column associated with another...

Table:base_data

id |---name----|-----des
1  | some name1 | The description1
2  | some name2 | The description2

Table: photos

id |---p_id----|-----photo
1  |     1     | img1s.jpg
2  |     1     | img1w.jpg
3  |     2     | img2.jpg
4  |     2     | img14.jpg
5  |     2     | img15.jpg

I want to select all data from table 1(base_data) and one row from associated row from photos: table how can I do that ????

I don't want to select by greatest n per group I want to select all data from the first table and only one row of the second table which matches with the first table row id, just first match not other.

The Result I want...

id |---name----|---des----|---p_id----|---photo----|
1  | some name |the des..1|   1       | img1s.jpg|
2  | some name |the des..2|   2       | img2.jpg|

2 answers

  • answered 2017-06-17 19:46 rd_nielsen

    If you want the alphanumerically lowest photo name, in MySQL you can do this:

    select
        t1.*,
        t2.photo
    from
        base_data as t1
        left join (
            select
                p_id,
                min(photo) as photo
            from
                photos
            group by
                p_id
            ) as t2 on t2.p_id = t1.id;
    

  • answered 2017-06-17 19:46 Stephan Lechner

    I suppose you want to associate base_data with the first photo taken, which should be the one with the lowest photos.id. In MySQL, you could write this as follows: Create an intermediate query which gives - for any p_id - the corresponding record with the lowest id. Then, left join base_data with this intermediate query result. Hope there are not to many typos in it :-) :

    select b.id, p2.photo
    from base_data b left join
       (select p.photo, p.p_id, min(id) from photos p group by p.p_id) p2 on b.id = p2.p_id