MySQL Error to selecting data

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


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:

        base_data as t1
        left join (
                min(photo) as photo
            group by
            ) as t2 on t2.p_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 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 :-) :

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