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.
digital1steinAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
digital1stein,

Have you tried the query I gave you with your tables?
If the table and field names you gave are correct, you should be able to just cut and paste it.

If you had any trouble with it, you can revert here and I can explain again. This is run of the mill stuff that is pretty common.  To state that "seems to be impossible" is an insult.

The only trick is that because "pic" and "samp" are aliases to the same table, that you end up with 2 sets of the same column names, e.g. "id" appears twice, once from pic.id and once from samp.id.  In PHP, you would have been able to access this by array position, but far better to alias the columns differently, e.g.

SELECT voices.*,
  pic.id as pic_id, pic.name as pic_name, # add the rest of the fields you need
  samp.id as samp_id, samp.name as samp_name    # etc
FROM voices
LEFT JOIN files pic ON (voices.picture=pic.id)
LEFT JOIN files samp ON (voices.defaultsample=samp.id);
0
 
stilliardCommented:
Have you tried setting it to  GROUP BY voices.id  ?
0
 
cyberkiwiCommented:
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);
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
digital1steinAuthor Commented:
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.
0
 
SharathData EngineerCommented:
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

0
 
cyberkiwiCommented:
@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);
0
 
digital1steinAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.