InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:56:34 GMT20http://www.sqlservercentral.com/Forums/Topic1394934-149-1.aspx[quote][b]narendra.kongara (12/11/2012)[/b][hr]1> -- Correlated subquery using the department table in both inner and outer queries2>3> SELECT t1.*4> FROM department t15> WHERE t1.location IN6> (SELECT t2.location7> FROM department t28> WHERE t1.dept_no <> t2.dept_no)9> GO[/quote]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.Tue, 11 Dec 2012 08:57:08 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic1394934-149-1.aspxNot sure what you are askingYou can run the following in a query window in management studio and get the output - but will be left with the table in a databaseCREATE TABLE department(dept_no CHAR(4) NOT NULL,dept_name CHAR(25) NOT NULL,location CHAR(30) NULL)goinsert into department values ('d1', 'developer', 'Dallas')insert into department values ('d2', 'tester', 'Seattle')insert into department values ('d3', 'marketing', 'Dallas')select * from departmentWhat you have there looks like it was the output from something like osqlTue, 11 Dec 2012 01:59:31 GMTnigelrivetthttp://www.sqlservercentral.com/Forums/Topic1394934-149-1.aspxWhat's the question?Tue, 11 Dec 2012 01:59:27 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1394934-149-1.aspxCREATE TABLE department(dept_no CHAR(4) NOT NULL,3> dept_name CHAR(25) NOT NULL,4> location CHAR(30) NULL)5>6> insert into department values ('d1', 'developer', 'Dallas')7> insert into department values ('d2', 'tester', 'Seattle')8> insert into department values ('d3', 'marketing', 'Dallas')9>10> select * from department11> GO(1 rows affected)(1 rows affected)(1 rows affected)dept_no dept_name location------- ------------------------- ------------------------------d1 developer Dallasd2 tester Seattled3 marketing Dallas(3 rows affected)1> -- Correlated subquery using the department table in both inner and outer queries2>3> SELECT t1.*4> FROM department t15> WHERE t1.location IN6> (SELECT t2.location7> FROM department t28> WHERE t1.dept_no <> t2.dept_no)9> GOdept_no dept_name location------- ------------------------- ------------------------------d1 developer Dallasd3 marketing Dallas(2 rows affected)Tue, 11 Dec 2012 01:47:45 GMTnarendra.kongara