Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    17

    Unanswered: noob alert! What i thought was a real simple query...

    Hi

    I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
    I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

    By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.

    Code:
    SELECT itemNumber FROM itemNumberSet
    WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
    ORDER BY itemNumber
    Thanks for any suggestions you might have.
    J

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this perhaps?
    Code:
    SELECT itemNumberSet.itemNumber 
      FROM itemNumberSet
    LEFT OUTER
      JOIN itemUPCtable
        ON SUBSTRING(itemUPCtable.UPCcode,9,3) = itemNumberSet.itemNumber
     WHERE SUBSTRING(itemUPCtable.UPCcode,9,3) IS NULL
    ORDER
        BY itemNumberSet.itemNumber
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    17

    Thanks man.

    I really need to practice playing with joins, cause as it stands im a hack. I should really be taking a course or something. I see you are in Toronto. I'm in Mississauga. Any tips as to where one might go to be properly educated in SQL?

    Thanks again
    J

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can go a long way with online free sql tutorials

    however, you need to make sure you are on an actual tutorial site, as in here's-some-good-information-because-i-love-sql type of tutorial site, rather than here's-some-sql-stuff-which-i-lifted-from-somewhere-so-i-can-run-a-lot-of-ads-and-make-money type of tutorial site

    there are a couple of good sites listed here: http://r937.com/links.cfm?links=sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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