0

I have five tables and when I try to list all items of a buy, if the item has size it should show the code of size of the product, i.e:

# tb_product
product_id
product_name
product_cod

# tb_size
size_id
size_name

# tb_product_size
product_id
size_id
product_size_cod

# tb_buy
buy_id
~

# tb_buy_item
product_id
size_id
buy_id

Here is the query:

select b.product_name,b.product_cod,c.size_name from tb_buy_item a
inner join tb_product b using(product_id)
left join tb_size c using(size_id)
where buy_id=x

The problem is when the item has size and how to show the code of this size.

I tried to do something like:

select b.product_name,b.product_cod,c.size_name from tb_buy_item a
inner join tb_product b using(product_id)
left join(tb_size c inner join tb_product_size d on c.size_id=d.size_id and d.product_id=b.product_id) a.size_id=c.size_id
where buy_id=x

but that shows this error:

unknown column 'b.product_id'

halfer
  • 19,471
  • 17
  • 87
  • 173
StackAwalke
  • 21
  • 4
  • Does the first query not work? Your join is from the original select table's size id to the size table via that column, it's no different to the inner join above it and it should work fine (looks fine to me here). Hard to test unless your could provide an [SQLFiddle](http://sqlfiddle.com) – scrowler Aug 27 '14 at 03:19
  • @scrowler well it work when the item of buy not has a size cuz product_cod is the cod of product not the cod of size of product, the cod size of product is in tb_product_size. – StackAwalke Aug 27 '14 at 03:29
  • I see. So join tb_product_size ON product_size_code = product_cod, then join tb_size on tb_size.size_id = tb_product_size.size_id, and select tb_size.size_name then – scrowler Aug 27 '14 at 04:11
  • @scrowler, i fixed it doing: select b.product_name,b.product_cod,c.size_name,d.product_size_cod from tb_buy_item a inner join tb_product b on a.product_id=b.product_id left join tb_size c on a.size_id=c.size_id left join tb_product_size d on c.size_id=d.size_id and d.product_id=b.product_id ---- the main problem was in my inner join style cuz i was using () between the inners, but i would like to know if is there some problem in doing it? – StackAwalke Aug 27 '14 at 04:12
  • I haven't got much experience with `USING()`, I always use `ON` - [see this question for more information](http://stackoverflow.com/questions/11366006/mysql-on-vs-using) – scrowler Aug 27 '14 at 04:14

0 Answers0