CODEWITHSUNDEEP
mysqlsqljoin
Shaheer
Shaheer

Reputation: 2147

mysql query join doesnt work but sql "where" does

i am trying to run a join query on mysql i am using following query:

SELECT `Reservation`.`id`
FROM `reservations` AS `Reservation` 
LEFT JOIN rates ON Reservation.postal_code=rates.postal_code

this gives my results only for "Reservation" table, and no results for the "rates" table at all, but the following query works fine 

SELECT `Reservation`.`id`, rates.id 
FROM `reservations` AS `Reservation`, rates 
WHERE Reservation.postal_code = rates.postal_code

i am unsure what am i doing wrong, can someone please help?

edit

I was using cakephp and this is just a modified version of a query generated by cakephp and it didnt specify the fields in "select" case so i thought it isn't needed.

Upvotes: 0

Views: 79

Answers (3)

santiagobasulto
santiagobasulto

Reputation: 11686

You have to include them in the SELECT

SELECT `Reservation`.`id`, rates.* 
FROM `reservations` AS `Reservation` 
LEFT JOIN rates ON Reservation.postal_code=rates.postal_code

In your second query you have rates.id, that is including it.

NOTE: Don't use the kind of join from the second query you showed us. That's a cross join (theta join) and will make your query go really slow. Always use JOINS.

Upvotes: 4

StevieG
StevieG

Reputation: 8709

The first query isn't selecting from the Rates table. Try this:

SELECT `Reservation`.`id`, `rates`.`id` 
FROM `reservations` AS `Reservation` 
LEFT JOIN rates ON Reservation.postal_code=rates.postal_code

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

The first query returns only a single column, id, from reservations. Are you expecting to see data from rates as well? You must mention those columns after the keyword SELECT.

The second query includes the column id from rates.

If you modify the queries so they return the same list of columns they will produce similar (but not identical results). They will still differ in that the second query may return fewer rows — it will not include reservations with 0 corresponding rates (because it uses an INNER JOIN).

Upvotes: 0

Related Questions