• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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
0
micro_learner
Asked:
micro_learner
  • 2
2 Solutions
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now