Joins

0 Jennifer SMith · October 6, 2014
I love Bucky's tutorials but I am STILL confused about joins...

I have a Pet_owner and a Pet_3 table
I need to display display the OwnerLastName, OwnerFirstName, and OwnerEmail of any owners of cats.

Here is what I have:
SELECT PET_OWNER.OwnerLastName, Pet_OWNER.OwnerFirstName, PET_OWNER.OwnerEmail
FROM PET_OWNER, PET_3
WHERE PET_OWNER.OwnerId=PetId
ORDER BY PET_3.PetType = 'Cat';

It displays the information but it displays all owners and I want JUST the cat owners...

Help!

Post a Reply

Replies

Oldest  Newest  Rating
0 Poly Morphist · October 15, 2014
I'm not really up on MySQL but i'll give this a go at answering your question. 

I think you want to actually do an INNER JOIN on your query.  Right now you're joining the ID's of each table in the WHERE clause which is effectively returning all pet owners.  An INNER join will restrict the result set to return pet owners which have a matching ID in both tables. 

Something like:

SELECT
         Columns
FROM
         Table A as A
         INNER JOIN Table B as B ON A.OwnerID = B.PetOwnerID
WHERE
        B.PetType = 'Cat'
0 Eugene Botma · October 16, 2014
All that you needed in your original query is to append "and PET_3.PETTYPE='CAT'" to your where clause. you're just sorting it to whether it's a cat or not.

But, the join suggested by poly is probably a better approach. the classic join as in your original statement is outdated and can be slower than using a join statement with a lot of data.
  • 1

SQL & Databases

107,306 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator