How to write CROSS JOIN using subquery?

Question

How to write CROSS JOIN using subquery?

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

smunson (7/2/2008)
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...

The bigger question in my mind is what kind of a course wants to teach you to use loops and WHILE over JOINs? That's like trying to teach you how to run a combustion engine without motor oil. Why would you ever want to "know" this technique, because when would it ever be useful?

Sounds like an entirely (hmm - let's word this politely) "wrong-headed" question. The perf implications of such a "technique" are woeful if you should play with some actual "right-sized" data.

If I may ask - what the heck kind of class is this for?

----------------------------------------------------------------------------------
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?

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 2

Guys, feel free to correct me, but I believe that the answer to this Exam Question is "NO!". You can do RBAR to solve several problems within SQL Server. But 99.99% of the time, for data access, using joins, tally tables, and even CTE, you do not need to. This question sounds like it was written by an OOP programmer who recently learned the "T-SQL Langauge".

Just my unsolicited 2 cents.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

MentalWhiteNoise (7/2/2008)
but I believe that the answer to this Exam Question is "NO!".

Have to agree with this, there are a lot better examples of SQL puzzles that can be solved by different methods and this isn't a very good one at all

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Matt,

I by no means advocate a loop solution, but was just trying to get the OP to come clean on the whole deal instead of feeding it in piecemeal. I quite agree that there's little point in pursuing such methods the vast majority of the time - which kind of leads to the most important question of all, ALL the time: Why do you need that kind of solution? The why will ALWAYS tell you 99.9999% of what you need to know. The rest is cosmetic details.

Sead .J,

Again, let's be clear about exactly what the problem is. If this is some exam question, then either the question writer isn't being realistic in expecting an answer, or it's one of those questions you leave behind and focus on the ones that CAN be answered. If there's anything to be learned from such a question, is that it's a case where we really don't HAVE enough information.

Regardless of whether it gets solved or not, I know that I don't have enough information, and that I don't have a clear picture of the problem that needs solving. If what I previously posted about a general class of problem is the case, then I don't believe there can be a solution. The reason I don't believe there is one is because you appear to be seeking to do a multiply operation without any way to repeat the addition process in a programmatic fashion.

Steve

(aka smunson)

:):):)

Matt Miller (7/2/2008)
smunson (7/2/2008)
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...
The bigger question in my mind is what kind of a course wants to teach you to use loops and WHILE over JOINs? That's like trying to teach you how to run a combustion engine without motor oil. Why would you ever want to "know" this technique, because when would it ever be useful?
Sounds like an entirely (hmm - let's word this politely) "wrong-headed" question. The perf implications of such a "technique" are woeful if you should play with some actual "right-sized" data.
If I may ask - what the heck kind of class is this for?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

smunson (7/2/2008)
Matt,
I by no means advocate a loop solution, but was just trying to get the OP to come clean on the whole deal instead of feeding it in piecemeal. I quite agree that there's little point in pursuing such methods the vast majority of the time - which kind of leads to the most important question of all, ALL the time: Why do you need that kind of solution? The why will ALWAYS tell you 99.9999% of what you need to know. The rest is cosmetic details.

Oh - totally agree. I just hit the "quote" button instead of the reply, and was too lazy to do anything about it...:) I wasn't implying you were "on board" for any of this, it just annoys me to see this kind of question as an "exam" question, since pretty much every possible way to do this leads to some ugly practice or another, meaning NOT the kind of stuff you want to reinforce.

----------------------------------------------------------------------------------
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?

noeld SSC Guru Points: 96590 More actions · Answer 6

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...

Isn't the "comma" on the the FROM clause (without a WHERE clause) what you were looking for ?

No JOIN, No APPLY, No IF, No Loop, No While, still you get a CROSS JOIN

* Noel

sead.j Say Hey Kid Points: 703 More actions · Answer 7

smunson (7/2/2008)
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

Forgive me Steve, I didn't see this answer... anyway I have executed the query and get this error:

Msg 207, Level 16, State 1, Line 13

Invalid column name 'Title'.

I don't know am I blind or too tired but really can't see what's wrong?

People, I agree with your oppinions about this exam question... but please note, I'm a student, not a professor (one day maybe 😉 I will say that this question sounds pretty stupid and totally unuseful rather then tricky, but what to do, that's life...

I'm curious about FULL OUTER JOIN - what do you think can it be solved without using while loops, but just subqueries?

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

A comma is the same as a cross join.

It returns a cartesian product. 😉

Personally, I don't see how it is possible using a nested subquery.

If you do find an answer, please post it here. That way we'll all learn something.:)

Thanks.

sead.j Say Hey Kid Points: 703 More actions · Answer 9

comma is just an abbreviation for CROSS JOIN... I think that only solution is to use Steve's code with WHILE loops... but I can't figure it out where is the mistake...

sgmunson SSC Guru Points: 110640 More actions · Answer 10

You'll have to look at the Orders table to ensure that Title is a field within it. If it's supposed to come from Employees instead, then I'll have to update the query to look at this a somewhat different way. Frankly, before I bother, why not ask the professor WHY one would want to pursue this method. What exactly, is he/she trying to teach?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

GSquared SSC Guru Points: 260824 More actions · Answer 11

Suggest my two-temp-tables-and-an-update method, see if that gets what he wanted.

- 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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

sead.j (7/2/2008)
smunson (7/2/2008)
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
Forgive me Steve, I didn't see this answer... anyway I have executed the query and get this error:
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Title'.
I don't know am I blind or too tired but really can't see what's wrong?
People, I agree with your oppinions about this exam question... but please note, I'm a student, not a professor (one day maybe 😉 I will say that this question sounds pretty stupid and totally unuseful rather then tricky, but what to do, that's life...
I'm curious about FULL OUTER JOIN - what do you think can it be solved without using while loops, but just subqueries?

Steve has the right idea, but he's pull the wrong info from the table (since as he mentioned, he doesn't know the table structure.

The answer is to use a cursor to pull titles out of employee, then run an insert based on ORDERS where one of the columns is the variable as pulled out of the cursor. Once the loop finishes, the table you've been inserting into has all of the values you want.

----------------------------------------------------------------------------------
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?

sgmunson SSC Guru Points: 110640 More actions · Answer 13

First, I suspect the following code will run VERY POORLY, but here goes anyway, and if EmployeeId is not the Primary Key field in the Employee table, replace that in the Row Number's ORDER BY clause with whatever is.

USE Northwind

DECLARE @RESULTS TABLE (

Title varchar(50),

CustomerId int

)

DECLARE @LOOPER bigint, @MAXRECS bigint

SET @LOOPER = 1

SELECT @MAXRECS = (SELECT COUNT(*) FROM Employees)

;WITH EMPTITLES AS (

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 EMPTITLES WHERE RN=@LOOPER) AS Title, CustomerId

FROM Orders

SET @LOOPER = @LOOPER + 1

END

SELECT *

FROM @RESULTS

Again, I can't test it as I have no access to a copy of Northwind.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

noeld SSC Guru Points: 96590 More actions · Answer 14

smunson (7/2/2008)
You'll have to look at the Orders table to ensure that Title is a field within it. If it's supposed to come from Employees instead, then I'll have to update the query to look at this a somewhat different way. Frankly, before I bother, why not ask the professor WHY one would want to pursue this method. What exactly, is he/she trying to teach?
Steve
(aka smunson)
:):):)

Totally agree with this part ..... What exactly, is he/she trying to teach?

* Noel

sead.j Say Hey Kid Points: 703 More actions · Answer 15

What is she trying to teach? Have no idea, just like you guys... Only reason I can see is brain weighing - if you like math, you'll know what I'm trying to say...