Cross Join

Hi,
I have to explain the problem with an example
TableA TableB
Id Amount id Amount
1 10 4 5
2 5 3 5
7 5
I want to join the tables on Amount. But I want to use each row once.
Query : select * from tableA a join tableB b on (a.amount = b.amount)
Results : 2 5 4 5
2 5 3 5
7 5 4 5
7 5 3 5
I don't want line 2 because I used tableA 2 5 line already .
The same for line 3 because I used tableB 4 5 already.
The purpose is that I want to join each line maximal 1 time .
Hope I explain well.
Thanks for help
JacYou haven't given us enough information to answer your question properly.
First, what are the keys of these two tables? I'll assume ID is the key in
both cases. It helps if you post proper DDL then we don't have to guess.
CREATE TABLE TableA (id INTEGER PRIMARY KEY, amount INTEGER NOT NULL)
CREATE TABLE TableB (id INTEGER PRIMARY KEY, amount INTEGER NOT NULL)
INSERT INTO TableA VALUES (1,10)
INSERT INTO TableA VALUES (2,5)
INSERT INTO TableA VALUES (7,5)
INSERT INTO TableB VALUES (3,5)
INSERT INTO TableB VALUES (4,5)
The main problem though is that you haven't explained by what rule you
expect to join TableA (2,5) to TableB (4,5) but not TableA (2,5) to TableB
(3,5). In SQL you have to declare exactly the result you want, you can't
just ask for any row joined to any one other row.
The only indication you've given seems to depend on the order in which you
listed the rows in your example. In SQL, a table has no inherent logical
order. A table is an unordered set. To join Row N in TableA to Row N in
TableB can only work if you have some column(s) that determines the value of
"N" for each row.
You can actually produce the result you requested by joining each row from
TableA in ascending ID number order to the sequentially corresponding row in
TableB based on descending ID number order. For completeness, here it is:
SELECT A.id, A.amount, B.id, B.amount
FROM TableA AS A
JOIN TableB AS B
ON A.amount = B.amount
AND
(SELECT COUNT(*)
FROM TableA
WHERE amount = A.amount
AND id >= A.id)
=
(SELECT COUNT(*)
FROM TableB
WHERE amount = B.amount
AND id <= B.id)
This may or may not meet your unspecified requirements but it does produce
the answer you asked for.
Consider whether your requirement is actually a problem caused by data
missing from these tables. Is there not some additional attribute that
should be present that would define which rows are related?
Hope this helps.
David Portas
SQL Server MVP
--|||I'm not sure what you are trying do, but I think both of these queries will
provide you with the results that you need.
SELECT *
FROM tableA a
JOIN (select amount, max(id) id from tableB group by amount) as b on (a.amou
nt = b.amount)
SELECT aid, aamount, Max(bid), bamount
FROM (SELECT a.id aid, a.amount aamount, b.id bid, b.amount bamount
FROM tableA a JOIN tableB b ON (a.amount = b.amount)) AS T
GROUP BY aid, aamount, bamount