Repeated Rows Sql Query

Question

I am running a query and I am getting duplicated rows. I dont understand why this is. Here is my Query:

SELECT c.FName,
       p.ProductName,
       s.Description,
       s.Quantity,
       s.Price
FROM   customers c,
       products p,
       sellers s,
       requests r
WHERE  c.ID = s.CID
       AND p.ProductID = s.ProductID
       AND r.ProductID = s.ProductID
       AND r.C_ID = 3
       AND r.MatchType = 'Price'
ORDER  BY s.Price ASC

=======edit=======

Okay here is an edit of what the values in the Requests table looks like. NOTE: CID 2=DAZ(generally a seller), 3=Paul (Generally purchase guy) and 5= compny1 (generally a seller) NOTE: product id 1= apple, product id 2=pear, productid 3= berries, product id4=orange

The Request table looks like this after selecting records MatchType=Price and cust ID=3:

 requestid |   cid   |   productid   | Quantity | Price  | matchtype
    ------------------------------------------------------------------
    1          3            1            3.0     2.00        price
    3          3            4            4.0     2.50        price
    4          3            3            2.5     2.00        exact  
    5          3            2            3.0     3.50        exact
    6          3            3            3.0     2.00        exact
    7          3            1            10.0    7.00        price

and here is the sellers table

promotionID |   cid   |   productid   | Quantity | Price | description  
    ------------------------------------------------------------------
    1          2            4            5.0     2.99        oranges
    2          2            3            1.5     1.00        hand strawberries        
    3          2            3            2.5     2.00        l stawberries  
    4          2            2            3.0     3.00        pear       
    5          5            1            5.0     5.00        royal apples fm appleco.         
    6          2            1            6.0     5.50          sweet apples

AFTER RUNNING THE QUERY I HAVE TRIED BOTH THE SUGGESTED JOINS AND THE ONE IN THIS QUESTION I KEEP GETTING THIS AS OUTPUT

FName   ProductName         Description         Quantity    Price

daz         Oranges     Fresh and sweet oranges.    5.0        2.99
compny1      Apple      royal apples fm appleco.    5.0        5.00
compny1      Apple      royal apples fm appleco.    5.0      5.00
daz         Apple       sweet apples                 6.0      5.50
daz         Apple       sweet apples                 6.0      5.50

I don't get why I am recieving rows that are repeating. The requested product id must be = sellers product id to match the requested products to the available products and the customerId selected in this case is 3...

I dont get why the last 4 records repeat them self? Why may this be??
Technically speaking only 4 records should be shown. i.e. records on rows.. 1,2 and 3

SUGGESTION/OBSERVATION OKay, After having looked at this... Do you think the rows are repeated because the productID1=apple has been requested by the same customer twice with different quantities???

  requestid |   cid   |   productid   | Quantity | Price  | matchtype
        ------------------------------------------------------------------
    1          3            1            3.0     2.00        price

    7          3            1            10.0    7.00        price

When you do a join, each joining pair is shown, so if you joined a table with one entry to and table that had its foreign key in 10 rows then the answer would have 10 rows. — Boris the Spider
– Boris the Spider, Commented Feb 17, 2013 at 22:37
@MartinSmith well the request table with customer ID 3 shows requests for the following: chocolate, 2kg, 2.00 choclate, 10kg, 8.00 sweets, 3kg, 3.00 they are the products requested by user 3.... There are two suppliers who are selling choclate and sweets... 1 supplier namely compny1 sells only one product choclate.. the other supplier daz sells both. I just dont get why the last 4 rows are repeating them self? — johnny
– johnny, Commented Feb 17, 2013 at 22:45
@bmorris591 what can I do to not show these repeating rows? Can I use the DISTINCT somehow?> — johnny
– johnny, Commented Feb 17, 2013 at 22:58
I would suggest that you go to sqlfiddle.com and create a schema and some sample data. And then use that to describe what kind of result you are looking for. This will make it easy for people to give you the results you are looking for. — jmoreno
– jmoreno, Commented Feb 17, 2013 at 23:19

Evyatar Nezer · Accepted Answer · 2013-02-18 22:05:33Z

1

Your need to use inner join for "filtering" the rows. try this:

