need repetition of a table when joined with another table

Question

Post reply

need repetition of a table when joined with another table

phani.gudmines

SSC Veteran

Points: 216
More actions
July 29, 2014 at 6:01 am

#291518

table1
id value
1 11
2 12
3 13
4 14
table2
id1 value1
1 21
2 22
1 31
2 32
in need output as follows
id value id1 value1
1 11 1 21
2 12 2 22
3 13 null null
4 14 null null
1 11 1 31
2 12 2 32
3 13 null null
4 14 null null
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT id, value, id1, value1

FROM #Table1 t1

CROSS JOIN (SELECT TOP(SELECT TOP(1) [Rows] = COUNT(*) FROM #table2 GROUP BY id1 ORDER BY COUNT(*) DESC) n FROM (VALUES (1), (2), (3), (4), (5)) rc (n)) x

LEFT JOIN (SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY value1) FROM #Table2) t2 ON t2.id1 = t1.id AND t2.rn = x.n

Starter for 10.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Sample data setup:

DROP TABLE #Table1

CREATE TABLE #Table1 (id int, value int)

INSERT INTO #Table1 (id, value) VALUES

(1, 11),

(2, 12),

(3, 13),

(4, 14)

DROP TABLE #table2

CREATE TABLE #table2 (id1 INT, value1 INT)

INSERT INTO #table2 (id1, value1) VALUES

(1, 21),

(2, 22),

(1, 31),

(2, 32)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

This will come close to what you want:

declare @two table (id int, value int)

insert into @one select 1, 11 union all select 2, 12 union all select 3, 13 union all select 4, 14

insert into @two select 1, 21 union all select 2, 21 union all select 1, 31 union all select 2, 32

select *

from @one one

full outer join @two two

on one.id = two.id

Only the duplicates of the non-matching rows won't be produced by the above query.

Edit: I see Chriss provided a sample including the duplicates...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

phani.gudmines SSC Veteran Points: 216 More actions · Answer 4

Hi chris

seems that you are using 1,2,3,4,5 staticly.we dont know how may rows we get as described.i have just given u a sample data.

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

phani.gudmines (7/29/2014)
Hi chris
seems that you are using 1,2,3,4,5 staticly.we dont know how may rows we get as described.i have just given u a sample data.
Thanks

How many rows might you expect?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

wBob Right there with Babe Points: 715 More actions · Answer 6

It's not entirely clear why you expect to get the 3 and 4 values twice, but you could create something like that using UNION ALL eg (using Chris' sample data)

SELECT *

FROM #table1 t1

LEFT JOIN #table2 t2 ON t1.id = t2.id1

UNION ALL

SELECT id, value, NULL, NULL

FROM #table1 t1

WHERE NOT EXISTS ( SELECT * FROM #Table2 t2 WHERE t1.id = t2.id1 )

phani.gudmines SSC Veteran Points: 216 More actions · Answer 7

Hi Chris,

I just chaged the query like this and it worked for me.

SELECT id, value, id1, value1

FROM @t1 t1

CROSS JOIN (

SELECT TOP(SELECT TOP(1) [Rows] = COUNT(*) FROM @t2 GROUP BY id1 ORDER BY COUNT(*) DESC) n FROM (select id from @t1) rc (n)

) x

LEFT JOIN (SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY value1) FROM @t2) t2 ON t2.id1 = t1.id AND t2.rn = x.n

Thanks a lot chris.

Thanks for your help.:-):-):-)

phani.gudmines SSC Veteran Points: 216 More actions · Answer 8

Hi SSC Rookie

Thanks for your reply.It worked for me.

Thanks a lot.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Thanks for the feedback. Here are a couple of ways of expanding on the number of rows you have to generate:

WITH iTally (n) AS (

SELECT TOP(SELECT TOP(1) [Rows] = COUNT(*) FROM #table2 GROUP BY id1 ORDER BY COUNT(*) DESC) n

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) units (n)

) -- max 10 rows

SELECT id, value, id1, value1

FROM #Table1 t1

CROSS JOIN iTally x

LEFT JOIN (SELECT id1, value1, rn = ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY value1) FROM #Table2) t2 ON t2.id1 = t1.id AND t2.rn = x.n

;

WITH

E1 (n) AS (SELECT 1 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1 (n)), --10E+1 or 10 rows

E2 (n) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

iTally (n) AS (SELECT TOP(SELECT TOP(1) [Rows] = COUNT(*) FROM #table2 GROUP BY id1 ORDER BY COUNT(*) DESC)

n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM E2 a, E2 b) --10E+4 or 10,000 rows max

SELECT id, value, id1, value1

FROM #Table1 t1

CROSS JOIN iTally x

LEFT JOIN (SELECT id1, value1, rn = ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY value1) FROM #Table2) t2 ON t2.id1 = t1.id AND t2.rn = x.n

;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden