Subquery in a Left Join?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Subquery in a Left Join?

  1. #1
    John Pierson Guest

    Subquery in a Left Join?


    I am looking for a way to do the following:

    SELECT A.ID, A.Field1, B.Field2
    FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 = "B")
    B ON A.Field1 = B.Field1;

    This short example of what I'm trying to accomplish, but I get a syntax error.
    I am starting to think that maybe my approach is wrong. If you can't tell
    by the statement, I need ALL the values in the first Table joined with the
    matched values from only a select portion of Table2.

    The only other way I can think to do this is to create a query of the portion
    of Table 2, then run this select statement with Table1 against that query
    instead of Table2. This is actually an undesirable, but possible solution.

    Any help would be much appreciated, even if it's to tell me it's not possible.

    Thanks.

    John

  2. #2
    John Pierson Guest

    Re: Subquery in a Left Join?


    "Colin McGuigan" <colin@chicor.com> wrote:
    >John Pierson <john_pierson@iname.com> wrote in message
    >news:3a660a86$1@news.devx.com...
    >>
    >> I am looking for a way to do the following:
    >>
    >> SELECT A.ID, A.Field1, B.Field2
    >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 = "B")
    >> B ON A.Field1 = B.Field1;

    >
    >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    >LEFT JOIN Table2 B ON A.Field1 = B.Field1
    >WHERE B.Field2 = "B"
    >
    >--
    >Colin McGuigan
    >


    This doesn't accomplish what I'm looking to do since the WHERE clause runs
    AFTER the data is pulled. Let me put in sum hypothetical data and show the
    results I wish to get that this does not do.

    Table1.ID Table1.Field1 Table2.Field1 Table2.Field2
    1 5/5 5/5 A
    2 5/6 5/7 B
    3 5/7 5/7 C
    4 5/8 5/6 B


    Results your suggestion gives:

    2 5/6 B
    3 5/7 B

    Results I want to get:

    5/5 <null>
    5/6 B
    5/7 B
    5/8 <null>

    Does this clarify it?

    John

  3. #3
    Colin McGuigan Guest

    Re: Subquery in a Left Join?

    John Pierson <john_pierson@iname.com> wrote in message
    news:3a660a86$1@news.devx.com...
    >
    > I am looking for a way to do the following:
    >
    > SELECT A.ID, A.Field1, B.Field2
    > FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 = "B")
    > B ON A.Field1 = B.Field1;


    SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    LEFT JOIN Table2 B ON A.Field1 = B.Field1
    WHERE B.Field2 = "B"

    --
    Colin McGuigan




  4. #4
    Kevin Downs Guest

    Re: Subquery in a Left Join?

    Try this

    SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    LEFT OUTER JOIN Table2 B
    ON A.Field1 = B.Field1 AND B.Field2 = "B"

    --
    Regards,
    Kevin

    "John Pierson" <john_pierson@iname.com> wrote in message
    news:3a660a86$1@news.devx.com...
    >
    > I am looking for a way to do the following:
    >
    > SELECT A.ID, A.Field1, B.Field2
    > FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 = "B")
    > B ON A.Field1 = B.Field1;
    >
    > This short example of what I'm trying to accomplish, but I get a syntax

    error.
    > I am starting to think that maybe my approach is wrong. If you can't tell
    > by the statement, I need ALL the values in the first Table joined with the
    > matched values from only a select portion of Table2.
    >
    > The only other way I can think to do this is to create a query of the

    portion
    > of Table 2, then run this select statement with Table1 against that query
    > instead of Table2. This is actually an undesirable, but possible solution.
    >
    > Any help would be much appreciated, even if it's to tell me it's not

    possible.
    >
    > Thanks.
    >
    > John




  5. #5
    John Pierson Guest

    Re: Subquery in a Left Join?


    "Join Expression not supported"


    "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >Try this
    >
    >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    >LEFT OUTER JOIN Table2 B
    >ON A.Field1 = B.Field1 AND B.Field2 = "B"
    >
    >--
    >Regards,
    >Kevin
    >
    >"John Pierson" <john_pierson@iname.com> wrote in message
    >news:3a660a86$1@news.devx.com...
    >>
    >> I am looking for a way to do the following:
    >>
    >> SELECT A.ID, A.Field1, B.Field2
    >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 = "B")
    >> B ON A.Field1 = B.Field1;
    >>
    >> This short example of what I'm trying to accomplish, but I get a syntax

    >error.
    >> I am starting to think that maybe my approach is wrong. If you can't

    tell
    >> by the statement, I need ALL the values in the first Table joined with

    the
    >> matched values from only a select portion of Table2.
    >>
    >> The only other way I can think to do this is to create a query of the

    >portion
    >> of Table 2, then run this select statement with Table1 against that query
    >> instead of Table2. This is actually an undesirable, but possible solution.
    >>
    >> Any help would be much appreciated, even if it's to tell me it's not

    >possible.
    >>
    >> Thanks.
    >>
    >> John

    >
    >



  6. #6
    John Pierson Guest

    Re: Subquery in a Left Join?


    Unfortunately it's an Access 95 database (hey, I didn't create it, I just
    have to deal with it).


    "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >Ah yes, forgot you may not be using a db that supports SQL92 compliant join
    >statements.
    >What DB (and version) are you using?
    >--
    >Regards,
    >Kevin
    >
    >"John Pierson" <john_pierson@iname.com> wrote in message
    >news:3a664ac8$1@news.devx.com...
    >>
    >> "Join Expression not supported"
    >>
    >>
    >> "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >> >Try this
    >> >
    >> >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    >> >LEFT OUTER JOIN Table2 B
    >> >ON A.Field1 = B.Field1 AND B.Field2 = "B"
    >> >
    >> >--
    >> >Regards,
    >> >Kevin
    >> >
    >> >"John Pierson" <john_pierson@iname.com> wrote in message
    >> >news:3a660a86$1@news.devx.com...
    >> >>
    >> >> I am looking for a way to do the following:
    >> >>
    >> >> SELECT A.ID, A.Field1, B.Field2
    >> >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 =

    "B")
    >> >> B ON A.Field1 = B.Field1;
    >> >>
    >> >> This short example of what I'm trying to accomplish, but I get a syntax
    >> >error.
    >> >> I am starting to think that maybe my approach is wrong. If you can't

    >> tell
    >> >> by the statement, I need ALL the values in the first Table joined with

    >> the
    >> >> matched values from only a select portion of Table2.
    >> >>
    >> >> The only other way I can think to do this is to create a query of the
    >> >portion
    >> >> of Table 2, then run this select statement with Table1 against that

    query
    >> >> instead of Table2. This is actually an undesirable, but possible

    >solution.
    >> >>
    >> >> Any help would be much appreciated, even if it's to tell me it's not
    >> >possible.
    >> >>
    >> >> Thanks.
    >> >>
    >> >> John
    >> >
    >> >

    >>

    >
    >



  7. #7
    Kevin Downs Guest

    Re: Subquery in a Left Join?

    Ah yes, forgot you may not be using a db that supports SQL92 compliant join
    statements.
    What DB (and version) are you using?
    --
    Regards,
    Kevin

    "John Pierson" <john_pierson@iname.com> wrote in message
    news:3a664ac8$1@news.devx.com...
    >
    > "Join Expression not supported"
    >
    >
    > "Kevin Downs" <kdowns@optushome.com.au> wrote:
    > >Try this
    > >
    > >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    > >LEFT OUTER JOIN Table2 B
    > >ON A.Field1 = B.Field1 AND B.Field2 = "B"
    > >
    > >--
    > >Regards,
    > >Kevin
    > >
    > >"John Pierson" <john_pierson@iname.com> wrote in message
    > >news:3a660a86$1@news.devx.com...
    > >>
    > >> I am looking for a way to do the following:
    > >>
    > >> SELECT A.ID, A.Field1, B.Field2
    > >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 = "B")
    > >> B ON A.Field1 = B.Field1;
    > >>
    > >> This short example of what I'm trying to accomplish, but I get a syntax

    > >error.
    > >> I am starting to think that maybe my approach is wrong. If you can't

    > tell
    > >> by the statement, I need ALL the values in the first Table joined with

    > the
    > >> matched values from only a select portion of Table2.
    > >>
    > >> The only other way I can think to do this is to create a query of the

    > >portion
    > >> of Table 2, then run this select statement with Table1 against that query
    > >> instead of Table2. This is actually an undesirable, but possible

    solution.
    > >>
    > >> Any help would be much appreciated, even if it's to tell me it's not

    > >possible.
    > >>
    > >> Thanks.
    > >>
    > >> John

    > >
    > >

    >




  8. #8
    Kevin Downs Guest

    Re: Subquery in a Left Join?

    Yuk.. in which case, your query method is probably the only way to go (sorry)

    --
    Regards,
    Kevin

    "John Pierson" <john_pierson@iname.com> wrote in message
    news:3a66509f$1@news.devx.com...
    >
    > Unfortunately it's an Access 95 database (hey, I didn't create it, I just
    > have to deal with it).
    >
    >
    > "Kevin Downs" <kdowns@optushome.com.au> wrote:
    > >Ah yes, forgot you may not be using a db that supports SQL92 compliant join
    > >statements.
    > >What DB (and version) are you using?
    > >--
    > >Regards,
    > >Kevin
    > >
    > >"John Pierson" <john_pierson@iname.com> wrote in message
    > >news:3a664ac8$1@news.devx.com...
    > >>
    > >> "Join Expression not supported"
    > >>
    > >>
    > >> "Kevin Downs" <kdowns@optushome.com.au> wrote:
    > >> >Try this
    > >> >
    > >> >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    > >> >LEFT OUTER JOIN Table2 B
    > >> >ON A.Field1 = B.Field1 AND B.Field2 = "B"
    > >> >
    > >> >--
    > >> >Regards,
    > >> >Kevin
    > >> >
    > >> >"John Pierson" <john_pierson@iname.com> wrote in message
    > >> >news:3a660a86$1@news.devx.com...
    > >> >>
    > >> >> I am looking for a way to do the following:
    > >> >>
    > >> >> SELECT A.ID, A.Field1, B.Field2
    > >> >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2 =

    > "B")
    > >> >> B ON A.Field1 = B.Field1;
    > >> >>
    > >> >> This short example of what I'm trying to accomplish, but I get a

    syntax
    > >> >error.
    > >> >> I am starting to think that maybe my approach is wrong. If you can't
    > >> tell
    > >> >> by the statement, I need ALL the values in the first Table joined with
    > >> the
    > >> >> matched values from only a select portion of Table2.
    > >> >>
    > >> >> The only other way I can think to do this is to create a query of the
    > >> >portion
    > >> >> of Table 2, then run this select statement with Table1 against that

    > query
    > >> >> instead of Table2. This is actually an undesirable, but possible

    > >solution.
    > >> >>
    > >> >> Any help would be much appreciated, even if it's to tell me it's not
    > >> >possible.
    > >> >>
    > >> >> Thanks.
    > >> >>
    > >> >> John
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  9. #9
    Martin Guest

    Re: Subquery in a Left Join?


    try using iif

    SELECT A.FIELD2, A.Field1, IIF( B.FIELD2 = "B", B.FIELD2, NULL)
    FROM Table1 AS A LEFT JOIN TABLE2 AS B ON A.Field1 = B.Field1;


    "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >Yuk.. in which case, your query method is probably the only way to go (sorry)
    >
    >--
    >Regards,
    >Kevin
    >
    >"John Pierson" <john_pierson@iname.com> wrote in message
    >news:3a66509f$1@news.devx.com...
    >>
    >> Unfortunately it's an Access 95 database (hey, I didn't create it, I just
    >> have to deal with it).
    >>
    >>
    >> "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >> >Ah yes, forgot you may not be using a db that supports SQL92 compliant

    join
    >> >statements.
    >> >What DB (and version) are you using?
    >> >--
    >> >Regards,
    >> >Kevin
    >> >
    >> >"John Pierson" <john_pierson@iname.com> wrote in message
    >> >news:3a664ac8$1@news.devx.com...
    >> >>
    >> >> "Join Expression not supported"
    >> >>
    >> >>
    >> >> "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >> >> >Try this
    >> >> >
    >> >> >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    >> >> >LEFT OUTER JOIN Table2 B
    >> >> >ON A.Field1 = B.Field1 AND B.Field2 = "B"
    >> >> >
    >> >> >--
    >> >> >Regards,
    >> >> >Kevin
    >> >> >
    >> >> >"John Pierson" <john_pierson@iname.com> wrote in message
    >> >> >news:3a660a86$1@news.devx.com...
    >> >> >>
    >> >> >> I am looking for a way to do the following:
    >> >> >>
    >> >> >> SELECT A.ID, A.Field1, B.Field2
    >> >> >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2

    =
    >> "B")
    >> >> >> B ON A.Field1 = B.Field1;
    >> >> >>
    >> >> >> This short example of what I'm trying to accomplish, but I get a

    >syntax
    >> >> >error.
    >> >> >> I am starting to think that maybe my approach is wrong. If you

    can't
    >> >> tell
    >> >> >> by the statement, I need ALL the values in the first Table joined

    with
    >> >> the
    >> >> >> matched values from only a select portion of Table2.
    >> >> >>
    >> >> >> The only other way I can think to do this is to create a query of

    the
    >> >> >portion
    >> >> >> of Table 2, then run this select statement with Table1 against that

    >> query
    >> >> >> instead of Table2. This is actually an undesirable, but possible
    >> >solution.
    >> >> >>
    >> >> >> Any help would be much appreciated, even if it's to tell me it's

    not
    >> >> >possible.
    >> >> >>
    >> >> >> Thanks.
    >> >> >>
    >> >> >> John
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  10. #10
    John Pierson Guest

    Re: Subquery in a Left Join?


    Already tried that one, doesn't give me the correct results, or rather, it
    doesn't give me the results I'm looking for.

    Hypothetical data, but similar to reality

    Table1.Field1 Table2.Field1 Table2.Field2
    3/5 3/5 A
    4/5 4/5 B
    5/5 3/5 B
    6/5 5/5 C

    Results:
    3/5 <null>
    3/5 B
    4/5 B
    5/5 <null>

    As you can see, it duplicates the 3/5, in reality that 3/5 is going to have
    multiple matches.

    I think Kevin is right and I am stuck with creating and storing a query to
    use against table1 with the LEFT JOIN.

    Thank you both for your suggestions and help.

    John

    "Martin" <martin_boyle@hotmail.com> wrote:
    >
    >try using iif
    >
    >SELECT A.FIELD2, A.Field1, IIF( B.FIELD2 = "B", B.FIELD2, NULL)
    >FROM Table1 AS A LEFT JOIN TABLE2 AS B ON A.Field1 = B.Field1;
    >
    >
    >"Kevin Downs" <kdowns@optushome.com.au> wrote:
    >>Yuk.. in which case, your query method is probably the only way to go (sorry)
    >>
    >>--
    >>Regards,
    >>Kevin
    >>
    >>"John Pierson" <john_pierson@iname.com> wrote in message
    >>news:3a66509f$1@news.devx.com...
    >>>
    >>> Unfortunately it's an Access 95 database (hey, I didn't create it, I

    just
    >>> have to deal with it).
    >>>
    >>>
    >>> "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >>> >Ah yes, forgot you may not be using a db that supports SQL92 compliant

    >join
    >>> >statements.
    >>> >What DB (and version) are you using?
    >>> >--
    >>> >Regards,
    >>> >Kevin
    >>> >
    >>> >"John Pierson" <john_pierson@iname.com> wrote in message
    >>> >news:3a664ac8$1@news.devx.com...
    >>> >>
    >>> >> "Join Expression not supported"
    >>> >>
    >>> >>
    >>> >> "Kevin Downs" <kdowns@optushome.com.au> wrote:
    >>> >> >Try this
    >>> >> >
    >>> >> >SELECT A.ID, A.Field1, B.Field2 FROM Table1 A
    >>> >> >LEFT OUTER JOIN Table2 B
    >>> >> >ON A.Field1 = B.Field1 AND B.Field2 = "B"
    >>> >> >
    >>> >> >--
    >>> >> >Regards,
    >>> >> >Kevin
    >>> >> >
    >>> >> >"John Pierson" <john_pierson@iname.com> wrote in message
    >>> >> >news:3a660a86$1@news.devx.com...
    >>> >> >>
    >>> >> >> I am looking for a way to do the following:
    >>> >> >>
    >>> >> >> SELECT A.ID, A.Field1, B.Field2
    >>> >> >> FROM Table1 A LEFT JOIN (SELECT * FROM Table2 WHERE Table2.Field2

    >=
    >>> "B")
    >>> >> >> B ON A.Field1 = B.Field1;
    >>> >> >>
    >>> >> >> This short example of what I'm trying to accomplish, but I get

    a
    >>syntax
    >>> >> >error.
    >>> >> >> I am starting to think that maybe my approach is wrong. If you

    >can't
    >>> >> tell
    >>> >> >> by the statement, I need ALL the values in the first Table joined

    >with
    >>> >> the
    >>> >> >> matched values from only a select portion of Table2.
    >>> >> >>
    >>> >> >> The only other way I can think to do this is to create a query

    of
    >the
    >>> >> >portion
    >>> >> >> of Table 2, then run this select statement with Table1 against

    that
    >>> query
    >>> >> >> instead of Table2. This is actually an undesirable, but possible
    >>> >solution.
    >>> >> >>
    >>> >> >> Any help would be much appreciated, even if it's to tell me it's

    >not
    >>> >> >possible.
    >>> >> >>
    >>> >> >> Thanks.
    >>> >> >>
    >>> >> >> John
    >>> >> >
    >>> >> >
    >>> >>
    >>> >
    >>> >
    >>>

    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center