asked on

Get Unique Rows with INNER Join

I join Two Tables, for each Join I get two Rows based on the Join.But essentially I need only one Row (unique) as the second row is reduntant.How do I achieve this.To give an example

I have two Tables A and B

say
A.id A.name

1 Tom
2. Dick
3. Buno

Second Table is
B.id B.source

1 3
1 4
2 3
2 4
3 5

Essentially I am looking to get the following result from the query

1 Tom
2 Dick

Note (there is no 3 Buno)

I do this

SELECT a.*
FROM A a
INNER JOIN B b ON b.id=a.id
WHERE b.source <> 5

But the following query returns

1 Tom
1 Tom
2 Dick
2 Dick
(Note the repitions)

How do I change the query so as to get only 1 row for each ? Thank You

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

this should work better:

SELECT a.*
      FROM A a
       WHERE EXISTS( SELECT NULL FROM B b WHERE b.id=a.id AND b.source <> 5 )

Open in new window

micro_learner

ASKER

SELECT distinct a.*
FROM A a
INNER JOIN B b ON b.id=a.id
WHERE b.source <> 5

does the trick but this fails when I need to get the count for pagination
SELECT distinct count(*) FROM A a
INNER JOIN B b ON b.id=a.id
WHERE b.source <> 5
This still counts all the records

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial