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
micro_learnerAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
USE distinct

SELECT distinct a.*
      FROM A a
        INNER JOIN B b ON b.id=a.id
      WHERE b.source <> 5
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
micro_learnerAuthor Commented:
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
 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would be:
SELECT  count(distinct   a.id)      FROM A a
       INNER JOIN B b ON b.id=a.id
     WHERE b.source <> 5

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.