CODEWITHSUNDEEP
mysqlsql
L0val
L0val

Reputation: 47

How to add data from one table into a 2nd table but only in a row that is a match

I have 2 tables. One Table is called "Location" and a 2nd table is called "items" they look like this.

        Location                           Items
----------------------------------------------------------
Bin_Number  | Contains |||  Item_Number    | Location
----------------------------------------------------------
A1          |          |||  12345          | A2
A2          |          |||  12346          | A4
A3          |          |||  12347          | A3
A4          |          |||

How would I write a query that would display all bins and also have the item number next to the bin if it contains one?

I would like an output like this.

        Location         
-----------------------
Bin_Number  | Contains 
-----------------------
A1          |          
A2          | 12345         
A3          | 12347         
A4          | 12346

Upvotes: 1

Views: 49

Answers (2)

etch_45
etch_45

Reputation: 792

Based on your question and how the columns appear to be presented, I gather that this may be the query that might be usable to start off with.

Assumptions:

  • Location table contains a single column named 'Bin_Number'
  • Items table contains two columns: 'Item_Number' and 'Location' 
select
   t1.bin_number,
   t2.item_number
   from location t1
   join items t2
   on t2.location = t1.bin_number
   where t2.item_number is not null

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use left join

select bin_number,item_number as contains
   from location left join items on location = bin_number

Upvotes: 1

Related Questions