Need help on a select query. - 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 2005 Forums

Transact-SQL (2005)

Need help on a select query.

Author

Topic

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 12:26:25

Hi,

I have a database where I want to represent categories by Country. Some categories are for all countries, and some are specific to a country. My base table is like this :

base_cat
---------
categoryID
countryID (1 is all countries, anything else is country specific)

In some countries the category titles can vary. I have another table that contains titles for the categories.

cat_titles
----------
categoryID
countryID
Name

There will always be an entry for countryid =1 in this table, and then there MAY be alternative entries for countries where a category is known by a specific name. Say for example, in america you have a realtor, in ireland its called an auctioneer. The default for all countries(1) might be 'real estate agent'.

The problem I have is when I want to select all the categories/names for a particular country. If the country is america (countryid 233) I need to

select * from base_cat, cat_titles
Where (base_cat.countryID =1 or base_cat.countryid = 233)
and cat_title.countryId = base_cat.countryid
and (cat_title.countryid = 1 or cat_title.countryid = 233)

The problem here is im getting the category back twice where it has a unique name for america. Im getting both the default name 'real estate agent' and the america specific name 'realtor'.

I want to select all categories from base_cat where (base_cat.countryID =1 or base_cat.countryid = 233) and then select the correct title. So if there is one in cat_titles for 233 select that, otherwise select the default one.

Is there a way I can do this in SQL??

Any help is much appreciated.

C

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 12:35:01

select *
from base_cat
inner join cat_titles on cat_title.categoryId = base_cat.categoryid
Where base_cat.countryID in (1, 233)

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

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 12:47:33

quote:
Originally posted by Peso

select *
from base_cat
inner join cat_titles on cat_title.categoryId = base_cat.categoryid
Where base_cat.countryID in (1, 233)

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

In this case if my titles table has a default name and a specific name, then I get two results in my resultset. I want to only retrieve the title for countryid 233 if its there, and if its not, then only return the title for countryid 1. The code you posted seems to return both if both are in the table? I may be missing something here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 12:53:25

You maye want to help us by posting some sample data and expected output, with an explanation?

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

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 13:26:09

quote:
Originally posted by Peso

You maye want to help us by posting some sample data and expected output, with an explanation?

base_cat
--------
categoryID | countryid
___________________________
1 1
2 1
3 1
4 1
5 1

cat_titles
--------
categoryID | countryid | Name
______________________________________________________
1 1 Real Estate Agent
1 233 Realtor
1 17 Auctioneer
2 1 Dentist
3 1 Doctor
4 1 Chemist
5 1 Butcher

Now there I want get a call a stored procedure that returns a list of categoryid's and names. If I know the current user is american (233) I expect :

CategoryID , Category Name

1 Realtor
2 Dentist
3 Doctor
4 Chemist
5 Butcher

whereas if the user is from an unknown country I would get :

CategoryID , Category Name

1 Real Estate Agent
2 Dentist
3 Doctor
4 Chemist
5 Butcher

So basically it returns the country specific name where possible, and if there is none it returns the default value (1).

I have simplified the tables a bit to just include the information necessary to the problem.

C

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 13:58:54

[code]declare @base_cat table (categoryid int, countryid int)

insert @base_cat
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1

declare @cat_titles table (categoryid int, countryid int, name sysname)

insert @cat_titles
select 1, 233, 'Realtor' union all
select 1, 1, 'Real Estate Agent' union all
select 1, 17, 'Auctioneer' union all
select 2, 1, 'Dentist' union all
select 3, 1, 'Doctor' union all
select 4, 1, 'Chemist' union all
select 5, 1, 'Butcher'

declare @cid int

set @cid = 233

select categoryid,
name
from (
select ct.categoryid,
ct.name,
row_number() over (partition by ct.categoryid order by case when ct.countryid = @cid then 0 else 1 end) as recid
from @cat_titles as ct
inner join @base_cat as bt on bt.categoryid = ct.categoryid
where ct.countryid in (bt.countryid, @cid)
) as d
where recid = 1
order by categoryid[/code]

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

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 14:04:09

Thanks for your reply. Ive never heard of the coalesce function. Just before i read it I came up with something and was wondering could you tell me if its valid, and how it performs as compared to yours.

Basically Its :

SELECT base_cat.categoryid, base_cat.countryId, cat_titles.name
FROM base_cat
INNER JOIN cat_titles
ON base_cat.countryID = cat_titles.countryID
AND cat_titles.countryID = @countryID

UNION

SELECT base_cat.categoryid, base_cat.countryId, cat_titles.name
FROM base_cat
LEFT OUTER JOIN cat_titles
ON base_cat.countryID = cat_titles.countryID
AND cat_titles.countryID = 1
WHERE base_cat.categoryID
NOT IN (

SELECT base_cat.categoryid
FROM base_cat
INNER JOIN cat_titles
ON base_cat.countryID = cat_titles.countryID
AND cat_titles.countryID = @countryID
)

I select the titles specific to the country and then union that to the default titles, less the specific ones I just selected. Yours is obvously easier to read. Performance while is it the same thing?

Thanks for all the support.

Conor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 14:11:05

[code] rows cpu dur reads writes
Peso 5 0 0 6 0
Jeff 5 0 0 18 0
Staplebottom 25 0 0 36 0[/code]

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-12 : 14:12:31

I removed mine, it wasn't the correct solution for what he was looking for.

- Jeff
http://weblogs.sqlteam.com/JeffS

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-12 : 14:43:02

quote:
Originally posted by Peso

rows cpu dur reads writes
Peso 5 0 0 6 0
Jeff 5 0 0 18 0
Staplebottom 25 0 0 36 0

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

Peso, what did you use to gather the data above?

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 15:16:34

SQL Profiler.

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

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 16:37:31

Sorry, i dont even understand the significance of that. Thanks for the post peso, ill have to spend a little bit of time getting my head around it. some stuff ive never seen before there.

Is my solution valid?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 16:38:52

It does not seem it is. You query returns 25 records...

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

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 17:26:58

quote:
Originally posted by Peso

It does not seem it is. You query returns 25 records...

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

Sorry Peso, I made a mistake. This is the code. What does it look like in the profiler?

declare @base_cat table (categoryid int, countryid int)

insert @base_cat
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1

declare @cat_titles table (categoryid int, countryid int, name sysname)

insert @cat_titles
select 1, 233, 'Realtor' union all
select 1, 1, 'Real Estate Agent' union all
select 1, 17, 'Auctioneer' union all
select 2, 1, 'Dentist' union all
select 3, 1, 'Doctor' union all
select 4, 1, 'Chemist' union all
select 5, 1, 'Butcher'

declare @cid int

set @cid = 233

SELECT bc.categoryid, bc.countryId, ct.name
FROM @base_cat as bc
INNER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = @cID

UNION

SELECT bc.categoryid, bc.countryId, ct.name
FROM @base_cat as bc
LEFT OUTER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = 1
WHERE bc.categoryID
NOT IN (

SELECT bc.categoryid
FROM @base_cat as bc
INNER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = @cID
)

Can I download the profiler for free?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-12 : 17:28:52

SQL Profiler packages with SQL Server, it's a trace tool, very slick. You can check it out on Books online, and on MSDN they have tutorials.

Future guru in the making.

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 17:33:20

quote:
Originally posted by Zoroaster

SQL Profiler packages with SQL Server, it's a trace tool, very slick. You can check it out on Books online, and on MSDN they have tutorials.

Future guru in the making.

Im using the free sql express for the moment. Can anyone test this for me, or is there a tool I can download to use with sql express.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 17:34:30

quote:
Originally posted by staplebottom

SELECT bc.categoryid, ct.countryId, ct.name
FROM @base_cat as bc
INNER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = @cID

UNION

SELECT bc.categoryid, ct.countryId, ct.name
FROM @base_cat as bc
LEFT OUTER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = 1
WHERE bc.categoryID
NOT IN (

SELECT bc.categoryid
FROM @base_cat as bc
INNER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = @cID
)

		rows	cpu	dur	reads	writes
Peso		 5	0	0	 6	0
Staplebottom	25	0	0	36	0
Staplebottom2	 5	0	0	51	0

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 17:36:18

SQL Profiler, I think, is installed when you install the Client Tools.
Have a look at the install screens.

Not available with SQL Express though.

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

staplebottom
Starting Member

29 Posts

Posted - 2007-09-12 : 17:37:07

quote:
Originally posted by Peso

quote:
Originally posted by staplebottom

SELECT bc.categoryid, ct.countryId, ct.name
FROM @base_cat as bc
INNER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = @cID

UNION

SELECT bc.categoryid, ct.countryId, ct.name
FROM @base_cat as bc
LEFT OUTER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = 1
WHERE bc.categoryID
NOT IN (

SELECT bc.categoryid
FROM @base_cat as bc
INNER JOIN @cat_titles as ct
ON bc.categoryID = ct.categoryID
AND ct.countryID = @cID
)
rows cpu dur reads writes
Peso 5 0 0 6 0
Staplebottom 25 0 0 36 0
Staplebottom2 5 0 0 51 0

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

Thanks for the super quick replies here! Im assuming that my SQL took about 9 times as much processing as yours by what you posted?

Thanks for all the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 17:49:40

Not necessarily

TotalSubtreeCost	TotalSubtreeCost
Peso			Staplebottom
----------------	----------------
0.03571353		0.06021475
0.03571353		0.06021475
0.03571306		0.04885023
0.03571298		0.01793257
0.03571296		0.01464499
0.02435154		0.00328310
0.02435144		0.00328310
0.00328310		0.03091746
0.00328310		0.02762940
			0.00328310
			0.02434212
			0.00328310
			0.00328310
			0.00328310
----------------	----------------
0.23383524		0.30444487

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 18:06:48

[code]Execution Tree for my query
-----------------------------
Filter(WHERE:([Expr1007]=(1)))
|--Sequence Project(DEFINE:([Expr1007]=row_number))
|--Segment
|--Sort(ORDER BY:([ct].[categoryid] ASC, [Expr1006] ASC))
|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN @cat_titles.[countryid] as [ct].[countryid]=[@cid] THEN (0) ELSE (1) END))
|--Hash Match(Inner Join, HASH:([bt].[categoryid])=([ct].[categoryid]), RESIDUAL:(@cat_titles.[categoryid] as [ct].[categoryid]=@base_cat.[categoryid] as [bt].[categoryid] AND (@cat_titles.[countryid] as [ct].[countryid]=[@cid] OR @cat_titles.[countryid] as [ct].[countryid]=@base_cat.[countryid] as [bt].[countryid])))
|--Table Scan(OBJECT:(@base_cat AS [bt]))
|--Table Scan(OBJECT:(@cat_titles AS [ct]))

Execution Tree for your query
-----------------------------
Sort(DISTINCT ORDER BY:([Union1018] ASC, [Union1019] ASC, [Union1020] ASC))
|--Concatenation
|--Nested Loops(Inner Join, WHERE:(@base_cat.[categoryid] as [bc].[categoryid]=@cat_titles.[categoryid] as [ct].[categoryid]))
| |--Sort(ORDER BY:([ct].[categoryid] ASC, [ct].[name] ASC))
| | |--Table Scan(OBJECT:(@cat_titles AS [ct]), WHERE:(@cat_titles.[countryid] as [ct].[countryid]=[@cid]))
| |--Table Scan(OBJECT:(@base_cat AS [bc]))
|--Nested Loops(Left Outer Join, WHERE:(@base_cat.[categoryid] as [bc].[categoryid]=@cat_titles.[categoryid] as [ct].[categoryid]))
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([bc].[categoryid]))
| |--Table Scan(OBJECT:(@base_cat AS [bc]))
| |--Hash Match(Inner Join, HASH:([ct].[categoryid])=([bc].[categoryid]), RESIDUAL:(@base_cat.[categoryid] as [bc].[categoryid]=@cat_titles.[categoryid] as [ct].[categoryid]))
| |--Table Scan(OBJECT:(@cat_titles AS [ct]), WHERE:(@cat_titles.[countryid] as [ct].[countryid]=[@cid]))
| |--Table Scan(OBJECT:(@base_cat AS [bc]), WHERE:(@base_cat.[categoryid] as [bc].[categoryid] IS NULL OR @base_cat.[categoryid] as [bc].[categoryid]=@base_cat.[categoryid] as [bc].[categoryid]))
|--Table Scan(OBJECT:(@cat_titles AS [ct]), WHERE:(@cat_titles.[countryid] as [ct].[countryid]=(1)))[/code]

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources