php - MySQL Search with Join on Table and Meta Data -

php - MySQL Search with Join on Table and Meta Data -

i'm trying setup join, listings table has meta in listing_meta table, these listings represented cars.

when search 1994 - 2014 auto , brand no results, want able homecoming listing's have meta values matching right year , brand future engine sizes , cc etc.

im unaware of best method current query:

select * listing left bring together listing_meta l_meta on l_meta.auction = listing.id status = '1' , cat_id in (24,25,26,27,28,29,30) , (((l_meta.field = 'year' , l_meta.value >= 1994) or (l_meta.field = 'year' , l_meta.value <= 2014)) , (l_meta.field = 'brand' , l_meta.value = '2')) , price_start >= 0 , price_start <= 999999 , time_end >= 1413347572 , time_start <= 1413347572 order opt_highlight desc, id desc limit 0,9

i thinking of doing exists might bit heavy... there simple? help appreciated.

if want match multiple rows in listing_meta table, need bring together multiple times.

select * listing l inner bring together listing_meta m1 on m1.auction = l.id inner bring together listing_meta m2 on m2.auction = l.id l1.field = 'year' , l1.value between 1994 , 2014 , l2.field = 'brand' , l2.value = '2' , price_start between 0 , 999999 , time_end between 1413357572 , 1413347572 order opt_highlight desc, id desc limit 0, 9

you should utilize inner join this, not left join; utilize left join when want result include rows have no match.

php mysql sql database join