how to combine 2 these 2 queries into 1? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to combine 2 these 2 queries into 1?

Author

Topic

metroix
Starting Member

16 Posts

Posted - 2008-07-10 : 20:04:54

this one

select  characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -110 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -10 THEN inventoryitems.itemid ELSE NULL END))  as HAT
from characters  join inventoryitems  on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -110 or inventoryitems.position = -10)
GROUP BY characters.name

and this one

select  characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -107 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -7 THEN inventoryitems.itemid ELSE NULL END))  as HAT
from characters  join inventoryitems  on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -107 or inventoryitems.position = -7)
GROUP BY characters.name

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-10 : 20:24:41

You can use Union and you don't need Coalesce function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 01:35:48

You dont need two queries at all. you can merge them into 1

select  characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -110 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -107 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -10 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -7 THEN inventoryitems.itemid ELSE NULL END))  as HAT
from characters  join inventoryitems  on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -110 or inventoryitems.position = -10
or inventoryitems.position = -107 or inventoryitems.position = -7)
GROUP BY  characters.id, characters.name

metroix
Starting Member

16 Posts

Posted - 2008-07-17 : 08:15:23

yeah but i have to combine like 10 scripts like that so i ill be better if i know how to compine

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 08:46:41

quote:
Originally posted by metroix

yeah but i have to combine like 10 scripts like that so i ill be better if i know how to compine

Query1
union all
Query2
union all
.
.
.

Madhivanan

Failing to plan is Planning to fail

metroix
Starting Member

16 Posts

Posted - 2008-07-17 : 11:13:23

kk look each queri has diferent colum using union all is giving me just 1 colum for everything =/ how i fix this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 11:59:45

quote:
Originally posted by metroix

kk look each queri has diferent colum using union all is giving me just 1 colum for everything =/ how i fix this?

didnt get that. can you explain your problem with some sample data?

metroix
Starting Member

16 Posts

Posted - 2008-07-17 : 13:04:33

ok if i use this script
select characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -110 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -10 THEN inventoryitems.itemid ELSE NULL END)) as HAT
from characters join inventoryitems on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -110 or inventoryitems.position = -10)
GROUP BY characters.name

will give me a column called HAT

select characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -107 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -7 THEN inventoryitems.itemid ELSE NULL END)) as HAT2
from characters join inventoryitems on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -107 or inventoryitems.position = -7)
GROUP BY characters.name

and this one here will give me a column called HAT2

what i want is combino those script and get 1 column called hat and another called hat2
if i use UNION ALL that will give me just 1 column
i want to combine them but got 2 column beside 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 13:10:11

ok. You should have given the sample output in beginning. use the below for getting two values

SELECT t1.id,t1.name,t1.HAT,t2.HAT2
FROM
(
select characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -110 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -10 THEN inventoryitems.itemid ELSE NULL END)) as HAT
from characters join inventoryitems on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -110 or inventoryitems.position = -10)
GROUP BY characters.name
)t1
INNER JOIN
(
select characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -107 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -7 THEN inventoryitems.itemid ELSE NULL END)) as HAT2
from characters join inventoryitems on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -107 or inventoryitems.position = -7)
GROUP BY characters.name
)t2
ON t1.id=t2.id
AND t1.name=t2.name

metroix
Starting Member

16 Posts

Posted - 2008-07-17 : 14:02:59

that not working for me cuz if some1 dosent have what one of the script give and has one from the other that person will be exclude from the results
Ex:
script 1 give HAT
sciprt 2 give HAT2
Peter has hat and hat 2
Max has just hat
and Jessica has just hat 2

the result of the query will be just PETER cuz he is the only that has both thats why this method:

SELECT t1.id,t1.name,t1.HAT,t2.HAT2
FROM
(
select characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -110 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -10 THEN inventoryitems.itemid ELSE NULL END)) as HAT
from characters join inventoryitems on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -110 or inventoryitems.position = -10)
GROUP BY characters.name
)t1
INNER JOIN
(
select characters.id, characters.name,
COALESCE(MAX(CASE WHEN inventoryitems.position = -107 THEN inventoryitems.itemid ELSE NULL END),MAX(CASE WHEN inventoryitems.position = -7 THEN inventoryitems.itemid ELSE NULL END)) as HAT2
from characters join inventoryitems on characters.id = inventoryitems.characterid
where inventoryitems.inventorytype = -1
and (inventoryitems.position = -107 or inventoryitems.position = -7)
GROUP BY characters.name
)t2
ON t1.id=t2.id
AND t1.name=t2.name

dont works for me but thax for try
can i have ur messenger so i can explain to u what a want better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources