TransWikia.com

find entry with needed information

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

3 Answers

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:

  • feature (feature_id, description)
  • buyer (buyer_id, money, ...)
  • buyer_feature (buyer_id, feature_id)
  • property (property_id, money, ...)
  • property_feature (property_id, feature_id)

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     

Demo

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP