Stack Overflow Asked by Ashley on December 29, 2020
I am trying to come up with a sql query related to division,
I have relational schemas like these:
property(PNO, price, fid); //PNO is the primary key, fid is the feature id, price is the price of the house
buyer(PEID, max_price, fid);//PEID is the primary key, fid is the feature id, max_price is the max price a buyer can pay for a house
feature(FID, content);
One property can have many features. One buyer can require multiple features.
The relationship I want to build is this : for each buyer, list all the properties that meet buyer’s requirements (features and price).
I tried many different ways, but still can not come up with the desired output.
Sample data would be like
//buyers require 0 - many features, and price is the maximum price they can pay for a house, not for a single feature
buyer(0, 10k, 1)
buyer(0, 10k, 2) //buyer 0 can pay 10k, and requires feature 1 and 2
buyer(1, 15k, 3)
buyer(1, 15k, 4) //buyer 1 can pay 15k, and requires feature 3 and 4
buyer(2, 150k, null) //buyer 2 can pay 150k, and no specific requirement
buyer(3, 20k, null) //buyer 3 can pay 20k, and no specific requirement
//property has 0 - many features, and price is the selling price of the house, not for a single feature
property(5, 5k, 1)
property(5, 5k, 2) //property 5 is 5k, and has feature 1 and 2
property(6, 6k, 1)
property(6, 6k, 4) //property 6 is 6k, and has feature 1 and 4
property(7, 6k, 1)
property(7, 6k, 2)
property(7, 6k, 3)
property(7, 6k, 4) //property 7 is 6k, and has feature 1,2,3,4
property(8, 100k, 3)
property(8, 100k, 4) //property 8 is 100k, and has feature 3,4
feature(1, '2000 square feet')
feature(2, 'two story')
feature(2, 'one story')
feature(4, 'two bathrooms')
Sample output would be like
buyer property
----- --------
0 5 //buyer 0 can afford property 5 with required features
0 7 //buyer 0 can afford property 7 with required features
1 7 //buyer 1 can afford property 7 with required features
2 5 //buyer 2 can afford property 5
2 6 //buyer 2 can afford property 6
2 7 //buyer 2 can afford property 7
2 8 //buyer 2 can afford property 8
3 5 //buyer 3 can afford property 5
3 6 //buyer 3 can afford property 6
3 7 //buyer 3 can afford property 7
You say that what you are showing is not the real tables, but query results on underlying tables. This is good for otherwise your database would not be normalized and thus prone to errors.
I assume in the followwing answer a properly normalized database:
We select from buyers and properties where the price matches. Then we see whether we find a missing feature for the found buyer/property pair.
select
b.buyer_id, p.property_id,
case when exists
(
select feature_id from buyer_feature bf where bf.buyer_id = b.buyer_id
minus
select feature_id from property_feature pf where pf.property_id = p.property_id
)
then 'feature(s) missing'
else 'all feature requirements met'
end as feature_status
from buyer b
join property p on p.money <= b.money
order by b.buyer_id, p.property_id;
Correct answer by Thorsten Kettner on December 29, 2020
I think you need aggregation through grouping by peid
and price
columns while joining subqueries having buyer
and property
tables with join conditions composed of concatenations of properties such as
SELECT b.peid AS buyer, p.pno AS property
FROM ( SELECT LISTAGG(fid,',') WITHIN GROUP (ORDER BY fid) AS features, pno
FROM property
GROUP BY pno ) p
JOIN ( SELECT LISTAGG(fid,',') WITHIN GROUP (ORDER BY fid) AS features, peid
FROM buyer
GROUP BY peid ) b
ON b.features = p.features;
BUYER PROPERTY
0 5
1 6
Answered by Barbaros Özhan on December 29, 2020
You can do a join
:
select b.*, p.*
from buyer b left join
property p
on p.price <= b.max_price;
If you want this all on one row, you can use aggregation:
select b.PEID, b.max_price,
listagg(pno, ',') within group (order by pno) as pnos
from buyer b left join
property p
on p.price <= b.max_price
group by PEID, max_price
Answered by Gordon Linoff on December 29, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP