July 2, 2008 at 8:56 pm
If you would post your DDL and some sample data, it would be easier for us to help you.
Create some temp tables and some insert statements with sample data.:)
See here for more information on how to get the best help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Now a question for you: Is the requirement not to use loops? Because if you can use loops then smunson's answer looks correct. 😎
I don't see how this can be done with only a nested subquery because I thought that a query with a subquery will never return more rows than are in the table in the parent query. But I could be wrong about that.
Like if you had
SELECT Table_A_PrimaryKey
FROM Table_A
WHERE Table_A_PrimaryKey IN
(
-- some subquery
)
Could you ever get more rows returned than the number of rows in Table_A? :unsure:
July 2, 2008 at 9:42 pm
Basic requirement is to use subqueries, but loops are alowed too...
I can post DDL and sample data but isn't it easier way just to post Northwind MDF and LDF files so you can just attach them? (before copy them to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)
July 3, 2008 at 8:00 am
sead.j (7/2/2008)
Basic requirement is to use subqueries, but loops are alowed too...I can post DDL and sample data but isn't it easier way just to post Northwind MDF and LDF files so you can just attach them? (before copy them to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)
No, that is not easier. The idea is to be able to play with the data. People do not want to spend time downloading and installing files for someone else's exam question.
Here's how you would post the DDL and sample data.
Now you should play with Steve's loop idea.
DECLARE @Orders TABLE
(
OrderID BIGINT
,CustomerID NCHAR(5)
,EmployeeID BIGINT
,OrderDate DATETIME
)
DECLARE @Employees TABLE
(
EmployeeID BIGINT
,LastName NVARCHAR(20)
,FirstName NVARCHAR(10)
,Title NVARCHAR(30)
)
INSERT INTO @Orders
SELECT 10248, 'VINET', 5, '1996-07-04 00:00:00.000'
UNION ALL SELECT 10249, 'TOMSP', 6 , '1996-07-05 00:00:00.000'
UNION ALL SELECT 10250, 'HANAR', 4 , '1996-07-08 00:00:00.000'
UNION ALL SELECT 10251, 'VICTE', 3 , '1996-07-08 00:00:00.000'
UNION ALL SELECT 10252, 'SUPRD', 4 , '1996-07-09 00:00:00.000'
UNION ALL SELECT 10253, 'HANAR', 3 , '1996-07-10 00:00:00.000'
UNION ALL SELECT 10254, 'CHOPS' , 5 , '1996-07-11 00:00:00.000'
UNION ALL SELECT 10255, 'RICSU', 9 , '1996-07-12 00:00:00.000'
UNION ALL SELECT 10256, 'WELLI', 3, '1996-07-15 00:00:00.000'
INSERT INTO @Employees
SELECT 1, 'Davolio', 'Nancy', 'Sales Representative'
UNION ALL SELECT 2, 'Fuller' , 'Andrew', 'Vice President, Sales'
UNION ALL SELECT 3, 'Leverling' , 'Janet', 'Sales Representative'
UNION ALL SELECT 4, 'Peacock' , 'Margaret', 'Sales Representative'
UNION ALL SELECT 5, 'Buchanan' , 'Steven', 'Sales Manager'
UNION ALL SELECT 6, 'Suyama' , 'Michael', 'Sales Representative'
UNION ALL SELECT 7, 'King' , 'Robert', 'Sales Representative'
UNION ALL SELECT 8, 'Callahan', 'Laura', 'Inside Sales Coordinator'
UNION ALL SELECT 9, 'Dodsworth', 'Anne', 'Sales Representative'
SELECT *
FROM @Orders
CROSS JOIN @Employees
July 3, 2008 at 8:14 am
Here's Steve's answer using 2 temp tables and a loop.
I couldn't get the CTE to work properly.
DECLARE @Results TABLE
(
Title varchar(50),
CustomerId nchar(5)
)
DECLARE @Looper bigint, @MAXRECS bigint
SET @Looper = 1
SELECT @MAXRECS = (SELECT COUNT(*) FROM @Employees)
DECLARE @EmployeeTitles TABLE
(
Title NVARCHAR(30)
,RowNumber INT
)
INSERT INTO @EmployeeTitles
SELECT Title
, ROW_NUMBER() OVER(ORDER BY EmployeeId) AS RN
FROM @Employees
WHILE @Looper <= @MAXRECS
BEGIN
INSERT INTO @Results (Title, CustomerId)
SELECT (SELECT Title FROM @EmployeeTitles WHERE RowNumber = @Looper) AS Title
, CustomerId
FROM @Orders
SET @Looper = @Looper + 1
END
SELECT *
FROM @Results
July 3, 2008 at 10:43 am
Thank you very but very much guys, specially you Steve!
@ggraber: code you have posted doesn't work, but anyway previous Steve's code gives me some ideas and tricks so here it is and it works great:
DECLARE @Results TABLE
(
Title varchar(50),
CustomerId nchar(5)
)
DECLARE @Looper bigint, @MAXRECS bigint
SET @Looper = 1
SELECT @MAXRECS = (SELECT COUNT(*) FROM Employees)
DECLARE @EmployeeTitles TABLE
(
Title NVARCHAR(30)
,RowNumber INT
)
INSERT INTO @EmployeeTitles
SELECT Title
, ROW_NUMBER() OVER(ORDER BY EmployeeId) AS RN
FROM Employees
WHILE @Looper <= @MAXRECS
BEGIN
INSERT INTO @Results (Title, CustomerId)
SELECT (SELECT Title FROM @EmployeeTitles WHERE RowNumber = @Looper) AS Title
, CustomerId
FROM Orders
SET @Looper = @Looper + 1
END
SELECT *
FROM @Results
July 3, 2008 at 10:46 am
Here's how you would post the DDL and sample data.
DECLARE @Orders TABLE
(
OrderID BIGINT
,CustomerID NCHAR(5)
,EmployeeID BIGINT
,OrderDate DATETIME
)
DECLARE @Employees TABLE
(
EmployeeID BIGINT
,LastName NVARCHAR(20)
,FirstName NVARCHAR(10)
,Title NVARCHAR(30)
)
INSERT INTO @Orders
SELECT 10248, 'VINET', 5, '1996-07-04 00:00:00.000'
UNION ALL SELECT 10249, 'TOMSP', 6 , '1996-07-05 00:00:00.000'
UNION ALL SELECT 10250, 'HANAR', 4 , '1996-07-08 00:00:00.000'
UNION ALL SELECT 10251, 'VICTE', 3 , '1996-07-08 00:00:00.000'
UNION ALL SELECT 10252, 'SUPRD', 4 , '1996-07-09 00:00:00.000'
UNION ALL SELECT 10253, 'HANAR', 3 , '1996-07-10 00:00:00.000'
UNION ALL SELECT 10254, 'CHOPS' , 5 , '1996-07-11 00:00:00.000'
UNION ALL SELECT 10255, 'RICSU', 9 , '1996-07-12 00:00:00.000'
UNION ALL SELECT 10256, 'WELLI', 3, '1996-07-15 00:00:00.000'
INSERT INTO @Employees
SELECT 1, 'Davolio', 'Nancy', 'Sales Representative'
UNION ALL SELECT 2, 'Fuller' , 'Andrew', 'Vice President, Sales'
UNION ALL SELECT 3, 'Leverling' , 'Janet', 'Sales Representative'
UNION ALL SELECT 4, 'Peacock' , 'Margaret', 'Sales Representative'
UNION ALL SELECT 5, 'Buchanan' , 'Steven', 'Sales Manager'
UNION ALL SELECT 6, 'Suyama' , 'Michael', 'Sales Representative'
UNION ALL SELECT 7, 'King' , 'Robert', 'Sales Representative'
UNION ALL SELECT 8, 'Callahan', 'Laura', 'Inside Sales Coordinator'
UNION ALL SELECT 9, 'Dodsworth', 'Anne', 'Sales Representative'
SELECT *
FROM @Orders
CROSS JOIN @Employees
the results:
(9 row(s) affected)
(9 row(s) affected)
OrderID CustomerID EmployeeID OrderDate EmployeeID LastName FirstName Title
-------------------- ---------- -------------------- ----------------------- -------------------- -------------------- ---------- ------------------------------
10248 VINET 5 1996-07-04 00:00:00.000 1 Davolio Nancy Sales Representative
10249 TOMSP 6 1996-07-05 00:00:00.000 1 Davolio Nancy Sales Representative
10250 HANAR 4 1996-07-08 00:00:00.000 1 Davolio Nancy Sales Representative
10251 VICTE 3 1996-07-08 00:00:00.000 1 Davolio Nancy Sales Representative
10252 SUPRD 4 1996-07-09 00:00:00.000 1 Davolio Nancy Sales Representative
10253 HANAR 3 1996-07-10 00:00:00.000 1 Davolio Nancy Sales Representative
10254 CHOPS 5 1996-07-11 00:00:00.000 1 Davolio Nancy Sales Representative
10255 RICSU 9 1996-07-12 00:00:00.000 1 Davolio Nancy Sales Representative
10256 WELLI 3 1996-07-15 00:00:00.000 1 Davolio Nancy Sales Representative
10248 VINET 5 1996-07-04 00:00:00.000 2 Fuller Andrew Vice President, Sales
10249 TOMSP 6 1996-07-05 00:00:00.000 2 Fuller Andrew Vice President, Sales
10250 HANAR 4 1996-07-08 00:00:00.000 2 Fuller Andrew Vice President, Sales
10251 VICTE 3 1996-07-08 00:00:00.000 2 Fuller Andrew Vice President, Sales
10252 SUPRD 4 1996-07-09 00:00:00.000 2 Fuller Andrew Vice President, Sales
10253 HANAR 3 1996-07-10 00:00:00.000 2 Fuller Andrew Vice President, Sales
10254 CHOPS 5 1996-07-11 00:00:00.000 2 Fuller Andrew Vice President, Sales
10255 RICSU 9 1996-07-12 00:00:00.000 2 Fuller Andrew Vice President, Sales
10256 WELLI 3 1996-07-15 00:00:00.000 2 Fuller Andrew Vice President, Sales
10248 VINET 5 1996-07-04 00:00:00.000 3 Leverling Janet Sales Representative
10249 TOMSP 6 1996-07-05 00:00:00.000 3 Leverling Janet Sales Representative
10250 HANAR 4 1996-07-08 00:00:00.000 3 Leverling Janet Sales Representative
10251 VICTE 3 1996-07-08 00:00:00.000 3 Leverling Janet Sales Representative
10252 SUPRD 4 1996-07-09 00:00:00.000 3 Leverling Janet Sales Representative
10253 HANAR 3 1996-07-10 00:00:00.000 3 Leverling Janet Sales Representative
10254 CHOPS 5 1996-07-11 00:00:00.000 3 Leverling Janet Sales Representative
10255 RICSU 9 1996-07-12 00:00:00.000 3 Leverling Janet Sales Representative
10256 WELLI 3 1996-07-15 00:00:00.000 3 Leverling Janet Sales Representative
10248 VINET 5 1996-07-04 00:00:00.000 4 Peacock Margaret Sales Representative
10249 TOMSP 6 1996-07-05 00:00:00.000 4 Peacock Margaret Sales Representative
10250 HANAR 4 1996-07-08 00:00:00.000 4 Peacock Margaret Sales Representative
10251 VICTE 3 1996-07-08 00:00:00.000 4 Peacock Margaret Sales Representative
10252 SUPRD 4 1996-07-09 00:00:00.000 4 Peacock Margaret Sales Representative
10253 HANAR 3 1996-07-10 00:00:00.000 4 Peacock Margaret Sales Representative
10254 CHOPS 5 1996-07-11 00:00:00.000 4 Peacock Margaret Sales Representative
10255 RICSU 9 1996-07-12 00:00:00.000 4 Peacock Margaret Sales Representative
10256 WELLI 3 1996-07-15 00:00:00.000 4 Peacock Margaret Sales Representative
10248 VINET 5 1996-07-04 00:00:00.000 5 Buchanan Steven Sales Manager
10249 TOMSP 6 1996-07-05 00:00:00.000 5 Buchanan Steven Sales Manager
10250 HANAR 4 1996-07-08 00:00:00.000 5 Buchanan Steven Sales Manager
10251 VICTE 3 1996-07-08 00:00:00.000 5 Buchanan Steven Sales Manager
10252 SUPRD 4 1996-07-09 00:00:00.000 5 Buchanan Steven Sales Manager
10253 HANAR 3 1996-07-10 00:00:00.000 5 Buchanan Steven Sales Manager
10254 CHOPS 5 1996-07-11 00:00:00.000 5 Buchanan Steven Sales Manager
10255 RICSU 9 1996-07-12 00:00:00.000 5 Buchanan Steven Sales Manager
10256 WELLI 3 1996-07-15 00:00:00.000 5 Buchanan Steven Sales Manager
10248 VINET 5 1996-07-04 00:00:00.000 6 Suyama Michael Sales Representative
10249 TOMSP 6 1996-07-05 00:00:00.000 6 Suyama Michael Sales Representative
10250 HANAR 4 1996-07-08 00:00:00.000 6 Suyama Michael Sales Representative
10251 VICTE 3 1996-07-08 00:00:00.000 6 Suyama Michael Sales Representative
10252 SUPRD 4 1996-07-09 00:00:00.000 6 Suyama Michael Sales Representative
10253 HANAR 3 1996-07-10 00:00:00.000 6 Suyama Michael Sales Representative
10254 CHOPS 5 1996-07-11 00:00:00.000 6 Suyama Michael Sales Representative
10255 RICSU 9 1996-07-12 00:00:00.000 6 Suyama Michael Sales Representative
10256 WELLI 3 1996-07-15 00:00:00.000 6 Suyama Michael Sales Representative
10248 VINET 5 1996-07-04 00:00:00.000 7 King Robert Sales Representative
10249 TOMSP 6 1996-07-05 00:00:00.000 7 King Robert Sales Representative
10250 HANAR 4 1996-07-08 00:00:00.000 7 King Robert Sales Representative
10251 VICTE 3 1996-07-08 00:00:00.000 7 King Robert Sales Representative
10252 SUPRD 4 1996-07-09 00:00:00.000 7 King Robert Sales Representative
10253 HANAR 3 1996-07-10 00:00:00.000 7 King Robert Sales Representative
10254 CHOPS 5 1996-07-11 00:00:00.000 7 King Robert Sales Representative
10255 RICSU 9 1996-07-12 00:00:00.000 7 King Robert Sales Representative
10256 WELLI 3 1996-07-15 00:00:00.000 7 King Robert Sales Representative
10248 VINET 5 1996-07-04 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10249 TOMSP 6 1996-07-05 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10250 HANAR 4 1996-07-08 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10251 VICTE 3 1996-07-08 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10252 SUPRD 4 1996-07-09 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10253 HANAR 3 1996-07-10 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10254 CHOPS 5 1996-07-11 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10255 RICSU 9 1996-07-12 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10256 WELLI 3 1996-07-15 00:00:00.000 8 Callahan Laura Inside Sales Coordinator
10248 VINET 5 1996-07-04 00:00:00.000 9 Dodsworth Anne Sales Representative
10249 TOMSP 6 1996-07-05 00:00:00.000 9 Dodsworth Anne Sales Representative
10250 HANAR 4 1996-07-08 00:00:00.000 9 Dodsworth Anne Sales Representative
10251 VICTE 3 1996-07-08 00:00:00.000 9 Dodsworth Anne Sales Representative
10252 SUPRD 4 1996-07-09 00:00:00.000 9 Dodsworth Anne Sales Representative
10253 HANAR 3 1996-07-10 00:00:00.000 9 Dodsworth Anne Sales Representative
10254 CHOPS 5 1996-07-11 00:00:00.000 9 Dodsworth Anne Sales Representative
10255 RICSU 9 1996-07-12 00:00:00.000 9 Dodsworth Anne Sales Representative
10256 WELLI 3 1996-07-15 00:00:00.000 9 Dodsworth Anne Sales Representative
(81 row(s) affected)
July 3, 2008 at 10:48 am
Hmm... I don't see a difference between my code and yours.
What's different about yours? :ermm:
July 3, 2008 at 10:54 am
I didn't look details, but now I see that your code is (almost) the same - just remove '@' before Employees and Orders.. that's all...
BTW. Now I'm breaking my head to figure it out what 'ON 1=1' means in Michael's FULL OUTER JOIN and how it can be written in standard way?
July 3, 2008 at 11:10 am
sead.j (7/3/2008)
I didn't look details, but now I see that your code is (almost) the same - just remove '@' before Employees and Orders.. that's all...BTW. Now I'm breaking my head to figure it out what 'ON 1=1' means in Michael's FULL OUTER JOIN and how it can be written in standard way?
:hehe: Mine has @ because I am using the table variables I created. Remember, I posted my DDL. I didn't want to create permanent tables....
Now, as for FULL OUTER JOIN. Do you know what that is? It means JOIN both tables even if key does not exist in either table.
The 1=1 evaluates to true. It's the same idea as saying WHILE(1) in code. It will join all of the rows in the first table with all of the rows in the second table. In other words, a cartesian product.
"how can it be written in a standard way?
:hehe: Using a CROSS JOIN! 😉
July 3, 2008 at 11:13 am
sead.j (7/3/2008)
I didn't look details, but now I see that your code is (almost) the same - just remove '@' before Employees and Orders.. that's all...BTW. Now I'm breaking my head to figure it out what 'ON 1=1' means in Michael's FULL OUTER JOIN and how it can be written in standard way?
It's just a trick to try to work around the "no cross join" prohibition. It's essentially another way to write a CROSS JOIN. The "ON 1=1" is there to keep the parser quiet (it will whine about you not having a matching ON clause if you leave it out); since 1=1 is always true, it's just there as a dummy clause.
Now, one thing - are you really looking to emulate a cartesian product (every title in employee matched up to every order ID in order) with no JOIN statements? or - just the title/orderID combinations that match based on the standard relationship? It still ends up being a distasteful scenario, but it's a vast improvement over a "cross join" (cartesian product).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 11:29 am
@ggraber:
FULL OUTER JOIN =
LEFT OUTER JOIN
UNION
RIGHT OUTER JOIN
Am I get the point?
@matt-2: How do you mean? As I can't get the cartesian product from combinations that match based on the standard relationship (any of JOINs)!? Or I didn't understand what are you tryng to say!?
July 3, 2008 at 11:35 am
Assume you had the following
Employee table
employeeID Title
1 boss
2 manager
Order table
orderID employeeID
1 1
2 2
3 5
-------------------------------------------------------
Is the correct result:
1 boss
2 boss
3 boss
1 manager
2 manager
3 manager
OR
1 Boss
2 Manager
3 (null)
---------------------------------------------------------
If #2 is correct, it's an inline correlated sub-query (i.e. a correlated sub-query implemented within the SELECT clause). Also rather disgusting in terms of performance, but still not as crazy a concept as a cartesian product.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 11:44 am
Almost.
FULL OUTER JOIN =
RIGHT OUTER JOIN
UNION ALL
LEFT OUTER JOIN
- duplicates
Try running these two queries to see what I mean.
The second produces duplicate rows, while the first does not.
SELECT *
FROM @Employees e
FULL OUTER JOIN @Orders o ON e.employeeid = o.employeeid
SELECT *
FROM @Employees E
LEFT OUTER JOIN @Orders o ON e.employeeid = o.employeeid
UNION ALL
SELECT *
FROM @Employees E
RIGHT OUTER JOIN @Orders o ON e.employeeid = o.employeeid
ORDER BY E.EMPLOYEEID
July 3, 2008 at 1:04 pm
Hi,
If you simply skip teh WHERE condition between the tables then that will go for Cross Join.
SELECT Title, Customerid
FROM Employees, Orders
Thanks -- Vj
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply