• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 44
  • Last Modified:

I try to avoid duplicate records without success.

I have 3 tables with the following structure:

 --------------------------------------------------------
| dbo.Katigories   |   dbo.Filtra     | dbo.Products     |
|------------------|------------------|------------------|
| katigories_ID    | f_Id             | product_id       |
| product_id       | product_id       | other data.....  |
| Cat_Main_ID      | FilterMain_ID    | ...............  |
| other data.....  | other data.....  | ...............  |
| ...............  | ...............  | ...............  |

Open in new window


And the following query:

SELECT DISTINCT * FROM dbo.Products
INNER JOIN dbo.Katigories ON dbo.Products.product_id = dbo.Katigories.product_id
INNER JOIN dbo.Filtra ON dbo.Products.product_id = dbo.Filtra.product_id
WHERE
dbo.Katigories.Cat_Main_ID = 1
AND
dbo.Filtra.FilterMain_ID IN (1, 3, 6)

Open in new window


The desired result from this query is to give me a list of all products that have the same Cat_Main_ID and also the products with the combination of the following 3 FilterMain_ID’s.

When I select only one FilterMain_ID, then the query works perfectly.

But when I need more than one FilterMain_ID (for example: 1, 3, 6) then the query does not work as I would like to and returns duplicated records from the Products table.

To explain better:
If I only use the FilterMain_ID with the number (1) I get the right results. But when I add the FilterMain_ID to the query with the number (3) then the result gives me the same product 2 times. And if I add to the query also the FilterMain_ID with number (6) then the result gives me the same product 3 times.
What I am doing wrong?
0
Vasilis Dimitrakas
Asked:
Vasilis Dimitrakas
  • 8
  • 7
  • 2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> SELECT DISTINCT * FROM dbo.Products

This is the first problem, instead of * place only the required columns. This will help you to get no. of records reduced as you are using DISTINCT clause. Using * in SELECT selects all columns including your Primary and Unique keys causing your records to get duplicated even though you use DISTINCT clause.
Try it and let us know the modified query along with result set it retrieves along with the required result set to assist you better.
0
 
ste5anSenior DeveloperCommented:
hmm, not sure whether it is possible. Do you need data from dbo.Filtra?

When not, then it is possible as

SELECT DISTINCT P.* ,
                K.*
FROM   dbo.Products P
       INNER JOIN dbo.Katigories K ON P.product_id = K.product_id
       INNER JOIN dbo.Filtra F ON P.product_id = F.product_id
WHERE  K.Cat_Main_ID = 1
       AND F.FilterMain_ID IN ( 1, 3, 6 );

Open in new window


Otherwise not. Cause it's the job of a JOIN to return this "duplicates" for the master table.

p.s. use always table alias names to increase readability.
0
 
Vasilis DimitrakasAuthor Commented:
@Raja Jegan R : I use (*) because i need to display all the fields of the Products table inside my web page.

@ste5an: No. I don't need any data from the table Filtra. I need only all the data from the table Products!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ste5anSenior DeveloperCommented:
This maybe even faster then:

SELECT P.* ,
       K.*
FROM   dbo.Products P
       INNER JOIN dbo.Katigories K ON P.product_id = K.product_id
WHERE  K.Cat_Main_ID = 1
       AND EXISTS (   SELECT *
                      FROM   dbo.Filtra F
                      WHERE  P.product_id = F.product_id
                             AND F.FilterMain_ID IN ( 1, 3, 6 ));

Open in new window


When you have properly indexed tables.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I use (*) because i need to display all the fields of the Products table inside my web page.

Okay then try removing the Primary key column from your SELECT to get unique records.
0
 
Vasilis DimitrakasAuthor Commented:
@Raja Jegan R
@ste5an

Can you please help and advice in real conditions, because am I losing my mind?

The relation between Products and Filtra is 1 to Many ( 1 product – Many Filtra)
The relation between Products and Katigories is also 1 to Many ( 1 product – Many Katigories)

The attached photo shows the real conditions of the involved my tables.

The product with id (312) have FilterMain_ID ( 1 ) & FilterMain_ID ( 3 ) & FilterMain_ID ( 6 )
The product with id (313) have FilterMain_ID ( 1 ) & FilterMain_ID ( 3 )
The product with id (314) have FilterMain_ID ( 1 ) & FilterMain_ID ( 3 ) & FilterMain_ID ( 6 )
The product with id (315) have FilterMain_ID ( 1 )
ps.jpgWhat I really want from my query is this:

When the FilterMain_ID is equal to ( 1 ) then the query should return as a result all the products that have a FilterMain_ID = 1 =  total 4 products (312 - 313 - 314 - 315)
When the FilterMain_ID is equal to ( 1, 3 ) then the query should return as a result all the products that have a FilterMain_ID = 1&3 = total 3 products (312 - 313 - 314)
When the FilterMain_ID is equal to ( 1, 3, 6 ) then the query should return as a result all the products that have a FilterMain_ID = 1 & 3 & 6 = total 2 products (312 - 314)
0
 
ste5anSenior DeveloperCommented:
have a FilterMain_ID = 1&3 = total 3 products (312 - 313 - 314)
This is clearly different then an IN () test, which is syntactic sugar for multiple OR's.

Here you need a different approach:

DECLARE @Products TABLE
    (
        ProductID INT ,
        Payload INT ,
        PRIMARY KEY ( ProductID )
    );
INSERT INTO @Products ( ProductID ,
                        Payload )
VALUES ( 1, 11 ) ,
       ( 2, 22 ) ,
       ( 3, 33 ) ,
       ( 4, 44 ) ,
       ( 5, 55 );

DECLARE @Filter TABLE
    (
        FilterID INT ,
        ProductID INT ,
        PRIMARY KEY
        (
            FilterID ,
            ProductID )
    );

INSERT INTO @Filter ( FilterID ,
                      ProductID )
VALUES ( 1, 1 ) ,
       ( 1, 2 ) ,
       ( 1, 3 ) ,
       ( 3, 1 ) ,
       ( 3, 2 ) ,
       ( 6, 2 ) ,
       ( 6, 4 );

SELECT P.*
FROM   @Products P
WHERE  EXISTS (   SELECT *
                  FROM   @Filter F
                  WHERE  P.ProductID = F.ProductID
                         AND F.FilterID = 1 )
       AND EXISTS (   SELECT *
                      FROM   @Filter F
                      WHERE  P.ProductID = F.ProductID
                             AND F.FilterID = 3 )
       AND EXISTS (   SELECT *
                      FROM   @Filter F
                      WHERE  P.ProductID = F.ProductID
                             AND F.FilterID = 6 );
-- or
WITH ProductFilter
AS ( SELECT   P.ProductID
     FROM     @Products P
              INNER JOIN @Filter F ON F.ProductID = P.ProductID
     WHERE    F.FilterID IN ( 1, 3, 6 )
     GROUP BY P.ProductID
     HAVING   COUNT(*) = 3 )
SELECT P.*
FROM   @Products P
       INNER JOIN ProductFilter PF ON PF.ProductID = P.ProductID;

Open in new window

0
 
Vasilis DimitrakasAuthor Commented:
@ste5an

Dear ste5an thank you for your attention,

The values of the FilterMain_ID are variables that I am getting them from the URL after the visitor selects the desired FilterMain_ID. So, depending of the click (the visitor’s choice) the FilterMain_ID are continually change…
For example: in the first click of a visitor my url looks like: /page.asp?FilterMain_ID=1
If the visitor selects on more filter then i am keeping the privius url and add the new choise like that: /page.asp?FilterMain_ID=1 & FilterMain_ID=3 & FilterMain_ID=6 and so on
0
 
ste5anSenior DeveloperCommented:
E.g.

DECLARE @Products TABLE
    (
        ProductID INT ,
        Payload INT ,
        PRIMARY KEY ( ProductID )
    );
INSERT INTO @Products ( ProductID ,
                        Payload )
VALUES ( 1, 11 ) ,
       ( 2, 22 ) ,
       ( 3, 33 ) ,
       ( 4, 44 ) ,
       ( 5, 55 );

DECLARE @Filter TABLE
    (
        FilterID INT ,
        ProductID INT ,
        PRIMARY KEY
        (
            FilterID ,
            ProductID )
    );

INSERT INTO @Filter ( FilterID ,
                      ProductID )
VALUES ( 1, 1 ) ,
       ( 1, 2 ) ,
       ( 1, 3 ) ,
       ( 3, 1 ) ,
       ( 3, 2 ) ,
       ( 6, 2 ) ,
       ( 6, 4 );

-- either as table type / filter table
DECLARE @UserFilterList TABLE
    (
        ID INT
    );

INSERT INTO @UserFilterList ( ID )
VALUES ( 1 ) ,
       ( 3 ) ,
       ( 6 );

WITH ProductFilter
AS ( SELECT   P.ProductID
     FROM     @Products P
              INNER JOIN @Filter F ON F.ProductID = P.ProductID
              INNER JOIN @UserFilterList UFL ON UFL.ID = F.FilterID
     GROUP BY P.ProductID
     HAVING   COUNT(*) = (   SELECT COUNT(*)
                             FROM   @UserFilterList UFL ))
SELECT P.*
FROM   @Products P
       INNER JOIN ProductFilter PF ON PF.ProductID = P.ProductID;

-- or as string
DECLARE @UserFilterString NVARCHAR(255) = N'1,3,6';

