I cant fix this script =( - 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 2000 Forums

SQL Server Development (2000)

I cant fix this script =(

Author

Topic

metroix
Starting Member

16 Posts

Posted - 2008-07-10 : 05:49:33

[code]if exists(
select
characters.name, inventoryitems.itemid as HAT from characters, inventoryitems
where
inventoryitems.inventorytype = -1
and
inventoryitems.position = -101
and
characters.id = inventoryitems.characterid)
else
(select
characters.name, inventoryitems.itemid as HAT from characters, inventoryitems
where
inventoryitems.inventorytype = -1
and
inventoryitems.position = -1
and
characters.id = inventoryitems.characterid;)[/code]
i want to make a script that first check the inventoryitems.inventorytype = -1 and inventoryitems.position = -101 if the script didnt found any inventoryitems.position = -101 in character then check for inventoryitems.position = -1

the thing is i have a game server.... so i want a query that tell me the items in the position -101 but if a character dosent have a -101 so it have a -1 so i want it to show it :) i think i explain a lil my self i dont speak to much english so i tried my best.. hope u guys can help me.

example:
peter has -1
michael has -101

so i want a script that show
name itemid
peter xxxxxxx

but michael dosent have a -1 so he has a -101
so the query result should be
name itemid
peter xxxxxxx
michael xxxxxxx

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-10 : 05:58:01

not sure i've understand you properly but perhaps something like....


select  characters.name, 
        case when i.position = -101 then i.itemid else -1 end as HAT 
from characters c join inventoryitems i on c.id = i.characterid
where i.inventorytype = -1

otherwise we really need some better sample data

Em

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-10 : 05:59:38

If it exists, what action do you want to take? The SQL will not work as you do not have an action following the if.

You want something like


if exists(select characters.name, inventoryitems.itemid as HAT 
				from characters, inventoryitems
				where inventoryitems.inventorytype = -1
				and inventoryitems.position = -101
				and characters.id = inventoryitems.characterid)
begin
	Do something here..
end
else
begin
	if exists (select characters.name, inventoryitems.itemid as HAT 
					from characters, inventoryitems
					where inventoryitems.inventorytype = -1
					and inventoryitems.position = -1
					and characters.id = inventoryitems.characterid)
	Do something here...
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 06:50:53

Still not clear about what you want. Can you provide some sample data of what you're looking at?You seem to add a if condition but you dont have anu code inside to perform any actions

metroix
Starting Member

16 Posts

Posted - 2008-07-10 : 07:02:15

ok..
ummm i need an script that PRINT the character name and item id
but i need the item id of the item in the position -1 and position -101 of each character
but check which characters already have an -101 item, the characters that dosent have a -101 postion then show the characters with -1
like show all the characters with -101 if someone dosent have a -101 then show his -1

EDIT:

quote:

Originally posted by RickD

If it exists, what action do you want to take? The SQL will not work as you do not have an action following the if.

You want something like


if exists(select characters.name, inventoryitems.itemid as HAT 
				from characters, inventoryitems
				where inventoryitems.inventorytype = -1
				and inventoryitems.position = -101
				and characters.id = inventoryitems.characterid)
begin
	Do something here..
end
else
begin
	if exists (select characters.name, inventoryitems.itemid as HAT 
					from characters, inventoryitems
					where inventoryitems.inventorytype = -1
					and inventoryitems.position = -1
					and characters.id = inventoryitems.characterid)
	Do something here...
end

i got this with urs
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if exists(select characters.name, inventoryitems.itemid as HAT
from charact' at line 1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-10 : 07:07:49

maybe just?....


select  c.name, 
        i.itemid  as HAT 
from characters c join inventoryitems i on c.id = i.characterid
where i.inventorytype = -1 
and (i.position = -101 or i.position = -1)

metroix
Starting Member

16 Posts

Posted - 2008-07-10 : 07:12:17

quote:

Originally posted by elancaster

maybe just?....


select  c.name, 
        i.itemid  as HAT 
from characters c join inventoryitems i on c.id = i.characterid
where i.inventorytype = -1 
and (i.position = -101 or i.position = -1)

u almost there but with that script is selecting both -101 and -1 from each characters, i need that first check who has -101 then check who hasent have -101 and post the characters name from the one that has -101 and post the name form the one that has the -1 but some of the characters has both got it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 07:17:45

[code]select c.name,
COALESCE(MAX(CASE WHEN i.position = -101 THEN i.itemid ELSE NULL END),MAX(CASE WHEN i.position = -1 THEN i.itemid ELSE NULL END)) as HAT
from characters c join inventoryitems i on c.id = i.characterid
where i.inventorytype = -1
and (i.position = -101 or i.position = -1)
GROUP BY c.name[/code]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-10 : 07:18:33

now i'm really confused

I think you may just need a second join to the inventory table though i'm really not clear from your decription. can you post some sample data and expected result? as in... what is in each table and based on that sample data, which rows you expect to show in the result

Em

metroix
Starting Member

16 Posts

Posted - 2008-07-10 : 07:22:07

quote:

Originally posted by visakh16

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

thax ALOT!
thax to all of u for the support :D

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-10 : 07:33:20

Did anyone else notice that he is running MySQL?!?

metroix
Starting Member

16 Posts

Posted - 2008-07-10 : 07:36:27