user460114 user460114 - 6 months ago 8
MySQL Question

SQL join involving table having “fieldname” and “fieldvalue” columns

Sorry, I completely messed up the original question while trying to simplify things for this question.

I have the following tables for an auction site. I need to return records from the "listings" table that match geo-related search criteria, which will be passed as follows:

province_id = 2
district_id = 1,4
suburb_id = 5,6


Note, this comma-delimation comes from a checkbox group in the search form, where user can select multiple provinces, suburbs and/or districts for their search.

listings

record_id title
----------------------
2231 Ford Mustang
2232 Nissan GTR
2233 Chev Camaro
2234 Plymouth Roadrunner
2235 Dodge Charger


listing_special_fields
(related to listings on listing_id = listings.record_id)

record_id listing_id fieldname fieldvalue
-----------------------------------------------
1231 2232 province_id 2
1232 2232 district_id 4
1233 2232 suburb_id 1
1234 2233 province_id 2
1235 2233 district_id 4
1236 2233 suburb_id 6
1237 2234 province_id 2
1238 2234 district_id 4
1239 2234 suburb_id 5


provinces

record_id province
----------------------
1 Northland
2 Auckland
3 Waikato


districts
(related to provinces on province_id = provinces.record_id)

record_id province_id district
------------------------------------
4 2 Auckland City
5 2 Franklin
6 2 Hauraki Gulf Islands


suburbs
(related to districts on district_id = districts.record_id)

record_id district_id suburb
------------------------------------
1 4 Arch Hill
5 4 Avondale
6 4 Balmoral


According to the given search parameters, I should get the following output:

listings

record_id title
----------------------
2233 Chev Camaro
2234 Plymouth Roadrunner


The query should contain joins on the provinces, districts and suburbs tables. Normally it would be straightforward if the listing_specials_fields table had columns named "province_id", "district_id" and "suburb_id" instead of "fieldname" and "fieldvalue".

Answer

You can use PIVOT to get your expected result. Here is the query for SQL Server:-

Query

Select Listings.record_id, Listings.title FROM 
(
    select ListingId, fieldId, fieldValue  FROM listing_special_fields
) AS BQ
PIVOT
(  
    MAX(fieldValue)
    FOR fieldId IN ([province_id], [district_id], [suburb_id])
) PV
INNER JOIN Listings ON Listings.record_id = PV.ListingId
WHERE PV.province_id IN (2)
    AND PV.district_id IN (1, 4)
    AND PV.suburb_id IN (5, 6)

Result

order_id          title
  2233         Chev Camaro         
  2234         Plymouth Roadrunner       

Hope, It helps to you.

UPDATE

MySQL Solution Here:

Select Listings.record_id, Listings.title from Listings
INNER JOIN
( Select  
    ListingId,
    MAX(
       case when fieldId = 'province_id' then fieldValue else null end
    ) AS province_id,
    MAX(
       case when fieldId = 'district_id' then fieldValue else null end 
    ) AS district_id,
    MAX(
       case when fieldId = 'suburb_id' then fieldValue else null end
    ) AS suburb_id
  from listing_special_fields
  group by ListingId
) AS PV ON PV.ListingId = Listings.record_id
WHERE PV.province_id IN (2)
   AND PV.district_id IN (1, 4)
   AND PV.suburb_id IN (5, 6)