Joing two tables but avoid cartesian product

Hi all, I have two tables that don't have any common data:
[Table1]
Column11 Int
AnotherColumn Int
[Table2]
Column21 Int
Data:
[Table1]
Column11 | AnotherColumn
111 | 8
112 | 8
113 | 8
114 | 8
[Table2]
Column21
211
212
213
214
I need to join them, to get a rowset that looks like this:
Column11 | Column21
111 | 211
112 | 212
113 | 213
114 | 214
When I try to join them, I use one of these two SQL Statements:
Select
Column11,
Column21
From Table2
Inner Join Table1 On Table1.AnotherColumn = 8
Select
Column11,
Column21
From Table2, Table1
Where Table1.AnotherColumn = 8
But I get a cartesian product (which I don't want). How can I just "put one
column next to the other" in my resultset, without having a Cartesian
product?
Thanks in advance,
FrankSeems like there are no relations between the tables like a
parent-child relation. Therefore only a cartesian product will make
sense. (?!)
HTH, jens Suessmeyer.|||Does the data really look like this? If you are trying to "line up"
physical rows or in the order of insertion, there's no way to tell SQL
Server to correlate that. If you are trying to match up 11, 12, 13 and 14
as "belonging to the same row", then you can do something like this:
SET NOCOUNT ON
CREATE TABLE #Table1
(
Column11 Int,
AnotherColumn Int
)
CREATE TABLE #Table2
(
Column21 Int
)
INSERT #Table1
SELECT 111,8
UNION SELECT 112,8
UNION SELECT 113,8
UNION SELECT 114,8
INSERT #Table2
SELECT 211
UNION SELECT 212
UNION SELECT 213
UNION SELECT 214
SELECT
t1.Column11,
t2.Column21
FROM
#Table1 t1
INNER JOIN #Table2 t2
ON t1.Column11 % 100 = t2.Column21 % 100
WHERE
t1.AnotherColumn = 8
DROP TABLE #table1, #table2
If this is not what you're looking for, please provide better requirements.
See http://www.aspfaq.com/5006
"John Francisco Williams" <JohnFranciscoWilliams1010@.Yahoo.Com> wrote in
message news:erVNz9iEGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi all, I have two tables that don't have any common data:
> [Table1]
> Column11 Int
> AnotherColumn Int
> [Table2]
> Column21 Int
> Data:
> [Table1]
> Column11 | AnotherColumn
> 111 | 8
> 112 | 8
> 113 | 8
> 114 | 8
> [Table2]
> Column21
> 211
> 212
> 213
> 214
> I need to join them, to get a rowset that looks like this:
> Column11 | Column21
> 111 | 211
> 112 | 212
> 113 | 213
> 114 | 214
> When I try to join them, I use one of these two SQL Statements:
> Select
> Column11,
> Column21
> From Table2
> Inner Join Table1 On Table1.AnotherColumn = 8
> Select
> Column11,
> Column21
> From Table2, Table1
> Where Table1.AnotherColumn = 8
> But I get a cartesian product (which I don't want). How can I just "put
> one column next to the other" in my resultset, without having a Cartesian
> product?
> Thanks in advance,
> Frank
>|||not sure I understand what you really need, but try this:
create table #t1(i1 int primary key)
insert into #t1 values(123)
insert into #t1 values(124)
insert into #t1 values(125)
insert into #t1 values(126)
create table #t2(i2 int primary key)
insert into #t2 values(23)
insert into #t2 values(24)
insert into #t2 values(25)
insert into #t2 values(26)
insert into #t2 values(27)
select i1, i2 from
(select i1, (select count(*) from #t1 t11 where t11.i1<t1.i1) rn from
#t1 t1) t1
full outer join
(select i2, (select count(*) from #t2 t21 where t21.i2<t2.i2) rn from
#t2 t2) t2
on t1.rn=t2.rn
i1 i2
-- --
123 23
124 24
125 25
126 26
NULL 27
(5 row(s) affected)
on SQL Server 2005 you can use row_number() to calculate rn|||is it just a conincidence, or is it really that you want to match rows from
Table1 and Table2 in a way that 111 in Table1.Column11 matches 211 in
Table2.column22, 112 matches 212, etc? you can do something like this:
select *
from table1 t1 inner join table2 t2 on t1.column11%100=t2.column21%100
dean
"John Francisco Williams" <JohnFranciscoWilliams1010@.Yahoo.Com> wrote in
message news:erVNz9iEGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi all, I have two tables that don't have any common data:
> [Table1]
> Column11 Int
> AnotherColumn Int
> [Table2]
> Column21 Int
> Data:
> [Table1]
> Column11 | AnotherColumn
> 111 | 8
> 112 | 8
> 113 | 8
> 114 | 8
> [Table2]
> Column21
> 211
> 212
> 213
> 214
> I need to join them, to get a rowset that looks like this:
> Column11 | Column21
> 111 | 211
> 112 | 212
> 113 | 213
> 114 | 214
> When I try to join them, I use one of these two SQL Statements:
> Select
> Column11,
> Column21
> From Table2
> Inner Join Table1 On Table1.AnotherColumn = 8
> Select
> Column11,
> Column21
> From Table2, Table1
> Where Table1.AnotherColumn = 8
> But I get a cartesian product (which I don't want). How can I just "put
> one column next to the other" in my resultset, without having a Cartesian
> product?
> Thanks in advance,
> Frank
>|||and you can use PIVOT as well:
select [i1], [i2]
from (
select
row_number() over (order by i1) as rn,
'i1' as Src,
i1 as x
from #t1
union all
select
row_number() over (order by i2),
'i2',
i2
from #t2
) T PIVOT (
max(x) FOR Src in ([i1],[i2])
) as P
-- Steve Kass
-- Drew University
Alexander Kuznetsov wrote:

>not sure I understand what you really need, but try this:
>create table #t1(i1 int primary key)
>insert into #t1 values(123)
>insert into #t1 values(124)
>insert into #t1 values(125)
>insert into #t1 values(126)
>create table #t2(i2 int primary key)
>insert into #t2 values(23)
>insert into #t2 values(24)
>insert into #t2 values(25)
>insert into #t2 values(26)
>insert into #t2 values(27)
>select i1, i2 from
>(select i1, (select count(*) from #t1 t11 where t11.i1<t1.i1) rn from
>#t1 t1) t1
>full outer join
>(select i2, (select count(*) from #t2 t21 where t21.i2<t2.i2) rn from
>#t2 t2) t2
>on t1.rn=t2.rn
>
>i1 i2
>-- --
>123 23
>124 24
>125 25
>126 26
>NULL 27
>(5 row(s) affected)
>on SQL Server 2005 you can use row_number() to calculate rn
>
>|||This looks like you are creating the rows by matching the SORTED ORDER
OF THE VALUES IN EACH TABLE, in volation of the basic relational
principles. This means that the rows have no meaning whatsoever and
that you are probably doing this for display purposes, in violation of
the principle of a tiered archtecture.
However, look up a query I did to match boys and girls as dance
partners. The trick was to add a relative row in derived tables and to
use a view to close gaps when the base tables change.
CREATE VIEW DanceCard (boy_name, girl_name)
AS SELECT B.name, G.name
FROM
(SELECT B1.name, COUNT(B2.*)
FROM Boys AS B1, Boys AS B2
WHERE B2.name <= B1.name
GROUP BY B1.name) AS B(name, match_nbr)
FULL OUTER JOIN
(SELECT G1.name, COUNT(G2.*)
FROM Girls AS G1, Girls AS G2
WHERE G2.name <= G1.name
GROUP BY G1.name) AS G(name, match_nbr)
ON B.match_nbr = G.match_nbr;
This is not a good way to do such things; you really need a better
rule.|||On 5 Jan 2006 16:42:22 -0800, "--CELKO--" <jcelko212@.earthlink.net> wrote:
in <1136508142.931773.99530@.o13g2000cwo.googlegroups.com>
Is that your face in the piratesdinneradventure newspaper ads?|||>> in volation of the basic relational
principles. This means that the rows have no meaning whatsoever and
that you are probably doing this for display purposes, in violation of
the principle of a tiered archtecture. <<
In real life the problem is quite common, for instance:
- 20 non-smoking guests arrive in a hotel with 30 vacant identical
non-smoking rooms, each guest needs to get a room. And that does not
mean that "the rooms and the guests have no meaning whatsoever".
If this simple real life situation is in "volation of the basic
relational principles", as you say, that's just one more indication
that the relational theory is not perfect, it does not cover all the
bases.
Anyway, the vendors do listen to us practitioners, and they have
provided row_number() to deal with this very common problem. I guess
row_number() is in ANSI standard now, is it not?