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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
cyberkiwiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.