Display two fields of two UNRELATED tables WITHOUT crossjoin

  • Im trying to write this query for my personal fun and training.

    Imagine we have two tables:

    Table1:

    PK, Field

    -----------------

    1, XXXXXX

    2, XXXXXX

    Table2:

    PK, Field

    -----------------

    1, XXXXXX

    2, XXXXXX

    3, XXXXXX

    Now we try this query on this two tables:

    Select Count(a.PK) as q1, Count(b.PK) as q2

    From Table1 as a, Table2 as b

    What we expect to see:

    q1,q2

    ------

    02,03

    What sql display:

    q1,q2

    ------

    06,06

    Why? Because our query automatically turns to this query:

    Select Count(a.PK) as q1, Count(b.PK) as q2

    From Table1 as a CROSSJOIN Table2 as b

    So, is it any way to we prevent CROSSJOIN?

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

  • Something like this:

    SELECT

    dt2.q1,

    dt3.q2

    FROM

    (SELECT 1) dt1(n)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);

  • In this particular case, since you want the two counts...

    SELECT q1, q2 FROM

    (SELECT Count(a.PK) as q1 FROM Table1 as a) t1,

    (SELECT Count(b.PK) as q2 FROM Table2 as b) t2

    Only in this case though.

    In general the question can't be answered. How can you meaningfully join two completely unrelated tables and have the columns related to each other? If they are completely unrelated, then they probably shouldn't even be in the same query

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/5/2013)


    In this particular case, since you want the two counts...

    SELECT q1, q2 FROM

    (SELECT Count(a.PK) as q1 FROM Table1 as a) t1,

    (SELECT Count(b.PK) as q2 FROM Table2 as b) t2

    Only in this case though.

    In general the question can't be answered. How can you meaningfully join two completely unrelated tables and have the columns related to each other? If they are completely unrelated, then they probably shouldn't even be in the same query

    Thank you very much.

    I didn't want to join this two tables. I just wanted to understand how can I display two fields of two unrelated tables in one query without sql forcing me to do a crossjoin.

    Thank you very much, with your query now my question solved.

    I just have one more question, why should we write t1 and t2 in front of select statement? If I don't write them it throw error, I can not understand why should we write them.

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

  • masoudk1990 (2/5/2013)


    I didn't want to join this two tables. I just wanted to understand how can I display two fields of two unrelated tables in one query without sql forcing me to do a crossjoin.

    Oh, that's a cross join too, it's just that because there's only one row in each subquery cross joining them produces one row.

    SELECT q1, q2 FROM

    (SELECT Count(a.PK) as q1 FROM Table1 as a) t1 CROSS JOIN

    (SELECT Count(b.PK) as q2 FROM Table2 as b) t2

    Think about it, how do you combine records of two unrelated tables without a cross join? You can't do an inner or outer join because there's no relationship, so the only alternative is a cross join

    I just have one more question, why should we write t1 and t2 in front of select statement? If I don't write them it throw error, I can not understand why should we write them.

    In front, you don't. Behind, because subqueries must have an alias.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Lynn,

    Just to understand, Could you please explain how the query works? Especially the part

    (SELECT 1) dt1(n)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);

    What does (SELECT 1) dt1(n) means?

    And how it uses all these variables dt1, dt2, dt3, q1, q2 and n? Dont they need any declaration.

    Thanks in advance.

  • dgowrij (2/5/2013)


    Hi Lynn,

    Just to understand, Could you please explain how the query works? Especially the part

    (SELECT 1) dt1(n)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);

    What does (SELECT 1) dt1(n) means?

    And how it uses all these variables dt1, dt2, dt3, q1, q2 and n? Dont they need any declaration.

    Thanks in advance.

    Ah hah! You appear to be coming from a programming background. This now makes more sense. I might be able to help straighten a few things out for you.

    A SELECT statement is not an array that gets filled in afterwards, it's built during the data retrieval process. The reason you don't define those variables is they're scoped to that query only. dt1/2/3 are derived queries, and they are table name aliases. The syntax knows that from then on that those letters are aliases to queries. q1 and q2 are field name aliases, again, defined by the syntax of the subquery mechanism within SQL Server.

    They could as easily been written as (SELECT 1 AS n) AS dt1

    The reason for the starter query is cross apply runs once for each row from the previous set. A SELECT statement must always return a set of values (even if no rows), but you have to start it with something to work from. The dt1 there is the set that the cross applies hang off of.

    As I said, all queries start with looking towards a dataset. You can build an empty set with difficulty, but it's still a set to the query. All information in the query has to belong to the built set. JOINs, of all kinds, match set to set. When you take disparate information you're basically combining two arrays in some method. Either they're linked via data as the join you know or each is repeated for every component of the other, as in a cross join. To avoid that, and to basically put in pivoted data, you create a single row set to hang your other information off of.

    I'm not sure if that helped or hurt, but I'm hoping it will give you some insight.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Well, if ALL you want is the two counts, why not do this?

    select

    Count1 = (select COUNT(*) from #Table1),

    Count2 = (select COUNT(*) from #Table2)

  • Like Olga's suggestion, this approach is also pretty simple:

    DECLARE @Table1 TABLE (PK INT, Name VARCHAR(6))

    INSERT INTO @Table1 SELECT 1, 'XXXXXX' UNION ALL SELECT 2, 'XXXXXX'

    DECLARE @Table2 TABLE (PK INT, Name VARCHAR(6))

    INSERT INTO @Table2

    SELECT 1, 'XXXXXX' UNION ALL SELECT 2, 'XXXXXX' UNION ALL SELECT 3, 'XXXXXX'

    SELECT [From Table 1]=COUNT(PK1), [From Table 1]=COUNT(PK2)

    FROM (

    SELECT PK, NULL FROM @Table1

    UNION ALL

    SELECT NULL,PK FROM @Table2) a(PK1, PK2)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Always more than one way to skin a cat, even if it is the wrong thing to do.

    Biggest unanswered question asked so far is why is this needed? Maybe I missed it but I haven't seen a real viable answer.

  • Lynn Pettis (2/5/2013)


    Biggest unanswered question asked so far is why is this needed? Maybe I missed it but I haven't seen a real viable answer.

    masoudk1990 (2/5/2013)


    Im trying to write this query for my personal fun and training.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (2/5/2013)


    Lynn Pettis (2/5/2013)


    Biggest unanswered question asked so far is why is this needed? Maybe I missed it but I haven't seen a real viable answer.

    masoudk1990 (2/5/2013)


    Im trying to write this query for my personal fun and training.

    Like I said...

  • Hi Craig,

    Thank you for the explanation. I understood (The dt* are alias name for the derived queries and the q* and n are the alias name for the columns) how it works now.

    Thank You!!!

  • dgowrij (2/5/2013)


    Hi Craig,

    Thank you for the explanation. I understood (The dt* are alias name for the derived queries and the q* and n are the alias name for the columns) how it works now.

    Thank You!!!

    My pleasure. Been a very busy two weeks here with a massive rollout so I'd lost track of some things and am digging through my post history.

    I'm glad that was able to help.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • GilaMonster - Tuesday, February 5, 2013 12:41 PM

    In this particular case, since you want the two counts...SELECT q1, q2 FROM(SELECT Count(a.PK) as q1 FROM Table1 as a) t1, (SELECT Count(b.PK) as q2 FROM Table2 as b) t2Only in this case though. In general the question can't be answered. How can you meaningfully join two completely unrelated tables and have the columns related to each other? If they are completely unrelated, then they probably shouldn't even be in the same query

    Thank you.  Actually this was exactly what I needed to do. In my particular case, I simply needed to return two column summations from Table 1, and the row count from Table 2.  The values are all related in a business sense, but not in any direct way in the schema.

      SELECT q1, q2, q3 FROM (SELECT SUM(t.ext_reg_deny) as q1, SUM(t.ext_reg_err) as q2 FROM traffic t) t1, (SELECT COUNT(r.id) as q3 FROM registration r) t2;

Viewing 15 posts - 1 through 15 (of 16 total)

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