Results 1 to 4 of 4

Thread: combining query results

  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Red face combining query results

    Good Evening,

    I have the following query:

    select distinct inc_cat_sc from inc_cat
    that returns these results:

    3RD PARTY CAUSE
    ACCESS
    ACCESS DENIED
    APPROVE
    AUTHORIZATION

    I also have this query:
    select inc_cat_sc from inc_cat where inc_cat_sc NOT IN
    (select distinct
    inc_cat.inc_cat_sc
    from
    inc_cat inner join incident on incident.cause_id = inc_cat.inc_cat_id
    where
    inc_cat.inc_cat_id <> 0)
    That returns these results:
    3RD PARTY CAUSE
    ACCESS

    Then I have a 3rd query:
    select inc_cat_sc from inc_cat where inc_cat_sc NOT IN
    (select distinct
    inc_cat.inc_cat_sc
    from
    inc_cat inner join incident on incident.inc_cat_id = inc_cat.inc_cat_id
    where inc_cat.inc_cat_id <> 0)
    That returns:
    ACCESS
    ACCESS DENIED

    I need a query that will give me all the values in the first query that are not in either the 2nd query or the 3rd query.
    So the results I would be looking for are:

    APPROVE
    AUTHORIZATION

    Any suggestions are greatly appreciated.

    Thanks in Advance,
    Di-

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Possible to post related table schema?

  3. #3
    Join Date
    Feb 2009
    Posts
    5

    Here's the scheme

    Here's the scheme for both tables. It may be helpful to note that in the incident table both inc_cat_id and cause_id are from the inc_cat table.

    Thank you so much for the reply!

    Di-
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2009
    Posts
    5

    I got it

    I figured this out. Here's the query that worked.

    <--
    select distinct inc_cat_sc from inc_cat where inc_cat_sc not in
    ((select distinct
    inc_cat.inc_cat_sc
    from
    inc_cat inner join incident on incident.cause_id = inc_cat.inc_cat_id
    where
    inc_cat.inc_cat_id <> 0)
    union
    (select distinct
    inc_cat.inc_cat_sc
    from
    inc_cat inner join incident on incident.inc_cat_id = inc_cat.inc_cat_id
    where
    inc_cat.inc_cat_id <> 0))
    -->

Posting Permissions

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