CODEWITHSUNDEEP
mysqlsql
Neo Mosaid
Neo Mosaid

Reputation: 419

mysql: How to select from two tables

I have these two tables with same key id, but one of them has more records than the other, so an inner join does not work. the result of my query shows only the common records of the two tables :

   t1:
   ---------------------
   id, data
   ----------------------
   1,aaa
   2,bbb
   4,ccc


   t2: 
   -------------------
   id
   -------------------
   1
   2
   3
   4
   5

   join result :
   -------------------
   1,aaa
   2,bbb
   3,null
   4,ccc
   5,null

this query does not do the job :

$query=" SELECT t2.id, t1.data FROM table1 t1
         INNER JOIN table2 t2
         ON t1.id = t2.id
         ORDER BY t2.id     " ;

Upvotes: 0

Views: 345

Answers (3)

Hemang A
Hemang A

Reputation: 1012

Please try this.

$query=" SELECT t2.id, t1.data FROM table1 t1
         RIGHT JOIN table2 t2
         ON t1.id = t2.id
         ORDER BY t2.id" ;

Upvotes: 4

Iurii Tkachenko
Iurii Tkachenko

Reputation: 3199

Well, the query that you have provided works as expected for INNER JOIN operation.

    SELECT t2.id, t1.data 
      FROM table1 t1
INNER JOIN table2 t2
        ON t1.id = t2.id
  ORDER BY t2.id

Will produce:

| id | data |
| 1  | aaa  |
| 2  | bbb  |
| 4  | ccc  |

If you do want to see all the values from the second table you need to make LEFT JOIN operation on the table2 table.

    SELECT t2.id, t1.data 
      FROM table2 t2
 LEFT JOIN table1 t1
        ON t1.id = t2.id

Will produce:

| id | data   |
| 1  | aaa    |
| 2  | bbb    |
| 3  | (null) |
| 4  | ccc    |
| 5  | (null) |

Upvotes: 1

Vivek
Vivek

Reputation: 803

mysql> create table t1(id int,data varchar(20));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into t1 values(1,'aaa'),(2,'bbb'),(4,'ccc');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table t2(id int);
Query OK, 0 rows affected (0.36 sec)

mysql> insert into t2 values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select t2.id,t1.data from t2 left join t1 on (t2.id = t1.id);
+------+------+
| id   | data |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    4 | ccc  |
|    3 | NULL |
|    5 | NULL |
+------+------+
5 rows in set (0.00 sec)

mysql>

Upvotes: 2

Related Questions