Tag Info

New answers tagged

0

I have a function that I keep on the master database for these cases. I will start by creating my function as per code below: use master go create function dbo.getCities( @thecities varchar (100)) returns table as return WITH cteTally AS (SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n FROM ...


0

This query can expressed less awkwardly as a join. We join the two tables social_objects and social_permissions on their IDs (that is, match them up with IDs) and simplify the WHERE clause. SELECT so.* FROM social_objects so, social_permissions sp WHERE so.id = sp.id AND ((sp.model = 'User' AND sp.foreign_key = 1) OR (sp.model = 'SocialList' AND ...


2

I think the best way would be to "prove" that your subqueries - that are compared with a value - do always return one or no value. By making sure they all have either: TOP (1) ... ORDER BY ... (or the equivalent modern ORDER BY ... FETCH NEXT 1 ROWS ONLY) WHERE clause that uses the primary or a unique key, e.g.: WHERE table_id = 496 HAVING clause that ...


0

Just going to let you guys know what I did, thanks for responding. I ended up sticking with my original query but added keys to my table, which are apparently a thing I didn't know about. When I did ALTER TABLE tblstatushistory ADD KEY (id), ADD KEY (itemtype) It dropped the time waaaay down. Thanks for pointing me towards resources that taught me about ...


1

The EXPLAIN statement can be very useful when trying to track down the cause of a slow query. There's some great explanations of how to use the EXPLAIN statement and how to interpret its results here (with more real-word examples at /case{2..4}) and the official documentation here. Hope this helps!


0

Sounds like you want only the orders with a certain status/type... SELECT tblstockingorders.*, tblstatushistory.status AS actualStatus FROM tblstockingorders INNER JOIN tblstatushistory ON tblstatushistory.id=tblstockingorders.stockingorderid WHERE tblstatushistory.itemtype=2 AND tblstatushistory.status=0 editted version ...


1

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory. Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance ...


0

I found one way that works, finally. 'm not sure why I was throwing SQL syntax errors before (I didn't save a copy of my old query attempts) but the following worked, as a JOIN inside a SUBQUERY: SELECT skill_list_expanded.description FROM skill_list_expanded WHERE skill_list_expanded.skill_id = 1 AND skill_list_expanded.subclass = 1 AND ...



Top 50 recent answers are included