Join tables with additional conditions / OR
I am trying to make a left outer join with multiple OR conditions.
declare @table table (ID char(3));
insert into @table values
('001'), ('001'), ('002'), ('003'), ('004')
declare @join_table table (AUX_ID char(3), AUD_ID char(3));
insert into @join_table values
('001', ''),
('002','001'),
('002','003'),
('004','007'),
('005','006')
SELECT a.ID, b.AUX_ID, b.AUD_ID
FROM @table a LEFT JOIN @join_table b
ON a.ID = b.AUX_ID
OR a.ID = b.AUD_ID
with this code, I get:
ID AUX_ID AUD_ID
001 001
001 002 001
001 001
001 002 001
002 002 001
002 002 003
003 002 003
004 004 007
I need a list of all ids from the original table as well as mapped from the join table.
ID
001
001
002
003
004
007
source to share
Are you trying to match the first item in your state similar to a short circuit condition?
If so, try using COALESCE:
SELECT a.ID, b.AUX_ID, b.AUD_ID
FROM @table a LEFT JOIN @join_table b
ON a.ID = COALESCE( b.AUX_ID, b.AUD_ID, '' )
WHERE COALESCE( b.AUX_ID, b.AUD_ID, NULL ) IS NOT NULL
source to share
If you only want the ID (or other fields) from @Table?
Then you can use EXISTS where CLAUSE is.
SELECT a.ID
FROM @table a
-- add joins to other tables here
WHERE EXISTS
(
select 1
from @join_table b
where b.AUX_ID is not null
and b.AUD_ID is not null
and a.ID in(b.AUX_ID,b.AUD_ID)
);
source to share
Update based on comments.
Your requirement - "don't want different, but don't want duplicates" - would be contradictory using the standard definitions of "different" and "duplicate". So, we need a definition for "duplicate" because you are using the term.
What you seem to be saying is that ID
it is not unique in the original @table
, but you want a single result for each record @table
if it matches at least one record @join_table
. It can be done, but I expect trouble down the line unless there is something that we cannot see.
Basically, as queries currently show, there is no way to uniquely identify a string from @table
or - more to indicate - which string @table
was the source of the string in the combined result to ask.
If it @table
has additional records that form a unique key, then you can include them in the request so that DISTINCT
they are not considered as duplicates.
If not, you can use row_number()
to create a unique identifier; but this generated id is intrinsic to the request and cannot subsequently be used to bind to a specific string from @table
. However, there are several options, but here's one:
SELECT distinct a.RID, a.ID
FROM (select ID, row_number() over() as R_ID
from @table) as a
LEFT JOIN @join_table b
ON a.ID = b.AUX_ID
OR a.ID = b.AUD_ID
WHERE b.AUX_ID IS NOT NULL OR b.AUD_ID IS NOT NULL
or if you really only want the ID
end result, wrap that in select ID from (
...)
Original Answer
So the connection works fine, but of course it replicates strings (like all connections) when there are multiple matches.
But really the problem is that you are asking for all ID combinations when you want different values of the first ID. So:
SELECT DISTINCT a.ID
FROM @table a LEFT JOIN @join_table b
ON a.ID = b.AUX_ID
OR a.ID = b.AUD_ID
WHERE b.AUX_ID IS NOT NULL OR b.AUD_ID IS NOT NULL
The database can only provide what you want, if you ask what you want.
source to share