I'm creating a select including several sub-queries but I didn't find a way to use the sub queries values to create the where statement seems to be ignored. I had 1 content Table, 2 join tables with 2 other tables in join and another table in join wjth the first directly.
I tried several solution, this is latest one
select * from (SELECT a.idcontents, a.title, concat(a.filepath, '/', a.filename) as file,
a.captured_on, a.starred, a.file_type,
@PL:=(select GROUP_CONCAT(CONCAT(p.idplaces, '-', p.place) SEPARATOR ', ') from places p where a.idplaces=p.idplaces) as places,
@AL:=(select GROUP_CONCAT(CONCAT(al.idalbum, '-', al.album) SEPARATOR ', ') from album al, join_album_contents am where am.idalbum=al.idalbum and am.idcontents=a.idcontents) as album,
@PE:=(select GROUP_CONCAT(CONCAT(an.idpeople, '-', an.person) SEPARATOR ', ') from people an, join_people_contents ao where ao.idpeople=an.idpeople and ao.idcontents=a.idcontents) as people
FROM contents a) t
WHERE (@PE is null OR @AL is null OR @PL=1)
but I tried this too
SELECT a.idcontents, a.title, concat(a.filepath, '/', a.filename) as file,
a.captured_on, a.starred, CONCAT(p.idplaces, '-', p.place) as places,
(select GROUP_CONCAT(CONCAT(al.idalbum, '-', al.album) SEPARATOR ', ') from album al, join_album_contents am where am.idalbum=al.idalbum and am.idcontents=a.idcontents) as album,
(select GROUP_CONCAT(CONCAT(an.idpeople, '-', an.person) SEPARATOR ', ') from people an, join_people_contents ao where ao.idpeople=an.idpeople and ao.idcontents=a.idcontents) as people
FROM contents a, places p
WHERE a.idplaces=p.idplaces
and (@album IS NULL OR @people IS NULL or p.idplaces=1)
Without success, seems the where clause is ignored always and the result contains records with one or all where clauses inside. Only consolation the records are correctly displayed.
Just to know if there is a solution to resolve this issue.
