SELECT inner joined columns where only one column can not have duplicate record

I want to select inner joined tables, and if there is a duplicate record in 'column D', do not display entire inner joined row.

Table 1

a b 
1 car
1 boat
1 man

Table 2

c  d
1  dog   *dog is a duplicate, so only display it once. 
1  dog
1  cat

Here is my inner joined sql select statement so far:

SELECT DISTINCT
a b c d FROM 
table1 INNER JOIN 
table2 ON table1.a = table2.c开发者_高级运维
WHERE <duplicate> NOT ALREADY IN RESULT

Result should be:

1 car 1 dog
*1 boat 1 dog* <--dog is a duplicate so should not be displayed
1 man 1 cat

It sounds like you want some arbitrary pairs drawn from the two tables, as long as the second column does not have any repeated values. Whether you get car/dog or boat/dog is unspecified, you just don't want to get both.

declare @Table1 table ( id1 Int, value1 VarChar(8) )
insert into @Table1 ( id1, value1 ) values ( 1, 'car' )
insert into @Table1 ( id1, value1 ) values ( 1, 'boat' )
insert into @Table1 ( id1, value1 ) values ( 1, 'man' )

declare @Table2 table ( id2 Int, value2 VarChar(8) )
insert into @Table2 ( id2, value2 ) values ( 1, 'dog' )
insert into @Table2 ( id2, value2 ) values ( 1, 'dog' )
insert into @Table2 ( id2, value2 ) values ( 1, 'cat' )

-- All combinations.
select value1, value2, Row_Number() over ( order by value2 ) as 'RowNumber'
  from @Table1 as L inner join @Table2 as R on L.id1 = R.id2

-- Some combinations in which values in the second column do not recur.
select value1, value2
  from ( select value1, value2, Row_Number() over ( order by value2 ) as 'RowNumber'
    from @Table1 as L inner join @Table2 as R on L.id1 = R.id2 ) as Blue
  where RowNumber = ( select min( RowNumber ) from
    ( select value1, value2, Row_Number() over ( order by value2 ) as 'RowNumber'
    from @Table1 as L inner join @Table2 as R on L.id1 = R.id2 ) as Grey where value2 = Blue.value2 )

I do believe this is what he is looking for, thx for the hasty minus. Notice i only changed the test data since it seemed wrong.

declare @t1 table(a int, b varchar(5)) 
declare @t2 table(c int, d varchar(5)) 

insert @t1 
select 1, 'car'  union all--*
select 2, 'boat' union all--*
select 3, 'man'

insert @t2
select 1, 'dog' union all--*
select 2, 'dog' union all--*
select 2, 'dog' union all--*
select 3, 'cat'

SELECT 
a, b, c, d FROM 
@t1 t1 INNER JOIN 
@t2 t2 ON t1.a = t2.c
GROUP BY a,b,c,d
HAVING COUNT(*) = 1

Added "all" to my unions thanks to JNK's sharp eye

SELECT DISTINCT
table1.a, table1.b, tab2.c, tab2.d 
FROM
table1 
INNER JOIN
(SELECT table2.c, table2.d AS tab2) ON table1.a = tab2.c
GROUP BY tab2.d