CODEWITHSUNDEEP
mysqljoinproject
Mark
Mark

Reputation: 195

MySQL project design - conditionally select from one table based on rows from another select query

I have 2 tables in a MySQL database that are regularly appended with new data. One is a list of rental properties and their characteristics eg 2 bed, 2 bath, location, rental price etc. The second table is a list of properties currently for sale. From the rental table I can determine the average rent a particular type of property is getting in a particular location using the query:

SELECT bed, bath, type, suburb, postcode, AVG(price)
FROM rent_suburbs
GROUP BY bed, bath, type, suburb, postcode

I want to be able to select properties from the buy_items table where a user defined percentage of the sale price is less than the average rental price for a property matching the type and location.

I am trying trying to fix code below that someone suggested but I'm stuck.

select listing, bed, bath, type, address, postcode, state, price
from 
   buy_items
where (price*.0014) < avg(price) from 
select 
    bed, bath, type, suburb, postcode, avg(price)
from
    rent_items
group by bed , bath , type , suburb , postcode
/* and bed, bath, type, suburb and postcode match the buy_items property ????

I'm new so any help is appreciated. Thanks

Table structure is as follows:

buy_items

buy_items_id2   int(11)
car         int(11)
price   int(11)
listing varchar(12)
bed         int(11)
bath    int(11)
suburb  varchar(25)
state   int(11)
scrapedate  int(11)
address varchar(45)
type    varchar(25)
postcode    int(11)

and 

rent_items

rent_items_id2  int(11)
car         int(11)
price   int(11)
listing int(11)
bed         int(11)
bath    int(11)
suburb  varchar(25)
state   varchar(5)
scrapedate  int(11)
address varchar(45)
type    varchar(25)
postcode    int(11)

Upvotes: 0

Views: 193

Answers (1)

Ashalynd
Ashalynd

Reputation: 12563

try this:

   select listing, b.bed, bath, type, address, postcode, state, price
    from 
       buy_items b
    join (
    SELECT bed, bath, type, suburb, postcode, AVG(price) as avg_price
    FROM rent_suburbs
    GROUP BY bed, bath, type, suburb, postcode ) a
    on a.bed=b.bed and a.bath=b.bath and a.suburb=b.suburb and a.postcode=b.postcode
    where (b.price*.0014) < a.avg_price;

Upvotes: 1

Related Questions