Really badly coded query that doesn't work!

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 2005 Forums

Transact-SQL (2005)

Really badly coded query that doesn't work!

Author

Topic

sulman
Starting Member

20 Posts

Posted - 2007-09-28 : 05:43:19

Hi,

I am struggling with a query:

I am trying to extract data from 3 tables based on certain criteria

I have this so far:

SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' OR fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID

That gets me a correct record set with 13 rows. But I only need the rows where the Response is not 'Y' or not 'N' from it. So I have ammended the query to :

SELECT * FROM
(

SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' OR fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID
)as tmp2
WHERE tmp2.Response <> 'Y' OR tmp2.Response <> 'N'

tmp2 returns 13 rows with only 1 row that has a Y Response (all other Responses are either NULL or P)

So i thought that this new query would return 12 rows now. But it only returns 1 row...the row that does have a Y response.
My odd logic finds this wierd!

Can anyone see what I am doing wrong?

I know this is a pretty messy query!!

Many thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 05:47:34

quote:
Originally posted by sulman

Hi,

I am struggling with a query:

I am trying to extract data from 3 tables based on certain criteria

I have this so far:

SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' OR fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID

That gets me a correct record set with 13 rows. But I only need the rows where the Response is not 'Y' or not 'N' from it. So I have ammended the query to :

SELECT * FROM
(

SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' OR fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID
)as tmp2
WHERE tmp2.Response <> 'Y' AND tmp2.Response <> 'N'

tmp2 returns 13 rows with only 1 row that has a Y Response (all other Responses are either NULL or P)

So i thought that this new query would return 12 rows now. But it only returns 1 row...the row that does have a Y response.
My odd logic finds this wierd!

Can anyone see what I am doing wrong?

I know this is a pretty messy query!!

Many thanks

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 05:57:23

If Harsh's suggestion doesn't work, please post proper and accurate sample daat together with your expected output.

E 12°55'05.25"
N 56°04'39.16"

sulman
Starting Member

20 Posts

Posted - 2007-09-28 : 08:35:10

Thanks for your replies.
Harsh that doesn't fix it, but thanks.
Peso, I have tried to include as much data as poss:

tblClubs:
Just returns club name from this

tblFixtureData:

tblFixtureResponseTable:

Query:
SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' AND fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID
)as tmp2

this query gives:

So i thought that getting only the required rows of that query (based on the Response field of tblFixtureResponseTable) would be as easy as wrapping it in another select clause:

SELECT * FROM
(
SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' AND fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID
)as tmp2
WHERE tmp2.Response <> 'Y' OR tmp2.Response <> 'N'

However that gives me just one row:

Where I would expect to have only 8 (tmp2 minus the one that should be removed by the Response Y/N condition)
Like this:

I hope this makes sense to you and I appreciate your time.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 08:43:14

I asked you to post sample data, not a picture.

Here is an excellent example of a clear problem statement and accurate and proper sample data.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81373

OP also provided expected output.
Read the topic, learn from it, and please come back here.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 08:44:04

[code]SELECT * FROM
(

SELECT * FROM
(
SELECT fd.*, cl.OrganisationID
FROM tblFixtureData fd JOIN tblClubs cl
ON fd.HostClub = cl.ClubID
where fd.Date > getDate()
AND
(fd.FixtureStatus<>'Confirmed' OR fd.FixtureStatus <> 'Cancelled')
)as tmp left join tblFixtureResponseTable
ON tmp.HostClub = tblFixtureResponseTable.HostClubID
)as tmp2
WHERE (tmp2.Response <> 'Y' AND tmp2.Response <> 'N') OR tmp2.Response IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sulman
Starting Member

20 Posts

Posted - 2007-09-28 : 08:52:59

Harsh, thanks so much that is what I was missing!

Peso I appreciate the need to get full details from someone before you can help them, however you are right; you asked for sample data. I gave you a picture of some sample data.

If you want text based sample data please be more specific and less derogatory.

Many thanks again Harsh.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 08:59:11

I showed you that explicit topic just because of the NULL issue.
No hard feelings from my part you didn't notice that.

Also, if you have posted sample data as I suggested, I am pretty sure we can rewrite your query to be more efficient.

It is up to you if you want that help or not.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:04:00

For example, this row

AND (fd.FixtureStatus <> 'Confirmed' OR fd.FixtureStatus <> 'Cancelled')

will always evaluate as TRUE!

Why?

Because if fd.FixtureStatus = 'Confirmed' the evaluation gets TRUE because of fd.FixtureStatus <> 'Cancelled'.
If fd.FixtureStatus = 'Cancelled' the evaluation gets TRUE because of fd.FixtureStatus <> 'Confirmed'.
If fd.FixtureStatus = 'Other' the evaluation gets TRUE because of either fd.FixtureStatus <> 'Confirmed' or fd.FixtureStatus <> 'Cancelled'.

Do you get my drift?

What I think you ment to write was

AND fd.FixtureStatus NOT IN ('Confirmed', 'Cancelled')

meaning fd.FixtureStatus should be NEITHER of them.

This may also be the one of the reasons your query doesn't work as suggestion in first post from you.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:07:20

So if you think I was derogative towards you, please ignore my future advices.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:22:34

The same logic applies to your very last WHERE

WHERE	tmp2.Response <> 'Y' OR tmp2.Response <> 'N'

It will always evaluate as TRUE, based on the same principles as described above.

You can try this

WHERE COALESCE(tmp2.Response, 'X') NOT IN ('Y', 'N')

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:29:00

I can also see that you have separated DATE and TIME. It seems that this data originally come from Microsoft Access, right?

SELECT GETDATE() function includes the TIME part information too, as 2007-09-28 09:29:03

But you don't get that record, because your DATE is only 2007-09-28 00:00:00!
So you will only get TOMORROW's and later games. But that might be right thing according you your business rules, I don't know that.
That's why I suggested you should explain your business rules.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:43:26

Try this suggestion. I think it will suite your needs, and there is no need to write extra IS NULL case.

SELECT		fd.*,
		cl.OrganisationID
FROM		tblFixtureData AS fd
INNER JOIN	tblClubs AS cl ON cl.ClubID = fd.HostClub
LEFT JOIN	tblFixtureResponseTable AS frt ON frt.HostClubID = fd.HostClub
			AND frt.Response NOT IN ('Y', 'N')
WHERE		fd.Date > GETDATE()
		AND fd.FixtureStatus NOT IN ('Confirmed', 'Cancelled')

It is very dangerous to filter an outer IS NULL for an inner table.
In your case, even if you have LEFT JOINed tblFixtureResponseTable table over HostClubID column, ALL records NOT mathching that criteria will get evaluated as NULL in your outer WHERE ... IS NULL check.

I hope you have learned some things today.

Try my suggestion above and please post back your results and findings.

E 12°55'05.25"
N 56°04'39.16"

sulman
Starting Member

20 Posts

Posted - 2007-09-28 : 10:02:06

Hi Peso,

Thanks for your time and help with this.

I am running out of time today but I will look over your posts from above and have a go at restructuring the query based on them. That way I will learn many things.

Your final post still returns nine rows where I would expect it to return only 8. It still seems to include the row that has a 'Y' response. (although the Response column is not returned in that query)

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 10:08:50

I wrote that query based on the pictures, so I can't test it before posting it.
My nine records should be eight (I believe you when you say it) due to some thing I have overseen but can't test

Add an extra line to the query for debugging purposes

SELECT		fd.*,
		cl.OrganisationID,
		frt.Response
FROM		tblFixtureData AS fd
INNER JOIN	tblClubs AS cl ON cl.ClubID = fd.HostClub
LEFT JOIN	tblFixtureResponseTable AS frt ON frt.HostClubID = fd.HostClub
			AND frt.Response NOT IN ('Y', 'N')
WHERE		fd.Date > GETDATE()
		AND fd.FixtureStatus NOT IN ('Confirmed', 'Cancelled')

What data is in frt.Response column?

E 12°55'05.25"
N 56°04'39.16"

sulman
Starting Member

20 Posts

Posted - 2007-09-28 : 11:22:59

Hi Peso,

The Response column contains only NULLS?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 12:14:14

Then, without having some proper and accurate data to test with, I would say nine is the number of records to get.

E 12°55'05.25"
N 56°04'39.16"

sulman
Starting Member

20 Posts

Posted - 2007-09-28 : 12:24:51

Thanks.

The data involved is very extensive. 30-40 fields per table (all linked through FK's). So getting test data together will take some time.

I will try and get sone together.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources