SQL Query - Review actions table and show/omit records

• • Reply

  10 years ago

  by ovomaster

  DALWIN MALLIGNA

  los angeles, United States

  Joined 10 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?

  Report abuse
• • Reply

  10 years ago

  by renjithgnair

  Renjith Nair

  Bangalore, India

  Joined 10 years ago

  please ignore the previous one

  Report abuse
• • Reply

  10 years ago

  by renjithgnair

  Renjith Nair

  Bangalore, India

  Joined 10 years ago

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

  Report abuse