Query to narrow results

[bobby44]

Hi,
I hope someone can help with a query I'm stuck on.

I think it will involve inner join but I can't get my head around it.

I have two tables that relate to venues fr weddings:

-- listingDBElements --
ID    field_name    field_value    listing_id    user_id
23    county          Yorkshire       34          56
43    county          Yorkshire       26          45
45    city               London          22           1
67    venue_name   The Mill         2          22
55    venue_type      Hall            34           8
55    venue_type      Hall            22           9
55    description     Nice place    34           8
59    venue_type      Hotel           2          55
 

etc..

-- listingDB --
ID    user_ID    Title                                     active
1      8             Eastbury Manor House              yes
2      9             Avenue House                          yes
3     16            The Red House                            no
 

etc..

I have a search form on the site that people can select or enter information they want to search for on a venue. The fields are as follows:

Venue name (variable = $venue_name)
City or town (variable = $city)
County (variable = $county, a drop down list)
Venue type (variable = $venue_type, a drop down list)

I'd like to return distinct listing ID references from the first table listed above and check with the second table that the listings are marked as 'active=yes'.

But also if someone searches for $venue_types = Halls and also selects $county = Yorkshire I don't want all the venues in Yorkshire and all the Halls in other counties.

Just the Halls in Yorkshire. Same goes for if they search for a town, county, venue type and venue name in one search the result would be only those venues that match all the variables.

Basically its re-writing the directory search form on the home page of weddingvenues.com which passes a url string like:
http://www.weddingvenues.com/listing...enue_type=Barn


So far I have this sql working for just one variable but not sure how to expand to include all 4:

SELECT DISTINCT listingsDBElements.listing_id, listingsDBElements.user_id FROM listingsDBElements, listingsDB WHERE listingsDB.active = 'yes' AND listingsDBElements.field_name = 'venue_type' AND listingsDBElements.field_value like '%barn%' order by listingsDBElements.user_id DESC 
 

Any ideas and I'd be really grateful.

In long hand I guess I'm saying, find all venues where all the field_name values in the first table match the search field names, and all the field_value values match search field values and all have corresponding listing_id's, and finally that these listings are marked as active venues in the second table.

I'm also thinking maybe it's only possible to do using multiple queries and then comparing the results in PHP by looping through each. But I hope to get it working in one sql query.

I am using MySQL client version: 4.1.20 and PHP 4.

Many thanks

Bob

[bobby44]

Oh, all sorted.
Someone posted this on another site which works a treat. I hope it helps anyone who is in a similar stop

SELECT vn.listing_id, ld.user_ID, ld.Title
 FROM listingsDB ld
LEFT JOIN listingsDBElements vn
    ON vn.listing_id = ld.ID
LEFT JOIN listingsDBElements vc
    ON vc.listing_id = ld.ID
WHERE ld.active = 'yes'
  AND ( (vn.field_name = 'venue_type' AND vn.field_value like '%$venue_type%')
     OR '$venue_type' = ''
    )
  AND ( (vc.field_name = 'county' AND vc.field_value like '%$county%')
     OR '$county' = ''
    )
order by ld.user_ID DESC
 

cheers
Bob