0

Hi we have a table that looks like this (call it table A). It contains both rows if parent and children data. I need to get rows like below containing both the item_id and the parent_item_id if it exists (otherwise the item_id). Now I need to join on the

item_id (if parent) + parent_item_id as new_id and item_id (if no parent) + item_id as new_id

I have used the IFNULL function like this IFNULL(parent_item_id, item_id) as new_id and then later I use new_id to join on the same table A again against item_id to get some final values

To make a long story short, my query is

select item_id, IFNULL(parent_item_id, item_id) AS new_id 
from table A as A1
INNER JOIN table A as A2 ON A1.new_id = A2.item_id 
where A1.type = simple

The problem is that I don't seem to be allowed to join on the same table again using my newly created column new_id

QUESTION: how do I rephrase this query so my join is allowed? or is there a smarter way to go altogether to get this done?

Data example

|--item_id --|--parent_item_id--|---type---|--price--|
   123          124               simple     0.00
   124          null              config     9.00
   125          null              simple     8.00

I need to join this table A on itself on a variabel column

Error is

Error in query (1054): Unknown column 'new_id' in 'on clause'

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
snh_nl
  • 2,877
  • 6
  • 32
  • 62

2 Answers2

1

put the calculation direct into the INNER JOIN like this :

select item_id, IFNULL(parent_item_id, item_id) AS new_id 
from table A as A1
INNER JOIN table A as A2 ON IFNULL(A1.parent_item_id, A1.item_id) = A2.item_id 
where A1.type = simple
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
0
select * from A as A2 
JOIN
(
select item_id, type, IFNULL(parent_item_id, item_id)  AS new_id from  A
)A1
ON (A1.new_id = A2.item_id)
where A1.type = 'simple'
sumit
  • 15,003
  • 12
  • 69
  • 110