Multiple Joins. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Joins.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-13 : 10:59:55

Hi,
I am trying to join two tables to a third table, but so far my code isn't working. Here is my code, any ideas why it isn't working?
SELECT TOP varAI * FROM
Game G LEFT JOIN LastPlayed LP ON G.ID = LP.gID
LEFT OUTER JOIN GamePopularity GP ON G.YourPopularity=GP.Popularity
WHERE G.Type=1
AND (((LP.gID IS NULL) OR (LP.LastPlayedDate > varDate)) AND (LP.uID LIKE varUserName))
AND ((GP.gID=G.ID AND GP.uID LIKE varUserName) OR (GP.gID=NULL) OR (GP.gID IS NOT NULL AND GP.uID NOT LIKE varUserName))
Thanks.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

jhermiz

3564 Posts

Posted - 2005-06-13 : 11:03:04

Dont know, since you did not state a sample set, what should be returned, and what shouldn't be returned. You also did not post DDL or any further information to help you out. BTW, I am assuming this is a join in your client application ?

That would help us as well...

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-13 : 11:33:53

It is for a client application written in ASP.NET 2.0 using SQL Server Express as the db backend.
Here is a sample set:
INSERT INTO Game (title, type, description) VALUES (dodgeball, 1, game)
INSERT INTO Game (title, type, description) VALUES (baseball, 1, game)
Results:
ID: 1 Title: Dodgeball Type: 1 Description: Game
ID: 2 Title: Baseball Type: 1 Description: Game
INSERT INTO Popularity (gID, uID, Popularity) VALUES (1, jdoe, 8)
INSERT INTO Popularity (gID, uID, Popularity) VALUES (2, mdoe, 9)
Results:
ID: 1 gID: 1 uID: jdoe Popularity: 8
ID: 2 gID: 2 uID: mdoe Popularity: 9
INSERT INTO LastPlayed (gID, uID, LastPlayedDate) VALUES (1, jdoe, 3/19/1999)
INSERT INTO LastPlayed (gID, uID, LastPlayedDate) VALUES (2, mdoe, 3/20/2005)
Results:
ID: 1 gID: 1 uID: jdoe LastPlayedDate: 3/19/1999
ID: 2 gID: 2 uID: mdoe LastPlayedDate: 3/20/2005

The query now is supposed to get a number from the user (varAI) which states how many games of type 1 they want to see. It then searches for games of type 1 where the game has not been played within the last month by the given user. It also seeks to include in the YourPopularity field of the Games table through a join the rating they gave the game the last time they played it. In the above sample set, the first item should come back. However, currently, something is making no answers come back. One of the options is not giving back any valid results, but I can't for the life of me figure out which one.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

jhermiz

3564 Posts

Posted - 2005-06-13 : 11:44:26

Your joins are ok, take it one step at a time...

First try this:

Posted - 06/13/2005 : 10:59:55 Show Profile Email Poster Visit davidshq's Homepage Reply with Quote
Hi,
I am trying to join two tables to a third table, but so far my code isn't working. Here is my code, any ideas why it isn't working?
SELECT TOP varAI * FROM
Game G LEFT JOIN LastPlayed LP ON G.ID = LP.gID
LEFT JOIN GamePopularity GP ON G.YourPopularity=GP.Popularity
WHERE G.Type=1

...then start tacking on more SQL to it to find out what is causing it. It is more than likely your ANDs and ORs...

I would do this:

Posted - 06/13/2005 : 10:59:55 Show Profile Email Poster Visit davidshq's Homepage Reply with Quote
Hi,
I am trying to join two tables to a third table, but so far my code isn't working. Here is my code, any ideas why it isn't working?
SELECT TOP varAI * FROM
Game G LEFT JOIN LastPlayed LP ON G.ID = LP.gID
LEFT JOIN GamePopularity GP ON G.YourPopularity=GP.Popularity
WHERE G.Type=1
(
AND
.....
AND...
)

Like I said this is not a syntax problem its prolly some boolean logic issues with AND or OR. My suggestion is to add one AND / OR at a time and see how it filters the result set. I have run into these same issues in the past so just keep adding till you get what you want.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-13 : 11:54:30

Thanks for the suggestion. I found the problem. I had the statement GP.gID=NULL when it should have been GP.gID IS NULL.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources