SQL Server 2000 doesn't like my SQL Server 7 query

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)

SQL Server 2000 doesn't like my SQL Server 7 query

Author

Topic

nomore
Starting Member

1 Post

Posted - 2004-11-29 : 08:27:01

Hi

I have a very large program which includes a facility which allows users to search for a client based on a number of parameters they select.

This has worked perfectly with SQL Server 7 and MSDE until we upgraded to SQL Server 2000.

Query analyser returns results when connected to SQL Server 7, but returns no results when connected to SQL Server 2000. Both databases have the exact same data in them.


SELECT 
tbl_patient.ClientID, 
tbl_patient.ID AS PatientID, 
tbl_client_title.Description AS Title, 
tbl_client.FirstName, 
tbl_client.Surname, 
tbl_client.Street, 
tbl_client.District, 
tbl_client.Town, 
tbl_client.PostCode, 
tbl_county.Description AS County, 
tbl_client.Email, 
tbl_patient.Colour, 
tbl_patient.IDChip, 
tbl_patient.Name, 
tbl_species.Description AS Species, 
tbl_breed.Description AS Breed From tbl_client 
LEFT OUTER JOIN tbl_patient ON (tbl_client.ID = tbl_patient.ClientID) 
INNER JOIN tbl_species ON (tbl_patient.SpeciesID = tbl_species.ID) 
INNER JOIN tbl_breed ON (tbl_patient.BreedID = tbl_breed.ID) 
INNER JOIN tbl_county ON (tbl_client.CountyID = tbl_county.ID) 
INNER JOIN tbl_client_title ON (tbl_client.TitleID = tbl_client_title.ID) 
WHERE (tbl_client.Surname LIKE 'bar%' OR tbl_client.PostCode LIKE '%bar%' OR tbl_patient.IDChip LIKE 'bar%' OR tbl_patient.Name LIKE 'bar%' ) ORDER BY surname

Are there incompatibilities between SQL Server 7 and SQL Server 2000?... or am I missing something?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-29 : 08:51:19

is sql server 2000 set up as case-senstive?

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-29 : 08:53:44

could it be this maybe;
tbl_client.PostCode LIKE '%bar%'
that you PostCode doesn't have bar in it? i'm not sure if it matters because you have or's in a where....

otherwise it seems ok.

Go with the flow & have fun! Else fight the flow

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-29 : 10:30:21

This might be your problem:
[CODE]...
LEFT OUTER JOIN tbl_patient ON (tbl_client.ID = tbl_patient.ClientID)
INNER JOIN tbl_species ON (tbl_patient.SpeciesID = tbl_species.ID)
INNER JOIN tbl_breed ON (tbl_patient.BreedID = tbl_breed.ID)
...[/CODE]
You are joining a table using an outer join and then using inner join with that same table. If the query returns null rows for tbl_patient, the inner join on breed & species will force those rows to be removed because they don't match.

Instead, change:
[CODE]...
INNER JOIN tbl_species ON (tbl_patient.SpeciesID = tbl_species.ID)
INNER JOIN tbl_breed ON (tbl_patient.BreedID = tbl_breed.ID)
...[/CODE]To:[CODE]...
LEFT JOIN tbl_species ON (tbl_patient.SpeciesID = tbl_species.ID)
LEFT JOIN tbl_breed ON (tbl_patient.BreedID = tbl_breed.ID)
...[/CODE]and see if that does the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources