URGENT! Help with SQL query - 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

General SQL Server Forums

New to SQL Server Programming

URGENT! Help with SQL query

Author

Topic

B_Velli
Starting Member

2 Posts

Posted - 2012-08-03 : 11:01:42

I was given a SQL query as a part of a job training exercise. I can understand most of this query, except how to fix it, and where there would be an error. Please, anyone with feedback, let me hear your idea of how to make this query more efficient, and if it will work.

SELECT o.*, (
SELECT 1 FROM order_shipping os
WHERE os.order_id=o.order_id
) as shipped
FROM orders o HAVING o.type=5 AND shipped != NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 11:13:49

first let us know what your understanding from above query is and how you expect it to work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

B_Velli
Starting Member

2 Posts

Posted - 2012-08-03 : 21:55:15

Well, I have experience working with queries such as using those statements. You SELECT a column, FROM a certain table, but I am not used to seeing another SELECT statement inside of another. It almost looks like a JOIN. I was just given this problem, no details or anything like that. Just work the problem out to make it more efficient, and if there is mistakes fix them. If I were to do this, it would come out like this.

SELECT o.*
FROM order_shipping
WHERE os.order_id = o.order_id
HAVING o.type = 5 AND shipped != null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 22:12:12

quote:
Originally posted by B_Velli

Well, I have experience working with queries such as using those statements. You SELECT a column, FROM a certain table, but I am not used to seeing another SELECT statement inside of another. It almost looks like a JOIN. I was just given this problem, no details or anything like that. Just work the problem out to make it more efficient, and if there is mistakes fix them. If I were to do this, it would come out like this.

SELECT o.*
FROM order_shipping
WHERE os.order_id = o.order_id
HAVING o.type = 5 AND shipped != null

nope this is not equivalent to posted query as it does not refer orders table at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 22:25:17

Actually what first query does is to return orders which have been shipped. the inner query checks for matches in orders_shipping to check if order is shipped or not

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources