Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2009
    Posts
    18

    Unanswered: Convert nested-select into Join's

    How can I turn the following nested-select query into one that has no nested select and uses join only:

    select col11 from t1 where col12 in
    (select col21 from t2 where col22 in
    (select col31 from t3 where col32 = 'value'
    )
    )

    I'm struggling with the challenge here being that:

    t1 can join with t2

    t2 can join with t3

    but t1 and t3 have no "joinable" columns

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What have you covered so far in class? I'd assume by this point in the class session that this would already be "old hat", so I'm having trouble understanding what is confusing you.

    I'm trying to understand what your problem is so that I can help you learn. Give me a few clues and I can probably help!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Pretty simple to just join them
    select col11 from t1
    inner join t2
    on col12 = col21
    inner join t3
    on col22 = col31
    and col32 = 'value'
    dave

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dav1mo View Post
    Pretty simple to just join them
    True, and as long as you'll simply do the homework there is neither incentive nor opportunity to learn.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    plus, it eliminates the opportunity for us to teach

    after all, teaching is even harder than learning, and we relish these n00b homework assignments where we can practise our teaching skillz while not actually handing out the answer

    by the way, dave, i'd give you less than full marks for that answer, it's missing all the table qualifiers

    any query involving more than one table ~must~ qualify all columns used in the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2009
    Posts
    18
    Pat Phelan: I'm not taking any class. I'm just trying to teach myself with books such as "SQL for dummies" which I like the most since it's the easiest for me to understand.

    Dav1mo: Thank you for your example. It looks good, but did not work. Let me explain:

    My objective of my exercise is to get the same result set in each of 3 following approaches:

    (I) Using 3 separate queries each one is a select statement.

    (II) colapse the 3 separate queries above into 1 single query that uses sub-query.

    (III) colapse the 3 separate queries above into 1 single query that use join statement only (no sub-query).


    Here's the queries I used:

    (I) The original 3 separate select's:

    select col11 from tab1 where col12 = value

    result: (to be fed into the next select)
    1
    2
    3

    select col21 from tab2 where col22 in (1,2,3)

    result: (to be fed into the next select)
    a
    a
    b
    b
    c
    c

    select col31 from tab3 where col32 in (a,a,b,b,c,c)
    result:
    x
    y
    z


    (II) Colapse the above 3 separate select's into 1 statement using sub-queries yielded the same result

    select col31 from tab3 where col32 in
    (select col21 from tab2 where col22 in
    (select col11 from tab1 where col12 = value))

    result:
    x
    y
    z

    (III) Colapse the above 3 separate select's into 1 statement using join did not yield the same result:

    select col31 from tab3 t3
    join tab2 t2 on t2.col21 = t3.col31
    join tab1 t1 on t1.col11 = t2.col21
    where t1.col12 = value

    result:
    x
    x
    y
    y
    z
    z

    Why this did NOT yield x, y, z as in (I) and (II)?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by knhcmp View Post
    Why this did NOT yield x, y, z as in (I) and (II)?
    i'm guessing it was because of this --

    a
    a
    b
    b
    c
    c

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2009
    Posts
    18
    I saw that too. But then that didn't explain why the queries used in (I) and (II) did not suffer any duplication--their result is x, y, z and not x, x, y, y, z, z?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the answer is quite simple -- IN works differently than JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2009
    Posts
    18
    Thanks, r937. Does that mean I cannot convert the nested sub-queries in (II) into one that uses join only due to IN and JOIN working differently?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by knhcmp View Post
    Thanks, r937. Does that mean I cannot convert the nested sub-queries in (II) into one that uses join only due to IN and JOIN working differently?
    no, it means that if you use JOIN instead of IN, you must do something else to achieve distinct results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2009
    Posts
    18
    I see. I'll need to work the "DISTINCT" key word into my join somehow.

    Meanwhile, I don't understand why ALL the duplications that meet the "IN" test did NOT show up in the resut set? How is it possible for "IN" to act as if it has a built-in "DISTINCT" already!!!

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    (II) 
    select col31 
    from tab3 
    where col32 in
    	(select col21 
    	from tab2 
    	where col22 in (select col11 
    		from tab1 
    		where col12 = value)
    	)
    
    (III)
    select col31 
    from tab3 t3
    	join tab2 t2 on 
    		t2.col21 = t3.col31
    	join tab1 t1 on 
    		t1.col11 = t2.col21
    where t1.col12 = value
    You were joining different columns.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937 View Post
    the answer is quite simple -- IN works differently than JOIN
    I don't think that's quite accurate

    They probably materialize the IN as a join...without the 1 to M

    IN is doing the EXISTANCE of a thing...the JOIN is doing, well a JOIN.....Like if you had 1 beer you have only 1 row

    If you had 6, you'd be drunk...(or 6 rows...or 6 row barley...which is used to make most american style lagers)
    Last edited by Brett Kaiser; 11-16-09 at 15:12.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Oct 2009
    Posts
    18
    Sorry, actually my query is: (t2.col21 is used only once in the 2nd join)


    select col32 from tab3 t3

    join tab2 t2 on t2.col22 = t3.col31

    join tab1 t1 on t1.col11 = t2.col21

    where t1.col12 = value

Posting Permissions

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