Re: SQL Select Question

[Don Mellen <donm@ONTKO.COM>]

Donnel,

Given two tables like...
  Table A   Table B
  -------   -------
        1         1
        2         2
        3         3
        4

Your code would return 9 rows: 1 / 1 / 2 / 2 / 3 / 3 / 4 / 4 / 4.
I don't think this is what you intended.  I beleive Duncan will need to
use a sub-select to check for existence, a "not exists (select 'x' ...)",
or a "not in (select key ....)".

HTH,
Don

On Thu, 9 Jul 1998, Donnell Rogers wrote:
> Many other answers may be more elegant.  I opted for
>  SQR  (the bang-sign is the comment indicator and a few other details, but
> other-wise it's the same)
>  and Brute force:
>
> begin-select
> A.PKEY                              ! don't select any PKEYs from B
> (,other A.fields)
> from Table_A A,               ! using aliases to keep things tidy
>         Table_B  B
> where A.PKEY <> B.PKEY    !  or '!=' if you prefer
>          ! and other conditions  (e.g. ' and A.PKEY >= 100')  to
>          ! limit the selection
> end-select
>
> of course, really-really brutish force would select only for the keys you
> asked for.  ' where A.PKEY in ( 101, 105, 107, 111)'
>
> good luck in your choices.
>
> Donnell.Rogers@orst.edu <mailto:Donnell.Rogers@orst.edu>
>         -----Original Message-----
>         From:   C. Duncan Hudson [SMTP:c_duncan_hudson@YAHOO.COM]
>         Sent:   Thursday, July 09, 1998 7:34 AM
>         To:     Multiple recipients of list SQR-USERS
>         Subject:        SQL Select Question
>
>         I realize that this is really an SQL question, and not an SQR
> question
>         but I was hoping that with everyone's expertise here I could get a
>         quick (and satisfactory) answer.
>
>         Here's my question.  I want to create a report that selects about 10
>         columns from a single table.  The selection should be based upon
> field
>         values within that table.  Also (and here's where I'm having
>         problems), I need to look at another table to see if the first
> table's
>         primary key is represented in it.  If it is, I want to exclude it
> from
>         my selection.  Do you follow that?  Here's a crude visual:
>
>         TABLE A                 TABLE B
>         100                     98
>         101                     100
>         105                     108
>         107                     110
>         110
>         111
>
>         In the above example I would want to select 101, 105, 107 and 111
> from
>         Table A.  Can anyone give me a hand?  Thanks in advance,
>
>
>         Dunc.
>
>
>
>
>         _________________________________________________________
>         DO YOU YAHOO!?
>         Get your free @yahoo.com address at http://mail.yahoo.com
>

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"