Query Fail

[fontener]

"SELECT s.*, r.property_type FROM spaces s, ref_property_types r WHERE s.space_type_ref = r.id AND s.use_type_ref = r.id AND s.property_id = '$id'"

So, I have two columns from Table Spaces referencing an id in Table ref_property_types and both are returning r.property_type, but are different values.. how do I accomplish this ? (obviously this is not working)... thanks.

[Weedpacket]

Sorry? What do you have? What do you want to accomplish? What's not working?

[johanafm]

I'm taking a guess at what you want, but other than that, I recommend you make some other changes as well.

Never select *. Always specify which columns to select, even if you select them all. Otherwise you won't know what data you're dealing with when reading the code, and if the table changes, you are more likely to run into problems.

While queries using inner joins or listing several tables in the from clause, separated by commas are identical, it's a lot easier to see what the join conditions are if you use joins and stick the proper stuff in their on clauses.

SELECT s.stuff, s.more_stuff, r.property_type
FROM spaces s
INNER JOIN ref_property_types r ON
	-- unless space_type_ref and use_type_ref are identical, 
	-- when joining them on the same target column, you must
	-- use OR rather than AND, else they will obviously never match
	-- anything
	(s.space_type_ref = r.id AND r.property_type = 'one_kind')
	OR s.use_type_ref = r.id ANDr.property_type = 'other_kind')
WHERE s.property_id = '$id'"