CODEWITHSUNDEEP
mysql
Michal M
Michal M

Reputation: 9480

How to get data from two related tables?

I am trying to get data from two related tables in a single query. Tables look like this:

Table 1:

--------------------------
| ID        | username   |
--------------------------
| 1         | user1      |
| 2         | user2      |
| 3         | user3      |
| 4         | user4      |
--------------------------

Table 2:

----------------------------------------------------
| ID        | user_id    | key        | value      |
----------------------------------------------------
| 1         | 1          | key1       | value1     |
| 2         | 1          | key2       | value2     |
| 3         | 2          | key2       | value3     |
| 4         | 3          | key3       | value4     |
| 5         | 4          | key1       | value5     |
| 6         | 4          | key3       | value5     |
----------------------------------------------------

I want to get a result including all rows from Table 1 and key-value pairs from table 2 where key = key1. If the key is not present in Table 1 for a given row from table 1, fill it with NULL. Expected result example:

----------------------------------------------------
| user_id   | username   | key        | value      |
----------------------------------------------------
| 1         | user1      | key1       | value1     |
| 2         | user2      | NULL       | NULL       |
| 3         | user3      | NULL       | NULL       |
| 4         | user4      | key1       | value5     |
----------------------------------------------------

Alternatively - all the rows from Table 1 which don't contain key-value pair for key=key1 in Table 2.

I tried standard SELECT from 2 tables and SELECT from Table 1 with JOIN from Table 2, but all I get is all rows from Table 1 which contain key-value pair with key=key1 in Table 2 and this is exactly the opposite of what I want.

Any suggestions?

Upvotes: 3

Views: 1582

Answers (3)

Genic
Genic

Reputation: 193

Try this:

SELECT t1.username,t2.user_id,t2.key,t2.value FROM table1 as t1 left join table2 as t2 on(t1.id = t2.user_id) and t2.key = 'key'

Upvotes: 1

Ryan Briscall
Ryan Briscall

Reputation: 121

Query might look something like this:

SELECT t1.username, t2.key, t2.value FROM table1 AS t1
LEFT OUTER JOIN table2 AS t2 ON t1.id = t2.user_id;

For example, if there is no user_id 3 in table2, then the query (above) will produce: 

+----------+------+--------+
| username | key  | value  |
+----------+------+--------+
| user1    | key1 | value1 |
| user1    | key2 | value2 |
| user2    | key2 | value3 |
| user3    | NULL | NULL   |
| user4    | key1 | value5 |
| user4    | key3 | value5 |
+----------+------+--------+

Upvotes: -1

DRapp
DRapp

Reputation: 48139

select
      t1.id,
      t1.username,
      t2.key,
      t2.value
   from
      table1 t1
         left join table2 t2
            on t1.id = t2.user_id
           and t2.key = 'key1'

For your "Alternative" to get all that DO NOT have, just add a where clause..

where t2.user_id IS NULL

Upvotes: 8

Related Questions