December 11, 2012 at 1:47 am
selecting table
December 11, 2012 at 1:59 am
What's the question?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 11, 2012 at 1:59 am
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.
December 11, 2012 at 8:57 am
narendra.kongara (12/11/2012)
1> -- Correlated subquery using the department table in both inner and outer queries2>
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply