mysql join 2 table results

Question

I have 3 tables:

Sites: ID, name
Assoc: site_id, assoc_type, assoc_id
Options: ID, name

I would like to do the following query in one

SELECT * FROM sites FOREACH site SELECT * FROM assoc WHERE assoc_type='option' AND site_id = site.ID FOREACH assoc SELECT name FROM options WHERE ID = assoc.ID FOREACH SITE echo name, array(option 1, option2, option3);

is this possible?

Code I am trying to shorten

 $getsites = mysql_query("SELECT * FROM sites")or die(mysql_error()); while($row = mysql_fetch_array($getsites)){ echo $row['name']; $getassoc = mysql_query("SELECT * FROM assoc WHERE type='options' AND site_id = '$row[ID]'")or die(mysql_error()); echo'<ul>'; while($subrow = mysql_fetch_array($getassoc)){ $getoption = mysql_query("SELECT * FROM options WHERE ID = '$subrow[assoc_id]'")or die(mysql_error()); $option = mysql_fetch_assoc($getoption); echo '<li>'.$option['name'].'</li>'; } echo'</ul><br/>'; }

Every site is related to maximum 3 options?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2011-07-08 16:40:15 +00:00
Commented Jul 8, 2011 at 16:40 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 8, 2011 at 16:40

ypercubeᵀᴹ · Accepted Answer · 2011-07-08 21:34:49Z

SELECT s.name , ( SELECT o.name FROM Options AS o JOIN Assoc AS a ON a.assoc_id = o.ID WHERE a.site_id = s.id AND a.assoc_type='option' ORDER BY o.id LIMIT 1 OFFSET 0 ) AS option1 , ( SELECT o.name FROM Options AS o JOIN Assoc AS a ON a.assoc_id = o.ID WHERE a.site_id = s.id AND a.assoc_type='option' ORDER BY o.id LIMIT 1 OFFSET 1 ) AS option2 , ( SELECT o.name FROM Options AS o JOIN Assoc AS a ON a.assoc_id = o.ID WHERE a.site_id = s.id AND a.assoc_type='option' ORDER BY o.id LIMIT 1 OFFSET 2 ) AS option3 FROM Sites AS s

If you want to perform a usual join of the 3 tables, to find all sites and related options, you can use:

SELECT s.name AS site , o.name AS option FROM Sites AS s JOIN Assoc AS a ON a.site_id = s.id JOIN Options AS o ON o.ID = a.assoc_id WHERE a.assoc_type = 'option' ORDER BY s.name , o.name

I have included the code I would have used if I couldnt do it with one query in the original post.
I have simplified the question stackoverflow.com/questions/6627578/mysql-join-2-table-results
Thanks, the second query would return an array with the site details and a sub array for the options am i right?

Collectives™ on Stack Overflow

mysql join 2 table results

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related