Getting duplicate outputs in my INNER JOIN sql query

Question

I am new to SQL and I am learning inner joins. However, when I run my query, I am receiving more outputs than I should be.

SELECT          pfr.pno AS 'Property Number', 
                    pfr.street, 
                    pfr.rooms, 
                    pfr.rent, 
                    CONCAT(o.fname, ' ', o.lname) AS 'Owner Name', 
                    CONCAT(s.fname,' ',s.lname) AS 'Staff Name', 
                    v.date, 
                    v.comment
From                property_for_rent AS pfr
INNER JOIN  owner AS o
ON                  pfr.ono = o.ono
INNER JOIN  staff AS s
ON                  pfr.sno = s.sno
INNER JOIN  viewing AS v
ON                  pfr.pno = v.pno
WHERE           pfr.pno = 'PG4';

I have attached a screenshot of my output. I hope this is enough information!

Query Output Image

Table in use Image

Your duplicates aren't duplicate. You have 2 rows in viewing that match that pfr number (I would assume that means the property has been viewed 2 times). One with no comment in may and one with 'too remote' sometime in April. — Twelfth
– Twelfth, Commented Oct 10, 2017 at 17:23
Obviously you have one-to-many relationship on these tables. I think it behaves correctly. It's just a matter of what logic do you want to handle the duplicated data. — Eric
– Eric, Commented Oct 10, 2017 at 17:24
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. — user1930591
– user1930591, Commented Oct 10, 2017 at 17:27
I see now, the viewing table has 2 viewings for the property. Thanks! — Carl O'Beirne
– Carl O'Beirne, Commented Oct 10, 2017 at 17:28

Fenton · Accepted Answer · 2017-10-10 17:26:50Z

1

When you join two tables, this will happen if you don't have a 1:1 relationship.

For example, you have two records in the "viewing" table, so there must be two rows shown even though there is just one property.

If want a single property result, you need to logically aggregate the viewing table, for example by getting the most recent viewing for the property.

answered Oct 10, 2017 at 17:26

Fenton

253k74 gold badges404 silver badges414 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peace_Man · Accepted Answer · 2017-10-10 19:11:48Z

0

I would not consider these duplicate rows. If you look at the date column for the PG4 transaction, you'll notice that the dates are different. These are two different transactions and are not considered dups. You can consider use the Distinct clause if you are looking to return only one record of a particular column. Hope this helps!

answered Oct 10, 2017 at 19:11

Peace_Man

451 silver badge11 bronze badges

Collectives™ on Stack Overflow

Getting duplicate outputs in my INNER JOIN sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related