Prev: How to narrow the selection
Next: Sql 2008 - Failover & Domain
From: Frank Uray on 21 Dec 2009 10:35
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: Stefan Hoffmann on 21 Dec 2009 10:44
hi Frank,

On 21.12.2009 16:35, Frank Uray wrote:
> I need to get all candidates with
> Profession = Informatiker AND Source = Quelle1
>
> The result should look like this:
> 1 'Profession' 'Informatiker'
> 1 'Source' 'Quelle1'
E.g.:

SELECT *
FROM #test
WHERE [FK_Candidate] IN
(
SELECT [FK_Candidate]
FROM #test
WHERE [Key] = 'Profession' AND [Value] = 'Informatiker'
INTERSECT
SELECT [FK_Candidate]
FROM #test
WHERE [Key] = 'Source' AND [Value] = 'Quelle1'

)



mfG
--> stefan <--
From: Plamen Ratchev on 21 Dec 2009 13:50
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 14:01
Hi Stefan

Thank you for your answer.

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 ?

Thanks and best regards
Frank Uray

"Stefan Hoffmann" wrote:

> hi Frank,
>
> On 21.12.2009 16:35, Frank Uray wrote:
> > I need to get all candidates with
> > Profession = Informatiker AND Source = Quelle1
> >
> > The result should look like this:
> > 1 'Profession' 'Informatiker'
> > 1 'Source' 'Quelle1'
> E.g.:
>
> SELECT *
> FROM #test
> WHERE [FK_Candidate] IN
> (
> SELECT [FK_Candidate]
> FROM #test
> WHERE [Key] = 'Profession' AND [Value] = 'Informatiker'
> INTERSECT
> SELECT [FK_Candidate]
> FROM #test
> WHERE [Key] = 'Source' AND [Value] = 'Quelle1'
>
> )
>
>
>
> mfG
> --> stefan <--
> .
>
From: Gert-Jan Strik on 21 Dec 2009 15:34
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'
 |  Next  |  Last
Pages: 1 2 3
Prev: How to narrow the selection
Next: Sql 2008 - Failover & Domain