SQL query not displaying the desired result

Question

I have this query which works fine. No errors or what soever. But it doesn't seem to work with last part and doesn't populate the last three fields from contact_data cd.

 select c.*,cd.* FROM
 (select * from
 (select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, null as s1, null as s2
 from contact con where id_user=123 and firstname != '' and firstname != ' ' and firstname is not null) as tbl235768 where 1=1 AND v IN
 (select v from
   (select * from
      (select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, null as s1, null as s2
      from contact con where id_user=123 and email = '') as tbl235770 where 1=1) as tblAnd) AND v IN
      (select v from
          (select * from (select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, null as s1, null as s2
          from contact con where id_user=123 and mobile != '' and mobile != ' ' and mobile is not null) as tbl235772 where 1=1) as tblAnd)) as tblBucket
          left join contact c on c.id_contact=v left join
          (select id_contact, group_concat(name) as custom_names,group_concat(value) as custom_values FROM contact_data where
          id_user=20 group by id_contact) as cd on cd.id_contact=c.id_contact group by (c.id_contact)

But, if i run the last part only which is the following

 select id_contact, group_concat(name) as custom_names,group_concat(value) as custom_values 
 FROM contact_data where id_user=798 group by id_contact

It gives me desired result. what is wrong with my query? Any help would be greatly apprecited, Thanks.

Edit : i am editing my question after getting a few answers.

I have removed all the nested parts but still no luck.

select c.*,cd.* FROM (select * from (select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0,  null as s1, null as s2 from contact con where id_user=10879) as tbl235785
where 1=1) as tblBucket  left join contact c on c.id_contact=v left join  (select  id_contact, group_concat(name) as custom_names,group_concat(value) as     custom_values 
FROM contact_data where id_user=798 group by id_contact) as cd on cd.id_contact=c.id_contact group by (c.id_contact)

Check if you are using the correct join functions: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins? — Silvia Doomra, Jul 01 '15 at 17:01
You are using the alias V twice. Plus You select one field, but on the sub query you select *. Those could be cleaned up. — Chuck, Jul 01 '15 at 17:18
I removed all the nested parts but still i am not able to get the last three parts. :( — Shaonline, Jul 01 '15 at 17:38
Could you please post some sample data and desired resultset? — Quassnoi, Jul 01 '15 at 17:48
So what are you really trying to get here, the way the query is written it is very to difficult to even see what you want. — Chuck, Jul 01 '15 at 20:29

score 2 · Answer 1 · answered Jul 01 '15 at 21:00

Here is your sql made simpler:

All of the 1=1 and SELECT * FROM (sub-query) can go away -- they don't do anything. Then the logic becomes clear -- you just need some additional clauses on your where statement instead of the sub-queries. I think when it is cleaned up like this you can see - in the first select you have id_user=123 and in the second you have id_user=20.

Probably want those to be the same? You don't even need the need the where clause in the 2nd joined select. Since it is joined you should just join this field the value in the outer query.

select c.*,cd.* 
FROM (select con.id_contact as v,substr(concat(ifnull(con.firstname,''),ifnull(con.lastname,'')),1,4) as s0, null as s1, null as s2
        from contact con 
        where id_user=123 and 
              firstname != '' and firstname != ' ' and firstname is not null
          and 
            email = ''
              OR
            (
               mobile != '' and mobile != ' ' and mobile is not null
            )  
      ) as tbl235768 
left join contact c on c.id_contact=v 
left join (select id_contact, group_concat(name) as custom_names,group_concat(value) as custom_values
           FROM contact_data 
           where id_user=20 
           group by id_contact
          ) as cd on cd.id_contact=c.id_contact 
group by (c.id_contact)

score 1 · Accepted Answer · answered Jul 01 '15 at 20:31

1

In one place you are matching in id_user=10879 and then in different sub query you are matching on id_user=798, then you join the two and since the id_user fields are both looking at different numbers, you get no matches on your join.

answered Jul 01 '15 at 20:31

Chuck

1,001
1
13
19

If you write your query with just one match on id_user, it will be cleaner, the joins will automatically filter the data. If you want both numbers, maybe you want, id_user in (798, 10879) – Chuck Jul 01 '15 at 20:33
It was the issue with id_user, It was hardcoded and i didn't pay attention. my mistake ofcourse :) but thanks a lot. – Shaonline Jul 02 '15 at 10:27

SQL query not displaying the desired result

2 Answers2