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
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|
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;
I suppose you want to associate
base_datawith 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_datawith 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