Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Unanswered: Help In Creating A Subquery

    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..

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2008
    Posts
    13
    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!!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Mary,

    do you understand the solution I have provided?
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2008
    Posts
    13
    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...

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you get extra marks for answering these optional questions?
    George
    Home | Blog

  8. #8
    Join Date
    Oct 2008
    Posts
    13
    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.

  9. #9
    Join Date
    Oct 2008
    Posts
    13
    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.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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 :-)
    George
    Home | Blog

  11. #11
    Join Date
    Oct 2008
    Posts
    13
    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

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about the first position part?
    George
    Home | Blog

  13. #13
    Join Date
    Oct 2008
    Posts
    13
    Quote Originally Posted by georgev
    What about the first position part?
    where place=1 ?

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    full code please
    George
    Home | Blog

  15. #15
    Join Date
    Oct 2008
    Posts
    13
    SELECT dog_id,name
    FROM dog
    JOIN (SELECT dog_id
    FROM place where place=1
    GROUP BY dog_id
    HAVING COUNT(place) >=2)
    on place.dog_id=dog.dog_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •