Need help with Complex Select Statement

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)

Need help with Complex Select Statement

Author

Topic

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-16 : 08:53:09

Ok, this one is a doozy!!!
What I want to do is display the Business_Name where the TYPE is equal to OWNR and IMPORT_ID is equal to 37. I just choose this line from my sql statement so that I wouldn't bumbar with my entire stored procedure unless you want to see my code for the store procedure. I think if I can get help with this then I shouldn't have a problem with correcting the rest of it I HOPE!!!!!!!!

Select Business_Name
From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact
Where Import_ID = 37
and Type = 'OWNR'

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-16 : 08:56:30

So, what error does the select you posted generate?

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-16 : 10:17:31

I don't receive an error.
Thing is the result I get is nothing.
The result I'm suppose to get is Lincoln Co Brd of Ed

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-16 : 10:25:42

Your query looks fine. The problem is either your table's column types and / or the data.

Post some sample data?

If you have time, try loosening the WHERE criteria: remove the id=37 - do you get results? No? Remove the Type='OWNR' - do you get results? No? Then something other than the query is wrong. You don't mention how you're invoking this query. Is it ADO or Query Analyzer?

Sam

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-16 : 11:26:41

Sample Data:

ID Business_Name Type
---------------------------------------------------
37 Lincoln Co Brd of Ed OWNR
37 Stanford Elem USER

-No results when I removed the id = 37

-Yes I did get results w/ id = 37 except it gave me results for the TYPE: OWNR & USER. The 2 results I got for business_name was Lincoln Co Brd Of Ed & Stanford Elem

I'm invoking this query in the Query Analyzer.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-16 : 12:25:43

The type field has unprintable chars in it

Do Type Like 'OWNER%' and tell us...

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-16 : 13:52:45

Post the entire select statement as it exists in your query.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-16 : 14:10:30

Is the datatype of the "type" column char or varchar? If it is char, you will need to account for the trailing spaces to get an exact match.

OS

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-19 : 09:34:40

The datatype of Type is varchar.

When I use change it to this: Do Type Like 'OWNER%' and tell us...
I don't get any results.

Here's the entire stored procedure.

CREATE PROCEDURE sp_GetLegacyContactInfo
(
@Inspection_ID as int --16981
)
AS
Declare @OwnerRecordID as varchar(10)
Declare @UserRecordID as varchar(10)
Declare @RecordID as varchar(10)

Set @OwnerRecordID = (Select Record_ID From HBC_Boiler_Inspection_Contact_Link Where ID = (Select Owner_Contact_ID From HBC_Boiler_Inspection Where ID = @Inspection_ID))
Set @UserRecordID = (Select Record_ID From HBC_Boiler_Inspection_Contact_Link Where ID = (Select User_Contact_ID From HBC_Boiler_Inspection Where ID = @Inspection_ID))

