sql question

Discussion in 'OT Technology' started by dexedrine, Jul 1, 2003.

  1. dexedrine

    dexedrine Guest

    Anyways, I’m having a SQL problem right now. I’m developing an application for receiving and sales. Currently I have two tables, one is the "scan" table, which is the items as they are scanned (that's why it's called scan). It has a unique 'id' field that is auto incrementing. Also, there's a serial, partid, PO #, and some other things. The other table is called "sales", which obviously has the sales. This is the one we are most concerned about. In the sales, I have the following columns. Unique auto incrementing id column, iid which is the item id, which refers back to the 'scan' table's id column, the date, serial, partid, returned (bit), datereturned, invoice, customer, and validated.

    Now what I'm trying to do is to find out which items are still in stock. Now I'm thinking to take the rows from 'sales' that have returned = 0 and some how not include them while SELECTing from 'scan'. I've tried a few different things, however whatever I do, and it doesn't quite work out well.

    Anybody have any clue what I should do here?
     
  2. Early Apex

    Early Apex Guest

    select * from scan where id not in (select iid from sales where returned = 0)

    if your RDBMS allows subqueries.
     
  3. dexedrine

    dexedrine Guest

    :bowdown:

    awesome thanks man.

    i know a pretty decent amount of SQL, why the hell didn't i know this? where did you learn about this stuff?
     
  4. Early Apex

    Early Apex Guest

    Let's put it this way: if you put "SQL expert" on your resume, and you get a job because of that, you have to learn SQL pretty quickly ;)
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I do have a suggestion for you by the way - linking fields within tables should have the same name. It's not mandatory but it makes for a much cleaner layout. So a unique Id for items should always be referred to ItemsID. That way when you do link fields to each other, you aren't confusing yourself what should be linked and how the field is referred to in each table. Also, it's redundant to have a date returned and a returned boolean. If it hasn't been returned, the value will be null, if it hasn't, it won't be. You may take a slight hit on performance when searching or indexing, but you save yourself from having to update two fields every time that value changes.
     
  6. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    EDIT: nevermind
     
    Last edited: Jul 2, 2003

Share This Page