SQL Server - Select data from one table based on a string value

Question

I am wanting to look up data from one of my database tables based on a varchar stored in another table.

We have a table of Manufacturers (M) and a table of Parameters (P), and rather than having to have a parameter per Manufacturer (as there is a lot of crossover between 60% of them), we thought it would be cleaner to have parameters per manufacturer where required, and for the other ones just have a set of default parameters.

This means that I cannot store ManufacturerID in both tables and create a simple link, instead I need to link using the ManufacturerName.

So I need to know if it is possible to Link via ManufacturerName, and where there is no match look in the Parameters table (P) for Manufacturer 'Default'.

As part of the link, we are also including:

P.CategoryID = M.CategoryID AND (P.PriceFrom <= M.BasePrice AND P.PriceTo >= M.BasePrice)

Here is the data structures:

Manufacturers (M):

ManufacturerID, ManufacturerName, CategoryID, BasePrice

Parameters (P)

CategoryID, ManufacturerName, PriceFrom, PriceTo, Percentage

Here is some sample data:

Manufacturers (M):

ManufacturerID | ManufacturerName | CategoryID | BasePrice
3              | Apple            | 1          | 150.00
3              | Apple            | 9          | 99.99
10             | HTC              | 1          | 50.00
15             | Nokia            | 1          | 25.00
19             | Samsung          | 1          | 80.00

Parameters (P):

CategoryID | ManufacturerName | PriceFrom | PriceTo | Percentage | 
1          | Samsung          | 0.00      | 99.99   | 50%        | 
1          | Apple            | 0.00      | 99.99   | 55%        |
1          | Apple            | 100.00    | 149.99  | 45%        | 
9          | Apple            | 0.00      | 99.99   | 65%        |
1          | Default          | 0.00      | 99.99   | 60%        |

So we still need to just return 1 result in each link.

Any suggestions of guidance much appreciated.

Thanks in advance.

Deepshikha · Accepted Answer · 2014-04-11 09:53:10Z

1

As per the conditions you have posted a Left Join should serve the purpose:

select M.ManufacturerID, P.ManufacturerName, M.CategoryID
from Manufacturers M
left join Parameters P On P.CategoryID = M.CategoryID AND (P.PriceFrom <= M.BasePrice AND P.PriceTo >= M.BasePrice)

Please post your table structure here if I have missed something: SQL Fiddle

answered Apr 11, 2014 at 9:53

Deepshikha

10.3k2 gold badges24 silver badges21 bronze badges

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

3 Comments

Richard Gale Over a year ago

Hi. I cannot see how you are getting around the issue of ManufacturerName Matching or selecting 'Default'

Deepshikha Over a year ago

is my table structure and proposed data correct else please update the same with your's .. i'll update my answer according to that!!

Richard Gale Over a year ago

Hi. Please see updated question, hope this helps a bit

Serpiton · Accepted Answer · 2014-04-11 10:14:03Z

Without a table structure to work with is difficoult but something within the line of

SELECT m.name Manufacturer, p.name, p.value, p.Manufacturer alias
FROM   Manufacturers m
       INNER JOIN Parameter p ON (m.name = p.Manufacturer)
UNION ALL
SELECT m.name Manufacturer, p.name, p.value, p.Manufacturer alias
FROM   Manufacturers m
       LEFT JOIN Parameter p on p.Manufacturer = 'default'
WHERE  m.name + p.name not in (SELECT m.name + p.name
                               FROM   Manufacturers m
                                      INNER JOIN Parameter p 
                                            ON (m.name = p.Manufacturer)
                              )
ORDER BY 1, 2

Can get what you need. The first part get you the linked part, the second part get the default value only for the parameters name that don't have already a match.

This is a SQLFiddle with a few data and the query

Richard Gale · Accepted Answer · 2014-05-02 12:17:34Z

0

Ok, so it turns out this was another situation where I was overthinking the problem. I managed to get this to work by having the following Select statement nested within another select:

(SELECT 
    TOP(1) Percentage 
 FROM 
    Parameters AS P 
 WHERE 
    P.CategoryID = R.CategoryID 
    AND (P.PriceFrom <= M.BasePrice AND P.PriceTo >= M.BasePrice) 
    AND (P.ManufacturerName = M.ManufacturerName OR P.ManufacturerName = 'Default') 
 ORDER BY 
    CASE WHEN P.ManufacturerName = 'Default' THEN 'ZZZZZ' ELSE P.ManufacturerName END
 ) AS Markup

Many thanks for your help.

answered May 2, 2014 at 12:17

Richard Gale

1,9526 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL Server - Select data from one table based on a string value

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related