How to select from table with JOIN instead 3 queries

Question

My table structures are :

table1 :

id      |    title    |    category    | body
--------+-------------+----------------+---------------
1       | blah blah   | 2              | blah blah blah
2       | blah blah   | 3              | blah blah blah
3       | blah blah   | 2              | blah blah blah
4       | blah blah   | 1              | blah blah blah

table2 :

id      |   cid    |   type    |     link
--------+----------+-----------+--------------------
1       | 1        | c1        | http://........
1       | 2        | c1        | http://........
1       | 3        | c1        | http://........
1       | 4        | c1        | http://........
1       | 2        | c2        | http://........
1       | 3        | c2        | http://........
1       | 1        | c2        | http://........

Now I use this code to select from this tables in 3 separate queries :

$query = mysql_select("SELECT * FROM table1");
while ($result = mysql_fetch_array($query)) {
   $c1 = mysql_fetch_array(mysql_query("SELECT link FROM table2 WHERE cid=".$result['id']." AND type = 'c1' ORDER BY id DESC LIMIT 1"));
   $c2 = mysql_fetch_array(mysql_query("SELECT link FROM table2 WHERE cid=".$result['category']." AND type = 'c2' ORDER BY id DESC LIMIT 1"));

  ....
}

Is it possible to write these queries in one query for mysql ?

Second query : `WHERE cid=".$result['id']`. Third query : `WHERE cid=".$result['category']`. Are you sure ? — Theox, Feb 27 '14 at 09:08
Yes, this is my problem, I have 2 types records in one table — MajAfy, Feb 27 '14 at 09:10

Ulrich Thomas Gabor · Answer 1 · 2014-02-27T10:07:31.280

0

SELECT * FROM table1 a
LEFT JOIN table2 b ON b.cid = a.id AND type = 'c1'
LEFT JOIN table2 c ON b.cid = a.category AND type = 'c2'
ORDER BY a.title ASC, b.id DESC, c.id DESC

maybe expanded with an ORDER BY.

You could also try INNER JOIN instead, if you only want to get those rows of table1, which have the requested rows in table2.

edited Feb 27 '14 at 10:07

answered Feb 27 '14 at 09:09

Ulrich Thomas Gabor

6,584
4
27
41

You are not selecting the `link` from table2. – Theox Feb 27 '14 at 09:13
Thanks, I use `ORDER` because may be we have more than one record in table2 so I ordered by `id` to select latest record, how can I do this in this query? – MajAfy Feb 27 '14 at 09:14
@Theox I use `SELECT *` which selects just everything. – Ulrich Thomas Gabor Feb 27 '14 at 09:15
@MajAfy You have to decide how to sort table1 too, because you must sort first this, than that. So what is your preferred order of table 1? – Ulrich Thomas Gabor Feb 27 '14 at 09:16
@MajAfy Use something like `ORDER BY a.id, b.id DESC, c.id DESC` – Barmar Feb 27 '14 at 09:18
@GhostGambler I want order table1 by title : `ORDER BY title ASC` but from table2 we should select just one record like: `ORDER BY id DESC LIMIT 1` – MajAfy Feb 27 '14 at 09:18
1

See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group. – Barmar Feb 27 '14 at 09:22
@GhostGambler your query work for me but How can I use `ORDER` ? – MajAfy Feb 27 '14 at 09:57
I updated my answer, but see that this is not the same as the two queries with `LIMIT 1`! – Ulrich Thomas Gabor Feb 27 '14 at 10:07

How to select from table with JOIN instead 3 queries

1 Answers1