select c.FName, p.ProductName, s.Description, s.Quantity, s.Price 
FROM requests r
inner join sellers s on r.ProductID = s.ProductID
inner join products p on p.ProductID=s.ProductID 
inner join customers c on c.ID=s.CID       
where r.C_ID = 3 AND r.MatchType='Price'
ORDER BY s.Price ASC

hope that i don't have any mistake here (its late here), but its the main idea. for columns that exist in two tables and you wish to use the for filtering use inner join, for filtering from one table use the were clause .. (that the theory on one leg) ...

--- edit ----

this query can show the diffidence between the requests ...

select c.FName, p.ProductName, s.Description, s.Quantity, s.Price, r.demandid as 'Request ID'
FROM requests r
inner join sellers s on r.ProductID = s.ProductID
inner join products p on p.ProductID=s.ProductID 
inner join customers c on c.ID=s.CID       
where r.C_ID = 3 AND r.MatchType='Price'
ORDER BY r.demandid s.Price ASC

edited Feb 18, 2013 at 22:05

answered Feb 17, 2013 at 22:37

Evyatar Nezer

611 silver badge3 bronze badges

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

11 Comments

johnny Over a year ago

This seems to return the same result as mines:( the 2 records are still repeating:(

Evyatar Nezer Over a year ago

First you can add distinct after the select, but it wont "solve" the problem entirely .. its just a work around .. what are you trying to ask in that query ?

johnny Over a year ago

wow yes that is a work around... Okay the whole aim of me asking this is because I have a small problem. If it is okay for you I have asked the question in more detail here: link and also here [link]stackoverflow.com/questions/14915675/… You may read the first link and then the second one if you wish. I am so stuck with this problem spent 2 days on it and I cant figure it out at all:(

johnny Over a year ago

I am going to go sleep now (head and eyes kill lol!) but if you could provide any help I would be truly grateful.

Evyatar Nezer Over a year ago

I've read the problem you introduced .. its not exactly small, but first thing first .. I think the positioning of the joins need to be changed. i'm going to change the query again but i'll change the order of the inner joins ... so the filtering order would be different, check it in the morning .. good night.

|

Just Aguy · Accepted Answer · 2013-02-18 01:48:09Z

0

select c.FName, p.Name, s.Description, s.Quantity, s.Price 
FROM customers c
left join sellers s on c.ID = s.cid
left join requests r on r.ProductID = s.ProductID
left join products p on p.productid = s.productid
where r.C_ID = 1
AND r.MatchType='Price'
ORDER BY s.Price ASC

I setup a fiddle for it SQL Fiddle and threw some dummy data in. The code works if I set the data up correctly.

edited Feb 18, 2013 at 1:48

answered Feb 17, 2013 at 23:19

Just Aguy

3271 silver badge7 bronze badges

6 Comments

johnny Over a year ago

Thanks for your reply. I have tried this and still the rows are repeating. I have edited the question showing what my tables look like. If that helps explain why?

johnny Over a year ago

as I said to previous commenter for the answer.. okay so I think my edit explains why the row was repeating its because that same customer has request the product twice with different quantities. So, inevitabilty it will match it one time for the first request(1) and again for second time (requestid=7)... Is there a way however, if the customer does place a order twice then it only displays the matches once? I guess I would have to use distinct right?

Just Aguy Over a year ago

My question is why would you want to hide an active order by a customer? There should be some key constraints or code in the application that prevents duplicate entries. Remember, your database relies on these kinds of checks and balances for data integrity. Without them your db will be bloated with garbage data and you'll never know which one is the actual request and which is not.

johnny Over a year ago

Yes I do agree with you entirely. I realised what the issue was. You are right in the sence, that I need to put a check so that the same customer is not entering the EXACT same amounts and quantites twice. In theory I am not hiding it but I saw what my fault was. It was because if the same customer has requested say apples, then it would show the same supplier twice once for the apple requested the first time and then again for the second request made. Hence, the rows were repeating. I now have my next problem to tackle.

johnny Over a year ago

If you wish I would be highly grateful if you could take a look at link as this is my main problem I am dealing with. If you look at post number5 which is made by me. Like I said any help or advice provided would be of great help.

|

Collectives™ on Stack Overflow

Repeated Rows Sql Query

2 Answers 2

11 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related