Link to home
Start Free TrialLog in
Avatar of digital1stein
digital1steinFlag for Belgium

asked on

Mysql left join to search 2 rows in the same join table

I'm trying to do the following:

Select all rows from table 1 and make a join on table 2 to search for two rows.
I can already achieve something like this:

SELECT * FROM voices LEFT JOIN files ON (voices.picture=files.id OR voices.defaultsample=files.id);

Each record found in voices can have up to 2 rows in the files table, one for the picture and one for the defaultsample.
But the statement above returns me two rows as result. I would to have the query return one row containing all queried fields, not two.

Or is there no other way but to split it up into 2 queries? One query that joins on picture and another that joins on defaultsample?

Any advice is mostly appreciated.
Avatar of stilliard
stilliard
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you tried setting it to  GROUP BY voices.id  ?
Avatar of cyberkiwi
SELECT voices.*, pic.*, samp.*  # tune this list
FROM voices
LEFT JOIN files pic ON (voices.picture=pic.id)
LEFT JOIN files samp ON (voices.defaultsample=samp.id);
Avatar of digital1stein

ASKER

Thanks, stilliard, the GROUP BY would have been a solution but only returns results if there are 2 pictures.

Cyberkiwi, also thanks, but you assume that there is one main table with two joined tables, while my example was one main table with a condition for 2 resulting records on 1 join table.

I think I'm simply to going to split my query up in 2 queries.
try this.
SELECT *, 
       (SELECT filename 
        FROM   files 
        WHERE  voices.picture = files.id 
        LIMIT  1), 
       (SELECT filename 
        FROM   files 
        WHERE  voices.defaultsample = files.id 
        LIMIT  1) 
FROM   voices;

Open in new window

@digital1stein

Please read http:#35355254 carefully
There is only one "files" table.  It is aliased twice,

once as "pic" joined by picture=pic.id (pic here is the alias for files)
and once as "samp"(le) joined by defaultsample=samp.id (samp = files, again)

SELECT voices.*, pic.*, samp.*  # tune this list
FROM voices
LEFT JOIN files pic ON (voices.picture=pic.id)
LEFT JOIN files samp ON (voices.defaultsample=samp.id);
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No offense at all, cyberkiwi ;-)
I think I just gave up too quickly.
Your solution as well as Sharath_123's work fine, although I prefer cyberkiwi's solution as the columns are easier to distinguish.
Thanks for persisting!