Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: impossible SQL query???

    I've just taken over the duties of reporting on a rather sloppy inventory system with missing chunks of data here and there. This missing data has brought a loop hole to my queries.....

    I have two tables:
    Workstat Table
    WS_NUM
    NAME
    TYPE

    Items Table
    WS_NUM
    ASSET
    PUR_PRICE
    WARRANTY
    KIND

    The two tables are inner joined @ WS_NUM and I need to query 'WS_NUM', 'NAME', 'TYPE', 'ASSET', 'PUR_PRICE', 'WARRANTY', & 'KIND' It is a one to many relationship with Workstat being 1 and Items being many. I need to see all of this data only for Items of 'Kind' = Computer. Here's where the problem occurs: some entries in 'Workstat' do not have corresponding entries in Items of 'Kind' = Computer, so how do I only display the 'WS_NUM' and 'NAME' for these special cases as opposed to what my query does now - not display any entry that does not have an associated Item of 'KIND' = computer. Any help would be extremely helpful. Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: impossible SQL query???

    Originally posted by williamd
    I've just taken over the duties of reporting on a rather sloppy inventory system with missing chunks of data here and there. This missing data has brought a loop hole to my queries.....

    I have two tables:
    Workstat Table
    WS_NUM
    NAME
    TYPE

    Items Table
    WS_NUM
    ASSET
    PUR_PRICE
    WARRANTY
    KIND

    The two tables are inner joined @ WS_NUM and I need to query 'WS_NUM', 'NAME', 'TYPE', 'ASSET', 'PUR_PRICE', 'WARRANTY', & 'KIND' It is a one to many relationship with Workstat being 1 and Items being many. I need to see all of this data only for Items of 'Kind' = Computer. Here's where the problem occurs: some entries in 'Workstat' do not have corresponding entries in Items of 'Kind' = Computer, so how do I only display the 'WS_NUM' and 'NAME' for these special cases as opposed to what my query does now - not display any entry that does not have an associated Item of 'KIND' = computer. Any help would be extremely helpful. Thanks
    Use an outer join:

    SELECT w.ws_num, ..., i.kind
    FROM workstat w
    LEFT OUTER JOIN items i
    ON w.ws_num = i.ws_num;

  3. #3
    Join Date
    Jul 2003
    Location
    The Netherlands
    Posts
    1
    I was working on a same kind of problem today. In my case an outer join did not work, because we have a db in ORACLE 8 .
    Our dba gave me the following statement for ORACLE 8 that is equal to an outer join (I translate it to you case);

    SELECT w.ws_num, ..., i.kind
    FROM workstat w, items i
    WHERE w.ws_num = i.ws_num (+);

  4. #4
    Join Date
    Jul 2003
    Posts
    2
    Problem Solved! In order to show the information in the 1st table given that data is missing from the right table, you must do a LEFT JOIN. However, you cannot do a left join if you are selecting based on criteria in the 2nd table. I ended up performing a nested SELECT statement and setting an alias for a subquery

    SELECT table1.contents alias.contents
    FROM table1
    (SELECT table2.contents
    FROM table2 LEFT JOIN
    WHERE table2.contents = 'criteria') alias
    ON table1.valuetojoin = alias.valuetojoin
    WHERE table1 criteria

    maybe it'll help you as well

Posting Permissions

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