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.
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
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
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
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.
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
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
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
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
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
