Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display two fields of two UNRELATED tables WITHOUT crossjoin


Display two fields of two UNRELATED tables WITHOUT crossjoin

Author
Message
masoudk1990
masoudk1990
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 299
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24278 Visits: 37987
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);




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
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


masoudk1990
masoudk1990
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 299
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
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


dgowrij
dgowrij
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 75
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Olga B
Olga B
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 455
Well, if ALL you want is the two counts, why not do this?


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


dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4291 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24278 Visits: 37987
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search