outer joins

[andreas57]

is there a workaround for outer joins in mysql? i've got to tables (a,b). what i want to do is select all of a and only the first match from b.

thanks

andreas owen
aowen@swissonline.ch

 

[BNPMike]

MySQL has outer joins.

You seem to be asking a different question. You want an inner join but only present one matching B record per A record. For us to solve this you need to explain the term 'first match'. In English how would you tell SQL which record is the first?

 

[andreas57]

i just want it to select 1 entry from the b-table. i dont care wich one!

regardes

andreas owen
aowen@swissonline.ch

 

[BNPMike]

That means you might have different and unpredictable data each time you run the query or the data in B will be the same no matter which record you pick. The latter suggests some duplication of data in B. I know just for the moment that's not answering your question but maybe there's another problem that, if we solve it, will also solve this issue.

 

[andreas57]

no that's not the problem. i've done it deliberatley this way for sveral reasons and it doesn't matter if i always get the same back or not!!!

andreas owen
aowen@swissonline.ch

 

[BNPMike]

I was hoping you were going to get me off the hook there.

Just trying to think how to do this...

 

[TonyGroves]

How about something like the following:

You have 2 tables:[tt]
CREATE TABLE parents (parentid INT, parentname CHAR(20),PRIMARY KEY (parentid))
CREATE TABLE kids (parentid INT, kidid INT, kidname CHAR(20), PRIMARY KEY (parentid,kidid))[/tt]

Do the following:
(1) Create a temporary table with the same field structure as kids:[tt]
CREATE TABLE temptbl AS SELECT * FROM kids LIMIT 1
TRUNCATE temptbl[/tt]
(2) Create a primary key on temptbl using the field parentid:[tt]
ALTER TABLE temptbl ADD PRIMARY KEY (parentid)[/tt]
(3) Load data from kids into temptbl so that only one kid of each parent is loaded:[tt]
INSERT IGNORE temptbl SELECT * FROM kids[/tt]
(4) Join all parents with one of their kids, if any:[tt]
SELECT * FROM parents LEFT JOIN temptbl USING (parentid)[/tt]
(5) Finish up:[tt]
DROP TABLE temptbl[/tt]

 

[r937]

do the left outer join from the "one" table to the "many" table

then do a left outer to a second copy of the "many" table

i.e. join each row to every other row of the same group

do a GROUP BY on all the fields you're selecting from the first copy of the "many" table

then use HAVING to pull the row you want based on it matching a particualr row from the second copy

like this:

select a.foo
     , a.bar
     , b1.qux
     , b1.fap
  from a
left outer
  join b as b1
    on a.id = b1.aid
left outer
  join b as b2     
    on a.id = b2.aid                
group
    by a.foo
     , a.bar
     , b1.qux
     , b1.fap
having b1.fap = min(b2.fap)

you'll want to test this to make sure it still works when returning results for an unmatched row from the a table

the above is equivalent to what database optimizers do "under the covers" anyway, whenever executing a query with a correlated subquery, such as this:

select a.foo
     , a.bar
     , b.qux
     , b.fap
  from a
left outer
  join b 
    on a.id = b.aid
 where b.fap 
     = ( select min(fap)      
           from b
          where aid = a.id )

rudy

http://r937.com/

SQL Consulting