CODEWITHSUNDEEP
phpmysqlcodeigniter
Jose Faro
Jose Faro

Reputation: 366

Join query between 2 tables

I have three tables:

REGISTERS
------------------
id, name, idColony
------------------
1 , some, 3
2 , othe, 6
3 , sann, 3
------------------

EXTRA_COLONIES
------------------
id, idRegister, idColony
------------------
1,   1,   4
2,   1,   5
3,   2,   8
-----------------

COLONIES
------------------
1, some_colony
2, another_colony
...
...

I have a table called e.g. REGISTER. This table will have an idColony (1 idColony is mandatory) from the COLONIES table.But this REGISTERS record/row can have up to 10 extra idColonys. That was the reason for me to separate to another table.

So the query I am doing is this:

SELECT table1.field,table2.field...
FROM (`REGISTERS`)
JOIN `EXTRA_COLONIES` ON `EXTRA_COLONIES`.`idRegister` = `REGISTERS`.`id`
WHERE (REGISTERS.idColony = '1' or EXTRA_COLONIES.idColony = '1')
GROUP BY `REGISTERS`.`id`
LIMIT 30

The problem with this query is that gets only the records that have EXTRA_COLONIES because of the link JOIN EXTRA_COLONIES ON EXTRA_COLONIES.idRegister = REGISTERS.id.

How can I get all the records, EXTRA_COLONIES or not?

Upvotes: 0

Views: 65

Answers (1)

Code Krieger
Code Krieger

Reputation: 201

LEFT JOIN will solve the problem.

Irrespective of any matching idRegister in EXTRA_COLONIES, records from REGISTER should be returned

More on MySQL JOIN: http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 1

Related Questions