[mySQL] resultaten adhv specifieke foreign key relaties

[bRight]

Hallo,

Ik heb een query waar ik helemaal niet uit kom.
Ik heb deze tabellen (versimpeld):

code:

content
contentId   | title
====================
1           | foo
2           | bar
3           | foobar
4           | barfoo

code:

content_property
fk_contentId | fk_propertyId
============================
1            | 1
1            | 2
1            | 3
2            | 1
3            | 2
4            | 3

Uit tabel "content" wil ik de records hebben die een foreign key relatie hebben met tabel "content_property" en waarvan het totaal aan relaties voldoet aan deze conditie:

(fk_propertyId = 1 AND fk_propertyId = 2) OR (fk_propertyId = 3)

De bedoeling is dat hiermee dus alleen de records met contentId 1 en 4 worden teruggegeven.

Ik heb even geen idee hoe ik dit moet aanpakken.
Kan iemand me op weg helpen?

[Otherside1982]

Dan zal je gebruik moeten maken van verschillende exists subqueries.
Zoiets als dit:

SQL:

select *
from content
where (exists(een content_property record voor contentId met propertyId = 1)
       and 
       exists(een content_property record voor contentId met propertyId = 2)
      )
      or
      (exists(een content_property record voor contentId met propertyId = 3)
       )

[P.O. Box]

volgens mij kun je dat op meerdere manieren wel voor elkaar krijgen...

ik zou waarschijnlijk zoiets doen:

pseudo-achtig:

SQL:

select 
fk_contentid
from content_property
where (sum(fk_propertyid = 1) = 1 AND  sum(fk_propertyid = 2) = 1) OR  sum(fk_propertyid = 3) = 1
group by 
fk_contentid

[ Voor 3% gewijzigd door P.O. Box op 21-11-2012 09:38 ]

[edeboeck]

Is het niet eenvoudig iets als dit:

SQL:

SELECT DISTINCT
    C.*
FROM
    Content AS C INNER JOIN Content_Property AS CP ON C.contentId = fk_contentId
WHERE
    (fk_propertyId = 1 AND fk_propertyId = 2) OR (fk_propertyId = 3)

Moeilijker moeten we het toch echt niet maken lijkt me, of vergis ik me?

Otherside1982 schreef op woensdag 21 november 2012 @ 08:29:
Dan zal je gebruik moeten maken van verschillende exists subqueries.
Zoiets als dit:

SQL:

select *
from content
where (exists(een content_property record voor contentId met propertyId = 1)
       and 
       exists(een content_property record voor contentId met propertyId = 2)
      )
      or
      (exists(een content_property record voor contentId met propertyId = 3)
       )

Subqueries lijken me dus niet nodig (omdat je die tabellen in de FROM al kan samenbrengen)

P.O. Box schreef op woensdag 21 november 2012 @ 09:36:
volgens mij kun je dat op meerdere manieren wel voor elkaar krijgen...

ik zou waarschijnlijk zoiets doen:

pseudo-achtig:

SQL:

select 
fk_contentid
from content_property
where (sum(fk_propertyid = 1) = 1 AND  sum(fk_propertyid = 2) = 1) OR  sum(fk_propertyid = 3) = 1
group by 
fk_contentid

Waarom die je die sum? Ik weet dat het pseudo-code is, maar waarom zou je die sum doen op de fk?

[ Voor 72% gewijzigd door edeboeck op 21-11-2012 09:46 . Reden: reactie op Otherside1982 en P.O.Box toegevoegd ]

[Otherside1982]

edeboeck schreef op woensdag 21 november 2012 @ 09:42:
Is het niet eenvoudig iets als dit:

SQL:

SELECT DISTINCT
    C.*
FROM
    Content AS C INNER JOIN Content_Property AS CP ON C.contentId = fk_contentId
WHERE
    (fk_propertyId = 1 AND fk_propertyId = 2) OR (fk_propertyId = 3)

Moeilijker moeten we het toch echt niet maken lijkt me, of vergis ik me?


[...]
Waarom die je die sum? Ik weet dat het pseudo-code is, maar waarom zou je die sum doen op de fk?

Dat werkt niet, want de voorwaarde (fk_propertyID = 1 and fk_propertyId = 2) kan nooit gelden voor 1 record.
Zie ook http://sqlfiddle.com/#!9/87676/3 om één en ander te proberen

[edeboeck]

Otherside1982 schreef op woensdag 21 november 2012 @ 09:48:
[...]

Dat werkt niet, want er de voorwaarde (fk_propertyID = 1 and fk_propertyId = 2) kan nooit gelden voor 1 record.
Zie ook http://sqlfiddle.com/#!9/87676/3 om één en ander te proberen

Inderdaad, mijn fout... slecht gelezen : ik dacht aan (fk_contentID = 1 and fk_propertyId = 2)

@TS: Bedoel je dat je enkel de (content)records met fk_propertyID = 1 wilt hebben als voor datzelfde contentrecord ook fk_propertyID = 2 bestaat? In dat geval lijkt de code van Otherside1982 me inderdaad de oplossing te geven.

[ Voor 6% gewijzigd door edeboeck op 21-11-2012 09:59 . Reden: verwijzing naar oplossing Otherside1982 ]

[P.O. Box]

edeboeck schreef op woensdag 21 november 2012 @ 09:42:

[...]
Waarom die je die sum? Ik weet dat het pseudo-code is, maar waarom zou je die sum doen op de fk?

met de sum tel je het aantal keer dat fk = x voorkomt... de sum is ook niet op de fk, maar op fk = x

[ Voor 7% gewijzigd door P.O. Box op 21-11-2012 10:03 ]

[Otherside1982]

P.O. Box schreef op woensdag 21 november 2012 @ 10:02:
[...]


met de sum tel je het aantal keer dat fk = x voorkomt... de sum is ook niet op de fk, maar op fk = x

Leuk idee, maar bij welke databank kan je een sum() in een where clause gebruiken? Ik slaag er niet in om dat werkend te krijgen in MySQL, SQL server, PostgreSQL of Oracle. Zie http://sqlfiddle.com/#!2/87676/17
Bij sommige databanken lukt een sum van een boolean niet en moet de boolean naar int gecast worden trouwens.

[P.O. Box]

Otherside1982 schreef op woensdag 21 november 2012 @ 10:20:
[...]

Leuk idee, maar bij welke databank kan je een sum() in een where clause gebruiken? Ik slaag er niet in om dat werkend te krijgen in MySQL, SQL server, PostgreSQL of Oracle. Zie http://sqlfiddle.com/#!2/87676/17
Bij sommige databanken lukt een sum van een boolean niet en moet de boolean naar int gecast worden trouwens.

ok, das waar...

dan gebruik je HAVING ipv WHERE...

over de casting: dat klopt, maar in MySQL kan het wel

[Otherside1982]

P.O. Box schreef op woensdag 21 november 2012 @ 10:45:
[...]


ok, das waar...

dan gebruik je HAVING ipv WHERE...

over de casting: dat klopt, maar in MySQL kan het wel

Oh ja, HAVING ipv WHERE juist ja. Die vergeet ik wel eens want die gebruik ik zelden. Jouw oplossing is dan een stuk korter.

Voor de 2 oplossingen zie http://sqlfiddle.com/#!2/87676/21

[bRight]

Dank voor jullie reacties!
Ik wist niet dat een EXISTS constructie op deze manier mogelijk was en de SUM oplossing is slim gevonden

De query om direct de records uit content op te halen met SUM wordt dan dus:

code:

SELECT c.* 
FROM content_property p
INNER JOIN content c ON c.contentId = p.fk_contentId
GROUP BY p.fk_contentId
HAVING SUM(p.fk_propertyId = 1) = 1 AND SUM(p.fk_propertyId = 2) = 1 or SUM(p.fk_propertyId = 3) = 1

In werkelijkheid gaat het om een grote dataset.
Ik ga even benchmarken welke oplossing het snelst is!