left outer join query

[Fiqhi]

$sql='select a.id, a.first_name, a.last_name, a.image, b.sh_id, b.uid, b.parent_id, b.sh_det, b.attach_url, b.attach_image, b.attach_title, b.attach_description, b.date, c.ltotal, d.ptotal from share b inner join user a on b.uid=a.id left outer join (select count(sid) as ltotal, sid from share_like group by sid) c on b.sh_id=c.sid left outer join (select count(sh_id) as ptotal, parent_id from share group by parent_id ) d on b.sh_id=d.parent_id  where b.uid in ('.$all_id.') and b.parent_id=0 '.$sqladd.' order by b.date desc';

this query retrieve information about user from multiple table, but it's excluded user from different group. i want to include all users from different group too. i tried to change left outer join into join but i got syntax error

Edited February 28, 2014 by Fiqhi

[jazzman1]

and b.parent_id=0 '.$sqladd.' //error

[Fiqhi]

and b.parent_id=0 '.$sqladd.' //error

i didn't get it...

i think inner join user a on b.uid=a.id filtering my user list. how to remove it so i can include all user without filtered

[jazzman1]

What do you want to achieve? To concatenate a php variable to an sql string?

 

See what would happen if I set two randomly php variables to your sql query.

<?php

$all_id = 10; 

$sqladd = "abc";

$sql = "select 
    a.id,
    a.first_name,
    a.last_name,
    a.image,
    b.sh_id,
    b.uid,
    b.parent_id,
    b.sh_det,
    b.attach_url,
    b.attach_image,
    b.attach_title,
    b.attach_description,
    b.date,
    c.ltotal,
    d.ptotal
from
    share b
        inner join
    user a ON b.uid = a.id
        left outer join
    (select 
        count(sid) as ltotal, sid
    from
        share_like
    group by sid) c ON b.sh_id = c.sid
        left outer join
    (select 
        count(sh_id) as ptotal, parent_id
    from
        share
    group by parent_id) d ON b.sh_id = d.parent_id
where
    b.uid in ('.$all_id.') and b.parent_id = 0 '.$sqladd.'
order by b.date desc
";

echo $sql;

Result:

select 
    a.id,
    a.first_name,
    a.last_name,
    a.image,
    b.sh_id,
    b.uid,
    b.parent_id,
    b.sh_det,
    b.attach_url,
    b.attach_image,
    b.attach_title,
    b.attach_description,
    b.date,
    c.ltotal,
    d.ptotal
from
    share b
        inner join
    user a ON b.uid = a.id
        left outer join
    (select 
        count(sid) as ltotal, sid
    from
        share_like
    group by sid) c ON b.sh_id = c.sid
        left outer join
    (select 
        count(sh_id) as ptotal, parent_id
    from
        share
    group by parent_id) d ON b.sh_id = d.parent_id
where
    b.uid in ('.10.') and b.parent_id = 0 'abc'
order by b.date desc 

I see two errors here - b.uid in ('.10.') and b.parent_id = 0 'abc'

 

For a list of tables in your current schema, use the DESCRIBE command, which provides a decription of the specified table, if you want to help you effectively.

Edited March 1, 2014 by jazzman1

[Fiqhi]

the current code

$sqladd=" and b.sh_id='".$cid."'";

$friend=$rwiclas->friends($iduser);

$groups=$rwiclas->groups($iduser);

$companies=$rwiclas->companies($iduser);

$all_id=$iduser;

if($friend!='') $all_id=$all_id.",".$friend;

if($groups!='') $all_id=$all_id.",".$groups;

if($companies!='') $all_id=$all_id.",".$companies;

$sql='select a.id, a.first_name, a.last_name, a.image, b.sh_id, b.uid, b.parent_id, b.sh_det, b.attach_url, b.attach_image, b.attach_title, b.attach_description, b.date, c.ltotal, d.ptotal from share b inner join user a on b.uid=a.id left outer join (select count(sid) as ltotal, sid from share_like group by sid) c on b.sh_id=c.sid left outer join (select count(sh_id) as ptotal, parent_id from share group by parent_id ) d on b.sh_id=d.parent_id  where b.uid in ('.$all_id.') and b.parent_id=0 '.$sqladd.' order by b.date desc';

is only shows result to friend user. The current query is work correctly

for example user 1 and user 2 = friend / group. user 1 and user 3 = aren't friend

if user 1 post something. only user 2 can see the post. user 3 cant see it.

 

 

i want all users sees it

i think it's the inner join who filtered user 3. if i change it to full join i got syntax error.

Edited March 1, 2014 by Fiqhi

[jazzman1]

 

i want all users sees it

I want to run the DESCRIBE command to list all involved tables in this query first. second, try to provide some sample of data and third, describe what result you're expecting to get. 

Edited March 1, 2014 by jazzman1