Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can any one explain how this query execute Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 12:50 AM
Points: 2, Visits: 27
selecting table
Post #1394934
Posted Tuesday, December 11, 2012 1:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 13,724, Visits: 10,680
What's the question?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1394942
Posted Tuesday, December 11, 2012 1:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
Not sure what you are asking
You can run the following in a query window in management studio and get the output - but will be left with the table in a database

CREATE TABLE department
(
dept_no CHAR(4) NOT NULL,
dept_name CHAR(25) NOT NULL,
location CHAR(30) NULL
)
go
insert into department values ('d1', 'developer', 'Dallas')
insert into department values ('d2', 'tester', 'Seattle')
insert into department values ('d3', 'marketing', 'Dallas')

select * from department

What you have there looks like it was the output from something like osql



Cursors never.
DTS - only when needed and never to control.
Post #1394943
Posted Tuesday, December 11, 2012 8:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 1,882, Visits: 2,028
narendra.kongara (12/11/2012)
1> -- Correlated subquery using the department table in both inner and outer queries
2>
3> SELECT t1.*
4> FROM department t1
5> WHERE t1.location IN
6> (SELECT t2.location
7> FROM department t2
8> WHERE t1.dept_no <> t2.dept_no)
9> GO

The correlated subquery works by executing once for each row in the department table aliased as t1. For each of those records, it sees if the location in the record of t1 exists in the same department table aliased as t2 but with a different dept_no. Since Dallas is the only location with multiple departments in your sample data, it is the only location that satisfies the WHERE clause.
Post #1395172
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse