CODEWITHSUNDEEP
mysqlsqldatabase
Hanon
Hanon

Reputation: 3927

Select a cloumn from given condition which is not the column name

I have a table which have the following structure

Table1
itemName, codeA, codeB, codeC, codeD

Table2
purchaseDate, shopCode, itemCode

Table 2 itemCode is a code corresponding to the shop code

For example, I have following items:

Table1
Item A, A1, B1, C1, D1
Item B, A2, B2, C2, D2

Table2 
2012-03-27 11:00:00, 1, A1
2012-03-27 11:00:00, 2, B2
2012-03-27 13:00:00, 4, D1

In table 2, shopCode (1-4) is equal to Table 1 (codeA-codeD), How can I select table2 records together with the item name?

Upvotes: 1

Views: 124

Answers (2)

Phil
Phil

Reputation: 164760

SELECT t2.*, t1.itemName
from Table2 t2
INNER JOIN Table1 t1 ON
  (t2.shopCode = 1 AND t2.itemCode = t1.codeA) OR
  (t2.shopCode = 2 AND t2.itemCode = t1.codeB) OR
  (t2.shopCode = 3 AND t2.itemCode = t1.codeC) OR
  (t2.shopCode = 4 AND t2.itemCode = t1.codeD);

Demo here - http://sqlfiddle.com/#!2/773a4/2

Upvotes: 3

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

SELECT
    t1.itemName,
    t2.purchaseDate,
    t2.shopCode,
    t2.itemCode
FROM
    table1 t1 JOIN
    table2 t2 ON t2.itemCode IN (t1.codeA, t1.codeB, t1.codeC, t1.codeD)

Upvotes: 3

Related Questions