Trying to join across multiple tables

[moagrius]

Hi,

 

I'm working on a search query I'm having some trouble formulating.  I'll try to explain as best I can, starting with the table structure of the DB, in abbreviated form.

 

(Abbreviated) Tables:

 

PROPERTIES

ID  |  ADDRESS  |  MODEL  |  COMMUNITY

1  |  123 Abc St.  |  1  |  1

2  |  45 Some Street  |  1  |  2

3  |  #6 Another Av.  |  2  |  2

 

MODELS

ID  |  SQ_FT

1  |  2000

2  |  3000

 

COMMUNITIES

ID  |  NAME

1  |  Deerbrook

2  |  Ashford

 

LOCATIONS

ID  |  NAME

1  |  North

2  |  Northwest

3  |  West

 

LOCATION_BRIDGE

ID  |  COMMUNITY  |  LOCATION

1  |  1  |  1

2  |  1  |  2

3  |  2  |  2

4  |  2  |  3

 

So pretty  much anything in the above with a 1-digit number is a FK to another table.  LOCATION_BRIDGE takes a community and a location, one for each, since any community might be in multiple location codes...  In this example, the community with ID1 (Deerbrook) qualifies for location 1 (North) and location 2 (Northwest), and community 2 (Ashford) qualifies for location 2 (Northwest) and location 3 (West).

 

How would I structure a query that takes a post variable of location (equal to the ID of the location - for example, "1" for a search on North), and get back all the information for properties that match that location code (Address, Community Name, Square Footage, etc)?

 

The sticky for the board said to include a raw SQL statement, which I don't really have, but started with something like:

 

select * from PROPERTIES, MODELS where PROPERTIES.MODEL=MODELS.ID ...

 

The ... is where I get lost, trying to get back properties whose communities qualify for the location code provided by user input.

 

I'm using MySQL 5 and PHP 5.

 

Any help would be greatly appreciated.

 

TYIA

[ignace]

SELECT *
FROM location_bridge lb
JOIN community c ON lb.community = c.id
JOIN properties p ON c.id = p.community
JOIN models m ON p.model = m.id
JOIN location l ON lb.location = l.id
WHERE l.id = $lid;

 

If you can be a little more specific of what information you want to show we may be able to further optimize this query

[moagrius]

that's great, ignace - thanks very much...

 

before seeing your reply, i had come up with this:

 

select * from PROPERTIES, MODELS, COMMUNITIES where PROPERTIES.MODEL=MODELS.ID and PROPERTIES.COMMUNITY=COMMUNITIES.ID and PROPERTIES.COMMUNITY in (select COMMUNITY from LOCATION_BRIDGE where LOCATION={$location})

 

which seemed to work, but i wonder if you would recommend one approach over the other?

 

thanks again

[ignace]

Nope they are equal (in results)

 

PROPERTIES.COMMUNITY in (select COMMUNITY from LOCATION_BRIDGE where LOCATION={$location})

 

Is the same as saying:

 

JOIN properties p ON ..
JOIN location_bridge lb ON p.community = lb.community
WHERE lb.location = $lid

 

My method is preferred as there is no need to use sub-queries they only make it more complex to understand.

[moagrius]

Makes sense.  Thanks.