Adding a condition to a JOIN?

Databases
1

Hi,

I have 4 tables holding data on houses. Using a join in the query is straight forward enough for three of the tables.

SELECT 
jos_hp_properties.id AS prop_id, 
jos_hp_properties.name AS prop_name, 
jos_hp_prop_types.name AS prop_type,
price, intro_text, xpos, ypos, thumb
 
FROM jos_hp_properties

JOIN 
    jos_hp_prop_types 
ON 
    jos_hp_prop_types.id=jos_hp_properties.type

JOIN 
    jos_hp_photos 
ON 
    jos_hp_photos.property=jos_hp_properties.id

 WHERE agent=$dev_id

However, the fourth table ‘jos_hp_properties2’ contains multiple entries for each house. E.g. ‘Type of heating’, ‘no. of rooms’ and ‘parking space’

Constructed as;
id | property | field | value

I would like to extract the ‘no. of rooms’ which I know has a ‘field’ of 18.

Am I close with?

SELECT 
jos_hp_properties.id AS prop_id, 
jos_hp_properties.name AS prop_name, 
jos_hp_prop_types.name AS prop_type,
[COLOR="Red"]jos_hp_properties2.value AS prop_value,[/COLOR]
price, intro_text, xpos, ypos, thumb
 
FROM jos_hp_properties

JOIN 
    jos_hp_properties2 
ON 
    [COLOR="Red"]jos_hp_properties2.property=jos_hp_properties.id (WHERE field = 18)[/COLOR]

JOIN 
    jos_hp_prop_types 
ON 
    jos_hp_prop_types.id=jos_hp_properties.type

JOIN 
    jos_hp_photos 
ON 
    jos_hp_photos.property=jos_hp_properties.id

 WHERE agent=$dev_id

I’d appreciate any guidance.

2 
FROM jos_hp_properties

JOIN 
    jos_hp_properties2 
ON 
    jos_hp_properties2.property=jos_hp_properties.id 
AND jos_hp_properties2.field = 18
3

That’s brilliant! Thank you so much. Another command learnt!

4

Ok - sorry to be a pain but if Im using a join does that mean there has to be some data in that table?

Some of my properties do not have photos and therefore nothing in the jos_hp_photos table. These ones seem to be being left out of the result. Is this because of the JOIN?

JOIN 
    jos_hp_photos 
ON 
    jos_hp_photos.property=jos_hp_properties.id
5

yes, if some properties don’t have photos, then you must use a LEFT OUTER JOIN

6

That’s great! Thank you again.

7

Hmmm. I’ve just noticed that if a property has more than 1 photo it is returned the same number of times that it has pictures.

E.g 3 pictures = That property 3 times in the results.

Is there a way to prevent this? Or to remove the repeated results afterward?

8

Do you need the photos table? Do you select any value from that table?
If the answer is no, eliminate it from your query, and the problem will disappear :slight_smile:
If the answer is yes, then you’ll have to use PHP (or whatever language you use) to loop through the result and handle the duplicate values.

9

Thanks guido,

I do need the photos as I want to use the first one from each and ignore the others.

Can I use array_unique to do as you suggest? I am currently using the following to output the data.


$result = mysql_query($query);
$result = mysql_query($query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}
while($row = mysql_fetch_array($result)){
echo '<strong>'.$row['prop_name'].'</strong>'; ///Etc.
10

If you only want the first photo for each property, then I’m sure there’s a way to do it with MySQL.
What does ‘first’ mean? The photo with the lowest id?

11

Thanks for idea. I imagined that the lowest id would work until the first or primary image was changed. However, the jos_hp_photos table does have a ‘order’ column. I’ll try using this.


 `id` int(11) NOT NULL auto_increment,
  `property` int(11) NOT NULL default '0',
  `ordering` int(11) NOT NULL default '0',
12 
SELECT jos_hp_properties.id AS prop_id
     , jos_hp_properties.name AS prop_name
     , jos_hp_prop_types.name AS prop_type
     , jos_hp_properties2.value AS prop_value
     , price
     , intro_text
     , xpos
     , ypos
     , thumb
  FROM jos_hp_properties
INNER
  JOIN jos_hp_properties2 
    ON jos_hp_properties2.property = jos_hp_properties.id 
   AND jos_hp_properties2.field = 18
INNER
  JOIN jos_hp_prop_types 
    ON jos_hp_prop_types.id=jos_hp_properties.type
LEFT OUTER
  JOIN [COLOR="Blue"]( SELECT property
              , MIN(ordering) AS first_ordering
           FROM jos_hp_photos
         GROUP
             BY property )[/COLOR] AS m
    ON m.property = jos_hp_properties.id
LEFT OUTER
  JOIN jos_hp_photos 
    ON jos_hp_photos.property = jos_hp_properties.id
   AND [COLOR="blue"]jos_hp_photos.ordering = m.first_ordering[/COLOR]
 WHERE agent = $dev_id

joins for photos are LEFT OUTER in case the property has no photos

13

Ok. Im struggling. I’ve included the vlaue for the photo order value but Im not sure how to apply it.


	 SELECT 
jos_hp_properties.id AS prop_id, 
jos_hp_properties.name AS prop_name, 
jos_hp_agents.name AS prop_agent,
jos_hp_properties.plot AS prop_plot,
jos_hp_prop_types.name AS prop_type,
jos_hp_properties2.value AS prop_value,
jos_hp_photos.ordering AS photo_order,
featured, note, price, situation, intro_text, xpos, ypos, thumb
 
FROM jos_hp_properties 

JOIN 
   jos_hp_properties2 
ON 
   jos_hp_properties2.property=jos_hp_properties.id 
AND jos_hp_properties2.field = 18

JOIN 
    jos_hp_prop_types 
ON 
    jos_hp_prop_types.id=jos_hp_properties.type
	
JOIN
	jos_hp_agents
ON
	jos_hp_agents.id=jos_hp_properties.agent

LEFT OUTER JOIN 
    jos_hp_photos 
ON 
    jos_hp_photos.property=jos_hp_properties.id
	
	 WHERE featured=1 LIMIT 1

This works but returns results with using secondary photos.

I need to only get the results with the lowest ‘photo_order’ so that I can Limit all the results to 1 and then ORDER BY RAND(). Can this be done in MySQL?

14

did you see my post just before yours?

15

This is known as EAV table type. It is easy to put information in and dreadful to retrieve information from it.

Read up on Database Normalization and EAV to find out why you’d want to model this data in a different way before you get further.