January 20, 2015 at 7:11 pm
can you please help answer this?
how to join 2 heap tables with out any common fields.
for example tbl1 has
col1
1
2
3
4
5
and tbl1 2 has
col2
a
b
c
i want the output like
col1 col2
1 a
2 b
3 c
4
5
is this possible with out using row_number()?
thanks
January 20, 2015 at 8:49 pm
Why the "not using ROW_NUMBER()" restriction? It works!!
Table scripts:
CREATE TABLE A (num1 TINYINT PRIMARY KEY);
GO
INSERT INTO A(num1) VALUES (1),(2),(3),(4),(5);
CREATE TABLE B(ltr CHAR PRIMARY KEY);
GO
INSERT INTO B(ltr) VALUES('a'),('b'),('c');
SQL:
SELECT num1, x.ltr
FROM A LEFT JOIN
(SELECT B.ltr
, ROW_NUMBER() OVER(ORDER BY ltr) rn
FROM B) x
ON A.num1 = x.rn;
January 20, 2015 at 10:51 pm
wannabe1 (1/20/2015)
how to join 2 heap tables with out any common fields.
It depends on the data in those tables and the properties of that data, there aren't any other options but to inspect the data and determine whether there are any related or parallel properties.
😎
Looking at the first sample
for example tbl1 has
col1
1
2
3
4
5
shows a natural number sequence where X > 0 and X{n} + 1 = X{n + 1}.
The second sample
and tbl1 2 has
col2
a
b
c
is an unbroken alphabetical ordered sequence of lower case characters. Those characters can also be represented as sequence of character codes (T-SQL has two suitable functions ASCII() and UNICODE()), to which the previous statement also applies, apart from the definition of the lowest value, 97 for the latter sequence. Now the "relation" has been established the rest is easy;-)
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE #A (num1 TINYINT NOT NULL PRIMARY KEY);
INSERT INTO #A(num1) VALUES (1),(2),(3),(4),(5);
CREATE TABLE #B(ltr CHAR NOT NULL PRIMARY KEY);
INSERT INTO #B(ltr) VALUES('a'),('b'),('c');
SELECT
A.num1
,B.ltr
FROM #A A
LEFT OUTER JOIN #B B
ON A.num1 = (ASCII(B.ltr) - 96);
DROP TABLE #A;
DROP TABLE #B;
Results
num1 ltr
---- ----
1 a
2 b
3 c
4 NULL
5 NULL
January 20, 2015 at 11:07 pm
nice suggestion Eirikur. but my unstoppable urge for asking from OP is WHY 🙂
January 20, 2015 at 11:16 pm
I agree. Cool solution Eirikur... I just cheated on the ASCII part... and ROW_NUMBER() was a handy surrogate.
January 21, 2015 at 12:38 am
twin.devil (1/20/2015)
.. unstoppable urge for asking from OP is WHY 🙂
One of those $M questions, boils down to trying to apply a technology specific solution to an incompatible problem.
😎
pietlinden (1/20/2015)
...and ROW_NUMBER() was a handy surrogate.
The ROW_NUMBER() solution implies there is an order of the elements within the set, incidentally works when both sequences are unbroken, fails as soon as there are any elements missing. Of course the solution I suggested is relevant only to the data sample posted and that is the point I'm trying to make;-)
January 21, 2015 at 5:09 pm
Another way using Erikur's sample data and no ROW_NUMBER():
CREATE TABLE #A (num1 TINYINT NOT NULL PRIMARY KEY);
INSERT INTO #A(num1) VALUES (1),(2),(3),(4),(5);
CREATE TABLE #B(ltr CHAR NOT NULL PRIMARY KEY);
INSERT INTO #B(ltr) VALUES('a'),('b'),('c');
SELECT num1=MIN(num1), ltr=MIN(ltr)
FROM
(
SELECT num1, ltr=NULL
FROM #A
UNION ALL
SELECT NULL, ltr
FROM #B
) a
GROUP BY ISNULL(num1, ASCII(ltr) - 96);
GO
DROP TABLE #A;
DROP TABLE #B;
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
January 21, 2015 at 5:21 pm
And the safe way would be with 2 ROW_NUMBER() functions.
This is a relatively common question used to get a report with unrelated columns.
WITH cteA AS(
SELECT num1, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn --Does the order really matter?
FROM A
),
cteB AS(
SELECT ltr, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn --Does the order really matter?
FROM B
)
SELECT num1, cteB.ltr
FROM cteA
FULL
JOIN cteB ON cteA.rn = cteB.rn;
January 21, 2015 at 5:30 pm
I just read the ROW_NUMBER restriction. Is this cheating?
SELECT IDENTITY(int, 1, 1) id, num1
INTO #A
FROM A;
SELECT IDENTITY(int, 1, 1) id, ltr
INTO #B
FROM B;
SELECT num1, ltr
FROM #A
FULL
JOIN #B ON #A.id = #B.id;
DROP TABLE #A;
DROP TABLE #B;
January 23, 2015 at 8:48 am
Just curious , no business need. I was able to implement using CTE, but wanted to see if there was any ingenious solution to do it.
A case might be custom reporting where an user can drag columns from different tables to one report. Each column on the report is a select from a different table.
Thanks All
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply