SQL Server Performance

Select Statement help.

Discussion in 'General Developer Questions' started by mhamblin, Jul 14, 2003.

  1. mhamblin New Member

    I am not a SQL guru by any means so I hope you can help me out. I am not even certain this is the place to post this question.

    I have 2 tables, users and weeklypicks.

    users has:
    user_id
    username

    weeklypicks has:
    user_id
    week_id
    teamname

    My problem is this. I need to be able to show all users and their pick for a certain week, even if they have not made a pick for that week. In other words I want to be able to pick a weeknumber and only show the picks users have made for that week along with users that have not yet made a pick for that week. As users make their pick a record is created in the weeklypick table so users that have not yet made a pick will not have a record in that table for that week.

    I have gone around and around and just can't figure it out.

    Any help would be appreciated.

    Thanks.
  2. gaurav_bindlish New Member

    SELECT a.username, teamname
    FROM users a
    LEFT OUTER JOIN weeklypicks b
    ON a.user_id = b.user_id
    WHERE b.week_id = @weekid

    Replace @weekid with the desired week_id.
    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. mhamblin New Member

    That is what I have so far. That only gives me the people that have picks for that week. Now I need the users that have not made a pick or where there is not a record for that user for that specific week.

    Example.
    week# username teamname
    1 bob dallas
    1 sam null

    2 bob null
    2 sam null
  4. gaurav_bindlish New Member

    The reults that u have posted show all the users even if they havn't made a pick. E.g. in week 1 sam does not have a pick but he is shown in the resultset. If this is not what you are looking for, post some sample data from table and expected output.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. mhamblin New Member

    That is exactly what I am looking for. Basically I have a web page that has a drop down list showing the week numbers. When a week is selected I want the page to show all users in the table whether they have a pick or not for that week. I want people to see the team everyone has picked for a specific week as well as show those users that forgot to make a pick.
  6. gaurav_bindlish New Member

    What is the output from the query that I posted? Post some sample data as well.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. mhamblin New Member

    I hope this helps. And if you don't wish to continue with helping out I totally understand. The Query you posted gave me an error but basically this is it. The only thing missing is the where statement with the week ID. Unfortunatly I don't have alot of data in the system yet. If you want I can send you the SQL script and the data somehow.

    SELECT dbo.weeklypick.week_id, dbo.weeklypick.teamname, dbo.users.user_id, dbo.users.username, dbo.users.systemAccount
    FROM dbo.weeklypick RIGHT OUTER JOIN
    dbo.users ON dbo.weeklypick.user_id = dbo.users.user_id


    The Result is as follows.
    week_idteamnameuser_idusername
    1Broncos 2mhamblin
    2Falcons 2mhamblin
    nullnull 3jfalk
    1Panthers 13bhamblin
    nullnull 5chamblin
    nullnull 16asdf

    Here is the data in my users table.

    user_idusername
    2mhamblin
    3jfalk
    13bhamblin
    5chamblin
    16asdf

    My weeklypicks table contains the following. The teampick_id is the unique ID autogenerated for this table. The week_id is linked to another table containing the Weeks in the season. The user_id is the id from the users table and the teamname is the team they picked for this week.

    teampick_idweek_iduser_idteamname
    171 12Broncos
    172 22Falcons
    173 113Panthers

    Based on the data above if the drop down list is for week_id = 1 it should show.
    mhamblinBroncos
    bhamglinPanthers
    jfalkNO PICK
    chamblinNO PICK
    asdfNO PICK

    for Week_id = 2 it should look like this
    mhamblinFalcons
    bhamblinNO PICK
    jfalkNO PICK
    chamblinNO PICK
    asdfNO PICK

    for week_id = 3 - 17 it should look like this
    mhamblinNO PICK or NULL
    bhamblinNO PICK or NULL
    jfalkNO PICK or NULL
    chamblinNO PICK or NULL
    asdfNO PICKor NULL

  8. gaurav_bindlish New Member

    Sorry I was not able to generate the output before. Here you go
    select a.username, ISNULL(b.teamname, 'NO PICK')
    from users a
    left outer join (select * from weeklypicks where week_id = @Week_ID) As B
    on a.userid = b.userid
    Replace Week_ID with the desired week.

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. mhamblin New Member

    Yiiiiiiiiiiiiiiiiiipppppppppppeeeeeeeeeeeee, you are a god. I think that did it for me. Thank You, Thank You!!!!!

    I would like to ask you a question though. Why do you use the a and b? I assume that is just a way to shorten the dbo.weeklypick to a simple form? Forgive my rookieness.

    Once again, thank you. Now I gave you the simple form of what I was after, let me see if I can incorporate it into my more complex statement that I have.

    J
  10. gaurav_bindlish New Member

    Hey chill it buddy. a. and b. is needed because the field usrrid is common in both the tables and so if we specify just userid, the optimizer will not understand which table are we referring to.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page