CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Richard Gale
Richard Gale

Reputation: 1952

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

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.

Upvotes: 1

Views: 482

Answers (3)

Richard Gale
Richard Gale

Reputation: 1952

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.

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

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

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10274

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

Upvotes: 1

Related Questions