Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: need help with a query

    Hello,

    Here is my problem:

    I have 2 tables. USERS (userId, userName) and FOLLOWLIST (whoId, whomId).

    I want to create a suggestion list.

    userId - userName
    1 - user1
    2 - user2
    3 - user3
    4 - user4
    5 - user5
    6 - user6
    7 - user7

    whoId - whomId
    1 - 2
    1 - 3
    2 - 3
    2 - 4
    2 - 5
    3 - 5
    3 - 7


    The suggestion list for user1 should be: user4, user5, user7

    Thank for all help

    Kind regards
    Last edited by edemirler; 09-13-10 at 10:54.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have you had a go? Post the best you have\ can come up with and I'll help you tweak it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    I was prepearing the correct explanation. Hope it is more clear now

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    All clear.

    Have you had a go? I promise you that you will learn more by trying and failing than by us just handing you the answer.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2010
    Posts
    3
    Yep i come up with:

    SELECT K.* FROM users AS K WHERE K.userId IN
    (SELECT DISTINCT TL.whomId FROM followlist AS TL WHERE TL.whoId IN
    (SELECT TKL.whomId FROM followlist AS TKL WHERE TKL.whoId=1))


    Any suggestions?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's an easier method I'm sure.
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.FOLLOWLIST')) 
    BEGIN
        DROP TABLE dbo.FOLLOWLIST
    END
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.USERS')) 
    BEGIN
        DROP TABLE dbo.USERS
    END
    
    CREATE TABLE dbo.USERS
        (
            userId      INT                                                 NOT NULL
          , userName    VARCHAR(11)         COLLATE Latin1_General_CI_AS    NOT NULL
          , CONSTRAINT pk_USERS PRIMARY KEY CLUSTERED (userId) WITH (FILLFACTOR = 100)
        ) ON [PRIMARY]
    GO
    
    CREATE TABLE dbo.FOLLOWLIST
        (
            whoId      INT                                                  NOT NULL
          , whomId     INT                                                  NOT NULL 
          , CONSTRAINT pk_FOLLOWLIST PRIMARY KEY CLUSTERED (whoId, whomId) WITH (FILLFACTOR = 100)
          , CONSTRAINT fk_FOLLOWLIST_USERS FOREIGN KEY (whoId) REFERENCES dbo.USERS (userId)
          , CONSTRAINT fk_FOLLOWLIST_USERS2 FOREIGN KEY (whomId) REFERENCES dbo.USERS (userId)
        ) ON [PRIMARY]
    GO
    
    INSERT INTO USERS
    VALUES  (1, 'user1')
          , (2, 'user2')
          , (3, 'user3')
          , (4, 'user4')
          , (5, 'user5')
          , (6, 'user6')
          , (7, 'user7')
    
    INSERT INTO FOLLOWLIST
    VALUES  (1, 2)
          , (1, 3)
          , (2, 3)
          , (2, 4)
          , (2, 5)
          , (3, 5)
          , (3, 7)
          
    SELECT  DISTINCT recomendation_name.userName
    FROM    dbo.FOLLOWLIST  AS follower
    INNER JOIN 
            dbo.USERS       AS follower_name
    ON  follower_name.userId            = follower.whoId
    INNER JOIN 
            dbo.FOLLOWLIST  AS recommendations
    ON  recommendations.whoId           = follower.whomId
    INNER JOIN 
            dbo.USERS       AS recomendation_name
    ON  recomendation_name.userId       = recommendations.whomId
    WHERE   follower_name.userName      = 'user1'
        AND NOT EXISTS (
                            SELECT  *
                            FROM    dbo.FOLLOWLIST  AS exclusion
                            WHERE   exclusion.whoId             = follower_name.userId
                                AND exclusion.whomId            = recommendations.whomId
                        )
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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