Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2012
    Posts
    10

    Unanswered: Re: help with two table query

    Table T1 has the following data:

    ID1 NAME
    1 Steve
    2 Joe
    3 Bob
    4 Julie
    5 Harry
    6 Fred
    7 Jill

    Table T2 has the following data:
    SCORE ID2
    300 4
    120 6
    12 3
    140 6
    543 1
    455 2
    311 3
    555 6
    234 5

    Write an SQL query to show all the names in the T1 table that do not have cumulative scores of at least 450 (note that Jill should be included in such a list as she has taken no tests at all and therefore not scored!). The ID2 column in the T2 table matches the ID1 column in the T1 table.

    So far I have come up with this SQL query I just need someone to point me in the right direction to see if I am right

    select NAME
    FROM tablet1 a, tablet2 b
    WHERE a.id1 = b.id2
    AND SCORE < 450;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to clarify your requirements more.

    (1) Who in T1 should be included in the result?
    You wrote "Jill should be included ...".
    How about others in T1?
    Steve
    Joe
    Bob
    Julie
    Harry
    Fred

    (2) If (8, Alice) was in T1 and (100, 8), (200, 8) and (300, 8) were in T2,
    should Alice be included in the result?

  3. #3
    Join Date
    Mar 2012
    Posts
    10

    re: help with two table query

    Sorry I meant Jill should not be included

  4. #4
    Join Date
    Mar 2012
    Posts
    10

    re: help with two table query

    no I dont need Alice in the result thanks

  5. #5
    Join Date
    Mar 2012
    Posts
    10

    Re: help with two table query

    Table T1 has the following data:

    ID1 NAME
    1 Steve
    2 Joe
    3 Bob
    4 Julie
    5 Harry
    6 Fred
    7 Jill

    Table T2 has the following data:
    SCORE ID2
    300 4
    120 6
    12 3
    140 6
    543 1
    455 2
    311 3
    555 6
    234 5

    Write an SQL query to show all the names in the T1 table that do not have cumulative scores of at least 450 (note that Jill should be included in such a list as she has taken no tests at all and therefore not scored!). The ID2 column in the T2 table matches the ID1 column in the T1 table.

    So far I have come up with this SQL query I just need someone to point me in the right direction to see if I am right

    select NAME
    FROM tablet1 a, tablet2 b
    WHERE a.id1 = b.id2
    AND SCORE < 450;

  6. #6
    Join Date
    Mar 2012
    Posts
    10

    Re: help with two table query

    Table T1 has the following data:

    ID1 NAME
    1 Steve
    2 Joe
    3 Bob
    4 Julie
    5 Harry
    6 Fred
    7 Jill

    Table T2 has the following data:
    SCORE ID2
    300 4
    120 6
    12 3
    140 6
    543 1
    455 2
    311 3
    555 6
    234 5

    Write an SQL query to show all the names in the T1 table that do not have cumulative scores of at least 450 (note that Jill should be included in such a list as she has taken no tests at all and therefore not scored!). The ID2 column in the T2 table matches the ID1 column in the T1 table.

    So far I have come up with this SQL query I just need someone to point me in the right direction to see if I am right

    select NAME
    FROM tablet1 a, tablet2 b
    WHERE a.id1 = b.id2
    AND SCORE < 450;

  7. #7
    Join Date
    Mar 2012
    Posts
    10

    Re: help with two table query

    Table T1 has the following data:

    ID1 NAME
    1 Steve
    2 Joe
    3 Bob
    4 Julie
    5 Harry
    6 Fred
    7 Jill

    Table T2 has the following data:
    SCORE ID2
    300 4
    120 6
    12 3
    140 6
    543 1
    455 2
    311 3
    555 6
    234 5

    Write an SQL query to show all the names in the T1 table that do not have cumulative scores of at least 450 (note that Jill should be included in such a list as she has taken no tests at all and therefore not scored!). The ID2 column in the T2 table matches the ID1 column in the T1 table.

    So far I have come up with this SQL query I just need someone to point me in the right direction to see if I am right

    select NAME
    FROM tablet1 a, tablet2 b
    WHERE a.id1 = b.id2
    AND SCORE < 450;

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well why not try running the query
    ...and seeing if it meets the requirement.

    if there are errors
    ...try and analyse why there are errors
    ...and attempt to fix the faults
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    please don't multi post the same thread in different forums
    if you think you may have posted in the wring forum then use the 'report post' option next to #xxx(permalink) and rerquest the thread be moved

    if you don't think you have got an adequate reply then consider if you have posted it incorrectly, if your post is unclear or confusing, if so consider re posting in the same thread but with a revised text

    and /or bump the thread up the list again using ***BUMP*** as the post text
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2012
    Posts
    10

    Re: need some help with two table query

    Table T1 has the following data:

    ID1 NAME
    1 Steve
    2 Joe
    3 Bob
    4 Julie
    5 Harry
    6 Fred
    7 Jill

    Table T2 has the following data:
    SCORE ID2
    300 4
    120 6
    12 3
    140 6
    543 1
    455 2
    311 3
    555 6
    234 5

    Write an SQL query to show all the names in the T1 table that do not have cumulative scores of at least 450 (note that Jill should be included in such a list as she has taken no tests at all and therefore not scored!). The ID2 column in the T2 table matches the ID1 column in the T1 table.

    So far I have come up with this SQL query I just need someone to point me in the right direction to see if I am right

    select NAME
    FROM tablet1 a, tablet2 b
    WHERE a.id1 = b.id2
    AND SCORE < 450;

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Posting the exaxt same question over and over again will not give you any more answer. The opposite will happen.

  12. #12
    Join Date
    Mar 2012
    Posts
    10

    re: help with two table query

    Ok many thanks

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    I want to clarify your requirements more.

    (1) Who in T1 should be included in the result?
    You wrote "Jill should be included ...".
    How about others in T1?
    Steve
    Joe
    Bob
    Julie
    Harry
    Fred

    (2) If (8, Alice) was in T1 and (100, 8), (200, 8) and (300, 8) were in T2,
    should Alice be included in the result?
    Could you answer my questions?

    It is better to understand the requirements for me to show sample data(of tables) and expected results from the sample data,
    than many words, phrases, sentences or more literary.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tonkuma View Post
    ... than many words, phrases, sentences or more literary.
    but... but... it's his homework assignment!!

    i.e. he wasn't given the result set that he's expected to produce!!

    dear marques, you asked "someone to point me in the right direction"

    that pointer is: outer join

    you probably won't receive much more help from us until you make another attempt based on that hint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... it's his homework assignment!!
    Really!
    So, I have nothing to say.
    I may be better to stop comment more.

    I'll watch this thread silently, now.

Posting Permissions

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