CODEWITHSUNDEEP
sqldatabase
Salah Atwa
Salah Atwa

Reputation: 1728

Inner join database query?

I have 2 tables, items_table and item_bidding_table.

items_table has the following columns:

id, name, desc, quantity, unit_price

while item_bidding_table has these columns

id, item_id(FK), amount

where item_id is a foreign key to item_bidding_table.

I need a query to return full row from items_table (all columns) for these conditions:

  1. amount value in item_bidding_table must be greater than unit_price in items_table

  2. returned unit_price value must be the maximum amount in item_bidding_table

Example:

items_table:

[id | name | desc | quantity | unit_price ]
[1  | rice | food |    5     |     10     ]
[2  | Eggs | food |    6     |     15     ]

item_bidding_table:

[id | item_id | amount ]
[1  |    1    |    9   ]
[2  |    1    |    12  ]

Expected output:

 [1  | rice | food |    5     |     12     ]

where 12 is the largest amount in the item_bidding_table.

So what's the query required to return this output?

Upvotes: 0

Views: 85

Answers (4)

honey
honey

Reputation: 53

Try this :

select a.*, b.amt as amount from items_table a

cross apply (select max(amount) as amt from item_bidding_table where item_id = a.id group by item_id) b

Upvotes: 0

Saurabh Agrawal
Saurabh Agrawal

Reputation: 649

You can use below query to get the desired result. 

select 
    i.id, i.name, i.desc, i.quantity,
    max(ib.amount) 
from 
    items_table i 
inner join 
    item_bidding_table ib on i.id = ib.item_id 
where 
    ib.amount > i.unit_price 
group by 
    i.id, i.name, i.desc, i.quantity

Hint: whenever you need to use aggregate functions like (min, max, avg) etc think of group by clause and try to frame your query in that direction.

Upvotes: 3

Cetin Basoz
Cetin Basoz

Reputation: 23797

This would work in almost all SQL backends and be efficient (also doesn't depend on items_table id being unique - you didn't really say if it were or not):

   select name , desc , quantity, amount
    from items_table i
    inner join 
    (select item_id, max(amount) as amount 
    from item_bidding_table
   where amount > unit_price
   group by item_id) tmp on i.id = tmp.item_id

Note: It wouldn't matter with Max() in this query but whenever there are joins, first do the aggregate then join.

Upvotes: -1

alamoot
alamoot

Reputation: 2144

Try this query:

SELECT * FROM 
(
    SELECT *
    FROM item_bidding_table 
    ORDER BY amount DESC
) t
JOIN items_table t ON t.id = b.item_id
WHERE t.unit_price <= b.amount
GROUP BY t.id

The subquery orders the bids in descending order. With the addition of the group by you then get the maximum bid with each order. Finally the join and the where clause are needed to satisfy your conditions.

Upvotes: 0

Related Questions