Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Display two fields of two UNRELATED tables WITHOUT crossjoin Expand / Collapse
Author
Message
Posted Tuesday, February 05, 2013 12:24 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
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.
Post #1416029
Posted Tuesday, February 05, 2013 12:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:47 PM
Points: 22,504, Visits: 30,216
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);





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)
Post #1416037
Posted Tuesday, February 05, 2013 12:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1416038
Posted Tuesday, February 05, 2013 1:03 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
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.
Post #1416052
Posted Tuesday, February 05, 2013 1:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1416072
Posted Tuesday, February 05, 2013 5:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 28, 2013 5:32 AM
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.
Post #1416167
Posted Tuesday, February 05, 2013 5:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1416180
Posted Tuesday, February 05, 2013 6:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 4:26 PM
Points: 582, Visits: 451
Well, if ALL you want is the two counts, why not do this?

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

Post #1416190
Posted Tuesday, February 05, 2013 6:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1416194
Posted Tuesday, February 05, 2013 8:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:47 PM
Points: 22,504, Visits: 30,216
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

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)
Post #1416212
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse