Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 My SP returns repeated records

Author  Topic 

cesark
Posting Yak Master

215 Posts
Posted - 2005-10-06 : 05:02:12
I have a SP to return orders to web app according to sent parameters by app. If the data I have to return is from Orders and Users table there is no problem:
[CODE]
USE market
GO
CREATE PROC find_orders
@Order_date datetime, @User_id int
As

SELECT order_date, us.User_address, us.User_phone, ...
FROM Orders As ord

JOIN users As us
On ord.user_num = us.user_id

WHERE user_num = @User_id And order_date = @Order_date

GO
[/CODE]

But if the data I have to return is from Products_by_orders, I don’ t know how to return orders so that these orders are not repeated, since in Products_by_orders table several records can belong to the same order. This code returns repeated orders:
[CODE]
USE market
GO
CREATE PROC find_orders
@Order_date datetime, @User_id int, @Quality_prod int
As

SELECT order_date, us.User_address, us.User_phone, ...
FROM Orders As ord

JOIN users As us
On ord.user_num = us.user_id
LEFT JOIN Products_by_orders As prod
On ord.order_id = prod.order_num

WHERE user_num = @User_id And order_date = @Order_date And prod.Quality_prod = @Quality_prod

GO
[/CODE]

How can I do it so that when returned order has several products (several records in Products_by_orders table with the same order number) is not returned the same order several times?

Thank you

ditch
Master Smack Fu Yak Hacker

1466 Posts
Posted - 2005-10-06 : 05:11:39
LEFT JOIN (select distinct order_num from Products_by_orders) prod
On ord.order_id = prod.order_num



Duane.
Go to Top of Page

cesark
Posting Yak Master

215 Posts
Posted - 2005-10-06 : 06:00:55
It does the same ditch
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts
Posted - 2005-10-06 : 06:04:16
oh ... and my query probably didn't work either cos I didn't include the Quality_Prod field in the sub select.

prod.Quality_prod = @Quality_prod


so post the full statement as it currently is then we can see what's wrong.


Duane.
Go to Top of Page

cesark
Posting Yak Master

215 Posts
Posted - 2005-10-06 : 06:13:31
[code]
USE market
GO
CREATE PROC find_orders
@Order_date datetime, @User_id int, @Quality_prod int
As

SELECT order_date, us.User_address, us.User_phone, ...
FROM Orders As ord

JOIN users As us
On ord.user_num = us.user_id
LEFT JOIN (Select distinct Order_num, Quality_prod From Products_by_orders) prod
On ord.order_id = prod.order_num

WHERE user_num = @User_id And order_date = @Order_date And (prod.Quality_prod = @Quality_prod Or @Quality_prod = 0)

GO
[/code]

When the user does not specify any Quality_prod (@Quality_prod = 0) then repeated orders are returned.
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts
Posted - 2005-10-06 : 06:19:55
(prod.Quality_prod = @Quality_prod Or @Quality_prod = 0)

This is probably what is causing more than 1 row per order.
you need to decide on exactly what you want here - cos you could perhaps change it to min(Quality_Prod) or max(quality_prod) - but then that all depends on the business requirement.




Duane.
Go to Top of Page

cesark
Posting Yak Master

215 Posts
Posted - 2005-10-06 : 12:00:05
I don’ t know how can I apply your suggestion to my real code. Please help..

Here is my real code (left joins affected, and ‘where’ clause part affected):


    LEFT JOIN Offers_quality As of_qual  
    On offe.Offer_id = of_qual.Offer_num And of_qual.Quality_num = @Quality_num 
    LEFT JOIN Offers_rippening As of_rip  
    On of_qual.Offer_qual_id = of_rip.Offer_qual_num And of_rip.Rippening_num = @Rippening_num


 WHERE And ... And ...
   And IsNull(of_qual.Quality_num, 0) = CASE WHEN IsNull(@Quality_num, 0) = 0 THEN IsNull(of_qual.Quality_num, 0) ELSE @Quality_num END    
     And ((of_qual.Caliber >= @Caliber_min And of_qual.Measure_caliber = @Measure_caliber_min And of_qual.Kind_caliber = @Kind_caliber_min) Or @Caliber_min = 0)
     And ((of_qual.Caliber <= @Caliber_max And of_qual.Measure_caliber = @Measure_caliber_max And of_qual.Kind_caliber = @Kind_caliber_max) Or @Caliber_max = 0)
     And IsNull(of_rip.Rippening_num, 0) = CASE WHEN IsNull(@Rippening_num, 0) = 0 THEN IsNull(of_rip.Rippening_num, 0) ELSE @Rippening_num END    
     And ((of_qual.Picture_name Is Not Null And @Picture = 1) Or @Picture = 0)


Now all the logic and filters works fine, except repeated offers returned to web application.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-10-06 : 12:31:20
How about showing us a small example of what you are getting returned as a result, and then what you want returned.
Go to Top of Page

cesark
Posting Yak Master

215 Posts
Posted - 2005-10-06 : 13:08:52
Offers_quality table Primary Key is Offer_qual_id
Offers_ripening table Foreign Key (linked to Offers_quality) is Offer_qual_num


@variables involved in the part I posted are only these:

@Quality_num: The user can specify search offers by quality number (1 or 2 or 3) or none (0).

@Rippening_num: The user can specify search offers by ripening number (1 or 2 or 3) or none (0).

@Caliber_min, @Measure_caliber_min and @Kind_caliber_min: If user specify a minimum caliber (@Caliber_min), calibers >= to @Caliber_min are searched, and for that minimum caliber is also checked these two variables (of_qual.Measure_caliber = @Measure_caliber_min And of_qual.Kind_caliber = @Kind_caliber_min), both variables specified by user if he/she has specified some minimum caliber. None of these variables are searched if user doesn’ t specify a minimum caliber (@Caliber_min = 0).

@Caliber_max, @Measure_caliber_max and @Kind_caliber_max: The same as previous but with maximum caliber.

@Picture: If user want to see only offers with pictures (images), he/she send @Picture = 1, else @Picture = 0.


Do you understand now a little bit more the logic? 



    LEFT JOIN Offers_quality As of_qual  
    On offe.Offer_id = of_qual.Offer_num And of_qual.Quality_num = @Quality_num 
    LEFT JOIN Offers_rippening As of_rip  
    On of_qual.Offer_qual_id = of_rip.Offer_qual_num And of_rip.Rippening_num = @Rippening_num


 WHERE And ... And ...
   And of_qual.Quality_num = CASE WHEN @Quality_num = 0 THEN of_qual.Quality_num ELSE @Quality_num END    
     And ((of_qual.Caliber >= @Caliber_min And of_qual.Measure_caliber = @Measure_caliber_min And of_qual.Kind_caliber = @Kind_caliber_min) Or @Caliber_min = 0)
     And ((of_qual.Caliber <= @Caliber_max And of_qual.Measure_caliber = @Measure_caliber_max And of_qual.Kind_caliber = @Kind_caliber_max) Or @Caliber_max = 0)
     And of_rip.Rippening_num = CASE WHEN @Rippening_num = 0 THEN of_rip.Rippening_num ELSE @Rippening_num END    
     And ((of_qual.Picture_name Is Not Null And @Picture = 1) Or @Picture = 0)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-10-06 : 13:34:02
How about showing us a small example of what you are getting returned as a result, and then what you want returned.
Go to Top of Page

cesark
Posting Yak Master

215 Posts
Posted - 2005-10-06 : 14:17:36
I am getting the correct offers according to parameters/variables sent by user from web application, but some offers are repeated because the reason I explained in my first post.


A simple example of what is returned:

According to several parameters sent I can get;
Offer_title, Company_name, Date

120 Units of onions, Fruits & Vegetables, 2005-10-06
120 Units of onions, Fruits & Vegetables, 2005-10-06
120 Units of onions, Fruits & Vegetables, 2005-10-06

(The same offer repeated three times)

And I only want:
120 Units of onions, Fruits & Vegetables, 2005-10-06



