14

Suppose I have two tables in one to many relationship.

And, I want to select columns from each main record along with the first record from a related table.

I tried some ways but it just doesn't go...

Here I end up with this SQL fiddle:

http://sqlfiddle.com/#!2/39fdb/3

The problem there is that it just cannot reference a.ID from a subselect.

This does not work, of course, but it's just all I could think of

select a.*,b.* from event a left join 
(select * from event_pictures where a.ID=article limit 1)
b on a.ID=b.article;

Any ideas on how to fix it?

Anonymous
  • 4,692
  • 8
  • 61
  • 91

4 Answers4

30

No, you can't reference a.ID in a subselect that is joined to a. You can do the following, but you better supply an ordering. Otherwise, there is no "first" row. A (more or less) random row from table b will be selected:

select a.*, b.* 
from event a 
  left join event_pictures b
    on b.PK =                        --- the PRIMARY KEY
       ( select bb.PK                --- of event_pictures 
         from event_pictures bb 
         where a.ID = bb.article
         ORDER BY bb.something 
         limit 1
       ) ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • I'll do an order of course, this is just a simplified version :) Yet even random record with an image might be ok... Let us see – Anonymous Aug 09 '12 at 14:21
2

If you don't care which image gets returned for an article, you can select the MIN or MAX image grouped by article (rather than doing a LIMIT 1) in your subquery

SQL Fiddle

Bort
  • 7,398
  • 3
  • 33
  • 48
  • I'm not an expert but it looks like group by will work slower and loop through each subset. I guess I would better stick with limit, and order them in the natural way by an auto increment ID field (which is kinda default isn't it?) – Anonymous Aug 09 '12 at 14:26
  • Agreed, I'd go with ypercube's more flexible answer. – Bort Aug 09 '12 at 14:30
2

You could use min or max as suggested already:

select
    e.*,
    (
        select min(ep.img)
        from event_pictures as ep
        where ep2.article = e.article
    ) as img
from
    event as e

If you want img based on highest ID:

select
    e.*,
    (
        select ep2.img
        from event_pictures as ep2
        where ep2.ID = last_ep.last_ID
    ) as img
from
    event as e inner join -- could be a left join if necessary
    (
        select ep.article, max(ep.ID) as last_ID
        from event_pictures as ep
        group by ep.article
    ) as last_ep
        on last_ep.article = e.ID

Neither approach requires the use of limit.

shawnt00
  • 16,443
  • 3
  • 17
  • 22
0

Here is one way to do it:

select e.*, ep.*
from (select e.*,
             (select article
              from event_pictures ep
              where ep.article = e.id
              order by rand()
              limit 1
             ) as eparticle
      from event e
     ) e left join
     event_pictures ep
     on e.eparticle = ep.article

The subquery finds one randome "article". The information for this is then joined in.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786