Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2004
    Posts
    55

    Unanswered: Need help extending a SELECT query to avoid duplicate records

    I need some help forming a query

    I have 3 Tables...

    Table1: Users
    Fields: UserID Int
    Username Varchar(50)

    Table2: User_Categories
    Fields: User_CatID Int
    User_ID Int

    Table3: Categories
    Fields: CatID Int
    Catname Varchar(100)


    Now consider Table 3 has the following values

    CatID | Catname
    1 | cat1
    2 | cat2
    3 | cat3
    4 | cat4
    5 | cat5
    6 | cat6
    7 | cat7
    8 | cat8
    9 | cat9


    Now I need to select users who fall into the categories with CatID 5, 1, 3. Also users can fall into more than one category.

    The users should be sorted by the 1st the users who fall under 5, then users who fall under 1 and then the users who fall under 3 and then finally by the userID in the descending order.


    SELECT userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    ORDER BY userid DESC;

    How do I extend this to get the above needed result? Any help will be highly appreciated.
    Last edited by oldie123; 09-26-05 at 14:36.

  2. #2
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Try something like this.

    SELECT catname, userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    ORDER BY CASE when catid = 5 then 1 when catid = 1 then 2 else 3 end, userid DESC;

  3. #3
    Join Date
    Dec 2004
    Posts
    55
    So thi sis what CASE is used for

    Thanks a lot.

    One more question... does this query include duplicate records? If YES then how do I avoid duplicate records?

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Duplicate records

    Depends what you mean by duplicate records.

    If you use a 'distinct' that will ensure that there are no duplications of the combination of catname, userid, username.

    SELECT distinct catname, userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    ORDER BY CASE when catid = 5 then 1 when catid = 1 then 2 else 3 end, userid DESC;

    If you are instead meaning no duplicate occurances of a userid (that is, if a user appears in catid = 5 and catid = 1, you want to show only the user in catid = 5?) that would be more difficult.

  5. #5
    Join Date
    Dec 2004
    Posts
    55
    If you are instead meaning no duplicate occurances of a userid (that is, if a user appears in catid = 5 and catid = 1, you want to show only the user in catid = 5?) that would be more difficult.
    This was what I wanted.

    I think I'll use ASP to solve that problem. Thanks for all your help acg_ray.

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    methinks you still have to decide which one of the two CatID's you wanna show on the single row output, after you've decided that you could use something like.

    SELECT Max(CatID), catname, userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    GROUP BY catname, userid, username
    ORDER BY CASE when catid = 5 then 1 when catid = 1 then 2 else 3 end, userid DESC;

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Dec 2004
    Posts
    55
    Quote Originally Posted by GWilliy
    methinks you still have to decide which one of the two CatID's you wanna show on the single row output, after you've decided that you could use something like.

    SELECT Max(CatID), catname, userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    GROUP BY catname, userid, username
    ORDER BY CASE when catid = 5 then 1 when catid = 1 then 2 else 3 end, userid DESC;

    GW
    Thanks for your reply GWilliy. There is something wrong with this query.

    SELECT Max(CatID), catname, userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    GROUP BY catname, userid, username
    ORDER BY CASE when catid = 5 then 1 when catid = 1 then 2 else 3 end, userid DESC;
    corrected should be
    SELECT Max(User_CatID), catname, userid, username
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    GROUP BY catname, userid, username
    ORDER BY CASE when User_CatID = 5 then 1 when User_CatID = 1 then 2 else 3 end, userid DESC;
    The above query give me the error

    Column name 'User_Categories.User_CatID' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

    So I added User_Categories.User_CatID to the groupny clause and the records were still repeated.

    I would appreciate any help

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Maybe if you post the table DDL

    Read the sticky hint
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2004
    Posts
    55
    Hi Brett,

    Below is the information you requested. Please let know if you need more information.

    I have 3 Tables...

    CREATE TABLE Users (
    UserID Int PRIMARY KEY,
    Username Varchar(50) NOT NULL
    )


    CREATE TABLE User_Categories (
    User_ID Int NOT NULL,
    User_CatID Int NOT NULL
    )


    CREATE TABLE Categories (
    CatID Int PRIMARY KEY,
    Catname Varchar(100) NOT NULL
    )


    INSERT INTO Categories (CatID, Catname) VALUES (1, 'cat1')
    INSERT INTO Categories (CatID, Catname) VALUES (2, 'cat2')
    INSERT INTO Categories (CatID, Catname) VALUES (3, 'cat3')
    INSERT INTO Categories (CatID, Catname) VALUES (4, 'cat4')
    INSERT INTO Categories (CatID, Catname) VALUES (5, 'cat5')
    INSERT INTO Categories (CatID, Catname) VALUES (6, 'cat6')
    INSERT INTO Categories (CatID, Catname) VALUES (7, 'cat7')
    INSERT INTO Categories (CatID, Catname) VALUES (8, 'cat8')
    INSERT INTO Categories (CatID, Catname) VALUES (9, 'cat9')


    INSERT INTO Users (UserID, Username) VALUES (1, 'user1')
    INSERT INTO Users (UserID, Username) VALUES (2, 'user2')
    INSERT INTO Users (UserID, Username) VALUES (3, 'user3')
    INSERT INTO Users (UserID, Username) VALUES (4, 'user4')


    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (1, 1)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (1, 3)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (1, 5)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (2, 7)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (2, 1)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (3, 1)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (3, 5)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (3, 7)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (4, 2)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (4, 3)
    INSERT INTO User_Categories (User_ID, User_CatID) VALUES (4, 7)


    So now I need to select distinct users who fall into the categories with CatID 5, 1, 7, 3 and the users should be sorted by first by the categories selected and then by the UserID. Duplicate users are avoided.

    So for users who fall under CatID 5, 7, 1 the result should be

    UserID | Username | User_CatID
    -------------------------------
    1 | user1 | cat5
    3 | user3 | cat5
    2 | user2 | cat7
    4 | user4 | cat7


    So above the results are sorted by (a) the CatID order and (b) UserID

    Any help will be appreciated.
    Last edited by oldie123; 09-26-05 at 16:30.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think you're mistaken about User # 3

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myUsers99 (
    UserID Int PRIMARY KEY,
    Username Varchar(50) NOT NULL
    )
    
    CREATE TABLE myUser_Categories99 (
    User_ID Int NOT NULL,
    User_CatID Int NOT NULL
    )
    
    CREATE TABLE myCategories99 (
    CatID Int PRIMARY KEY,
    Catname Varchar(100) NOT NULL
    )
    
    
    INSERT INTO myCategories99 (CatID, Catname) VALUES (1, 'cat1')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (2, 'cat2')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (3, 'cat3')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (4, 'cat4')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (5, 'cat5')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (6, 'cat6')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (7, 'cat7')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (8, 'cat8')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (9, 'cat9')
    
    
    INSERT INTO myUsers99 (UserID, Username) VALUES (1, 'user1')
    INSERT INTO myUsers99 (UserID, Username) VALUES (2, 'user2')
    INSERT INTO myUsers99 (UserID, Username) VALUES (3, 'user3')
    INSERT INTO myUsers99 (UserID, Username) VALUES (4, 'user4')
    
    
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (1, 1)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (1, 3)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (1, 5)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (2, 7)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (2, 1)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (3, 1)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (3, 5)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (3, 7)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (4, 2)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (4, 3)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (4, 7)
    
    
      SELECT UserID, Username, MAX(User_CatID) 
        FROM myUser_Categories99 a 
        JOIN myUsers99 b 
          ON a.User_ID = b.UserID 
        JOIN myCategories99 c 
          ON a.User_CatID = c.CatID
    GROUP BY UserID, Username
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myUser_Categories99, myUsers99, myCategories99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Wink

    Ref :"Maybe if you post the table DDL, Read the sticky hint"

    O Go on then Brett - tell us wot the sticky hint is then - lol

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Lol

    Sorry just found it 8-)

    http://www.dbforums.com/t1196943.html

    PS thanks for correcting me Oldie ! - not absolutely crap for Freehand though - I'm afraid I'm Guilty (possibly like many others) of just throwing the SQL at existing Schema's until it works

    GW
    Last edited by GWilliy; 09-26-05 at 19:56.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  13. #13
    Join Date
    Dec 2004
    Posts
    55
    Thanks for your reply.

    Quote Originally Posted by Brett Kaiser
    I think you're mistaken about User # 3

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myUsers99 (
    UserID Int PRIMARY KEY,
    Username Varchar(50) NOT NULL
    )
    
    CREATE TABLE myUser_Categories99 (
    User_ID Int NOT NULL,
    User_CatID Int NOT NULL
    )
    
    CREATE TABLE myCategories99 (
    CatID Int PRIMARY KEY,
    Catname Varchar(100) NOT NULL
    )
    
    
    INSERT INTO myCategories99 (CatID, Catname) VALUES (1, 'cat1')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (2, 'cat2')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (3, 'cat3')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (4, 'cat4')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (5, 'cat5')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (6, 'cat6')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (7, 'cat7')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (8, 'cat8')
    INSERT INTO myCategories99 (CatID, Catname) VALUES (9, 'cat9')
    
    
    INSERT INTO myUsers99 (UserID, Username) VALUES (1, 'user1')
    INSERT INTO myUsers99 (UserID, Username) VALUES (2, 'user2')
    INSERT INTO myUsers99 (UserID, Username) VALUES (3, 'user3')
    INSERT INTO myUsers99 (UserID, Username) VALUES (4, 'user4')
    
    
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (1, 1)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (1, 3)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (1, 5)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (2, 7)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (2, 1)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (3, 1)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (3, 5)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (3, 7)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (4, 2)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (4, 3)
    INSERT INTO myUser_Categories99 (User_ID, User_CatID) VALUES (4, 7)
    
    
      SELECT UserID, Username, MAX(User_CatID) 
        FROM myUser_Categories99 a 
        JOIN myUsers99 b 
          ON a.User_ID = b.UserID 
        JOIN myCategories99 c 
          ON a.User_CatID = c.CatID
    GROUP BY UserID, Username
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myUser_Categories99, myUsers99, myCategories99
    GO
    I tried the above.

    Maybe what I wanted was not very clear.

    Consider I wanted all the users who fall under CatID's 5, 1, 7, 3. The users should be sorted by the 1st the users who fall under 5, then users who fall under 1 and then the users who fall under 7 and then the users who fall under 3 and then finally by the userID in the descending order. acg_ray query solves the problem

    At the same time I want a user who falls under 2 or more categories to only appear once. acg_ray query doesn't solve the duplicate users problem.

  14. #14
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    No duplicate output in this but I'm wary it may not be the right way to do this - but maybe worth a try

    SELECT userid, username, Max(User_Categories.User_CatID)
    FROM Users, User_Categories
    WHERE userid=user_catid AND user_catID IN (5, 1, 3)
    GROUP BY userid, username
    ORDER BY CASE when MAX(User_CatID) = 5 then 1 when Max(User_CatID) = 1 then 2 else 3 end, userid DESC;

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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