Here is the complete query: (I am sorry but I can’ t do more)


    Select Offer_id, User_num, Offer_date, Offer_title, us.Company_name, prod_city.City_name
    From Offers As offe 

    JOIN Users As us
    On offe.User_num = us.User_id 
    LEFT JOIN Names_products As prod   
    On offe.Product_num = prod.Product_Id
    LEFT JOIN Families_Products As fam
    On prod.Family_num = fam.Family_Id
    JOIN Cities As prod_city
    On offe.place_City_num = prod_city.City_id
    JOIN States As state_prod
    On state_prod.State_Id = prod_city.State_num  
    LEFT JOIN DOP_IGP_Product As DI_prod
    On offe.DOP_IGP = DI_prod.DOP_IGP_Product_id 
    LEFT JOIN DOP_IGP As DI
    On DI.DOP_IGP_id = DI_prod.DOP_IGP_num
    LEFT JOIN Offers_quality As of_qual  
    On offe.Offer_id = of_qual.Offer_num And of_qual.Quality_num = @Quality_num  
    LEFT JOIN Offers_rippening As of_rip  
    On of_qual.Offer_qual_id = of_rip.Offer_qual_num And of_rip.Rippening_num = @Rippening_num 
   
    Where ((Offer_date < @Offer_date) Or (Offer_date = @Offer_date And Offer_id <= @Id)) And
     ((IsNull(fam.Family_Id, 0) = CASE WHEN IsNull(@Family, 0) = 0 Then IsNull(fam.Family_Id, 0) Else @Family End) Or (@Family = 9 AND Other_families IS NOT NULL))
     And IsNull(offe.Product_num, 0) = CASE WHEN IsNull(@Product, 0) = 0 Then IsNull(offe.Product_num, 0) Else @Product End 
     And IsNull(Variety, '') = CASE WHEN IsNull(@Variety, '') = '' Then IsNull(Variety, '') Else @Variety End   
     And IsNull(Trade_name, '') = CASE WHEN IsNull(@Trade_name, '') = '' Then IsNull(Trade_name, '') Else @Trade_name End  
     And IsNull(prod_city.State_num, 0) = CASE WHEN IsNull(@State, 0) = 0 Then IsNull(prod_city.State_num, 0) Else @State End
     And IsNull(place_City_num, 0) = CASE WHEN IsNull(@place_City_num, 0) = 0 Then IsNull(place_City_num, 0) Else @place_City_num End 
     And (Harvests_date <= DateAdd(day, DateDiff(day, 0, getdate()), 0) And Harvests_date > dateadd(day, -@Harvests_date, DateAdd(day, DateDiff(day, 0, getdate()), 0)) Or @Harvests_date = 0)    
     And IsNull(origin_State_num, 0) = CASE WHEN IsNull(@origin_State_num, 0) = 0 THEN IsNull(origin_State_num, 0) ELSE @origin_State_num END    
     And IsNull(origin_City_num, 0) = CASE WHEN IsNull(@origin_City_num, 0) = 0 THEN IsNull(origin_City_num, 0) ELSE @origin_City_num END  
     And IsNull(Kind_production_num, 0) = CASE WHEN IsNull(@Kind_production_num, 0) = 0 THEN IsNull(Kind_production_num, 0) ELSE @Kind_production_num END 
     And IsNull(DI_prod.DOP_IGP_num, 0) = CASE WHEN IsNull(@DOP_IGP, 0) = 0 Then IsNull(DI_prod.DOP_IGP_num, 0) Else @DOP_IGP End  
     And IsNull(of_qual.Quality_num, 0) = CASE WHEN IsNull(@Quality_num, 0) = 0 THEN IsNull(of_qual.Quality_num, 0) ELSE @Quality_num END    
     And ((of_qual.Caliber >= @Caliber_min And of_qual.Measure_Caliber = @Measure_Caliber_min And of_qual.Kind_Caliber = @Kind_Caliber_min) Or @Caliber_min = 0)
     And ((of_qual.Caliber <= @Caliber_max And of_qual.Measure_Caliber = @Measure_Caliber_max And of_qual.Kind_Caliber = @Kind_Caliber_max) Or @Caliber_max = 0)
     And IsNull(of_rip.Rippening_num, 0) = CASE WHEN IsNull(@Rippening_num, 0) = 0 THEN IsNull(of_rip.Rippening_num, 0) ELSE @Rippening_num END     
     And ((of_qual.Picture_name Is Not Null And @Picture = 1) Or @Picture = 0)

    Order by Offer_date Desc, Offer_id Desc
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-10-06 : 15:00:27
In your original SQL from your first post, try replacing


LEFT JOIN 
   Products_by_orders As prod
      On ord.order_id = prod.order_num 
WHERE user_num = @User_id And 
      order_date = @Order_date And 
      prod.Quality_prod = @Quality_prod


with

LEFT JOIN  (SELECT DISTINCT order_num 
            FROM Products_by_orders
            WHERE Quality_prod = @Quality_prod) prod
ON
  ord.order_id = prod.order_num 
WHERE 
  user_num = @User_id And 
  order_date = @Order_date
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-10-06 : 15:24:49
Did you post the DDL for those two tables?

Read the hint link below



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site