How to write CROSS JOIN using subquery?

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

  • Is this a homework question? This seems like a rediculous restriction.

    I would suggest you look at the APPLY operator.

  • sead.j (7/1/2008)


    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.

    USE Northwind

    SELECT Title, Customerid

    FROM Employees

    CROSS APPLY Orders


    * Noel

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

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

  • 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

  • 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

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

  • 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

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

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

  • 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)

  • 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)

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

  • 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