CODEWITHSUNDEEP
sqlms-access
Splanger
Splanger

Reputation: 189

Two tables concatenation

I've stuck with my homework on Data Bases for a two days...

I'm working in MS Access, and I have two tables R and S, with the same number of rows. They don't have any relationship. Let's say table R has only two columns (named A, B), and so the table S (named B, C).

In the table R there is only three records: (a1,b1), (a1,b2), (a2,b1)

In the table S there is only three records: (b1,c1), (b1,c3), (b2,c2)

I need to concatenate these two tables into one, so the new table will have four columns (RA, RB, SB, SC) and will have the next records:

(a1,b1,b1,c1),  
(a1,b2,b1,c3),  
(a2,b1,b2,c2)

That's all... The question is: How do I accomplish this with SQL??

Upvotes: 2

Views: 363

Answers (4)

Tom Collins
Tom Collins

Reputation: 4069

OK, my answer is very different from the others.

You stated that there's no relation between the 2 tables. Everyone else is trying to make a relation between R.B & S.B, but you said there is no relation.

So, if there's no relation, then you don't put one in. So what you'll get is: SELECT R., S. FROM R,S

The results will be every column from both tables and the rows returned will equal the rows from R times the rows from S. In your example above, you'll get 9 records back.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

Very contrived, but runs in MS Access.

SELECT x.f0, x.F1, y.F0, y.F1
FROM (
     SELECT DISTINCT r.F0, r.F1, 
         (select count(*) 
         from r a 
         where a.f0+a.f1<=r.f0+r.f1) AS Expr1
      FROM r)  AS x 
INNER JOIN (
     SELECT DISTINCT s.F0, s.F1, 
        (select count(*) 
         from s a 
         where a.f0+a.f1<=s.f0+s.f1) AS Expr1
     FROM s)  AS y 
ON x.Expr1 = y.Expr1;

Table S

F0  F1 <-- Field names
b1  c1
b1  c3
b2  c2

Table R

F0  F1 <-- Field names
a1  b2
a2  b1
a1  b1

Query result

x.F0    x.F1    y.F0    y.F1
a1      b1      b1      c1
a1      b2      b1      c3
a2      b1      b2      c2

Upvotes: 3

CodeTalk
CodeTalk

Reputation: 3667

I'm with Chase on this one, it seems like you want to do an SQL join like:

SELECT *
FROM TableR R, TableS S
WHERE R.id=S.id;

What this is saying is in plain english: Select all the columns/rows from both Table R and Table S where the primary key ID in Table R equals the same primary key ID value in Table S

Not sure which database you are using, but here's a bit more information.

Upvotes: 0

ChaseMedallion
ChaseMedallion

Reputation: 21794

It seems like what you want to do is to JOIN the two tables by row number. Check out this thread

For an example of how to do this.

Upvotes: 0

Related Questions