SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question filter distinct values based on 3 or more names (was "Hectic Query HELP")

    Hi all any idea how to write a query to successfully achieve this ? I need a query that brings back all the distinct values for ALL the values in the table and then filters those distinct values based on 3 or more names :

    SELECT Distinct SerialNumber, SMSDateStamp,
    OperatorID FROM smslog where smslog.operatorid IN
    (Select operatorid from smslog where operatorid IN('BON','EVAS','WIL2'))

    Something like this but this query causes mysql to crash because its very wrong I am sure.

    HELP ! any ideas will be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    SELECT Distinct SerialNumber, SMSDateStamp,
    OperatorID FROM smslog where smslog.operatorid IN ('BON','EVAS','WIL2')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude u are the king and i am a supreme idiot. thanks man

  4. #4
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wait I have tried the query like that originally but it only brings back the distinct records for the thre users that are in the where criteria. It has to first bring back all the distinct values and then only filter the records. If the query is done as you listed above the records returned are not correct.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please give a few sample rows of data that illustrate what you're trying to do

    i'm pretty sure my query returns exactly the same as the one you posted, so there must be something else you're trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query returns data that is correct without a doubt. its just not totally correct becuase when the where clause is used it will only bring back distinct serialnumbers for the the users that are specified in the where clause. The problem with this is that there might be other serialnumbers under other user names that are not removed by the distinct statement as these users are not in the where section.

    This is the problem I have. Is there any way to write a sub query like the dodge one that i showed. ?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please give a few sample rows of data that illustrate what you're trying to do

    i still don't get it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically the serial number must be unique for each user stored in the databaase. So if there is 10 users in the system, there are duplicate serialnumbers often for multiple users. IE User1 might have used a serial number 3 times and another guy might have used it twice as well. so when querying like we have spoken about with the where criteria the user who has used the same serial number as is not listed in the where criteria does not return the correct amount of distinct numbers.

    If i was to do this in code with VB for example I would return all distinct records into a listview and then remove all the names that do not match the users I am searching for.

    Its quite difficult for me to give you a few example rows as you can only see whats happening when filtering the whole table which is 50000 + records. I have included some rows but I doubt it will help. It took me a week to understand what the client needed with this search. We have tested running a distinct serial query on the whole db and then copying three users names out and totalling the number of records. We then ran the query with the where clause and found out that the two totals dont' match. its all so confusing

    Serial, Date, Name

    '3980', '2005-08-13 12:15:09', 'WIL2'
    '3822', '2005-08-22 13:23:13', 'WIL2'
    '4041', '2005-08-28 08:19:01', 'WIL2'
    '4040', '2005-08-31 21:02:21', 'WIL2'
    '4063', '2005-09-06 16:01:56', 'WIL2'
    '4202', '2005-09-08 11:04:41', 'WIL2'
    '3567', '2005-09-11 10:06:11', 'WIL2'
    '3825', '2005-08-15 12:03:40', 'WIL2'
    '4189', '2005-08-17 10:53:47', 'WIL2'
    '3983', '2005-08-19 10:50:49', 'WIL2'
    '4488', '2005-08-27 11:24:18', 'WIL2'

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    lost

    i am so totally lost

    "so when querying like we have spoken about with the where criteria the user who has used the same serial number as is not listed in the where criteria does not return the correct amount of distinct numbers."

    i read that three times and it still doesn't make sense

    sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that was fairly confusing, my apologies. This wont be much better

    if I run this query :

    SELECT Distinct SerialNumber, SMSDateStamp,
    OperatorID FROM smslog
    Where OperatorID IN ('BON','WIL2', 'EVAS') and SMSDateStamp Between '2005-01-01 00:00:00' and '2006-01-01 23:59:59' Group By 'SerialNumber' Order By 'operatorid'

    i get 251 rows returned.

    if I run this query :

    SELECT Distinct SerialNumber, SMSDateStamp,
    OperatorID FROM smslog
    Where SMSDateStamp Between '2005-01-01 00:00:00' and '2006-01-01 23:59:59' Group By 'SerialNumber' Order By 'operatorid'

    and copy and paste each users records into excel and total them up I get 217 rows.

    This is because a user IE User2 might have used a serialnumber before BON. If he used the serialnumber before BON then that number should not be displayed for BON. But when I run the first query it brings back distinct serialnumbers only for the users in the where clause, so if someone not included in the where clause used a serialnumber before a user listed in the where clause then it brings back a row is not directly linked to him. Basically the first user to use a serialnumber is the user that should only show up when running distinct.

    That is so confusing I think I will now jump out the window.


Bookmarks

Posting Permissions

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