CODEWITHSUNDEEP
mysqlsql
wuqn yqow
wuqn yqow

Reputation: 85

Join tables even if don't exist on other table

I have 2 tables and want to join but if row is not existing in other table results are not shown.

+-------------------+   +-------------------+   
|      TABLE 1|     |   |     Table 2       |
+-------------------+   +-------------------+   
|deviceID|  xxx     |   |deviceID|    yyy   |   
|-------------------|   |-------------------|   
| 1    |  Service 1 |   | 1    |   Asset 1  | 
| 2    |  Service 2 |   | 2    |   Asset 2  |   
| 3    |  Service 3 |   |      |   
+-------------------+   +-------------------+

SQL:

SELECT L.xxx, A.yyy from TABLE1 L JOIN TABLE2 A on L.deviceID=A.deviceID

The results are shown are deviceID:1,2 and 3 is not showing, I want to show it even if it doesn't exist on table2

I have tried LEFT JOIN and RIGHT JOIN but is not working

Upvotes: 1

Views: 2112

Answers (3)

Ahmed
Ahmed

Reputation: 924

Have you tried INNER JOIN?

SELECT L.xxx, A.yyy
FROM TABLE1 L INNER JOIN
     TABLE2 A 
     ON L.deviceID = A.deviceID;

This should work because INNER JOIN takes the common values in both tables; Inner Join Diagram

OUTPUT

| DeviceID |    xxx    |    yyy   |
|:--------:|:---------:|:--------:|
|     1    | Service 1 | Assest 1 |
|     2    | Service 2 | Assest 2 |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Did you try a simple left join?

SELECT L.xxx, A.yyy
FROM TABLE1 L LEFT JOIN
     TABLE2 A 
     ON L.deviceID = A.deviceID;

This should do what you want.

Upvotes: 1

nbk
nbk

Reputation: 49375

As this example shows, LEFT JOIN works just fine. For you.

So that doesn't seems to be the problem. cam you provide an dbfoddle example whre it doesn't work

Schema (MySQL v8.0)

CREATE TABLE Table2 (
  `deviceID` INTEGER,
  `yyy` VARCHAR(7)
);

INSERT INTO Table2
  (`deviceID`, `yyy`)
VALUES
  ('1', 'Asset 1'),
  ('2', 'Asset 2');

CREATE TABLE Table1 (
  `deviceID` INTEGER,
  `xxx` VARCHAR(9)
);

INSERT INTO Table1
  (`deviceID`, `xxx`)
VALUES
  ('1', 'Service 1'),
  ('2', 'Service 2'),
  ('3', 'Service 3');

Query #1

SELECT
t1.deviceID
,t1.xxx
,t2.yyy
FROM
Table1 t1 LEFT JOIN
Table2 t2 ON t1.deviceID = t2.deviceID;

| deviceID | xxx       | yyy     |
| -------- | --------- | ------- |
| 1        | Service 1 | Asset 1 |
| 2        | Service 2 | Asset 2 |
| 3        | Service 3 |         |

Query #2

SELECT

t1.xxx
,t2.yyy
FROM
Table1 t1 LEFT JOIN
Table2 t2 ON t1.deviceID = t2.deviceID;

| xxx       | yyy     |
| --------- | ------- |
| Service 1 | Asset 1 |
| Service 2 | Asset 2 |
| Service 3 |         |

View on DB Fiddle

Upvotes: 0

Related Questions