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

Seems like there are no relations between the tables like a
parent-child relation. Therefore only a cartesian product will make
sense. (?!)

HTH, jens Suessmeyer.

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

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:

Show quoteHide quote

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





Show quoteHide quote

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

Show quoteHide quote

Thank you all for your help. I used the solution proposed by Alexander
Kuznetsov, since the data in the first table is always incremental, and it
is no problem that the second column returns sorted:

Create Table dbo.DeleteThisTable1
(
   ColumnA1         Int,
   ColumnA2         Int
)

Insert Into dbo.DeleteThisTable1 Values( 1, 8 )
Insert Into dbo.DeleteThisTable1 Values( 2, 8 )
Insert Into dbo.DeleteThisTable1 Values( 3, 8 )
Insert Into dbo.DeleteThisTable1 Values( 4, 8 )
Insert Into dbo.DeleteThisTable1 Values( 5, 9 )
Insert Into dbo.DeleteThisTable1 Values( 6, 9 )
Insert Into dbo.DeleteThisTable1 Values( 7, 9 )

Create Table dbo.DeleteThisTable2
(
   ColumnB1         Int
)

Insert Into dbo.DeleteThisTable2 Values(  28 )
Insert Into dbo.DeleteThisTable2 Values(  12 )
Insert Into dbo.DeleteThisTable2 Values( 132 )
Insert Into dbo.DeleteThisTable2 Values( 100 )

--Verify:
Select * From dbo.DeleteThisTable1 Where ColumnA2 = 8
Select * From dbo.DeleteThisTable2

--Join:
Select
   ColumnA1,
   ColumnB1
   From
   (
      Select
         ColumnA1,
         ColumnA2,
         ( Select Count(*) From dbo.DeleteThisTable1 As DeleteThisTable1B
Where DeleteThisTable1B.ColumnA1 < DeleteThisTable1.ColumnA1 ) As CommonCol
      From dbo.DeleteThisTable1
   ) As DeleteThisTable1A
   Full Outer Join
   (
      Select
         ColumnB1,
         ( Select Count(*) From dbo.DeleteThisTable2 As DeleteThisTable2B
Where DeleteThisTable2B.ColumnB1 < DeleteThisTable2.ColumnB1 ) As CommonCol
      From dbo.DeleteThisTable2
   ) As DeleteThisTable2A
   On DeleteThisTable1A.CommonCol = DeleteThisTable2A.CommonCol
   Where DeleteThisTable1A.ColumnA2 = 8


Show quoteHide quote

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.

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?

in <1136508142.931773.99***@o13g2000cwo.googlegroups.com>

Is that your face in the piratesdinneradventure newspaper ads?

I might need to change my design. I didn't want to bother with too much
detail but, here I go:

This is about a database to store Questions and tests for students. I have:

1. A table [Object] that has information about documents, videos, Sounds,
etc. Used in the questions.
[Object]
ObjectID
URI
Other properties

2. A table [Question], to store questions to be formulated in a test.
[Question]
QuestionID
Other properties

3. Facts:
.. A question can be related with several possible answers
.. Case "a": Only one of the possible answers, is the correct one. Only that
one should be selected by the student.
.. Case "b": In some cases, more than one possible answer is correct. All of
them have to be selected.
.. Case "c": There are several correct answers. All of them yield different
score. Only one with a maximum score. Only one should be selected.
.. Case "d": Match elements in one column, with elements in another column,
and (maybe) another column, and ..

____________________________________________________________________________________________
Please, skip these examples if you want. I put them here in case anybody
wants more detail:
Case "a":
What is the capital of England
( ) Singapoore
( ) Berlin
( ) London
( ) Washington

Case "b":
which of the following cities are in Germany
[ ] Berlin
[ ] Tokyo
[ ] Munich
[ ] Hamburg
[ ] Cartagena

Case "c":
Best way to get from Atenas to Rome:
( ) They are very close. Just take highway 85 and you will be there in 30
minutes.          (Score:   0)
( ) First, take a plane from Atenas to London. Second, rent a car to go from
London to Rome.(Score:  50)
( ) They are the same city. No need to travel. You are already there.
(Score:   0)
( ) Take a plane from Atenas to Rome.
(Score: 100)

Case "d":
Match Countries, Presidents(or equivalent) and Periods

Column 1      Column 2      Column 3    Column 4
Helmut        Roosevelt     Russia      From xxxx to yyyy
Franklin      Yeltsin       Germany     From xxxx to yyyy
Margaret      Uribe         England     From xxxx to yyyy
Boris         Thatcher      Colombia    From xxxx to yyyy
Alvaro        Kohl          USA         From xxxx to yyyy
____________________________________________________________________________________________

To store possible answers like the above, I designed the following tables:

[Object]
ObjectID --------------- Identity( 1, 1)
URI
Other properties

[Question]
QuestionID ------------- Identity( 1, 1)
Other properties

[PossibleAnswer]
PossibleAnswerID ------- Identity( 1, 1)
QuestionID

[PossibleAnswerDetail]
PossibleAnswerDetailID - Identity( 1, 1)
PossibleAnswerID
ObjectID

[Matching]
MatchingID ------------- Identity( 1, 1)
PossibleAnswerDetailID
ColumnNumber

