digital1stein
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.
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
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.
Have you tried setting it to GROUP BY voices.id ?
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);
FROM voices
LEFT JOIN files pic ON (voices.picture=pic.id)
LEFT JOIN files samp ON (voices.defaultsample=samp
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.
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;
@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);
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!