Professional Web Applications Themes

SELECT to find items NOT common in a table - Microsoft SQL / MS SQL Server

SELECT * FROM PArts WHERE PKID NOT IN(SELECT FKDEVICE FROM INVENTORY) Also you can use EXISTS (check books online) "Michael Ingram (TAC)" <mdingramcisco.com> wrote in message news:ok7mgv0jj89nfk465pnrgapdfa9hs1sj5p4ax.com... > Hi, > > I need a query to solve the following MS SQL 7.0 T-SQL > > > > I have two tables, PARTS and INVENTORY > > Fields in Parts are PKID,NAME,DESCRIPTION,PRICE > > Fields in INVENTORY are PKID,FKDEVICE,NAME,DESCRIPTION... > > For referential integrity, INV.FKDEVICE equal PARTS.PKID > > Every part in Inventory is in the master PARTS database. > > I need a select statement to tell me which parts ...

  1. #1

    Default Re: SELECT to find items NOT common in a table

    SELECT * FROM PArts
    WHERE PKID NOT IN(SELECT FKDEVICE FROM INVENTORY)

    Also you can use EXISTS (check books online)



    "Michael Ingram (TAC)" <mdingramcisco.com> wrote in message
    news:ok7mgv0jj89nfk465pnrgapdfa9hs1sj5p4ax.com...
    > Hi,
    >
    > I need a query to solve the following MS SQL 7.0 T-SQL
    >
    >
    >
    > I have two tables, PARTS and INVENTORY
    >
    > Fields in Parts are PKID,NAME,DESCRIPTION,PRICE
    >
    > Fields in INVENTORY are PKID,FKDEVICE,NAME,DESCRIPTION...
    >
    > For referential integrity, INV.FKDEVICE equal PARTS.PKID
    >
    > Every part in Inventory is in the master PARTS database.
    >
    > I need a select statement to tell me which parts are NOT present
    > in inventory.
    >
    > For example,
    >
    > SELECT PKID FROM PARTS WHERE NAME LIKE 'ABC%'
    >
    > will list for me all parts that start as name ABC
    >
    > Say that this returns five records.
    >
    >
    > SELECT FKDEVICE FROM INVENTORY WHERE NAME LIKE 'ABC%'
    >
    > will list all parts in inventory starting with ABC
    >
    > Say that this returns four records.
    >
    > How can I write a query to list the single part that is not in
    > inventory but is in parts?
    >
    > Due to other existing factors, I need to make this determination by
    > way of the PKID to FKDEVICE relationship...
    >
    >
    > Thanks,
    >
    > Mike

    Ivan Demkovitch Guest

  2. #2

    Default Re: SELECT to find items NOT common in a table

    SELECT PKID FROM PARTS WHERE NAME LIKE 'ABC%'
    AND PKID NOT IN (SELECT FKDEVICE FROM INVENTORY WHERE NAME LIKE 'ABC%' )

    "Michael Ingram (TAC)" <mdingramcisco.com> escribió en el mensaje
    news:ok7mgv0jj89nfk465pnrgapdfa9hs1sj5p4ax.com...
    > Hi,
    >
    > I need a query to solve the following MS SQL 7.0 T-SQL
    >
    >
    >
    > I have two tables, PARTS and INVENTORY
    >
    > Fields in Parts are PKID,NAME,DESCRIPTION,PRICE
    >
    > Fields in INVENTORY are PKID,FKDEVICE,NAME,DESCRIPTION...
    >
    > For referential integrity, INV.FKDEVICE equal PARTS.PKID
    >
    > Every part in Inventory is in the master PARTS database.
    >
    > I need a select statement to tell me which parts are NOT present
    > in inventory.
    >
    > For example,
    >
    > SELECT PKID FROM PARTS WHERE NAME LIKE 'ABC%'
    >
    > will list for me all parts that start as name ABC
    >
    > Say that this returns five records.
    >
    >
    > SELECT FKDEVICE FROM INVENTORY WHERE NAME LIKE 'ABC%'
    >
    > will list all parts in inventory starting with ABC
    >
    > Say that this returns four records.
    >
    > How can I write a query to list the single part that is not in
    > inventory but is in parts?
    >
    > Due to other existing factors, I need to make this determination by
    > way of the PKID to FKDEVICE relationship...
    >
    >
    > Thanks,
    >
    > Mike

    Heberto Ramos Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Can't find Sound files in Common Library
    By Byron Kindig in forum Macromedia Flash
    Replies: 0
    Last Post: December 6th, 04:54 PM
  3. Problem with casting when using common-table-expression
    By Tarek M. Nabil in forum IBM DB2
    Replies: 3
    Last Post: September 23rd, 10:02 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139