CODEWITHSUNDEEP
mysqlsql
ivish
ivish

Reputation: 612

Unable to fetch data by joining 4 tables

I am trying to fetch data by combining 4 tables but not being able to get desired results. Following is my truncated table structure.

order_line
----------
id
item_id --> item_type_map(item_id) 
amount
quantity

item_type
---------
id --> item_type_map(type_id)    
category_name

item_type_map
-------------
item_id
type_id

international_description
-------------------------
foreign_id --> order_line(item_id)
content

Every item (table NOT shown above) belongs to an item_type.
Input to the query will be the list of item_type Ids. And then item wise, I need to select item_type.category_name, order_line.sum(amount), order_line.sum(quantity), international_description.content.

I have tried number of combinations but have failed to get the expected results.

For example one of the query that i tried is:-

select 
  item_type.category_name, 
  order_line.item_id, 
  international_description.content, 
  sum(order_line.amount) as amount, 
  sum(order_line.quantity) as quantity 
FROM order_line 
INNER JOIN item_type_map ON item_type_map.item_id = order_line.item_id 
INNER JOIN item_type ON item_type.id = order_line.item_id 
INNER JOIN international_description 
               ON international_description.foreign_id = order_line.item_id 
WHERE item_type_map.type_id IN (101, 300) 
GROUP BY order_line.item_id;

I have been trying to resolve this issue for almost 2 days now, and really looking forward to some guidance.

The sample data in my table is as follows and i am expecting 3 records in response to above query since there are 3 items that belong to 101 and 300 item_types. But in fact i am getting only 2 results in the response. Please find attached query response below as well.

order_line
----------------------------------------------
id  |  item_id  | amount     |    quantity      
----------------------------------------------
'900', '300', '150.0000000000', '1.0000000000'
'1000', '300', '122.0000000000', '1.0000000000'
'1004', '300', '1000.0000000000', '1.0000000000'
'901', '301', '200.0000000000', '1.0000000000'
'1001', '301', '150.0000000000', '1.0000000000'
'1101', '101', '100.0000000000', '1.0000000000'

item_type
----------------------------
id  | description
------------------------------
'101', 'Fees'
'300', 'Adjustments'

item_type_map
--------------------------
item_id | type_id
--------------------------
'101', '101'
'300', '300'
'301', '300'

international_description
-----------------------------
foreing_id  | content
-----------------------------
'101', 'NSF Fee'
'300', 'Adjust - Debit'
'301', 'Adjust - Credit'

And the result is:-

result
-----------------------------------------------------
category  | item_id  | content  |  amount  | quantity
-----------------------------------------------------
'Fees', '101', 'NSF Fee', '100.0000000000', '1.0000000000'
'Adjustments', '300', 'Adjust - Debit', '1272.0000000000', '3.0000000000'

Upvotes: 0

Views: 62

Answers (1)

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

The join with item_type has wrong criteria. Change it to:

INNER JOIN item_type ON item_type.id = item_type_map.type_id

Upvotes: 1

Related Questions