July 1, 2008 at 2:55 am
USE Northwind
SELECT Title, Customerid
FROM Employees
CROSS JOIN Orders
So, what I wanna do is to get the same results but using subquery (subqueries) and/or UNION operator(s). Also WHILE loops are permitted together with IF/ELSE.
However, JOINs are not permitted.
July 1, 2008 at 5:53 am
Is this a homework question? This seems like a rediculous restriction.
I would suggest you look at the APPLY operator.
July 1, 2008 at 8:33 am
sead.j (7/1/2008)
USE NorthwindSELECT Title, Customerid
FROM Employees
CROSS JOIN Orders
So, what I wanna do is to get the same results but using subquery (subqueries) and/or UNION operator(s). Also WHILE loops are permitted together with IF/ELSE.
However, JOINs are not permitted.
USE Northwind
SELECT Title, Customerid
FROM Employees
CROSS APPLY Orders
* Noel
July 1, 2008 at 10:01 am
Michael Earl (7/1/2008)
Is this a homework question? This seems like a rediculous restriction.I would suggest you look at the APPLY operator.
It's not homework, but it is an exam question.
Actually CROSS APPLY is not allowed too. Sincerly I have try everything but without results. What about WHILE loops?
July 1, 2008 at 10:25 am
sead.j (7/1/2008)
Michael Earl (7/1/2008)
Is this a homework question? This seems like a rediculous restriction.I would suggest you look at the APPLY operator.
It's not homework, but it is an exam question.
Actually CROSS APPLY is not allowed too. Sincerly I have try everything but without results. What about WHILE loops?
This seems like a strange question, are they asking you how to get the Cartesian product of the tables without using cross-join?
can you post the exact exam question?
July 1, 2008 at 10:49 am
sead.j (7/1/2008)
Michael Earl (7/1/2008)
Is this a homework question? This seems like a rediculous restriction.I would suggest you look at the APPLY operator.
It's not homework, but it is an exam question.
Actually CROSS APPLY is not allowed too. Sincerly I have try everything but without results. What about WHILE loops?
How about:
USE Northwind
SELECT Title, Customerid
FROM Employees, Orders
* Noel
July 1, 2008 at 11:40 am
Actually, I'm not sure there is any sane way to cross join two tables without using cross join.
I can imagine that you could create a temp table, with all the columns that you need from both tables, then insert the first table a number of times equal to the number of rows in the second table; include a unique row-number or ID. Then do the same thing to the second table, inserting it into a separate temp table a number of times equal to the number of rows in the first table. Then do an update statement in the first temp table, based on the row-number/ID.
Would it work? Sure. Is it about as stupid as tying your own shoes together and then trying to sprint? Sure. (One of the key abilities of a good data analyst is the ability to deliberately think like a total idiot. Has an amazing number of applications, so long as one can turn it off and on at will and can differentiate the thought processes.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 11:52 am
This is technically not a CROSS JOIN:
[font="Courier New"]USE Northwind
SELECT Title, Customerid
FROM Employees
FULL OUTER JOIN Orders ON 1=1[/font]
The query optimizer found it a pretty dumb thing to do as well. It performs far worse than a CROSS JOIN.
July 1, 2008 at 12:56 pm
Michael, the original spec said that joins aren't allowed. Loops, If/Else, etc., are, but joins aren't. I'm guessing the person who wrote the question thinks he came up with something seriously clever, in a Paula Bean kind of way (cf. http://www.thedailywtf.com). I could be wrong, but that's what it feels like right now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 12:47 am
@steveb-2: well yes 🙂 As I know, CROSS JOIN is Cartesian product 🙂
The question on exam is pretty simple:
USE Northwind
SELECT Title, Customerid
FROM Employees
CROSS JOIN Orders
Make the same query using subqueries but without using any type of JOIN clause (including CROSS JOIN/CROSS APPLY).
That's the question. I was asking my professor about UNION and WHILE loops, and he told me they can be used just like variables. The key thing is to solve the problem with subqueries...
@noeld: you have written an INNER JOIN 🙂
@michael-2: well sure it's not a CROSS JOIN, but it is still JOIN and JOINs are not allowed and accent is on subqueries!
@GSsquared: without temp tables... Well I feel the same, but it is definitely a brain-weighing question...
July 2, 2008 at 1:29 am
Michael Earl (7/1/2008)
This is technically not a CROSS JOIN:[font="Courier New"]USE Northwind
SELECT Title, Customerid
FROM Employees
FULL OUTER JOIN Orders ON 1=1[/font]
The query optimizer found it a pretty dumb thing to do as well. It performs far worse than a CROSS JOIN.
what this mean: ON 1=1 ? How can it be written in standard way?
July 2, 2008 at 6:52 am
I'm of the opinion that this is the old "TRICK QUESTION". I don't happen to have NORTHWIND available to me, nor have I ever done anything with it, but doesn't it seem rather odd to someone other than me to go looking for Title and CustomerId within an Employees table?
How about:
USE Northwind
SELECT Title, CustomerId
FROM Orders
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2008 at 7:07 am
Come to think of it, I realized this wasn't all that hard if a table variable is ok...
Try this on for size - I have no NORTHWIND available, so I couldn't validate the code:
USE Northwind
DECLARE @RESULTS TABLE (
Title varchar(50),
CustomerId int
)
DECLARE @LOOPER bigint, @MAXRECS bigint
SET @LOOPER = 1
SELECT @MAXRECS = (SELECT COUNT(*) FROM Employees)
WHILE @LOOPER <= @MAXRECS
BEGIN
INSERT INTO @RESULTS (Title, CustomerId)
SELECT Title, CustomerId
FROM Orders
SET @LOOPER = @LOOPER + 1
END
SELECT *
FROM @RESULTS
Steve
(aka smunson)
:):):)
smunson (7/2/2008)
I'm of the opinion that this is the old "TRICK QUESTION". I don't happen to have NORTHWIND available to me, nor have I ever done anything with it, but doesn't it seem rather odd to someone other than me to go looking for Title and CustomerId within an Employees table?How about:
USE Northwind
SELECT Title, CustomerId
FROM Orders
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2008 at 7:12 am
Steve, you're right, but this is not a key thing - it can be Customes instead of Employees if you like... the problem is the same... and is not solved yet...
July 2, 2008 at 7:51 am
Really? Perhaps you need to restate the problem. What it appears you are asking for is a solution to a general class of problem: Using subqueries and/or UNION, get the same result as a CROSS JOIN or CROSS APPLY, without using any kind of JOIN. I can't see any way to do that, as in theory, without a join, you need a loop, and at that point, you lose your subquery.
Also, if the tables involved aren't really relevant to the problem, then it's rather unrealistic to ask the question based on specific tables and then expect solutions that solve the entire class in a particular way, having NOT provided the entire true nature of the problem.
Or have I somehow missed your point? Try to remember that WE aren't the students here... you came to US for help.
Steve
(aka smunson)
:):):)
sead.j (7/2/2008)
Steve, you're right, but this is not a key thing - it can be Customes instead of Employees if you like... the problem is the same... and is not solved yet...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply