[T-SQL] Query om verschillende resultsets te koppelen*

[Verwijderd]

Ik ben bezig met het schrijven van een sql query (MS SQL Server), maar ik krijg er niet uit wat ik zoek.

De table waar ik de query op uitvoer ziet er als volgt uit:

table: persoon_hobby
persoon_id | hobby
1 | voetbal
2 | kaarten
2 | hardlopen
3 | voetbal
4 | hardlopen

Nu wil ik graag alle persoon_id's selecteren waarbij de hobby zowel kaarten als hardlopen is.

Als ik SELECT * FROM persoon_hobby WHERE hobby = 'kaarten' OR hobby='hardlopen' gebruik, dan krijg ik resultaten waarbij iedereen een van de twee hobbies heeft.
SELECT * FROM persoon_hobby WHERE hobby = 'kaarten' AND hobby='hardlopen' levert geen resultaten op, omdat iedere hobby op een nieuwe row staat.

Weet iemand hoe ik dit slim aan kan pakken? Het moet allemaal in een query gebeuren.

[whoami]

Dit heeft niets met Software Engineering & Architecture te maken, maar is gewoon programmeren.

Een tip: kijk eens naar INTERSECT

-> PRG

[ Voor 4% gewijzigd door whoami op 08-06-2007 10:22 ]

[NetForce1]

Je zou het met een subquery op kunnen lossen, maar dat wordt erg vervelend op het moment dat je meer dan twee hobby's wilt checken.

SQL:

SELECT *
FROM persoon_hobby AS outer
WHERE EXISTS 
  (SELECT persoon_id 
   FROM persoon_hobby AS inner 
   WHERE outer.persoon_id = inner.persoon_id 
   AND hobby = 'kaarten'
  )
AND hobby = 'hardlopen'

[Verwijderd]

Select
outer (persoon_id),
hobby
from persoon_hobby
where
hobby in ('kaarten', 'hardlopen')
order by persoon_id;

dacht ik mij te herinneren dat het zo werkt, ben benieuwd of mijn kennis nog een beetje is blijven hangen
*kuch* distinct moet natuurlijk 'outer' zijn.....
Maar volgens mij hebben de tweakers ná mij gelijk. in MS-SQL is dit lastig
ben benieuwd!

[ Voor 23% gewijzigd door Verwijderd op 08-06-2007 10:32 ]

[Janoz]

Het probleem waar je nu tegenaanloopt komt omdat een query per 'regel' kijkt. Het stukje "hobby = 'kaarten' AND hobby='hardlopen'" betekent dus eigenlijk "Zoek een rij waarbij de hobby kaarten is en de hobby ook nog hardlopen is". Dat kan natuurlijk niet in de zelfde rij. Oplossingen hiervoor zijn bijvoorbeeld zoals NetForce1 aangeeft, of een query met OR waarbij je ook het aantal voorkomens telt mbv HAVING.

@hieronder: Dat is hetzelfde als de in de topicstart genoemd OR query en levert niet het gewenste resultaat op.

[ Voor 12% gewijzigd door Janoz op 08-06-2007 10:30 ]

[Monkeybrains]

Ik weet niet zeker of het MS-SQL kan, maar in Oracle heb je zoiets als:

SELECT * FROM persoon_hobby WHERE hobby IN ('kaarten','hardlopen')

[whoami]

IN is een OR operatie.

[Creepy]

Aangezien IN opgenomen is in de SQL standaard kan dit ook prima in MS-SQL

[Exocet]

Monkeybrains schreef op vrijdag 08 juni 2007 @ 10:28:
Ik weet niet zeker of het MS-SQL kan, maar in Oracle heb je zoiets als:

SELECT * FROM persoon_hobby WHERE hobby IN ('kaarten','hardlopen')

Maar dan krijg je personen met hobby kaarten OF hardlopen. Zoals eerder genoemd moet een INTERSECT werken.

SQL:

SELECT * FROM persoon_hobby WHERE hobby = 'kaarten'
INTERSECT
SELECT * FROM persoon_hobby WHERE hobby = 'hardlopen'

Dan is het resultaat de personen die beide hobby's hebben.

[ Voor 4% gewijzigd door Exocet op 08-06-2007 11:05 ]

[Verwijderd]

whoami schreef op vrijdag 08 juni 2007 @ 10:21:
Dit heeft niets met Software Engineering & Architecture te maken, maar is gewoon programmeren.

Een tip: kijk eens naar INTERSECT

-> PRG

Intersect zit toch alleen in Analysis Services ?

Dit zou ook kunnen, maar dan moet je wel weten hoe hoeveel parameters je hebt

SQL:

select distinct persoon_id, 
count(Hobby) as Aantal  
from dbo.Persoon_hobby 
where hobby =  'kaarten' 
or hobby ='hardlopen' group by persoon_id
having count(Hobby) >= 2 

persoon_id	Aantal
-----------	-----------
2	2
(1 row(s) affected)

[ Voor 4% gewijzigd door Verwijderd op 08-06-2007 10:47 . Reden: aanpassing met having clause ]

[whoami]

Hmm, ik moet dat intersect verhaal nog eens bekijken.

Je kan zowiezo ook met UNION werken

code:

select * from .... where hobby = 'hardlopen'
union all
select * from ... where hobby='kaarten'

[NetForce1]

UNION gaat niet werken, een UNION is ook een OR operatie, books online:

Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union.

INTERSECT lijkt me inderdaad een mooiere oplossing, SQL Server ondersteund het vanaf versie 2005, books online lijkt te suggereren dat het altijd beschikbaar is: http://msdn2.microsoft.co...ary/ms188055(SQL.90).aspx

[whoami]

Idd, UNION plakt de 2 resultsets aan elkaar.

[Verwijderd]

Bij INTERSECT kreeg ik een foutmelding, waarschijnlijk omdat hier SQL Server 2000 draait. Ik heb de optie van Diradical geprobeerd, maar kwam erachter dat sommige personen 2x dezelfde hobbies hadden (dus twee keer dezelfde row), deze werden ook meegeteld in de COUNT). Uiteindelijk heb ik iets gevonden wat lijkt te werken (zie hieronder)

Uiteraard blijven alle tips welkom.

SQL Nested Queries

A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query. Here is a simple example of SQL nested query:

SELECT Model FROM Product
WHERE ManufacturerID IN (SELECT ManufacturerID FROM Manufacturer
WHERE Manufacturer = 'Dell')

The nested query above will select all models from the Product table manufactured by Dell:

Model
Inspiron B120
Inspiron B130
Inspiron E1705

http://www.sql-tutorial.com/sql-nested-queries-sql-tutorial/

[BikkelZ]

Dat wilde ik inderdaad net voorstellen, om IN te gebruiken.

Dingen als INTERSECT worden al gauw lastig als je gaat zeggen 'en nu met drie hobbies'. Overigens heb je wel een wat lastiger probleem als je een willekeurig aantal verplichte hobbies zoekt.

------

Oh dat is ook het geval.

[MySQL] Alleen als alles matcht

Mijn oplossing, ik heb niemand horen roepen dat ze een betere oplossing hadden....

[ Voor 88% gewijzigd door BikkelZ op 08-06-2007 15:06 ]

[drm]

Wat niemand opvalt is dat er geen foreign key gebruikt wordt voor de hobbies; dat vind ik wel apart, maar goed...

Ik zou kiezen voor een query waarbij het het aantal hobbies op basis van de voorwaarde dat de hobby "a" of "b" is (oftewel IN('a','b') is) gelijk is aan het aantal hobbies dat je wilde weten. Vergelijkbaar met wat Janoz zegt dus.

Dat gaat natuurlijk alleen op als de kolom voor de hobby's dan ook een unique constraint heeft, maar dat is logisch

[BikkelZ]

Geef eens een voorbeeld?

[drm]

BikkelZ:
Geef eens een voorbeeld?

is dat erg nodig? 't Is beter als je 't zelf uitvogelt namelijk.

[Dido]

Waarom niet simpelweg de table op zichzelf joinen

SQL:

select a.id
from table a
join table b on a.id=b.id and b.hobby="kaarten"
where a.hobby="hardlopen"

Waarbij vermeld zij dat ik een unieke key mis op deze table, maar dat mag in dit geval de pret niet drukken (ik ben voorstander van technische sleutels, maar dat is hier irrelevant)

[drm]

Waarom niet simpelweg de table op zichzelf joinen

't Bezwaar daartegen is dat je zoveel moet joinen als het aantal hobby's dat je wilt weten. Da's meestal niet zo'n goed teken voor de schaalbaarheid van e.e.a.

[Dido]

Datzelfde probleem heb je met de intersect ook, natuurlijk. Maar het is inderdaad een goed argument.

Een combinatie van een group by en een having count is makkelijker schaalbaar, maar dan moet de combinatie id/hobby wel uniek afgedwongen worden.

SQL:

select id, count(*)
from tabel
where hobby in ("kaarten", "hardlopen")
group by id
having count(*) = 2

die stond hierboven ook al ongeveer, maar een distinct is meestal overbodig, en in combinatie met een group by volslagen onzinnig

[tss68nl]

Als je nou in je 'persoon_hobby' koppeltabel eens bitsgewijs nummers gaat uitdelen....ik bedoel dus, 1,2,4,8,16,32 etc....

Dan kan je in een query groeperen op de persoon, en aan de hand van de sum() op de genoemde kolom bepalen of bepaalde hobbys er in zitten?

[BikkelZ]

Volgens mij zit het de tabelstructuur ongeveer zo in elkaar:

SQL:

CREATE TABLE `hobby` (
  `id` int(11) NOT NULL auto_increment,
  `hobby` varchar(25) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;

INSERT INTO `hobby` VALUES (1, 'kaarten');
INSERT INTO `hobby` VALUES (2, 'hardlopen');
INSERT INTO `hobby` VALUES (3, 'fietsen');


CREATE TABLE `persoon` (
  `id` int(11) NOT NULL auto_increment,
  `naam` varchar(100) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;


INSERT INTO `persoon` VALUES (1, 'Willem');
INSERT INTO `persoon` VALUES (2, 'Janus');
INSERT INTO `persoon` VALUES (3, 'Cor');


CREATE TABLE `persoon_hobby` (
  `persoon_id` int(11) NOT NULL,
  `hobby_id` int(11) NOT NULL,
  PRIMARY KEY  (`persoon_id`,`hobby_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


INSERT INTO `persoon_hobby` VALUES (1, 1);
INSERT INTO `persoon_hobby` VALUES (1, 2);
INSERT INTO `persoon_hobby` VALUES (2, 2);
INSERT INTO `persoon_hobby` VALUES (2, 3);
INSERT INTO `persoon_hobby` VALUES (3, 1);
INSERT INTO `persoon_hobby` VALUES (3, 2);
INSERT INTO `persoon_hobby` VALUES (3, 3);

En dan zou het zo moeten werken:

SQL:

SELECT naam, persoon_id, count( hobby_id )
FROM persoon
JOIN persoon_hobby ON persoon.id = persoon_hobby.persoon_id
WHERE hobby_id
IN (

SELECT id
FROM hobby
WHERE hobby
IN (
"kaarten", "fietsen"
)
)
GROUP BY persoon_id
HAVING count( hobby_id ) =2

[tss68nl]

Grappig, die mensen die weigeren in iets anders te denken dan MySQL

Maar goed, ik denk dat ook de oplossing zoals eerder genoemd met een subselect in het FROM statement moet werken. Dat is zonder de tabelstructuur te veranderen de meest nette optie. Een paar joins is een database niet bang van, en dat is overigens ook wat de engine onder water van een IN(...,...) statement maakt: afzonderlijke lookups.

[P.O. Box]

tss68nl schreef op zaterdag 09 juni 2007 @ 12:28:
Als je nou in je 'persoon_hobby' koppeltabel eens bitsgewijs nummers gaat uitdelen....ik bedoel dus, 1,2,4,8,16,32 etc....

Dan kan je in een query groeperen op de persoon, en aan de hand van de sum() op de genoemde kolom bepalen of bepaalde hobbys er in zitten?

dat werkt weer niet als 1 persoon twee keer met "voetbal" (met bijv. bit 1) in de lijst voorkomt... want de sum is dan 2, en dat betekent niet 2x voetbal... maar bijv. 1x hockey...

[Dido]

Edwardvb schreef op zaterdag 09 juni 2007 @ 15:39:
dat werkt weer niet als 1 persoon twee keer met "voetbal" (met bijv. bit 1) in de lijst voorkomt... want de sum is dan 2, en dat betekent niet 2x voetbal... maar bijv. 1x hockey...

En dus leg je (logischerwijs) een constraint op je tabel (zoals in het model hierboven dus een primary key op die twee velden samen). Dat moet ook als je met een group by en having count werkt.

[P.O. Box]

Dido schreef op zaterdag 09 juni 2007 @ 16:00:
[...]

En dus leg je (logischerwijs) een constraint op je tabel (zoals in het model hierboven dus een primary key op die twee velden samen). Dat moet ook als je met een group by en having count werkt.

inderdaad...

[Verwijderd]

Dit werkt bij mij:

SQL:

SELECT p.persoon_id
FROM persoon_hobby p, persoon_hobby q
WHERE p.persoon_id = q.persoon_id
AND p.hobby = 'hardlopen'
AND q.hobby = 'kaarten';

[Dido]

Of, je de join nou expliciet (zoals ik deed) of impliciet (zoals jij doet) uitvoert, het nadeel dat drm aanhaalt blijft bestaan, natuurlijk