StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCombining two select queries
text
Body
copied!<p>A the moment I am using two queries one is called initially, and the second is called during a loop through the results of the first. I want to combine both queries, but have been unable to so far. The tables the queries are pulling from are:</p> <pre><code>+--------------+ +--------------+ +--------------------------+ | table_1 | | table_2 | | table_3 | +----+---------+ +----+---------+ +----+----------+----------+ | id | name | | id | name | | id | tbl1_id | tbl2_id | +----+---------+ +----+---------+ +----+----------+----------+ | 1 | tbl1_1 | | 1 | tbl2_1 | | id | 1 | 1 | | 2 | tbl1_2 | | 2 | tbl2_2 | | id | 3 | 2 | | 3 | tbl1_3 | | 3 | tbl2_3 | | id | 3 | 3 | | 4 | tbl1_4 | +----+---------+ +----+----------+----------+ +----+---------+ </code></pre> <p>There is a many to many relationship between <code>table_1</code> and <code>table_2</code> in <code>table_3</code>. I have been using to separate queries so far. One query to return all the contents of <code>table_1</code> and a second query to return the values of <code>table_2</code> that are connected to <code>table_1</code> through <code>table_3</code>. However, I would like to do away with the loop and lessen the amount of queries being sent to the server. I have tried using a <code>JOIN</code>:</p> <pre><code>SELECT table_1.id, table_1.name, table_2.id, table_2.name FROM table_3 LEFT JOIN table_1 ON (table_3.tbl1_id = table_1.id) LEFT JOIN table_1 ON (table_2.tbl2_id = table_2.id) </code></pre> <p>This returned pretty much want I wanted except it only returned the values that were in <code>table_3</code> leaving out some of the values from <code>table_1</code>. I have tried using subqueries:</p> <pre><code>SELECT table_1.id, table_1.name, (SELECT table_2.id FROM table_2, table_3 WHERE table_2.id = table_3.tbl2_id AND table_1.id = table_3.tbl1_id) AS tbl_2_id, (SELECT table_2.name FROM table_2, table_3 WHERE table_2.id = table_3.tbl2_id AND table_1.id = table_3.tbl1_id) AS tbl_2_name FROM table_1 </code></pre> <p>This gave an <code>ERROR 1242</code>. So far, I have not been able get anything to work. The result I am looking for is similar to this.</p> <pre><code>+---------------+---------------+---------------+---------------+ |table_1.id |table_1.name |table_2.id |table_2.name | +---------------+---------------+---------------+---------------+ | 1 | tbl1_1 | 1 | tbl2_1 | | 2 | tbl1_2 | | | | 3 | tbl1_3 | 2 | tbl2_2 | | 3 | tbl1_3 | 3 | tbl2_3 | | 4 | tbl1_4 | | | +---------------+---------------+---------------+---------------+ </code></pre> <p>Also, I would like to be able to order the results on both <code>table_1.name</code> and <code>table_2.name</code>. If anyone has a suggestion please let me know.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload