How to compare comma separated ids on left join

joining a table based on comma separated values in sql server
query for comma-separated ids to comma-separated values
mysql compare two comma separated values
inner join comma separated
comma separated values in mysql
comma separated tables in sql
mysql split string by comma
mysql list comma separated

I want to get the count of cases which are there for other tale in a format of comma separated

I have a table like below

Table1

id        name
1         a
2         b
3         c
4         d
5         e
6         f

Table2

id       table1_ids   user_id
1        1,2,3,4,5    1
2        1,2,3        2
3        1,2,3,4,5    1
4        1,2,3,4      2

When i join them, i want to display the count of table_ids in table2 like below

 Expected:        a-4  b-4  c-4  d-3  e-5  f-0
 Getting output:  a-4  b-4  c-4

I have tried query like below using laravel raw query

DB::select('select t1.name, t1.id, count(t2.id) as count
    from table1 as t1
    left join table2 as t2 on FIND_IN_SET(t1.id, t2.table1_ids)>0
    where t2.user_id in ('1,2')
    group By t1.name, t1.id');

Please suggest me how can i acheive this


table2 is outer joined but the condition table2.user_id IN (...) inside the where clause changes the query to an inner join. Move the condition from WHERE to ON clause:

select t1.name, t1.id, count(t2.id) as count
from table1 as t1
left join table2 as t2 on
  find_in_set(t1.id, t2.table1_ids) > 0 and
  t2.user_id in (1, 2)
group by t1.name, t1.id

SQL Fiddle

PS: WHERE 1 IN ('1,2') attempts to convert '1,2' to a number and thus matches 1.

How to compare comma separated ids on left join, table2 is outer joined but the condition table2.user_id IN () inside the where clause changes the query to an inner join. Move the condition  Here's a kind of fun way to do it: WITH cteId_counts_by_project AS (SELECT PROJECT, NVL(REGEXP_COUNT(EMPLOYE_ID, '[^,]'), 0) AS ID_COUNT FROM PROJECTS), cteMax_id_count AS (SELECT MAX(ID_COUNT) AS MAX_ID_COUNT FROM cteId_counts_by_project), cteProject_employee_ids AS (SELECT PROJECT, EMPLOYE_ID, REGEXP_SUBSTR(EMPLOYE_ID, '[^,]',1, LEVEL) AS ID FROM PROJECTS CROSS JOIN cteMax_id_count m CONNECT


I am probably going to hate myself for this but this could work:

select
    t1.name,
    t1.id,
    count(t2.id) as count
from
    table1 as t1
left join
    table2 as t2 on
        (
            -- We need to account for all of the variations of finding t1.id in the comma-separated field
            t2.table1_ids = t1.id or -- exactly this ID
            t2.table1_ids LIKE concat( t1.id, ',%' ) or -- starts with this ID
            t2.table1_ids LIKE concat( '%,', t1.id ) or -- ends with this ID
            t2.table1_ids LIKE concat( '%,', t1.id, ',%' ) -- the ID is found between two commas
        )
where
    t2.user_id in (1,2)
group By
    t1.name, t1.id

How to join two tables using a comma-separated-list in the join field , Because it is a PITA to extract those comma separated values out agian in SQL. ifnull(c2.name,'')) as categories FROM movies m LEFT JOIN  Note the "," at beginning and end in Ids, you can convert it into procedure and use comma separated string as parameter starting with comma as shown in example. Other way is to use the dynamic query, convert entire query into string and finally use Exec (@string) to execute the query see this (no need to keep comma at begginig and end in ids)


Like a commenter suggested, should avoid adding comma separated data in table2 as it's bad practice.

However, that being said, you can use Laravel's Query Builder to build up your query to be more readable and cleaner. Building upon Salman A's point on changing the WHERE to ON you can do it like this:

DB::table("table1 as t1")
    ->leftJoin("table2 as t2", function($join) {
        $join->on(\DB::raw("find_in_set(t1.id, t2.table1_ids) > 0 and t2.user_id in (1, 2)"), \DB::raw(""), \DB::raw(""));
    })
    ->select("t1.name", "t1.id", \DB::raw("count(t2.id) as count"))
    ->groupBy("t1.name", "t1.id")
    ->get();

MySQL join by field with comma seperated list - Databases, yes, you can do the join on a comma-delimited column, but it will always require a jobs.id LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id  The method you're looking for is String.join(Iterable object, String separator). You won't be able to do what you want directly from your List<Customer__c>.Instead, you put your query directly into a for loop, and build the two lists (to be turned into comma-separated strings) at the same time.


MySQL joining two tables when ID from one table is in comma string , MySQL joining two tables when ID from one table is in comma string in other table styles (ID's) are stored in a TEXT file as a comma separated list (e.g. 1,2,3​). product_id 1, the styles field is “1, 3” instead of “1,3”, then 1 will match but not 3. this query should just list the styles left in the remaining products, for example. It is best when you want to find a value within a column that contains comma-separated values and It returns the position of search value in the list of values otherwise 0. IN It is used when to find value in the list of values and It returns 1 when it finds search value in the list of values otherwise 0 .


How to compare comma separated ids on left join – GiveMeAns , I want to get the count of cases which are there for other tale in a format of comma separated I have a table like below Table1 id name 1 a 2 b 3  In most cases it is better to use a separate lookup table or a column for each field instead of storing data as comma-separated values for later lookup. However, there could be rare cases where the trick above could be useful.


Join with comma separated values in SQL Server, You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. SQL SERVER – Create Comma Separated List From Table – I was preparing a statistical report and was stuck in one place where I needed to convert certain rows to comma separated values and put into a single row.