SQL Repetition, how to avoid in query?

Question

I'm trying to get the names and the addresses that are stored in the table but getting data repetition. I dont know how to avoid it as im a newbie to this field. here's some pictures of the commands and results.

Commands:

enter image description here

Results:

enter image description here

Owner Table:

enter image description here

Addresses Table:

enter image description here

Please help me out :(

P.S. They are all dummy data.

You will need to `Join` the two tables on some key. What other identifying columns exist in those tables that you can `Join` on? Does your Addresses table have an ID of which Owner it belongs to? — Siyual, Apr 18 '14 at 20:45
Learn how to use `join`. If you don't know what that is, then you need to learn the basics of relational databases. — Gordon Linoff, Apr 18 '14 at 20:46

score 4 · Accepted Answer · edited May 23 '17 at 12:05

You need to do a join between the two tables owners and addresses using column in tables that referenece each other.

SELECT firstname,lastname,addressline_1
FROM owners o
JOIN addresses a
ON o.colName=a.colName

Your query is performing cartesian product between the two tables which is giving all rows for table address for each row in table owners.

You would have avoided getting meaningless rows had you used recommended ANSI SQL syntax of performing join using ON clause rather than WHERE clause . Although you haven't specified condition for joining between the tables still old syntax of joining using WHERE clause got executed successfully but would have thrown error in case of using ON clause.

See this thread for detailed discussion ON vs WHERE

EDIT

As per your schema of tables the query would be

SELECT firstname,lastname,addressline_1
FROM owners o
JOIN addresses a
ON o.ownerid=a.owners_ownerid

@m hasan.. wow works!! can you explain these `highlighted` bit to me please? SELECT firstname,lastname,addressline_1 FROM owners `o` JOIN addresses `a` ON `o.ownerid=a.owners_ownerid` — envyM6, Apr 18 '14 at 21:03
you mean ON/WHERE part..plz click on the link..more detailed answers there — Mudassir Hasan, Apr 18 '14 at 21:05

Sampat Badhe · Answer 2 · 2014-04-18T21:05:01.903

1

you have to add where clause to your query:-

SELECT OWNERS.first_name, OWNERS.last_name, ADDRESSES.address_line1
FROM OWNERS, ADDRESSES
WHERE OWNERS.ownerid = ADDRESSES.owners_ownerid

or you can use join

SELECT OWNERS.first_name, OWNERS.last_name, ADDRESSES.address_line1
FROM OWNERS
JOIN ADDRESSES
ON OWNERS.ownerid = ADDRESSES.owners_ownerid

edited Apr 18 '14 at 21:05

answered Apr 18 '14 at 20:49

Sampat Badhe

8,710
7
33
49

Thanks @Sampat check the updated tables... I'll try your sql and let you know :) – envyM6 Apr 18 '14 at 21:00

SQL Repetition, how to avoid in query?

2 Answers2