Need help with SELECT statement against unusual table...
-
Hello everyone...
Probably a simple question, but this beginner is a bit perplexed.
I am sure I am missing the obvious... and would appreciate some help.
Given the following data (simplified) table...
CUSTOMER_ID FIELD_NUMBER FIELD_VALUE
--------------------------------------------
1 1 BLUE
1 2 RED
1 3 GREEN
2 1 BLUE
2 2 null
2 3 null
3 1 YELLOW
3 2 RED
3 3 GREEN
I need to retrieve all customer records having
FIELD_NUMBER = 1 AND FIELD_VALUE = BLUE
AND
FIELD_NUMBER = 3 AND FIELD_VALUE = GREEN
If we look at the above data, I need to retrieve ONLY CUSTOMER_ID 1
Of course, I may need to retrieve all customer records having
FIELD_NUMBER = 2 AND FIELD_VALUE = RED
AND
FIELD_NUMBER = 3 AND FIELD_VALUE = GREEN
in which case I would need to retrieve CUSTOMER_ID 1 and 3
I hope this is clear enought and I thank you all in advance for any suggestion.
Giorgio
-
Not sure you are providing all of your business rules, but... here goes
select customer_id
from unusualTable
where (field_number = 1 and field_value = 'BLUE')
OR ( field_number = 3 and field_value= 'Green')
-
DECLARE @C TABLE (CUSTOMER_ID int, FIELD_NUMBER int, FIELD_VALUE varchar(200) )
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(1,1,'BLUE')
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(1,2,'RED')
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(1,3,'GREEN')
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(2,1,'BLUE')
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(2,2, NULL)
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(2,3, NULL)
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(3,1,'YELLOW')
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(3,2,'RED')
INSERT INTO @C (CUSTOMER_ID, FIELD_NUMBER, FIELD_VALUE) VALUES(3,3,'GREEN')
SELECT DISTINCT C1.CUSTOMER_ID
FROM @C C1 JOIN @C C2 ON C1.CUSTOMER_ID = C2.CUSTOMER_ID
WHERE
(C1.FIELD_NUMBER = 2 AND C1.FIELD_VALUE = 'RED')
AND
(C2.FIELD_NUMBER = 3 AND C2.FIELD_VALUE = 'GREEN')
SELECT DISTINCT C1.CUSTOMER_ID
FROM @C C1 JOIN @C C2 ON C1.CUSTOMER_ID = C2.CUSTOMER_ID
WHERE
(C1.FIELD_NUMBER = 1 AND C1.FIELD_VALUE = 'BLUE')
AND
(C2.FIELD_NUMBER = 3 AND C2.FIELD_VALUE = 'GREEN')
Cheers!
* Noel -
Of course, I may need to retrieve all customer records having
FIELD_NUMBER = 2 AND FIELD_VALUE = RED
AND
FIELD_NUMBER = 3 AND FIELD_VALUE = GREEN
And that's the real rub, isn't it, Giorgio?
You need to retrieve the customer RECORDS which means ALL of the rows for a given customer.You could search by "Field_Number" and "Field_Value" to get the Customer_ID as many have done, but that doesn't get you the customer RECORD which consists of many rows from the "unusual table".
By the way, this long-skinny unusual table is better known as a "Name/Value" table. Without writing a book on the subject, these tables have 2 primary purposes...
- Allow users to add attributes to the RECORDS just by adding new "Field_Numbers" (attribute NAME) to the table without making a change to the schema of the table (think "customizable" table driven by data only).
- Drive programmers nuts
So, following Noel's good example, let's first make a test table and fill it with some test data...
CREATE TABLE #TestTable
(
Customer_ID INT NOT NULL,
Field_Number INT NOT NULL,
Field_Value VARCHAR(200),
PRIMARY KEY CLUSTERED (Customer_ID,Field_Number)
)
INSERT INTO #TestTable
(Customer_ID, Field_Number, Field_Value)
SELECT 1,1,'BLUE' UNION ALL
SELECT 1,2,'RED' UNION ALL
SELECT 1,3,'GREEN' UNION ALL
SELECT 2,1,'BLUE' UNION ALL
SELECT 2,2, NULL UNION ALL
SELECT 2,3, NULL UNION ALL
SELECT 3,1,'YELLOW' UNION ALL
SELECT 3,2,'RED' UNION ALL
SELECT 3,3,'GREEN'
Normally, some dynamic SQL is required to make this a truly data driven customizable table, but let's skip that and work just on your problem with some static code....
First, let's gather up RECORDS... that is, let's put all the information for a given Customer_ID on a single row... and let's be sure to do that will all the Customer_IDs just to make life easy...
SELECT Customer_ID,
MIN(CASE WHEN Field_Number = 1 THEN Field_Value ELSE NULL END) AS Col1,
MIN(CASE WHEN Field_Number = 2 THEN Field_Value ELSE NULL END) AS Col2,
MIN(CASE WHEN Field_Number = 3 THEN Field_Value ELSE NULL END) AS Col3
FROM #TestTable
GROUP BY Customer_ID
... that gives us the following return...
Customer_ID
Col1
Col2
Col3
1
BLUE
RED
GREEN
2
BLUE
3
YELLOW
RED
GREEN
Amazing, huh? Looks almost like a real table instead of an "unusual" table. In fact, we can use that result set as if it were a table. If we take the query, wrap it in parenthesis, and give it an alias, we can actually use it in the FROM clause of another query. These are known as "Derived Tables" and they are covered in Books Online.
The real key here is, once we get the "unusual" table into a layout similar to a real table, it suddenly becomes a lot easier to work with. For example, one of the problems you stated was...
Of course, I may need to retrieve all customer records having
FIELD_NUMBER = 2 AND FIELD_VALUE = RED
AND
FIELD_NUMBER = 3 AND FIELD_VALUE = GREEN
So, like I said, let's use the previous query's result set as if it were a table and add some criteria to find what we want AND return ALL the data for the customers that meet the criteria...
SELECT d.*
FROM (--Derived table "d" returns each customer RECORD as a single ROW
SELECT Customer_ID,
MIN(CASE WHEN Field_Number = 1 THEN Field_Value ELSE NULL END) AS Col1,
MIN(CASE WHEN Field_Number = 2 THEN Field_Value ELSE NULL END) AS Col2,
MIN(CASE WHEN Field_Number = 3 THEN Field_Value ELSE NULL END) AS Col3
FROM #TestTable
GROUP BY Customer_ID
)d
WHERE d.Col2 = 'Red'
OR d.Col3 = 'Green'
Here's the result...
Customer_ID
Col1
Col2
Col3
1
BLUE
RED
GREEN
3
YELLOW
RED
GREEN
Convert that bit of code to a stored procedure with paramters for Col1, Col2, and Col3 and your "unusual" table becomes a "piece of cake"
Feel free to write back if you have any questions...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I wonder if "Green" may have only Number = 3.
What if some customer has all colors: Blue, Yellow, Red and Green?
Will it shift Green to Number = 4?
And which numbers will be assigned to Blue and Yellow?
Which number will be assigned to Green if it's the only color for the customer? 3 or 1?
What if next day Red and Blue will be added? Will it change the number assigned to Green?
Usually such "unusual" tables are needed for flexibility. To let system record any set of colors in any order.
If it's the case none of the solutions will work because they're solving the wrong problem.
_____________
Code for TallyGenerator -
Thank you all - and especially thank you Jeff. The solution you provided is exactly what I need. Just as Sergiy pointed out, this table is the back-end data storage system of one application window which allows one "administrator" user to "define" up to 40 fields and basically store any type of data in those fields.
Jeff, thank you so much not only for the solution itself but for taking the time to explain in so much detail. I can say I have learned (and undersand!) a technique that will very likely be useful in a number of situations.
Giorgio
-
I agree Georgio, thank you, Jeff.
-
Thanks for the great feedback Georgio and Carlos.
Do look at Serqiy's post, again... as he says, these types of tables provide for some great flexibility, but at a potentially serious cost... Again, without writing a book on the subject, Field_Number should probably "ColumnName" instead. You could also add a "TableName" column to allow the same table to hold a "data customizable schema" for lot's of different tables. But, it's much more difficult to make constraints for valid data and what you can put in a "column".
And, just to reemphasize, in order to make this totally data driven, you're going to need to let the machine write queries for you using dynamic SQL... things could get really out of hand in a hurry unless you really mind your "P's and Q's".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I don't think there is any need of changing table.
I think in real life it does not matter which number is assigned to "Green" and which number is assigned to "Blue".
What does matter is the fact if any particular EmpID has both records - "Green" and "Blue".
So, we can ditch the numbers and get the right result no matter in which way and in which order those profiles were recorded.
Using "split" function we can check any number of profiles in one simple static SQL.
_____________
Code for TallyGenerator -
This looks like a relational division problem.
In simple terms, the solution is that the customer qualifies.
when the count of the customer rows that match the criteria is the same as the number of rows in the criteria.
Here is a solution that works for any number of criteria and for multiple criteria specifications.
if object_id('tempdb..#Criteria') is not null drop table #Criteria
Create table #Criteria
( CriteriaIdintegernot null
, Field_Number INT NOT NULL
, Field_Value VARCHAR(200)
, PRIMARY KEY CLUSTERED (CriteriaId,Field_Number)
)
insert into #Criteria
(CriteriaId, Field_Number, Field_Value)
select 1, 1 , 'BLUE' union all
select 1, 3 , 'GREEN' union all
select 2, 2 , 'RED' union all
select 2, 3 , 'GREEN' union all
select 3, 1 , 'YELLOW' union all
select 3, 2 , 'RED' union all
select 3, 3 , 'GREEN'
select CustomerCriteria.CriteriaId, Customer_ID
from(select Customer_ID, CriteriaId, count(*) as CustomerCriteriaCnt
from #TestTable
join#Criteria
on #Criteria.Field_Number = #TestTable.Field_Number
and #Criteria.Field_Value = #TestTable.Field_Value
group by Customer_ID, CriteriaId
)as CustomerCriteria
join(select CriteriaId , count(*) as CriteriaCnt
from #Criteria
group by CriteriaId
) as Criteria
on Criteria .CriteriaId = CustomerCriteria.CriteriaId
and Criteria .CriteriaCnt = CustomerCriteria.CustomerCriteriaCnt
SQL = Scarcely Qualifies as a Language
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply