February 5, 2013 at 12:24 pm
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.
February 5, 2013 at 12:39 pm
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);
February 5, 2013 at 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) 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
February 5, 2013 at 1:04 pm
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.
February 5, 2013 at 1:47 pm
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
February 5, 2013 at 5:00 pm
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.
February 5, 2013 at 5:45 pm
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.
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
February 5, 2013 at 6:08 pm
Well, if ALL you want is the two counts, why not do this?
select
Count1 = (select COUNT(*) from #Table1),
Count2 = (select COUNT(*) from #Table2)
February 5, 2013 at 6:19 pm
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 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
February 5, 2013 at 8:43 pm
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.
February 5, 2013 at 8:48 pm
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 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
February 5, 2013 at 8:56 pm
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...
February 5, 2013 at 9:25 pm
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!!!
February 19, 2013 at 2:46 pm
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.
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
March 28, 2017 at 6:31 am
GilaMonster - Tuesday, February 5, 2013 12:41 PMIn 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