Select
(Select Business_Name From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_Business_Name,
(Select Address1 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_Address1,
(Select Address2 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_Address2,
(Select City From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_City,
(Select State_ID From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_State_ID,
(Select ZIP From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_ZIP,
(Select Phone1 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_Phone1,
(Select Phone2 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_Phone2,
(Select Email From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'OWNR' and Import_ID = @OwnerRecordID) as Owner_Email,

(Select Business_Name From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_Business_Name,
(Select Address1 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_Address1,
(Select Address2 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_Address2,
(Select City From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_City,
(Select State_ID From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_State_ID,
(Select ZIP From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_ZIP,
(Select Phone1 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_Phone1,
(Select Phone2 From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_Phone2,
(Select Email From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact Where Type = 'USER' and Import_ID = @UserRecordID) as User_Email

Go

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-19 : 10:15:08

quote:
When I use change it to this: Do Type Like 'OWNER%' and tell us...
I don't get any results.

Could that be because you misspelled the WHERE criteria? Isn't it 'OWNR%' not 'OWNER%'?

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-19 : 10:25:02

No its correct. I have it spelled OWNR.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-19 : 10:28:28

Have you got a CASE sensitive database?...
"OWNR" and "ownr" are not the same thing in such a database.

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-19 : 10:36:02

Nope DB isn't case sensitive

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-19 : 11:05:18

At a simpler level.....why is your cdoe so complicated....the performance of it (never mind readability) of it....must be going west....mind you in this case you have a bigger problem in that it's not working..

What's wrong with coding it as follows??? (it's the standard (more accepted) of doing something like this)...

Select a.Business_Name as Owner_Business_Name, a.Address1 as Owner_Address1, a.Address2 as Owner_Address2, a.City as Owner_City, a.State_ID as Owner_State_ID, a.ZIP as Owner_ZIP, a.Phone1 as Owner_Phone1, a.Phone2 as Owner_Phone2, a.Email as Owner_Email,
b.Business_Name as User_Business_Name, b.Address1 as User_Address1, b.Address2 as User_Address2, b.City as User_City, b.State_ID as User_State_ID, b.ZIP as User_ZIP, b.Phone1 as User_Phone1, b.Phone2 as User_Phone2, b.Email as User_Email
from
HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact a Where a.Type = 'OWNR' and a.Import_ID = @OwnerRecordID
inner join
HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact b Where b.Type = 'USER' and b.Import_ID = @UserRecordID

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-19 : 11:41:03

I'm not real strong on inner joins, but when I ran it in my query analyer it didn't work.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-19 : 14:07:43

Maybe you have spaces in front of OWNR and you don't realize it.

Select Business_Name
From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact
Where Import_ID = 37
and Type LIKE '%OWNR%'

You may want to use that query to see if it is the problem. If it matches then you have spaces in front of OWNR

Bigced_21
Starting Member

9 Posts

Posted - 2004-04-19 : 14:32:15

Nope, there's no spaces at all.

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-19 : 14:56:22

quote:

Sample Data:



ID Business_Name          Type
---------------------------------------------------
37 Lincoln Co Brd of Ed   OWNR
37 Stanford Elem          USER

quote:
Select Business_Name
From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact
Where Import_ID = 37
and Type = 'OWNR'

quote:
-No results when I removed the id = 37

-Yes I did get results w/ id = 37 except it gave me results for the TYPE: OWNR & USER. The 2 results I got for business_name was Lincoln Co Brd Of Ed & Stanford Elem

Sounds like nothing reasonable is working.

What result doe you get with:

Select Business_Name
From HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact
Where Type Like '%O%'

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-20 : 05:30:28

Re (amended) code not working....I was rushing....I presumed you would/could get over the typo's....I've pushed it on somewhat.

I'd also advise you to look up the INNER JOIN construct....it's one of the most basic and important parts of the SQL language.

Select a.Business_Name as Owner_Business_Name, a.Address1 as Owner_Address1, a.Address2 as Owner_Address2, a.City as Owner_City, a.State_ID as Owner_State_ID, a.ZIP as Owner_ZIP, a.Phone1 as Owner_Phone1, a.Phone2 as Owner_Phone2, a.Email as Owner_Email,
b.Business_Name as User_Business_Name, b.Address1 as User_Address1, b.Address2 as User_Address2, b.City as User_City, b.State_ID as User_State_ID, b.ZIP as User_ZIP, b.Phone1 as User_Phone1, b.Phone2 as User_Phone2, b.Email as User_Email
from
HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact a
inner join HBC_Warehouse.dbo.HBC_Boiler_Temp_Contact b on 1 = 1
Where a.Type = 'OWNR' and a.Import_ID = @OwnerRecordID
and b.Type = 'USER' and b.Import_ID = @UserRecordID

On execution plan alone, ie predicted performance, this amended version should have to do 18 times less work....and less work (nearly) always = faster performance.

re your basic problem....
Can you supply DDL for the table and INSERT statements to (re)create your input data?....there is obviously something of a mismatch somewhere, from what we are visually seeing in these posts and what is actually in the database.

Are you using any unicode stuff?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources