Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM 4e_magic_items INNER JOIN 4e_magic_item_levels ON 4e_magic_items.id = 4e_magic_item_levels.itemid INNER JOIN 4e_monster_sources ON 4e_magic_items.source = 4e_monster_sources.id WHERE (itemlevel BETWEEN 1 AND 30) AND source!=16 AND source!=2 AND source!=5 AND source!=13 AND source!=15 AND source!=3 AND source!=4 AND source!=12 AND source!=7 AND source!=14 AND source!=11 AND source!=10 AND source!=8 AND source!=1 AND source!=6 AND source!=9 AND type!='Arms' AND type!='Feet' AND type!='Hands' AND type!='Head' AND type!='Neck' AND type!='Orb' AND type!='Potion' AND type!='Ring' AND type!='Rod' AND type!='Staff' AND type!='Symbol' AND type!='Waist' AND type!='Wand' AND type!='Wondrous Item' AND type!='Alchemical Item' AND type!='Elixir' AND type!='Reagent' AND type!='Whetstone' AND type!='Other Consumable' AND type!='Companion' AND type!='Mount' AND (type!='Armor' OR (false )) AND (type!='Weapon' OR (false )) ORDER BY type ASC, itemlevel ASC, name ASC
- select *
- from
- 4e_magic_items mi
- ,4e_magic_item_levels mil
- ,4e_monster_sources ms
- where mi.id = mil.itemid
- and mi.source = ms.id
- and itemlevel between 1 and 30
- and source not in(16,2,5,13,15,3,4,12,7,14,11,10,8,1,6,9)
- and type not in(
- 'Arms' ,'Feet' ,'Hands' ,'Head' ,'Neck' ,'Orb' ,
- 'Potion' ,'Ring' ,'Rod' ,'Staff' ,'Symbol' ,'Waist' ,
- 'Wand' ,'Wondrous Item' ,'Alchemical Item' ,'Elixir' ,
- 'Reagent' ,'Whetstone' ,'Other Consumable' ,'Companion' ,
- 'Mount'
- )
- and ((type != 'Armor') or (false))
- and ((type != 'Weapon') or (false))
- order by
- type asc
- ,itemlevel asc
- ,name asc
- /*
- Some thoughts:
- ==============
- 0 - Formatting really matters, in SQL even more than most languages.
- 1 - consider selecting only the columns you need, not "*"
- 2 - use of table aliases makes it short & clear ("MI", "MIL" in my example)
- 3 - joins in the WHERE clause will un-clutter your FROM clause
- 4 - use NOT IN for long lists
- 5 - logically, the last two lines can be added to the "type not in" section.
- I'm not sure why you have the "or false", but I'll assume some good reason
- and leave them here.
- */
- SELECT x, y, z
- FROM a, b
- WHERE fiz = 1
- AND foo = 2
- AND a.x = b.y
- AND b.z IN (SELECT q, r, s, t
- FROM c, d, e
- WHERE c.q = d.r
- AND d.s = e.t
- AND c.gar IS NOT NULL)
- ORDER BY b.gonk
Add Comment
Please, Sign In to add comment