• 0

Database - How to join this?


Question

I'm strugling a little here. Let me start from the beginning...

I want to search my product catalogue, by paramter. An example being paramters or Artist and Format for CDs. In fact I have a table that maps these as follows:

[b]t_product_parameter_options[/b]
ID -> 1  . .  description -> "Artist"
ID -> 2  . .  description -> "Format"

I then have a table for each parameter:

[b]t_product_parameter_values[/b]
ID -> 1  . .  valid_To -> 1  . .  description -> "Michael Jackson"
ID -> 2  . .  valid_To -> 1  . .  description -> "Depeche Mode"
ID -> 3  . .  valid_To -> 2  . .  description -> "The Prodigy"
ID -> 4  . .  valid_To -> 2  . .  description -> "CD"
ID -> 5  . .  valid_To -> 2  . .  description -> "Vinyl"

These are then mapped against products as follows:

[b]t_product_parameters[/b]
product_ID -> 123123  . .  option_ID -> 1  . .  value_ID -> 1
product_ID -> 123123  . .  option_ID -> 2  . .  value_ID -> 4
product_ID -> 456464  . .  option_ID -> 1  . .  value_ID -> 2
product_ID -> 456464  . .  option_ID -> 2  . .  value_ID -> 4
product_ID -> 896863  . .  option_ID -> 1  . .  value_ID -> 2
product_ID -> 896863  . .  option_ID -> 2  . .  value_ID -> 5
product_ID -> 557367  . .  option_ID -> 1  . .  value_ID -> 2
product_ID -> 557367  . .  option_ID -> 2  . .  value_ID -> 4

Okie dokie so far.

Into my stored procedure I am given a delimited string of integers, representing VALUE_IDs. I have turned these into a table through a function I wrote - so let's call that:

[b]splitParams[/b]
splitval_Val -> 2
splitval_Val -> 4

What I want to do is match:

* Single products

* Where ALL value_IDs for that product match splitval_Vals

* WHERE a related option is given

So, a product may also have a paramter for "RPM". If I look for ONLY artist and format - I am do not need to consider the values related to RPM.

Am I making sense here?

Cheers

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

You're table structure is just plain wrong.

You only need columns based on your initial parameters. Use numbers to represent format (0=cd, 1=video, 2 = vinyl, etc):

| PROD_ID | ARTIST | FORMAT | TITLE |

---------------------------------------------------------------------

1 Michael Jackson 0 Thriller

2 Michael Jackson 1 Thriller

3 Michael Jackson 2 Thriller

Pass a query string as such "Michael Jackson, 1,2,0"

query as similar to this:

CREATE PROCEDURE [dbo].[GetProducts]

@DATA as nvarchar(1024)

AS

DECLARE

@Artist as nvarchar(50)

@FLAG as boolean

,@Position as int

BEGIN

SET NOCOUNT ON;

Set @Flag = true

SET @Position = CHARINDEX(',', @Data)

' DETERMINE IF USER SELECTED AN ARTIST

IF ISINT(Substring(@Data, 1, @Position)) = FALSE

BEGIN

SET @FLAG = FALSE

SET @Artist = Substring(@Data, 1, @Position)

SET @Data = Substring(@Data, @Position+1, len(@Data))

END

IF @FLAG = TRUE

BEGIN

SELECT * FROM [PRODUCTS] WHERE FORMAT IN (@DATA)

END

IF @FLAG = FALSE

BEGIN

IF LEN(@DATA) >0 'USER HAS SELECTED ARTIST AND FORMAT

BEGIN

SELECT * FROM [PRODUCTS] WHERE ARTIST = @ARTIST AND FORMAT IN (@DATA)

END

ELSE 'USER SELECTED ONLY AN ARTIST

BEGIN

SELECT * FROM [PRODUCTS] WHERE ARTIST = @ARTIST

END

END

END

Link to comment
Share on other sites

  • 0

With respect I 100% disagree.

We have hundreds of thousands of products within different categories, and with a range of different parameter options each with a lot of values.

Storing them on one product line would be ridiculous.

My method allows me to compare products easily, recommend alternate products, show specific parameters per category, etc.

Link to comment
Share on other sites

  • 0

Sorry, I somehow got the impression this was for a small home collection, which is why I recommended one table.

you lost me here though...

What I want to do is match:

* Single products

* Where ALL value_IDs for that product match splitval_Vals

* WHERE a related option is given

So, a product may also have a paramter for "RPM". If I look for ONLY artist and format - I am do not need to consider the values related to RPM.

Are you asking for a unique products list that must match every value ID given? And if the user selects RPM, just ignore it and be concerned with the other values provided?

If so, what is RPM's value?

Link to comment
Share on other sites

  • 0

Bingo, that's exactly it :)

RPM's value doesn't matter, as it's a hypothetical 3rd, 4th, 90th option - if that makes sense. There's no fixed number of options or values.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.