Selecting from multiple tables

I'm trying to select information from several tables. The number of records on the 2nd table don't match the number of records of the 1st table. I want to get all records regardless of whether there's a record on the 2nd table or not. For example:

Table "animal"
+----+------+
Ś id Ś name Ś
+----+------+
Ś a1 Ś dog Ś
Ś a2 Ś fish Ś
Ś a3 Ś cat Ś
Ś a4 Ś bird Ś
+----+------+

Table "pets"
+----+-----+
Ś id Ś own Ś
+----+-----+
Ś a1 Ś yes Ś
Ś a3 Ś yes Ś
+----+-----+

Basically, "pets" table doesn't have a record for all "animal" ids. When selecting records using this:

SELECT
animal.id, animal.name, pets.own
FROM
animal, pets
WHERE
animal.id = pets.id

Returns this:

+----+------+-----+
Ś id Ś name Ś own Ś
+----+------+-----+
Ś a1 Ś dog Ś yes Ś
Ś a3 Ś cat Ś yes Ś
+----+------+-----+

My question is how do I make the query return this:

+----+------+-----+
Ś id Ś name Ś own Ś
+----+------+-----+
Ś a1 Ś dog Ś yes Ś
Ś a2 Ś fish Ś Ś
Ś a3 Ś cat Ś yes Ś
Ś a4 Ś bird Ś Ś
+----+------+-----+

I want the query to return all records regardless of whether table pets has an entry for it or not.

Anybody? ;-)

Selecting from multiple tables

Medianeer

Medianeer

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week