[CorrectAnswer]
CorrectAnswerID -------- Identity( 1, 1)
PossibleAnswerID
Score

.. When the questions are stored, I want only one trip to the database, to
store all the data pertaining to that question: [Question] properties,
possible answers, correct answers, matching information if it's the case,
etc.
.. I send all the information for [Question] properties, as regular
parameters
.. The rest of the information to be saved, varies, depending on the case (a,
b, c or d )
.. I send the rest of the information in an XML document

.. I want to avoid the use of cursors, Fetch, etc.

.. One procedure1, saves the information for [Question] and then, it pases
the new QuestionID, and the XML info to another procedure2, to save the
information for [PossibleAnswer]. That procedure later calls another one for
[PossibleAnswerDetail], ... etc.

.. When I save the information in [PossibleAnswer], QuestionID is the same
for each row, and N new PossibleAnswerIDs are generated. I now need those
PossibleAnswerIDs, to save the information in [PossibleAnswerDetail]. There,
those IDs will be associated with ObjectIDs, the order doesn't matter. Note
that, for a question type "d" (matching) with N columns, there will be N
rows in [PossibleAnswerDetail], related to one row in [PossibleAnswer]

.. The process continues, to fill the rest of the tables. ([Matching], not
always will be used)

.. When I'm saving the information in [PossibleAnswerDetail], how do I get
the PossibleAnswerIDs that were generated when I saved the information in
[PossibleAnswer]? I use QuestionID to do that.
.. Ok, now, by using QuestionID, I have the list of all PossibleAnswerIDs,
that I want to combine with all the ObjectIDs (in any order), and then save
them in [PossibleAnswerDetail]. When I tried to put that information
toghether, the cartesian product was the result/problem. That was when I
posted the original question here.

Thanks to everybody who read this book :-D

PS:
I don't know much of newsgroups etiquette. I posted a reply to the original
question but I was in doubt whether I should have posted this as a whole new
entry, because this question is already a bit old. Ok. I'll wait. If no
answer, then I'll post it as a new item.

I also have another design in mind, but not sure whether it is more correct
or worse:

[PossibleAnswer]
PossibleAnswerID
QuestionID
ObjectID

[Matching]
MatchingID
PossibleAnswerID
ColumnNumber
Group (That determines in which visual row should the item be put)

[CorrectAnswer]
CorrectAnswerID
PossibleAnswerID
Score

Also: Not all answer have a score, but I decided not to normalize that. What
do you think?


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.

This is a totally differrent problem.

One solution would be to get a package that is designed for test
administration instead of building your own from scratch.   There
should be some freeware out there for this.  I recommend this, but if
you want to do itself, here are some thoughts.

I would tend to use a different table for each kind of question,
multiple choice,  matching, true/false, etc.

Since I had students that tended to cheat a lot, my tables for mulitple
choice questiosn looked like this:

CREATE TABLE Questions
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt VARCHAR(1500) NOT NULL,
right_answer_txt VARCHAR(100) NOT NULL,
question_score INTEGER NOT NULL);

CREATE TABLE WrongAnswers
(question_nbr INTEGER NOT NULL
    REFERENCES Questions (question_nbr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
answer_nbr INTEGER NOT NULL,
PRIMARY KEY (question_nbr,  answer_nbr),
wrong_answer_txt VARCHAR(100) NOT NULL);

I then generate random tests for each student.

This is a totally differrent problem.

One solution would be to get a package that is designed for test
administration instead of building your own from scratch.   There
should be some freeware out there for this.  I recommend this, but if
you want to do itself, here are some thoughts.

I would tend to use a different table for each kind of question,
multiple choce,  matching, true/false, etc.

Since I had students that tended to cheat a lot, my tables for mulitple
choice questiosn looked like this:

CREATE TABLE Questions
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt VARCHAR(1500) NOT NULL,
right_answer_txt VARCHAR(100) NOT NULL,
question_score INTEGER NOT NULL);

CREATE TABLE WrongAnswers
(question_nbr INTEGER NOT NULL
    REFERENCES Questions (question_nbr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
answer_nbr INTEGER NOT NULL,
PRIMARY KEY (question_nbr,  answer_nbr),
wrong_answer_txt VARCHAR(100) NOT NULL);

I then generate random tests for each student.

This is a totally differrent problem.

One solution would be to get a package that is designed for test
administration instead of building your own from scratch.   There
should be some freeware out there for this.  I recommend this, but if
you want to do itself, here are some thoughts.

I would tend to use a different table for each kind of question,
multiple choce,  matching, true/false, etc.

Since I had students that tended to cheat a lot, my tables for mulitple
choice questiosn looked like this:

CREATE TABLE Questions
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt VARCHAR(1500) NOT NULL,
right_answer_txt VARCHAR(100) NOT NULL,
question_score INTEGER NOT NULL);

CREATE TABLE WrongAnswers
(question_nbr INTEGER NOT NULL
    REFERENCES Questions (question_nbr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
answer_nbr INTEGER NOT NULL,
PRIMARY KEY (question_nbr,  answer_nbr),
wrong_answer_txt VARCHAR(100) NOT NULL);

I then generate random tests for each student.

Dynamic SQL Column Total Help
Datetime constraint: is there a better way to do this?
Add a field
simple insert of strings
Complex Query