Tag Info

New answers tagged

3

why didn`t the first query fail? Because this is valid SQL. First of all consider that it is valid to run a SELECT against the table that doesn't reference any columns from that table. SELECT DISTINCT 'foo' FROM product_template WHERE type = 'import'; The above will return a single row result of foo if any rows exist that match the WHERE ...


1

To answer your first question: In the first query, some records are filtered out AFTER getting the result of the left join. These rows are the ones that meet this condition: card.package_id =14 AND user_card.user_id <> 5 [AND user_card.user_id IS NOT NULL] On the other hand, in the second query, ALL result rows with "card.package_id =14" are ...


0

Another way: WITH StatusChanges AS ( SELECT HistoryID,PartID,Status, DateAdded, rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded) FROM aAssetHistory ) SELECT X.SerialNumber, X.PartNumber, X.ProjectName, X.ProjectNumber, X.AssetDescription, X.Available, X.Location, X.LocStatus, X.AssetComment, X.Disposition, X.Fund, ...


0

WITH StatusChanges AS ( SELECT HistoryID,PartID,Status, DateAdded, rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded) FROM aAssetHistory ) SELECT A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber, A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName , ( SELECT Top 1 ...


2

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases. I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;) Schema and ...


2

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments. I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it. ...


1

As the comments have pointed out there are multiple tools available to format the query and some suggestions. http://format-sql.com http://poorsql.com Use [schema_name].[object_name] for all items Especially in Order By and Where clauses, avoid using the "Order By 5" style syntax. http://spaghettidba.com/2012/03/15/how-to-eat-a-sql-elephant/ Also ...



Top 50 recent answers are included