Prev: How to narrow the selection
Next: Sql 2008 - Failover & Domain
From: Frank Uray on 21 Dec 2009 17:57
Hi Plamen

Thanks a lot for your answer.

I have implemented your solution and it works fine :-))

Best regards
Frank Uray

"Plamen Ratchev" wrote:

> Here is another method:
>
> SELECT A.FK_Candidate, A.[Key], A.[Value]
> FROM #test AS A
> WHERE EXISTS(SELECT *
> FROM #test AS B
> WHERE B.FK_Candidate = A.FK_Candidate
> AND B.[Key] = 'Profession'
> AND B.[Value] = 'Informatiker')
> AND EXISTS(SELECT *
> FROM #test AS C
> WHERE C.FK_Candidate = A.FK_Candidate
> AND C.[Key] = 'Source'
> AND C.[Value] = 'Quelle1');
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Frank Uray on 21 Dec 2009 18:00
Hi Gert-Jan

Thanks a lot for your answer.

My datamodel is normalized, the Key and the Value are
in different tables with relation and constraints.
I have simplified it just for testing.

I have implemented the following solution now.

Thanks and best regards
Frank Uray

CREATE TABLE #test ([FK_Candidate] int, [Key] varchar(50), [Value]
varchar(50))
INSERT INTO #test SELECT 1, 'Profession', 'Informatiker'
INSERT INTO #test SELECT 2, 'Profession', 'Informatiker'
INSERT INTO #test SELECT 1, 'Source', 'Quelle1'
INSERT INTO #test SELECT 2, 'Source', 'Quelle2'
INSERT INTO #test SELECT 1, 'TestKey', 'X'
INSERT INTO #test SELECT 2, 'TestKey', 'Y'
INSERT INTO #test SELECT 1, 'Title', 'Mr'
INSERT INTO #test SELECT 2, 'Title', 'Mrs'


SELECT DISTINCT [FK_Candidate]
FROM #test q
WHERE EXISTS(SELECT *
FROM #test
WHERE [FK_Candidate] = q.[FK_Candidate]
AND [Key] = 'Profession'
AND [Value] = 'Informatiker')
AND EXISTS(SELECT *
FROM #test
WHERE [FK_Candidate] = q.[FK_Candidate]
AND [Key] = 'Source'
AND [Value] = 'Quelle1')
AND EXISTS(SELECT *
FROM #test
WHERE [FK_Candidate] = q.[FK_Candidate]
AND [Key] = 'Title'
AND [Value] = 'Mr')





"Gert-Jan Strik" wrote:

> Frank,
>
> I think the problem is not the SELECT, but the table design. Is there
> any particular reason why the design is not normalized? If the
> information belong together, and belongs to the same object, then it
> could be in the same row, instead of using the "flexible" key/value
> storage.
>
> The key/value storage gives you challenges like the query you are after
> (which requires "relational division", google that and you will find
> good solutions). Also, it makes it very hard to implement any kind of
> constraints on the data integrity, such as foreign keys, strong data
> typing, domain checks, etc. And finally, from a performance point of
> view, you might become very disappointed, because the optimizer is not
> particularly well build to optimizer these nonstandard situations.
>
> --
> Gert-Jan
> SQL Server MVP
>
>
> Frank Uray wrote:
> >
> > Hi all
> >
> > I have a little problem with a select.
> > I would need to have the most simple solution :-)
> >
> > I have the following table:
> > [FK_Candidate], [Key], [Value]
> > 1 'Profession' 'Informatiker'
> > 2 'Profession' 'Informatiker'
> > 1 'Source' 'Quelle1'
> > 2 'Source' 'Quelle2'
> >
> > I need to get all candidates with
> > Profession = Informatiker AND Source = Quelle1
> >
> > The result should look like this:
> > 1 'Profession' 'Informatiker'
> > 1 'Source' 'Quelle1'
> >
> > Any ideas ?
> > Thanks a lot for any help !
> >
> > Best regards
> > Frank Uray
> >
> > Script for testing:
> > CREATE TABLE #test ([FK_Candidate] int, [Key] varchar(50), [Value]
> > varchar(50))
> > INSERT INTO #test SELECT 1, 'Profession', 'Informatiker'
> > INSERT INTO #test SELECT 2, 'Profession', 'Informatiker'
> > INSERT INTO #test SELECT 1, 'Source', 'Quelle1'
> > INSERT INTO #test SELECT 2, 'Source', 'Quelle2'
> .
>
From: Plamen Ratchev on 21 Dec 2009 18:37
Another solution you may want to consider is pivoting (but it will return the data in different format):

SELECT FK_Candidate, profession, [source]
FROM (
SELECT FK_Candidate,
MAX(CASE WHEN [Key] = 'Profession' THEN [Value] END) AS profession,
MAX(CASE WHEN [Key] = 'Source' THEN [Value] END) AS [source]
FROM #test
GROUP BY FK_Candidate) AS T
WHERE profession = 'Informatiker'
AND [source] = 'Quelle1';

--
Plamen Ratchev
http://www.SQLStudio.com
From: Frank Uray on 21 Dec 2009 19:26
Hi Plamen

I have tried pivoting already,
it works also but I am generating the SQL Statement
dynamicly in C# and with the EXISTS it is much easyer to create.

But thanks anyway !

Regards
Frank Uray


SELECT *
FROM #test
PIVOT (MAX([Value])
FOR [Key] IN ([Profession],[Source])) p
WHERE [Profession] = 'Informatiker'
AND [Source] = 'Quelle1'





"Plamen Ratchev" wrote:

> Another solution you may want to consider is pivoting (but it will return the data in different format):
>
> SELECT FK_Candidate, profession, [source]
> FROM (
> SELECT FK_Candidate,
> MAX(CASE WHEN [Key] = 'Profession' THEN [Value] END) AS profession,
> MAX(CASE WHEN [Key] = 'Source' THEN [Value] END) AS [source]
> FROM #test
> GROUP BY FK_Candidate) AS T
> WHERE profession = 'Informatiker'
> AND [source] = 'Quelle1';
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Stefan Hoffmann on 22 Dec 2009 05:15
hi Frank,

On 21.12.2009 20:01, Frank Uray wrote:
> If there are only two keys, it works.
> But when you add
> INSERT INTO #test SELECT 1, 'TestKey', 'TK1'
> INSERT INTO #test SELECT 2, 'TestKey', 'TK2'
> it does not work any more.
>
> Do you have another idea ?
Not sure that I understand your problem here. If you need the only the
key-value pairs then simply filter the result:

WITH CTE
AS ( SELECT *
FROM #test
WHERE ( [Key] = 'Profession'
AND [Value] = 'Informatiker'
)
OR ( [Key] = 'Source'
AND [Value] = 'Quelle1'
)
)
SELECT *
FROM CTE
WHERE [FK_Candidate] IN ( SELECT [FK_Candidate]
FROM CTE
WHERE [Key] = 'Profession'
AND [Value] = 'Informatiker'
INTERSECT
SELECT [FK_Candidate]
FROM CTE
WHERE [Key] = 'Source'
AND [Value] = 'Quelle1' ) ;

Using a CTE for filtering has the better execution plan.


mfG
--> stefan <--
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to narrow the selection
Next: Sql 2008 - Failover & Domain