Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    SQL Comparison Statement


    Hi.

    I'm painfully thickheaded when it comes to querying so I hope that you can bear with me. My question revolves around a simple database. I've uploaded it to Megaupload.com (?d=3CMTKXEO) but since I'm considered a new account I can't post the url straight out. However you should be able to snag it up using the auth code above. You can have a look at the structure and understand what I'm referring to below...

    Basically the Duties table has duty names and unique IDs. The Signup table references those unique ids. I put five of the duties in the Signup table and left out the duty with the unique ID of '5' on purpose.

    If I wanted to match the Signup table against the Duties table to find out which duty is missing, how would I do do that? I'd really like to understand how the statement works rather get the answer straight away (that way I can understand the logic/syntax and not have to bug anyone further).

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    75
    Originally Posted by zenassassin
    Hi.

    I'm painfully thickheaded when it comes to querying so I hope that you can bear with me. My question revolves around a simple database. I've uploaded it to Megaupload.com (?d=3CMTKXEO) but since I'm considered a new account I can't post the url straight out. However you should be able to snag it up using the auth code above. You can have a look at the structure and understand what I'm referring to below...

    Basically the Duties table has duty names and unique IDs. The Signup table references those unique ids. I put five of the duties in the Signup table and left out the duty with the unique ID of '5' on purpose.

    If I wanted to match the Signup table against the Duties table to find out which duty is missing, how would I do do that? I'd really like to understand how the statement works rather get the answer straight away (that way I can understand the logic/syntax and not have to bug anyone further).

    Thanks.
    Hi,
    Don't know if you have seen this:
    http://www.w3schools.com/sql/sql_join.asp
    Take a look at the subsequent Inner, Left, Right,etc Joins also in case you have not seen the above link.

    Also give a thought to something like:
    tblduties.dutyid <> tblsignup.dutyid

    Thanks
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0
    Originally Posted by recyan
    Hi,
    Don't know if you have seen this:
    http://www.w3schools.com/sql/sql_join.asp
    Take a look at the subsequent Inner, Left, Right,etc Joins also in case you have not seen the above link.

    Also give a thought to something like:
    tblduties.dutyid <> tblsignup.dutyid

    Thanks
    I tried that but it didn't produce the value(s) I was expecting. Specifically I tried:

    SELECT *
    FROM Duties, Signup
    WHERE Signup.dID <> Duties.dID

    That produced about 15 records or so when I was hoping it would just show the one missing record. I also tried a varition of that on an INNER JOIN that produced the same results.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    Try this.
    Code:
    SELECT *
    FROM Signup s
    LEFT JOIN Duties d on s.dID = d.dID
    WHERE d.id is null
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0
    Originally Posted by Vomster
    Try this.
    Code:
    SELECT *
    FROM Signup s
    LEFT JOIN Duties d on s.dID = d.dID
    WHERE d.id is null
    I tried...

    SELECT *
    FROM Signup s
    LEFT JOIN Duties d ON s.dID = d.dID
    WHERE d.dID is null

    ... but the return was empty.
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    Code:
    SELECT Duties.*, Signup.*
    FROM Duties LEFT JOIN Signup ON Duties.dID = Signup.dID
    WHERE Signup.dID IS NULL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0
    Originally Posted by Vomster
    Code:
    SELECT Duties.*, Signup.*
    FROM Duties LEFT JOIN Signup ON Duties.dID = Signup.dID
    WHERE Signup.dID IS NULL
    Worked like a charm. Thanks!

    So basically the IS NULL value was a huge part of me not understanding how to get the result I was trying for. Thanks again for the assistance. It's much appreciated.
  14. #8
  15. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    I would say the [IS NULL] in addition to [LEFT JOIN].

    If you didn't know....

    From wikipedia:
    The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0
    Originally Posted by Vomster
    I would say the [IS NULL] in addition to [LEFT JOIN].

    If you didn't know....

    From wikipedia:
    That is good to know. JOINS have always made me skittish because it's hard for me to visualize what's going on at times.

    Just to pick your brain a bit further, if I wanted to sort this query even further how exactly is that done? The query above does what it's supposed to and returns the missing record but let's say there are multiple events. Some have a different amount of assigned duties than others. I tried the following but it didn't return anything (and I suspect why):

    SELECT Duties.*, Signup.*
    FROM Duties LEFT JOIN Signup ON Duties.dID = Signup.dID
    WHERE Signup.dID IS NULL AND Signup.eID = 1

    Is it possible to sort it by event and get the missing duties(s) or am I just making things too complicated for myself? I uploaded an updated version of the test DB with multiple events and members (megaupload.com/?d=Z5IEXVF8). Thanks again for all your assistance.
  18. #10
  19. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    The reason nothing showed up was because the one that is missing is 5. So if you were specifically looking to see if value 1 was missing then your query would be correct.

    As far as sorting is concerned, you can add the following to the end of your query.

    Code:
    ORDER BY [field1],[field2]
    You can use one field or many fields.

    Look here
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0
    Originally Posted by Vomster
    The reason nothing showed up was because the one that is missing is 5. So if you were specifically looking to see if value 1 was missing then your query would be correct.
    Hmm that one went over my head I think. I'm okay with SORT BY and GROUP BY. I've worked with those before. The problem I'm having is singling out a specific event and checking to see what duties are missing.

    In the example I have posted there are two faux events (with IDs of 1 and 2 respectively). Their IDs are stored in a (eID) column in the Signup table. The Signup table also has the Duty IDs stored there as well (dID). Needless to say if multiple duties are assigned to one event there are a lot of duplicates of that event ID. I'm working with ASP so the event ID will be coming in as a variable. For the sake of the example I made the variable set at 1 - meaning I just want the results from scanning through the duties that are assigned to the event with the ID of 1.

    My apologies if this is becoming frustrating for you. I used to tutor in college so I know how it is when someone keeps asking questions. I have solved several other problems I was having with SQL statements already on my own but this one is just irking the keck out of me. Your time and patience is well appreciated. Trust me.
  22. #12
  23. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    Post the tables here on the forum and tell me what you are trying to accomplish exactly.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0
    Signup


    Duties


    Events


    Members


    What I want to do is select only the highlighted values in the Signup table and check them against the Duties table to find out which value is missing. (In the example of event #1 it's duty 5 that's missing.)
  26. #14
  27. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    Substitute a variable where you see the 1.
    Code:
    SELECT Duties.*
    FROM Duties
    WHERE dID NOT IN  (SELECT Signup.dID FROM Signup WHERE Signup.eID = 1)

    Comments on this post

    • zenassassin agrees
    • recyan agrees
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by Vomster
    Code:
    SELECT Duties.*
    FROM Duties
    WHERE dID NOT IN  (SELECT Signup.dID FROM Signup WHERE Signup.eID = 1)
    much nicer alternative --
    Code:
    SELECT Duties.*
      FROM Duties
    LEFT OUTER
      JOIN Signup
        ON Signup.dID = Duties.dID 
       AND Signup.eID = 1
     WHERE Signup.dID IS NULL

    Comments on this post

    • Vomster agrees : You just gotta show me up... ;)
    • recyan agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo