SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Really badly coded query that doesn't work!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sulman
Starting Member

20 Posts

Posted - 09/28/2007 :  05:43:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5532 Posts

Posted - 09/28/2007 :  05:47:34  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  05:57:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

sulman
Starting Member

20 Posts

Posted - 09/28/2007 :  08:35:10  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  08:43:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5532 Posts

Posted - 09/28/2007 :  08:44:04  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sulman
Starting Member

20 Posts

Posted - 09/28/2007 :  08:52:59  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  08:59:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  09:04:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/28/2007 09:15:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  09:07:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
So if you think I was derogative towards you, please ignore my future advices.


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

Edited by - SwePeso on 09/28/2007 09:13:13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  09:22:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/28/2007 09:38:29
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  09:29:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  09:43:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/28/2007 09:49:37
Go to Top of Page

sulman
Starting Member

20 Posts

Posted - 09/28/2007 :  10:02:06  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  10:08:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/28/2007 10:18:58
Go to Top of Page

sulman
Starting Member

20 Posts

Posted - 09/28/2007 :  11:22:59  Show Profile  Reply with Quote
Hi Peso,

The Response column contains only NULLS?

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/28/2007 :  12:14:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

sulman
Starting Member

20 Posts

Posted - 09/28/2007 :  12:24:51  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000