select statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: select statement

  1. #1
    Pat Guest

    select statement


    Hi,

    I am trying to select all the records in table A which is not in table B.
    It will be simple if we have only one joining field.
    Select * from A where A.col1 not in (select B.col1 from B where B...).

    But my joining field are 2 columns instead, I tried
    Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    B WHERE) seems illegal.

    Has anybody done this before?

    Thanks
    Pat

  2. #2
    David Satz Guest

    Re: select statement

    you can do:

    Select *
    from A
    where not exists ( select 1
    from B
    where A.col1 = B.col1 )

    Select *
    from A
    left outer join B ON A.col1 = B.col1
    where B.col1 is null

    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions
    { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Pat" <pwang@uclink.berkeley.edu> wrote in message
    news:3c8f7bed$1@10.1.10.29...
    >
    > Hi,
    >
    > I am trying to select all the records in table A which is not in table B.
    > It will be simple if we have only one joining field.
    > Select * from A where A.col1 not in (select B.col1 from B where B...).
    >
    > But my joining field are 2 columns instead, I tried
    > Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    > also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    > B WHERE) seems illegal.
    >
    > Has anybody done this before?
    >
    > Thanks
    > Pat




  3. #3
    Pat Guest

    Re: select statement


    Thanks David:

    I tried no 1 works, no 2 not.

    Pat

    "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    >you can do:
    >
    >Select *
    >from A
    >where not exists ( select 1
    > from B
    > where A.col1 = B.col1 )
    >
    >Select *
    >from A
    >left outer join B ON A.col1 = B.col1
    >where B.col1 is null
    >
    >--
    >HTH,
    >David Satz
    >Principal Web Engineer
    >Hyperion Solutions
    >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS

    }
    >(Please reply to group only - emails answered rarely)
    >-----------------------------------------------------------------
    >"Pat" <pwang@uclink.berkeley.edu> wrote in message
    >news:3c8f7bed$1@10.1.10.29...
    >>
    >> Hi,
    >>
    >> I am trying to select all the records in table A which is not in table

    B.
    >> It will be simple if we have only one joining field.
    >> Select * from A where A.col1 not in (select B.col1 from B where B...).
    >>
    >> But my joining field are 2 columns instead, I tried
    >> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    >> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    >> B WHERE) seems illegal.
    >>
    >> Has anybody done this before?
    >>
    >> Thanks
    >> Pat

    >
    >



  4. #4
    Vishwas Guest

    Re: select statement


    Hi , Pat I Beleive this is quite Correct
    Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    >>> B WHERE)


    This is what ur trying instead u try

    select * from a where (a.col,a.col2) in (select b.col1,b.col2 from .....

    I hv done nothing , just a Bracket is there...try it.

    Regards
    Vishwas




    "Pat" <pwang@uclink.berkeley.edu> wrote:
    >
    >Thanks David:
    >
    >I tried no 1 works, no 2 not.
    >
    >Pat
    >
    >"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    >>you can do:
    >>
    >>Select *
    >>from A
    >>where not exists ( select 1
    >> from B
    >> where A.col1 = B.col1 )
    >>
    >>Select *
    >>from A
    >>left outer join B ON A.col1 = B.col1
    >>where B.col1 is null
    >>
    >>--
    >>HTH,
    >>David Satz
    >>Principal Web Engineer
    >>Hyperion Solutions
    >>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {

    VSS
    >}
    >>(Please reply to group only - emails answered rarely)
    >>-----------------------------------------------------------------
    >>"Pat" <pwang@uclink.berkeley.edu> wrote in message
    >>news:3c8f7bed$1@10.1.10.29...
    >>>
    >>> Hi,
    >>>
    >>> I am trying to select all the records in table A which is not in table

    >B.
    >>> It will be simple if we have only one joining field.
    >>> Select * from A where A.col1 not in (select B.col1 from B where B...).
    >>>
    >>> But my joining field are 2 columns instead, I tried
    >>> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    >>> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    >>> B WHERE) seems illegal.
    >>>
    >>> Has anybody done this before?
    >>>
    >>> Thanks
    >>> Pat

    >>
    >>

    >



  5. #5
    Kudel Guest

    Re: select statement


    This is what you must do..

    select *
    from a
    where a.col1 not in
    (
    select b.col1
    from b
    where b.col2 not like a.col2
    )


    ..if you want it to work

    -Kudel


    "Vishwas" <vbansal@ecomserver.com> wrote:
    >
    >Hi , Pat I Beleive this is quite Correct
    >Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    >>>> B WHERE)

    >
    > This is what ur trying instead u try
    >
    >select * from a where (a.col,a.col2) in (select b.col1,b.col2 from .....
    >
    >I hv done nothing , just a Bracket is there...try it.
    >
    >Regards
    >Vishwas
    >
    >
    >
    >
    >"Pat" <pwang@uclink.berkeley.edu> wrote:
    >>
    >>Thanks David:
    >>
    >>I tried no 1 works, no 2 not.
    >>
    >>Pat
    >>
    >>"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    >>>you can do:
    >>>
    >>>Select *
    >>>from A
    >>>where not exists ( select 1
    >>> from B
    >>> where A.col1 = B.col1 )
    >>>
    >>>Select *
    >>>from A
    >>>left outer join B ON A.col1 = B.col1
    >>>where B.col1 is null
    >>>
    >>>--
    >>>HTH,
    >>>David Satz
    >>>Principal Web Engineer
    >>>Hyperion Solutions
    >>>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {

    >VSS
    >>}
    >>>(Please reply to group only - emails answered rarely)
    >>>-----------------------------------------------------------------
    >>>"Pat" <pwang@uclink.berkeley.edu> wrote in message
    >>>news:3c8f7bed$1@10.1.10.29...
    >>>>
    >>>> Hi,
    >>>>
    >>>> I am trying to select all the records in table A which is not in table

    >>B.
    >>>> It will be simple if we have only one joining field.
    >>>> Select * from A where A.col1 not in (select B.col1 from B where B...).
    >>>>
    >>>> But my joining field are 2 columns instead, I tried
    >>>> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    >>>> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2

    from
    >>>> B WHERE) seems illegal.
    >>>>
    >>>> Has anybody done this before?
    >>>>
    >>>> Thanks
    >>>> Pat
    >>>
    >>>

    >>

    >



  6. #6
    Kudel Guest

    Re: select statement


    A "not" to much in the last one..

    select *
    from a
    where a.col1 not in
    (
    select b.col1
    from b
    where b.col2 like a.col2
    )


    -Kudel


  7. #7
    russel Guest

    Re: select statement


    how about this?

    select * from A
    left join B
    on A.col1=B.col1 and A.col2=B.col2
    Where B.col3 is null


    "Pat" <pwang@uclink.berkeley.edu> wrote:
    >
    >Hi,
    >
    >I am trying to select all the records in table A which is not in table B.
    >It will be simple if we have only one joining field.
    >Select * from A where A.col1 not in (select B.col1 from B where B...).
    >
    >But my joining field are 2 columns instead, I tried
    >Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    >also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    >B WHERE) seems illegal.
    >
    >Has anybody done this before?
    >
    >Thanks
    >Pat



  8. #8
    Steve Guest

    Re: select statement


    Try concatenating two fields in the tables.

    Example

    Table A Table B
    Col_1 Col_2 Col_3 Col_1 Col_2 Col_3
    A 1 A1 A 2 A2
    B 2 B2 B 2 B2

    Then it's simple to compare:
    select *
    from A
    where A.Col_3 != B.Col_3 and
    A.Col_1 = B.Col_1
    It should give you your exceptions

    Hope it helps.


    "Pat" <pwang@uclink.berkeley.edu> wrote:
    >
    >Hi,
    >
    >I am trying to select all the records in table A which is not in table B.
    >It will be simple if we have only one joining field.
    >Select * from A where A.col1 not in (select B.col1 from B where B...).
    >
    >But my joining field are 2 columns instead, I tried
    >Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
    >also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
    >B WHERE) seems illegal.
    >
    >Has anybody done this before?
    >
    >Thanks
    >Pat



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