InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:22:53 GMT20http://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspx[quote][b]dgowrij (2/5/2013)[/b][hr]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!!![/quote]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.Tue, 19 Feb 2013 14:46:55 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxHi 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!!!Tue, 05 Feb 2013 21:25:25 GMTdgowrijhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspx[quote][b]dwain.c (2/5/2013)[/b][hr][quote][b]Lynn Pettis (2/5/2013)[/b][hr]Biggest unanswered question asked so far is why is this needed? Maybe I missed it but I haven't seen a real viable answer.[/quote][quote][b]masoudk1990 (2/5/2013)[/b][hr]Im trying to write this query for my personal fun and training.[/quote][/quote]Like I said...Tue, 05 Feb 2013 20:56:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspx[quote][b]Lynn Pettis (2/5/2013)[/b][hr]Biggest unanswered question asked so far is why is this needed? Maybe I missed it but I haven't seen a real viable answer.[/quote][quote][b]masoudk1990 (2/5/2013)[/b][hr]Im trying to write this query for my personal fun and training.[/quote]Tue, 05 Feb 2013 20:48:06 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxAlways 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.Tue, 05 Feb 2013 20:43:21 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxLike Olga's suggestion, this approach is also pretty simple:[code="sql"]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)[/code]Tue, 05 Feb 2013 18:19:44 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxWell, if ALL you want is the two counts, why not do this?[code="sql"]select Count1 = (select COUNT(*) from #Table1), Count2 = (select COUNT(*) from #Table2)[/code]Tue, 05 Feb 2013 18:08:58 GMTOlga Bhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspx[quote][b]dgowrij (2/5/2013)[/b][hr]Hi Lynn,Just to understand, Could you please explain how the query works? Especially the part[code="sql"](SELECT 1) dt1(n) CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1) CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);[/code]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.[/quote]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 dt1The 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.Tue, 05 Feb 2013 17:45:30 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxHi Lynn,Just to understand, Could you please explain how the query works? Especially the part[code="sql"](SELECT 1) dt1(n) CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1) CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);[/code]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.Tue, 05 Feb 2013 17:00:12 GMTdgowrijhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspx[quote][b]masoudk1990 (2/5/2013)[/b][hr]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.[/quote]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.[code="sql"]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[/code]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[quote]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.[/quote]In front, you don't. Behind, because subqueries must have an alias.Tue, 05 Feb 2013 13:47:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspx[quote][b]GilaMonster (2/5/2013)[/b][hr]In this particular case, since you want the two counts...[code="sql"]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[/code]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[/quote]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.Tue, 05 Feb 2013 13:03:59 GMTmasoudk1990http://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxIn this particular case, since you want the two counts...[code="sql"]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[/code]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 queryTue, 05 Feb 2013 12:41:29 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxSomething like this:[code="sql"]SELECT dt2.q1, dt3.q2FROM (SELECT 1) dt1(n) CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1) CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);[/code]Tue, 05 Feb 2013 12:38:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416029-1292-1.aspxIm trying to write this query for my personal fun and training.Imagine we have two tables:[i]Table1:[/i]PK, Field-----------------1, XXXXXX2, XXXXXX[i]Table2:[/i]PK, Field-----------------1, XXXXXX2, XXXXXX3, XXXXXXNow we try this query on this two tables:[code="sql"]Select Count(a.PK) as q1, Count(b.PK) as q2From Table1 as a, Table2 as b[/code]What we expect to see:q1,q2------02,03What sql display:q1,q2------06,06Why? Because our query automatically turns to this query:[code="sql"]Select Count(a.PK) as q1, Count(b.PK) as q2From Table1 as a [b]CROSSJOIN[/b] Table2 as b[/code]So, is it any way to we prevent CROSSJOIN?Tue, 05 Feb 2013 12:24:35 GMTmasoudk1990