Help In Creating A Subquery

MaryH · #1 (**permalink**) 10-10-08, 01:04

I need to list a dog_id and name for each dog that has been placed two or more times.

I have two tables dog and place.

Dog table looks like this:

Name /dog_id
Star/102
Boxer/201
Daisy/301
Lilly/401
etc...

Place table looks like this:

dog_id/place
102/1
201/2
301/3
102/1
201/3
301/2
201/2
401/2

I need a subquery to check which dog has been placed two or more times.

Any help would be appreciable..

gvee · #2 (**permalink**) 10-10-08, 03:34

This query returns the dog_id from the place table where there is more than one occurance

Code:

SELECT dog_id
FROM   place
GROUP
    BY dog_id
HAVING Count(*) > 1

You can then either JOIN to this as a derived table, or you can use the IN operator.

healdem · #3 (**permalink**) 10-10-08, 04:47

MaryH
do yourself a favour, at least make an effort to do your homework before giving up and asking questions here. the reason for that is that in my books you have more chance of learning about SQL/MySQL than just having the answer provided for you. thats not to say NEVER ask questions.. but at least make an attempt, otherwise you are cheating yourself

MaryH · #4 (**permalink**) 10-10-08, 04:52

Quote:

Originally Posted by georgev

This query returns the dog_id from the place table where there is more than one occurance

Code:

SELECT dog_id
FROM   place
GROUP
    BY dog_id
HAVING Count(*) > 1

You can then either JOIN to this as a derived table, or you can use the IN operator.

Thank you for your help 'georgev' you gave me a clue of how to create the query!!

gvee · #5 (**permalink**) 10-10-08, 04:54

Mary,

do you understand the solution I have provided?

MaryH · #6 (**permalink**) 10-10-08, 05:01

Quote:

Originally Posted by healdem

MaryH
do yourself a favour, at least make an effort to do your homework before giving up and asking questions here. the reason for that is that in my books you have more chance of learning about SQL/MySQL than just having the answer provided for you. thats not to say NEVER ask questions.. but at least make an attempt, otherwise you are cheating yourself

Hi 'healdem', I made several attempts trying to create subquerys. I did not give up doing my work, im getting clues or ideas for creating subqueries.

I've read a textbook about mysql and researched hours. The reason why i'm asking these questions is because these questions in my assign are optional not compulsory.. So in other words im trying to get more knowledge of how to create sql subqueries...

gvee · #7 (**permalink**) 10-10-08, 05:03

Do you get extra marks for answering these optional questions?

MaryH · #8 (**permalink**) 10-10-08, 05:07

Quote:

Originally Posted by georgev

Mary,

do you understand the solution I have provided?

Hi Georgev

Yes,I do understand that the query you provided lists the dog_id for each dog that has been placed more than one time.

And to get the name of the dog i have to join the dog table on dog_id.

MaryH · #9 (**permalink**) 10-10-08, 05:14

Quote:

Originally Posted by georgev

Do you get extra marks for answering these optional questions?

No, This is not a graded assignment. Theres 15 questions which 10 of them are compulsory to complete.

gvee · #10 (**permalink**) 10-10-08, 05:24

Ok, for extra credit (from us friendly forum folks

) - can you write a query for the following

List a dog_id and name for each dog that has been placed two or more times in first position.

Go on, give it a go! If you can't manage it, post what you've tried and we'll explain it to you.
No harm in trying eh :-)

MaryH · #11 (**permalink**) 10-10-08, 05:35

Quote:

Originally Posted by georgev

Ok, for extra credit (from us friendly forum folks

) - can you write a query for the following

List a dog_id and name for each dog that has been placed two or more times in first position.

Go on, give it a go! If you can't manage it, post what you've tried and we'll explain it to you.
No harm in trying eh :-)

SELECT dog_id,name
FROM dog

JOIN (SELECT dog_id
FROM place
GROUP BY dog_id
HAVING COUNT(place) >=2)
on place.dog_id=dog.dog_id