Using another table to query a database

[Mr Chris]

Hi,

 

Say I have a table named properties like so:

 

 

and wanted to query it ie:

 

Select * from Properties where house_type ='Maisonette' AND rent_or_buy = 'buy'

That would be fine, but is there anyway in php to run a query like the above, but via another table ie:

 

Look in this properties_wanted table

 

 

and then loop through all the records asking it to

 

Select * from Properties where house_type ='columnvalue' AND rent_or_buy = 'columnvalue'

 

?

 

Thank you

 

[Caberman]

Certainly! What you want is called a JOIN. I tend to do joins in my WHERE clause which makes more sense to me personally. Your join would probably look something like this:

 

SELECT *
FROM properties AS P, properties_wanted as PW
WHERE p.house_type=pw.house_type AND p.rent_or_buy=pw.rent_or_buy
ORDER BY p.house_type

 

I haven't tried that code but I believe it will get you on the right track.

 

Cheers.

[Mr Chris]

Thanks Caberman, just what I needed!

[roopurt18]

Caberman is mostly correct, but I must warn you about performing the join by connecting the columns in your WHERE clause.  There are actually two syntaxes, one of which is more verbose:

 

SELECT *
FROM `table1`, `table2`
WHERE `table1`.`some_col`=`table2`.`some_col`

 

vs.

 

SELECT *
FROM `table1`
INNER JOIN `table2` ON `table1`.`some_col`=`table2`.`some_col`

 

I recommend the second (more verbose) method.  First off it is more verbose and declares your intentions more clearly.  Secondly, there is an issue in MySQL 5.x where you can't use the first method with OUTER JOINS.

 

This will work in MySQL 4.x but not 5.x

SELECT *
FROM `table1`, `table2`
LEFT JOIN `table3` ON `table1`.`another_col`=`table3`.`another_col`
WHERE `table1`.`some_col`=`table2`.`some_col`

 

I ran into this issue on my test server when I upgraded to MySQL 5.0.  I noticed a lot of queries that worked perfectly before (and still work on the production server) stopped working altogether.  It could be because I use table aliases but either way, it's better to be more verbose (and safer) IMO.

[Caberman]

Ah, thanks Roopurt. I did not know that about MySQL 5. It seems odd that they would take out being able to do joins in the WHERE clause. That is going to break a lot of code all over.

 

I am probably better off doing the more verbose joins, but like I said, they make more sense in my head in the WHERE clause. I can actually -see- what they are doing there rather than using the JOIN reserve word which seems less intuitive to me.

 

Cheers.

[roopurt18]

They will continue to work until you add an OUTER JOIN (and there is no "short" syntax for outer joins).  Most of your queries will chug along just fine, it's just a select few that go poo-poo.

[treehugger88]

Would this do a full outer join?

 

WHERE `table1`.`some_col` != `table2`.`some_col`

[roopurt18]

Not from my understanding. 

 

Inner joins match all of the records from one table to another and return results only if matches are found.

 

Outer joins match all of the records from one table to another and return all the records from one table matched with the records from the other table.  However if a joining record is not found, it is still returned but with NULL values.  (The difference is that an inner join would remove it from the result-set entirely).

 

So unless I am mistaken, your WHERE condition will not change it to an OUTER JOIN and you will get many, many more records than you intended, none of which will have NULLs like they should.

 

For example let's say that table `users` has one user with an `id` of 1.  There is a `user_profile` table that joins with the `users` table on its `user_id` column.  I don't know why this would be the case, but let's pretend that there are 10 records in `user_profile` but only one of them has `user_id` of 1.

 

If you ran something like:

SELECT * FROM `user_profile` p, `users` u WHERE p.`user_id`<>u.`id`

You would get an inner join where the columns do not match, which would be 9 records.

 

If you ran something like:

SELECT * FROM `user_profile` p LEFT JOIN `users` u ON u.`id`=p.`user_id`

This is a select from `user_profile` which I've stated has 10 records.  So we should have 10 results in our record-set.  For any where we can not make a match, we populate the columns from `users` with NULL.  So we will get 10 records and one of them will have data from the `users` table.

 

They are not the same (unless I am totally off base).

[treehugger88]

Hmm, yeah that makes sense. I think you are right for left and right outer joins.

 

If you ran something like:

 

Code:

SELECT * FROM `user_profile` p, `users` u WHERE p.`user_id`<>u.`id`You would get an inner join where the columns do not match, which would be 9 records.

 

I am pretty sure that is called a full outer join, isnt it? It is basically like getting everything that is not in both tables. It would be like doing a left and right outer join at the same time. I think that's what they told me in school, hehe!

[roopurt18]

Not sure.  TBH I've never used a full outer join.  Perhaps fenway or Barand will pop in and set us straight.