Cross Join

Hi
I have to explain the problem with an exampl
TableA Table
Id Amount id Amount
1 10 4
2 5 3
7
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
2 5 3
7 5 4
7 5 3
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 hel
JaYou 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.amount = 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
GROUP BY aid, aamount, bamount