Trouble with SQL statement - 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

SQL Server Development (2000)

Trouble with SQL statement

Author

Topic

Bunce
Starting Member

13 Posts

Posted - 2002-04-11 : 06:52:18

I'm in the process of setting up a football tipping application where entrants are able to enter / edit their tips for a particular week's fuxtures.

To the 'tipping' page I pass through the querystring, the RoundID and EntrantID of the entrant in question. On this form I need to diisplay the fixture details for all fixtures in that particular round, along with the user's tips (if they exist) for each of those fixtures.

My basic table structure is as follows (relationships should be obvious):
* ROUNDS - RoundID (PK), RoundDescription, etc.
* FIXTURES - FixtureID (PK), RoundID (FK), Date, Time, Venue etc.
* SELECTIONS - SelectionID (PK), FixtureID (FK), EntrantID (FK), Result
* ENTRANTS - EntrantID (PK), Name, etc.

I am having trouble drawing the fixture details when there have been no selections made. The two criteria I supply to this query are RoundID and EntrantID as supplied in the querystring. How do I return the rounds fixtures, when there have been no selections made? ie, if EntrantA has not yet made any tips for Round1, the query should only return the fxtures for that round, with blank or null entries from the selections table.

I've tried LEFT, RIGHT, INNER, OUTER, UNDERNEATH, ABOVE joins but can't seem to get it to work.. It seems to me that it's execuing the 'WHERE EntrantID = x", AFTER the join has been executed, rather than before.. Is there a way around this?

Here's a simplified example, using Left Outer:

SELECT tblFixtures.IDFixture, tblFixtures.IDRound, tblSelections.IDEntrant
FROM tblFixtures LEFT OUTER JOIN tblSelections ON tblFixtures.IDFixture = tblSelections.IDFixture
WHERE (tblFixtures.IDRound=1) AND (tblSelections.IDEntrant=9);

Now this will work fine when there exists records in the Selections table, where IDEntrant = 9. But if there are none, meaning that the entrant has not yet submitted any tips, the query will return 0 records.

I am hoping to still return all the relevant records (and fields) from the Fixtures table, along with blank, or null entries for fields from the Selections table. This will tell me that the entrant is yet to submit any tips for these fixtures.

Do I need a subquery?

TIA,

Andrew

Edited by - Bunce on 04/11/2002 06:55:09

Bunce
Starting Member

13 Posts

Posted - 2002-04-11 : 08:54:50

Found the answer:

SELECT *
FROM Fixtures F LEFT OUTER JOIN
(SELECT * FROM Selections S WHERE S.IDENTRANT = 9) AS S
ON F.IDFixture = S.IDFixture
WHERE O.RoundID = 1

Got the hint from http://www.swynk.com/friends/boyle/ansijoins.asp (near the bottom, under Cross Joins)

Cheers,
Andrew

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-11 : 08:57:12

quote:

Here's a simplified example, using Left Outer:

SELECT tblFixtures.IDFixture, tblFixtures.IDRound, tblSelections.IDEntrant
FROM tblFixtures LEFT OUTER JOIN tblSelections ON tblFixtures.IDFixture = tblSelections.IDFixture
WHERE (tblFixtures.IDRound=1) AND (tblSelections.IDEntrant=9);

Now this will work fine when there exists records in the Selections table, where IDEntrant = 9. But if there are none, meaning that the entrant has not yet submitted any tips, the query will return 0 records.

You can't make any assumptions about the order in which SQL Server evaluates the various clauses of the SELECT statement. It may evaluate the WHERE before the FROM, or after, or during. The key here is that the WHERE clause has a global scope - it applies to the entire rowset. So, when you refer to a table in the WHERE clause that was outer joined, you have effectively made an INNER JOIN.

Specifically, when you say: AND (tblSelections.IDEntrant=9), you eliminate the rows where tblSelections failed the join ( and thus tblSelections.IDEntrant would be NULL ). To fix the issue, place the criteria in the join's ON clause as follows (I added table aliases, always a good practice, to save typing:

SELECT f.IDFixture, f.IDRound, s.IDEntrant
FROM tblFixtures f
LEFT OUTER JOIN tblSelections s ON f.IDFixture = s.IDFixture and s.IDEntrant = 9
WHERE f.IDRound=1

setBasedIsTheTruepath
<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-11 : 08:58:00

wow, a self-sniping thread!

setBasedIsTheTruepath
<O>

Bunce
Starting Member

13 Posts

Posted - 2002-04-11 : 09:10:23

LOL, sorry, didn't mean to post that link to the other site - I had copied from another post.

I actually tried that join you listed (ON f.IDFixture = s.IDFixture and s.IDEntrant = 9) and it returned an error message (in Access anyway) - 'Join Type not supported'? Maybe I made a typo.

Thanks for the explanation though, its cleared a few things up for me in regard to 'evaluation order'.

Cheers,

Andrew

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-11 : 09:22:00

Hey no problem.

Most readers here will assume you're using SQL Server unless you explicitly state otherwise. In general, some of the solutions that people propose here aren't applicable to Access because Access does not have an ANSI-standard SQL implementation. Access doesn't support COUNT( DISTINCT {column} ) for example, and I suspect it doesn't fully support ANSI-style joins as evidenced by your difficulties.

Maybe you can port your app to SQL Server?

setBasedIsTheTruepath
<O>

Bunce
Starting Member

13 Posts

Posted - 2002-04-11 : 09:29:53

Would love to!

I usually use SQL Server and ASP for almost all apps, however circumstances for this are different (ie. boss wants me to use this as a learing experience and use Cold Fusion on Access!)

Cheers,
Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources