0

I have two tables in my database that have no relation between. The first table have a description of old_item. The second table have a description of an update of those old_item.

For example,

Table 1

mysql> SELECT
         coil_no
       FROM coil
       WHERE coil.coil_no = "DNA07X2B419011701A";
  Empty set (0.00 sec)

Table 2

mysql>SELECT
         coil_no
      FROM stock_taking_hanwa
         WHERE stock_taking_hanwa.coil_no = "DNA07X2B419011701A";
   +--------------------+
   |     coil           |
   +--------------------+
   | DNA07X2B419011701A |
   +--------------------+
1 row in set (0.00 sec)

If you can see, coil_no is the key. I use inner join like this :

SELECT
    c.coil_no as old_item,
    sth.coil_no as newest_item
 FROM coil c
 INNER JOIN stock_taking_hanwa sth
 ON sth.coil_no = c.coil_no

 WHERE c.coil_no = "DNA07X2B419011701A" OR sth.coil_no =  "DNA07X2B419011701A"

But it gives me Empty set (0.00 sec).

I need the format like this :

   +--------------------+--------------------+
   |     old_item       |      new_item      |  
   +--------------------+--------------------+
   |                    | DNA07X2B419011701A |
   +--------------------+--------------------+

Note : Sometimes in another case, its vice versa.

   +--------------------+--------------------+
   |     old_item       |      new_item      |  
   +--------------------+--------------------+
   |        "Some item" |                    |
   +--------------------+--------------------+
Fadly Dzil
  • 2,154
  • 3
  • 34
  • 85

2 Answers2

1

INNER JOIN means that you need a row that contain a record from table old_item and one from new_item. If no item exists in one of them no record will be returned.

in mysql you don't have FULL JOINS, so you can not pick any value in that way...

you can use a UNION ALL connecting two queries (one with LEFT JOIN and the other with RIGHT JOIN) but it's not really performant on large tables...

probably the best to do is making two separate queries and elaborate the results after that, but it depends on the back-end

Roberto Bisello
  • 1,235
  • 10
  • 18
0

That's because you use inner join, use two Joins and Union instead:

(SELECT
    c.coil_no as old_item,
    sth.coil_no as newest_item
 FROM coil c
 left JOIN stock_taking_hanwa sth
 ON sth.coil_no = c.coil_no    
 WHERE c.coil_no = "DNA07X2B419011701A" OR sth.coil_no =  "DNA07X2B419011701A" )
 UNION ALL
 (SELECT
    d.coil_no as old_item,
    s.coil_no as newest_item
 FROM  stock_taking_hanwa d
 left JOIN coil  s
 ON s.coil_no = d.coil_no    
 WHERE d.coil_no = "DNA07X2B419011701A" OR s.coil_no =  "DNA07X2B419011701A")
nacho
  • 5,280
  • 2
  • 25
  • 34