[TYPO3-mvc] JOIN

[bernd wilke]

Am 19.05.15 um 10:06 schrieb Christian Tauscher:
> Hello dear developers.
>
> The fear of me is JOIN. Never understood this really, so i'd like your
> help.
>
> My Example (tt_address->extbaseMapping is assuned to work correctly):
>
> tt_address:
> UID, Name, Group;Group...(groups names are uid, just for better reading)
> 3, Company1, Farbe;deutschland
> 5, Company2, deutschland;Maschine
>
> I'd like to write a filter, AND connected:
> Give me all addresses whit group "deutschland"
> -> 3,5
> Give me all addresses whit group "deutschland" AND "Farbe"
> -> 3
>
>
> SELECT DISTINCT uid, company FROM tt_address INNER JOIN
> tt_address_group_mm ON tt_address.uid = tt_address_group_mm.uid_local
> WHERE tt_address_group_mm.uid_foreign IN('deutschland','farbe')
>
> -> 3,5
> But I expect only id3

you get all records having a relation to 'deutschland' or 'farbe'

>
> Try this:
> WHERE
> tt_address_group_mm.uid_foreign = 'farbe' AND
> tt_address_group_mm.uid_foreign = 'deutschland'
>
> -> NULL
> I still expect id3...???

you have only one mm_record in your join, this record can not point to 
'farbe' and to 'deutschland' at the same time

so you need a double join:

SELECT DISTINCT uid, company FROM tt_address
INNER JOIN tt_address_group_mm as mm1 ON tt_address.uid = mm1.uid_local
          , tt_address_group_mm as mm2 ON tt_address.uid = mm2.uid_local
WHERE mm1.uid_foreign == 'deutschland'
   AND mm2.uid_foreign == 'farbe'



bernd
-- 
http://www.pi-phi.de/cheatsheet.html