CODEWITHSUNDEEP
sql-server-2008
None
None

Reputation: 5670

Join two tables with not repeating values

I have two tables TableA

id    name
1     asd
2     afg
3     qwe
4     dsf

TableB

id    desc    uni
1     kfgh    asad
1     oiuy    asad
1     wert    asad
3     wewe    sfds
4     lkjh    qwer
4     poiu    qwer

now i want join both these tables to get values in column uni when i tried inner join 

SELECT     uni FROM      tableA as A JOIN tableB as B  ON  A.id = B.id

it gives me all rows in TableB.as you noticed row with id 1 three times.And I can't use distinct here as this is a small part of a large query and this join statement will affect whole query.Can any one tell me what kind of join that I need to try.

Upvotes: 0

Views: 2827

Answers (2)

Hauke P.
Hauke P.

Reputation: 2843

Your tables do not seem to be normalized. In particular, they seem to violate the second normal form.

To fix that, you should change your table structure into something like the following:

TableA

id    name
1     asd
2     afg
3     qwe
4     dsf

Table Unis

id    uni
1     asad
3     sfds
4     qwer

Table Descs

id    desc
1     kfgh
1     oiuy
1     wert
3     wewe
4     lkjh
4     poiu

And use this query:

SELECT unis.uni FROM tableA AS A JOIN tableUnis AS unis ON A.id = unis.id

Upvotes: 0

Ian Kenney
Ian Kenney

Reputation: 6446

use a sub query to get the distinct bits you need

SELECT DISTINCT id, uni FROM tableb

so

SELECT     
  uni 
FROM
   tableA as A 
JOIN (
       SELECT DISTINCT id, uni FROM tableb
     ) as B  
ON  A.id = B.id

EDIT if you want all rows from tableA - you need to use a left join

SELECT     
  a.id, 
  b.uni 
FROM
   tableA as A 
LEFT JOIN (
       SELECT DISTINCT id, uni FROM tableb
     ) as B  
ON  A.id = B.id

Upvotes: 1

Related Questions