Post reply

SQL annoyance - Many to many IDs in query

  • July 22, 2009 at 10:18 am

    #223871

    Hi,

    I've a Product table as well as two other tables linked to the product table in a many-to-many relationship - so overall I've got 5 tables as follows:

    Products

    Parts

    Colours

    ProductParts (link table)

    ProductColours (link table)

    Now, I'm building a search that potentially could pass in a bunch of PartIds and ColourIds and I want to return a list of ALL products that contain ANY of those parts or colours. I also need to avoid duplicate product ids being returned.

    However, I need it to also accommodate the fact that the user might do a search that doesn't include ANY parts or ANY colours and maybe just searches by some other parameter (producet - year for example).

    I've read about optional parameters and have implemented it easily enough where the user is only able to select ONE item (using Isnull etc). In this case though, with trying to accommodate the potential multiple IDs I'm stumped.

    What I've got so far, and this is the umpteenth version of this so ignore the logic in this query as I'm getting nowhere with it and presume it's entirely incorrect and the wrong way to do it:

    SELECT *

    FROM Products

    WHERE (

    EXISTS (SELECT ProductId FROM ProductParts WHERE Products.ProductID= ProductParts.ProductId AND ProductParts.ProductPartsId IN (6,1,2))

    AND

    EXISTS (SELECT ProductId FROM ProductColour WHERE Products.ProductID= ProductColour..ProductId AND ProductColour .ProductColourID IN (11,12,9))

    )

    Again let me reiterate that the above is neither working nor likely to be even in the vague direction of the solution. It's my latest attempt and my latest failure.

  • July 22, 2009 at 10:23 am

    #1028496

    This might help you with the problem that they can pass the parameters or not.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Could you maybe post the entire procedure, the table defintions, some sample data and the expected results for certain sets of parameters?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 22, 2009 at 10:40 am

    #1028512

    GilaMonster (7/22/2009)

    This might help you with the problem that they can pass the parameters or not.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Could you maybe post the entire procedure, the table defintions, some sample data and the expected results for certain sets of parameters?

    No problem, the entire procedure doesn't exist yet, the above bit is basically all I have (I'm just sounding out the theory before trying to throw the whole thing together).

    I'm not 100% sure how to copy and paste the table info but I'll give it a go here and we'll see how it turns out.

    PRODUCT TABLE

    CREATE TABLE [dbo].[Products](

    [ProductId] [int] IDENTITY(1,1) NOT NULL,

    [Title] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Desc] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [ShortDesc] [nvarchar](1500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Copyright] [bit] NOT NULL,

    [Year] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [DateAdded] [datetime] NOT NULL CONSTRAINT [DF_Products_DateAdded] DEFAULT (getdate()),

    PRODUCTPART TABLE (Link Table)

    CREATE TABLE [dbo].[ProductPart](

    [ProductPartId] [int] IDENTITY(1,1) NOT NULL,

    [PartId] [int] NOT NULL,

    [ProductId] [int] NOT NULL,

    PRODUCTCOLOUR TABLE (Link Table)

    CREATE TABLE [dbo].[ProductColour](

    [ProductColourId] [int] IDENTITY(1,1) NOT NULL,

    [ColourId] [int] NOT NULL,

    [ProductId] [int] NOT NULL,

    COLOUR TABLE

    CREATE TABLE [dbo].[Colour](

    [ColourId] [int] IDENTITY(1,1) NOT NULL,

    [Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Desc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [DateAdded] [datetime] NOT NULL CONSTRAINT [DF_Colour_DateAdded] DEFAULT (getdate()),

    PARTS TABLE

    [PartId] [int] IDENTITY(1,1) NOT NULL,

    [Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Desc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [DateAdded] [datetime] NOT NULL CONSTRAINT [DF_Parts_DateAdded] DEFAULT (getdate()),

    Note these names and the content of the part / colour tables will change as I need to add more data to them. I'm just at the stage of building this without any real data so I've just dumped in three dummy products and a bunch of parts / colours that appear in multiple products.

  • July 22, 2009 at 10:43 am

    #1028514

    GilaMonster (7/22/2009)

    This might help you with the problem that they can pass the parameters or not.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    By the way, thanks for that link, very helpful.

  • July 22, 2009 at 1:35 pm

    #1028641

    The definition's fine, just need some sample data. Read this to see the best way to post this to get quick responses. http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply