#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    18
    Rep Power
    0

    SQL Count and compare


    I need some help figuring out a query, so i will do my best to explain. Hopefully DevShed can point me in the right direction.

    Table A
    [Kit_CODE] [ITEM_CODE]
    71124 71101
    71124 71102
    71124 71103
    71124 71104
    71124 71105
    71124 71106
    86104 86102
    86104 86103
    86104 86101
    86104 86121


    Table B
    [ORDER_NO] [ITEM_CODE] [KIT_CODE]
    76858 86102 86104
    76858 86103 86104
    76858 86101 86104
    76858 86121 86104
    76858 86102 86104
    76863 71124 71124
    76863 71101 71124
    76863 71102 71124
    76863 71103 71124
    76863 71104 71124
    76863 71105 71124
    76863 71106 71124

    Table B contains the order information.

    So my question is how can i compare a kit_code from Table B to see if it contains all of the items in table a? Then display an order number that does not contain all of the [item_code] related to that specific [kit_code]?

    *EDIT*
    Code:
    SELECT [KIT_CODE], COUNT(DISTINCT [ITEM_CODE])
      FROM [TABLE_A]
      GROUP BY [KIT_CODE]
      GO
      
    SELECT [ORDER_NO], COUNT([KIT_CODE])
      FROM [TABLE_B]
      GROUP BY [ORDER_NO]
      GO
    Running this gives me a count of each table, but how do i compare them and find which ORDER_NO has less the what is counted in TABLE_A?
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Does this work for you?
    Code:
    SELECT * 
    FROM Table A a 
    LEFT JOIN Table B b on b.kit_code = a.kit_code and  b.item_code = a.item_code
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10

    More importantly


    Why is your database built like this, why do you need item code in the order table - table A lists all the items in a kit, so if a customer orders a kit you don't need to list all the items, you just need to structure a query that will pull all the items from table a based on the kit when you need it

    It will make your database less cumbersome and more efficient

IMN logo majestic logo threadwatch logo seochat tools logo