3 failed SQL Attempts to insert a value + a select statement result

[Bohbo]

I am trying to take a task ID using 350 in this example. And based on a select criteria in this case looking at whether or not a mob is non named, race 455, in warrens.

So the idea is you can test this with a select * from npc_types to test your selection statement. Once you have that correct take the list of IDs select id from npc_types and insert that + fixed value of 350 into the goal lists table.

Any tips here to get any of the methods working would be appreciated. I feel like its a good teachable moment through my 3 examples of failure.

foreach(SELECT id
FROM npc_types
inner join spawnentry on npc_types.id = spawnentry.npcID
inner join spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID
where zone = 'warrens' and race = 455 and name not like '#%'
,
insert into goallists (listid, entry)
values (350,id))

insert into goallists (listid, entry)

set @goalid=
SELECT id
FROM npc_types
inner join spawnentry on npc_types.id = spawnentry.npcID
inner join spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID
where zone = 'warrens' and race = 455 and name not like '#%'
insert into goallists (listid, entry)
values (350,@goalid)



insert into goallists (listid, entry)

SELECT id
FROM npc_types
inner join spawnentry on npc_types.id = spawnentry.npcID
inner join spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID
where zone = 'warrens' and race = 455 and name not like '#%'

values (350,id)

[Zaela_S]

http://www.w3schools.com/sql/sql_insert_into_select.asp

Don't fully understand what you're trying to do, but maybe something like:


INSERT INTO goallists (listid, entry)
SELECT 350, npc_types.id FROM npc_types
JOIN spawnentry ON npc_types.id = spawnentry.npcID
JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID
WHERE zone = 'warrens' AND race = 455 AND name NOT LIKE '#%'

[Bohbo]

http://www.w3schools.com/sql/sql_insert_into_select.asp

Don't fully understand what you're trying to do, but maybe something like:


INSERT INTO goallists (listid, entry)
SELECT 350, npc_types.id FROM npc_types
JOIN spawnentry ON npc_types.id = spawnentry.npcID
JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID
WHERE zone = 'warrens' AND race = 455 AND name NOT LIKE '#%'



That nearly did it! So sometimes i want to make a task to kill any mob in a zone. So i used a goallist and added all the mobs. I wanted to have a sql script handle it. Or say a subset like Kill all skeletons.

EDIT:: the only reason that didn't work was i got a duplicate key value, but i see how the select 350, search works for the insert now thanks

EDIT::: is there a way to have it return only unique id / key values?

EDIT:::: This works perfectly and filters out doubles!

INSERT INTO goallists (listid, entry)
SELECT 350, npcid
FROM npc_types
JOIN spawnentry ON npc_types.id = spawnentry.npcID
JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID
WHERE zone = 'warrens' AND race = 455 AND name NOT LIKE '#%'
group by npcid