CODEWITHSUNDEEP
mysql
CST RAIZE
CST RAIZE

Reputation: 428

how to combine or merge two resultsets in mysql?

I have two resultsets A and B

The table A looks like this

+------+---------+------------+
| a_id | item no | total sold |
+------+---------+------------+
|    1 |     101 |         23 |
|    2 |     102 |         34 |
|    4 |     405 |         54 |
|    5 |     506 |         65 |
|    6 |     104 |         23 |
+------+---------+------------+

The table B looks like this

+------+---------+----------+
| b_id | item no | location |
+------+---------+----------+
|    1 |     101 | A1       |
|    2 |     102 | A2       |
|    3 |     103 | A3       |
|    4 |     104 | A4       |
+------+---------+----------+

I want to achieve the output as follows

+------+---------+------------+----------+
| a_id | item no | total sold | location |
+------+---------+------------+----------+
|    1 |     101 |         23 | A1       |
|    2 |     102 |         34 | A2       |
|    4 |     405 |         54 | NULL     |
|    5 |     506 |         65 | NULL     |
|    6 |     104 |         23 | A4       |
+------+---------+------------+----------+

I want to append the column 'LOCATION' to table A and display the location value for each item no which is present in table B. If the ITEM NO in table A does not have a location value, then the LOCATION value has to be NULL (i.e EMPTY).

Since I am a beginner, I don't know how to achieve it. I tried using UNION but I failed to write a proper query

Upvotes: 1

Views: 33

Answers (2)

Nir Levy
Nir Levy

Reputation: 12953

You should use LEFT JOIN (left cause you want to get nulls when the record is not found in the second table)

SELECT a.a_id, a.itemno, a.totalsold, b.location
  FROM tableA AS a LEFT JOIN tableB AS b ON
  a.itemno = b.itemno

Upvotes: 1

JNevill
JNevill

Reputation: 50034

What you are looking for is a Join. Specifically a Left Outer Join so you get all of the results from your Left table (TableA) and only those results from your right table (TableB) that match.

When specifying a join, you use the ON clause to tell the DB which fields are related between the tables:

SELECT
    a_id,
    a.itemno,
    a.totalsold,
    b.location
FROM 
    tableA
    LEFT OUTER JOIN tableB ON
        tableA.itemno = tableB.itemno

Upvotes: 1

Related Questions