SQL Query - Review actions table and show/omit records

sql , db2 , query Los Angeles, United States
  • 8 years ago

    Hello all,

    I have two tables, one list an items main information, and the other table holds all of the actions for those items (creation date, closed date, etc....)

    Here is how the tables are structured....

    * MAIN TABLE* * ACTIONS TABLE*

    ID              STATUS                                                 ID                  ACTIONTYPE     ACTIONTIME
    ---------     --------------                                               ----------        -------------------      -------------------
    ID0001                  01                                              ID00001                           99                     00:01
    ID0002                  07                                              ID00001                           85                     00:05
    ID0003                  08                                              ID00001                           84                     00:04
    ID0004                  01                                              ID00002                           99                     00:01
    ID0005                  07                                              ID00002                           85                     00:05
    ID0006                  08                                              ID00002                           77                     00:04
          etc...                                                                                              etc...
    

    As you can see, the actions table is structured to create a record with the main id everytime an action is performed on it.

    What I would like to do is create a query that would only show ID's that don't have a certain action. For instance, I would like to show all ID's that never had an actiontype of "77". This means that ID00001 should be the only item shown in my results. Currently if i use ...."WHERE ACTIONTYPE <> '77'", both ID00001 and ID00002 will show up in the results because there are plenty of other records with different action types. Can anyone assist me?

  • 8 years ago

    please ignore the previous one

  • 8 years ago
    SELECT DISTINCT ID FROM [MAIN TABLE] WHERE ID NOT IN(
    SELECT DISTINCT ID FROM [ACTIONS TABLE] WHERE ACTIONTYPE=77)
    

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Before software should be reusable, it should be usable.” - Ralph Johnson