CODEWITHSUNDEEP
mysqlsqlinner-join
Carl O'Beirne
Carl O'Beirne

Reputation: 329

Getting duplicate outputs in my INNER JOIN sql query

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

Upvotes: 1

Views: 1414

Answers (2)

Peace_Man
Peace_Man

Reputation: 45

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!

Upvotes: 0

Fenton
Fenton

Reputation: 250842

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.

Upvotes: 1

Related Questions