CODEWITHSUNDEEP
mysqlsql
rick9
rick9

Reputation: 3

SQL Combine 2 Tables without a primary key

I have just started learning sql and I am trying to understand how I can do the following in SQL.

I have 2 tables: Example - TableA (Id), TableB(Id, Text)

Following are the values in Table A:
1
2
1

Following are the values in Table B (Note - the text column will always have a similar value with the ID it has):

1, Tom
2, John
1, Tom
3, Doe

I tried doing the following query: "Select TableB.Text from TableB, TableA where TableA.Id = TableB.Id"

And it returned:

Tom
Tom
John
Tom
Tom

Where I am looking for:

Tom
John
Tom

Can anyone point me in the right direction if this is possible in SQL?

Upvotes: 0

Views: 187

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Instead of joining tableb directly you can "distinctify" it first in a subquery.

SELECT b.text
       FROM (SELECT DISTINCT
                    id,
                    text
                    FROM tableb) b
            INNER JOIN tablea a
                       ON a.id = b.id;

The distinct version of tableb looks like:

id   text
---------
1    Tom
2    John
3    Doe

The second 'Tom' is gone. Like that there aren't two rows matching ID 1 anymore, which caused the 'Tom's to be two times in the result of your original query for every row with ID 1 from tablea.

Upvotes: 1

Related Questions