CODEWITHSUNDEEP
mysqlsqlselect
iJade
iJade

Reputation: 23811

Any way to do such a select

Ok so here is my mysql Query

SELECT GROUP_CONCAT(CallCenterID) FROM tbl_mytable WHERE USER=1;

So this query gives me all those queries where user=1.Is there any way to get all those CallCenterID which have user!=1 and also user=1 both using a single select query.

what i want is some thing like GroupConcat(CallCenterIds_not_in_user=1),GroupConcat(CallCenterIds_in_user=1)

Upvotes: 0

Views: 83

Answers (4)

spencer7593
spencer7593

Reputation: 108460

Yes, there is a way to do such a select. The actual query really depends on the resultset you want returned. Do you want one row or two separate rows? Should CallCenterId ever be duplicated within a list? Do you want CallCenterId excluded from the list for user!=1 if it appears in the list for user=1? All of those are possible.

SQL Fiddle Here

Test case:

CREATE TABLE tbl_mytable( `user` INT UNSIGNED, CallCenterId VARCHAR(2));
INSERT INTO tbl_mytable 
 VALUES (1,'a'),(1,'b'),(2,'a'),(2,'c'),(2,'a'),(2,'d'),(NULL,'x');

One possible interpretation of the specification, is that you want a list of all CallCenterId for user=1, and you want another list of ALL CallCenterId that appear in the table but which do not appear the list for user=1. Basically, you want a CallCenterId to appear in one list or the other but NOT both. In the test case, this would mean you would want to return something like this:

user=1  NOT user=1  
------  ----------
a,b     c,d,x

That resultset can be returned by a query like this:

SELECT GROUP_CONCAT(IF(user_one,t.CallCenterId,NULL) 
       ORDER BY t.CallCenterId
       ) AS `user=1`
     , GROUP_CONCAT(IF(user_one,NULL,t.CallCenterId)
       ORDER BY t.CallCenterId
       ) AS `NOT user=1`
  FROM ( 
         SELECT u.CallCenterId
              , MAX(IF(u.user=1,1,0)) AS user_one
           FROM tbl_mytable u
          GROUP BY u.CallCenterId
       ) t

Another possible interpretation is that you want the two CallCenterID lists returned on the same row, with one list containing the CallCenterId for user=1, and the other list containing all CallCenterId for all users OTHER than user=1, meaning, the same CallCenterId can appear in both lists. In that case, something like this will work:

SELECT GROUP_CONCAT(DISTINCT IF(t.`user`=1,t.CallCenterID,NULL)
       ORDER BY t.CallCenterId
       ) AS `user=1`
     , GROUP_CONCAT(DISTINCT IF(t.`user`!=1,t.CallCenterID,NULL)
       ORDER BY t.CallCenterId
       ) AS `user!=1`
  FROM tbl_mytable t

returns:

user=1  user!=1  
------  ---------
a,b     a,c,d

It's also possible to return those lists as two separate rows, if that works better for you.

SELECT IF(t.`user`=1,'user=1','user!=1') AS `which_users`
     , GROUP_CONCAT(t.CallCenterID ORDER BY t.CallCenterId) AS `call_centers`
  FROM tbl_mytable t
 GROUP BY which_users
 ORDER BY which_users DESC

(NOTE: the call_centers list for 'user!=1' will also include values for rows where the user column is NULL; those rows can be excluded with a slight tweak.)

which_users  call_centers  
-----------  --------------
user=1       a,b           
user!=1      a,a,c,d,x

A slightly different query will eliminate duplicates in the list (using the DISTINCT keyword), and eliminating any rows where the user column IS NULL (adding a WHERE clause):

SELECT IF(t.`user`=1,'user=1','user!=1') AS `which_users`
     , GROUP_CONCAT(t.CallCenterID ORDER BY t.CallCenterId) AS `call_centers`
  FROM tbl_mytable t
 WHERE t.user IS NOT NULL 
 GROUP BY which_users
 ORDER BY which_users DESC

returns:

which_users  call_centers
-----------  ------------
user=1       a,b
user!=1      a,c,d

If none of these are what you are looking for, you'll need to specify more clearly what resultset you want returned.

Upvotes: 1

steffen
steffen

Reputation: 17028

Try this: 

 SELECT USER=1, GROUP_CONCAT(CallCenterID) FROM tbl_mytable GROUP BY USER=1

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

Hello try this out? Are you referring to something like this? You may replace name with callerid and someid with userid. The userid I have used is just the unique key for the table, it doesn't refer to the userid as per yours :)

REference SQLFIDDLE

Query

select group_concat(name)
from mytable
where someid = 1
group by someid
;

Results:

GROUP_CONCAT(NAME)
tim,ron,kate,pete

Upvotes: 0

George
George

Reputation: 36794

Im thinking you want all of them, so no condition:

SELECT USER, GROUP_CONCAT(CallCenterID) FROM tbl_mytable ORDER BY USER

This means that it won't even check the user column. The ORDER BY will group the records with similar USER values together.

Upvotes: 0

Related Questions