-- Before SQL Server 2016
WITH UserFilterAsXml
AS ( SELECT CAST('<ID>' + REPLACE(@UserFilterString, ',', '</ID><ID>') + '</ID>' AS XML) AS AsXml ) ,
     UserFilterList
AS ( SELECT ID.value('.', 'INT') AS ID
     FROM   UserFilterAsXml
            CROSS APPLY AsXml.nodes('ID') A(ID) ) ,
     ProductFilter
AS ( SELECT   P.ProductID
     FROM     @Products P
              INNER JOIN @Filter F ON F.ProductID = P.ProductID
              INNER JOIN UserFilterList UFL ON UFL.ID = F.FilterID
     GROUP BY P.ProductID
     HAVING   COUNT(*) = (   SELECT COUNT(*)
                             FROM   UserFilterList UFL ))
SELECT P.*
FROM   @Products P
       INNER JOIN ProductFilter PF ON PF.ProductID = P.ProductID;

-- SQL Server 2016+
WITH UserFilterList
AS ((SELECT SS.value AS ID
     FROM   STRING_SPLIT(@UserFilterString, ',') SS)) ,
     ProductFilter
AS ( SELECT   P.ProductID
     FROM     @Products P
              INNER JOIN @Filter F ON F.ProductID = P.ProductID
              INNER JOIN UserFilterList UFL ON UFL.ID = F.FilterID
     GROUP BY P.ProductID
     HAVING   COUNT(*) = (   SELECT COUNT(*)
                             FROM   UserFilterList UFL ))
SELECT P.*
FROM   @Products P
       INNER JOIN ProductFilter PF ON PF.ProductID = P.ProductID;

Open in new window


The key is that product_id, FilterMain_ID is unique in your filter table.
0
 
Vasilis DimitrakasAuthor Commented:
@ste5an
Also, please have in mind that the total number of my FilterMain_ID’s is 10. So, if we find the correct query that I have to use, then is not a problem for me to prepare 10 different IF conditions, that each one of them will point to 10 different corresponding queries.
0
 
ste5anSenior DeveloperCommented:
IF? Where does this now come from?

There's no conditional required. See #42520509. It's using a table. Either as table type or string (using STRING_SPLIT or XML to derive the table from it).
0
 
Vasilis DimitrakasAuthor Commented:
@ste5an
Dear ste5an,

I am sorry but I cannot understand you well. I am not an expert like you…

  • What is the final code that I have to use?

  • Where I have to place this code inside my Classic ASP page.

  • This code will cover all of the 10 different possible cases (the 10 different values) that the FilterMain_ID may have, depending on the visitor’s click?
0
 
ste5anSenior DeveloperCommented:
1) Use what you like.
2) No where. It should get on the server as stored procedure.
3) Sure.

E.g.

CREATE PROCEDURE p_Filter
    (
        @UserFilterString NVARCHAR(255)
    )
AS
    SET NOCOUNT ON;

    WITH UserFilterList
    AS ((SELECT SS.value AS ID
         FROM   STRING_SPLIT(@UserFilterString, ',') SS)) ,
         ProductFilter
    AS ( SELECT   P.ProductID
         FROM     dbo.Products P
                  INNER JOIN dbo.Filtra F ON F.ProductID = P.ProductID
                  INNER JOIN UserFilterList UFL ON UFL.ID = F.FilterMainID
         GROUP BY P.ProductID
         HAVING   COUNT(*) = (   SELECT COUNT(*)
                                 FROM   UserFilterList UFL ))
    SELECT P.*
    FROM   dbo.Products P
           INNER JOIN dbo.Katigories K ON K.ProductID = P.ProductID
           INNER JOIN ProductFilter PF ON PF.ProductID = P.ProductID;

Open in new window

0
 
Vasilis DimitrakasAuthor Commented:
@ste5an

And what i have to write inside my commant text? With what i must replace the below "SELECT FROM......" part in my commant text???

RS1_cmd.CommandText = "SELECT FROM ........"
0
 
ste5anSenior DeveloperCommented:
EXECUTE dbo.p_Filter '1,3,6';

Open in new window

0
 
Vasilis DimitrakasAuthor Commented:
@ste5an

Dear sta5an,

Is it possible in some other way to avoid all this messy thing with stored procedure? There is no other way to do what I want with a regular sql query?

All this thing is completely unknown to me and I do not know if I can do it and if I can handle it.

Can you please think better before you exclude at all the case of building a regular sql query?

Please…..
0
 
ste5anSenior DeveloperCommented:
Is it possible in some other way to avoid all this messy thing with stored procedure? There is no other way to do what I want with a regular sql query?

The stored procedure approach is the clean approach. Precompiled query plans vs adhoc queries (your "regular" queries). Also faster due to lesser server roundtrip data.

You can still stuff one of the queries in #42520494 into a client-side query. But this is not a clean apporach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 8
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now