Query using JOIN with same column name?

I having a problem with my JOIN query because two of the tables contain a column with the title ‘name’. Unfortunately, I cannot change the table structure.

My current Query is as follows -


SELECT
id,
jos_hp_properties.name,
jos_hp_prop_types.name

FROM jos_hp_properties

JOIN
    jos_hp_prop_types
ON
    jos_hp_prop_types.id=jos_hp_properties.type

How do I now ‘target’ / ‘access’ the ‘name’ of each?

echo '<p>name='.$row['name'].'<br>';
	echo '<p>type='.$row['name'].'<br>';

Just name output columns:

S ELECT
id AS id,
jos_hp_properties.name AS prop_name,
jos_hp_prop_types.name AS type_name

FROM jos_hp_properties

JOIN
    jos_hp_prop_types
ON
    jos_hp_prop_types.id=jos_hp_properties.type

and do:

echo '<p>name='.$row['prop_name'].'<br>';

    echo '<p>type='.$row['type_name'].'<br>'; 

Thanks so much! That ‘AS’ command is very handy.

Great! I managed to added another join to get the items thumbnail photo from the jos_hp_photos table.


JOIN
    jos_hp_photos
ON
    jos_hp_photos.property=jos_hp_properties.id

/images/thb/'.$row['thumb'].'

Not so great :frowning:

another join that I thought would let me get another field’s value (number of ‘rooms’) from an additional table.

JOIN
    jos_hp_properties2
ON
    jos_hp_properties2.property=jos_hp_properties.id

But now results all the same data? I.e. property one repeated until the query ends?

Can anybody see what i am doing wrong? :frowning:

Post the entire query

Thanks so much!

$query = "SELECT
jos_hp_properties.id AS prop_id,
jos_hp_properties.name AS prop_name,
jos_hp_prop_types.name AS type_name,
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
	
JOIN
    jos_hp_properties2
ON
    jos_hp_properties2.property=jos_hp_properties.id
	
	";
		
$result = mysql_query($query);

$result = mysql_query($query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}



while($row = mysql_fetch_array($result)){

	echo '<p>id='.$row['prop_id'].'<br>';
	echo 'name='.$row['prop_name'].'<br>';
	echo 'type='.$row['type_name'].'<br>';
	echo 'x='.$row['xpos'].'<br>';
	echo 'y='.$row['ypos'].'<br>';
	echo 'link="index.php?view=property&id='.$row['prop_id'].'<br>';
	echo '<img src="images/thb/'.$row['thumb'].'" alt="" />'.'<br>';
	echo 'name='.$row['name'].'<br>';
	echo 'price=£'. $row['price'].'<br>';
	echo 'intro='. $row['intro_text'].'';
	echo '</p>';
}

?>

Since you’re using inner joins, the property must be present in all tables, otherwise it won’t be selected.

Can you give an example of the data you have in your tables, the output you expect, and the output the query gives you?

This is my table structure

TABLE jos_hp_properties
id | name | agent | xpos | ypos | price | intro_text | type

1 | property one | 1 | 20051 | 23158 | 200000 | some text | house

TABLE jos_hp_photos
id | property | standard | thumb

2 | 1 | 1_temp-800px.jpg | 1_temp-400px.jpg

TABLE jos_hp_prop_ef
id | field_type | name | default_value | size | field_elements

18 | selectlist | bedrooms | 1 | 0 | 1|2|3|4|5

TABLE jos_hp_properties2
id | property | field | value

1 | 1 | 18 | 2

TABLE jos_hp_prop_types
id | name | desc | published | ordering

id | House | House Descripition | 1 | 1

I’ll post the require result shortly.

I would like to get the following data for each property;

jos_hp_properties.id AS prop_id eg. 1
jos_hp_properties.name AS prop_name eg. property one
jos_hp_prop_types.name AS type_name eg. House

  • price, intro_text, xpos, ypos from jos_hp_properties

  • thumb from jos_hp_photos

and the tricky bit

  • number of bedrooms

Too complicated?

This mkight save you a load of tyoing and make it easiere to understand what is happening.


SELECT
  jhp.id
, jhp.name
, jpt.name
, price
, intro_text
, xpos
, ypos
, thumb

FROM jos_hp_properties as jhp
JOIN
jos_hp_prop_types as jpt
ON
jpt.id=jhp.type
JOIN
jos_hp_photos as jphot
ON
jphot.property=jhp.id
JOIN
jos_hp_properties2 as jhp2
ON
jhp2.property=jhp.id

you’ll need to as the alias tag to price and the other four values.

if price is to be the addition of some cols, you will need to use SUM($price) likely in a sub select.

bazz