CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
user3541631
user3541631

Reputation: 4008

Having 3 Entities, get the third Entity as a result, based on the number of relations(count) of the other 2 Entities

I have 3 Entities, Type, Item, Owner

Owner

id name
1   owner1
2   owner2
3   owner3

An Owner can have multiple Items

Item

id   name  owner_id
1   item1  1
2   item2  1
3   item3  1 
4   item4  3
5   item5  2

Type 

id name
1  alpha 
2  beta

There is a Many to Many relationship between Item and Type. An Item can have multiple Type, and a Type can have multiple Item

Item_Type

id item_id type_id

1   1       1
2   1       2 
3   3       1
4   4       1

Giving a Type by name, I need to get all Owners which have Item of that Type, ordered by the number of Item per Type.

I'm using PostgreSQL 10.

So I start with:

SELECT Type.name, Item_Type.item_id from Type WHERE Type.name = 'my_var' INNER JOIN Item_Type ON Type.id = Item_Type.type_id

So I need to count Item per Type, and after get the Owners DISTINCT ordered by count.

I don't know how to approach this order by count.

Expected - Example:

So, if for Type I select the name : 'alpha' which corresponds to the id 1

The item_id corresponding to Type with id 1 are: 1,3,4

The Item(s) with id 1,3 corresponding owner_id is 1 (count=2). The Item with id 4 corresponding owner_id is 3 (count=1).

So the result, (in order) will be owner1, owner3

Upvotes: 0

Views: 49

Answers (2)

Nick
Nick

Reputation: 147216

This query will fetch your desired results. It finds all the items that each owner owns, then filters them by item type to only include those of type alpha, and then does a COUNT of the number of items the owner has and sorts the results by that value descending:

SELECT t.name, o.name, COUNT(i.id) AS num_type
FROM Owner o
JOIN Item i ON i.owner_id = o.id
JOIN Item_Type it ON it.item_id = i.id
JOIN Type t ON t.id = it.type_id
WHERE t.name = 'alpha'
GROUP BY t.name, o.name
ORDER BY num_type DESC

Output:

name    name    num_type
alpha   owner1  2
alpha   owner3  1

Demo on dbfiddle

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

You can try below -

DEMO

select d.name as ownername,count(distinct c.name) as itemcount from 
Item_Type a 
inner join Types b on a.type_id=b.id
inner join Item c on a.item_id=c.id
inner join Owner d on c.owner_id=d.id
group by d.name

Upvotes: 1

Related Questions