MySQL – Get Relation Data on Comma Separated Values

MySQL

I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table.

I have one table "Form table"

+------+-----------------+
| id   | Fname           |
+------+-----------------+
| 1    | f_934_tarun1    |
| 2    | f_934_tarun2    |
| 3    | f_934_a1        |
+------+-----------------+

2nd table "user assigned form table"

+------+--------------------------------+
| u_id | Fname                          |
+------+--------------------------------+
| 1    | f_934_xyz,f_934_tarun1         |
| 2    | f_934_tarun2,f_934_fhh         |
| 3    | f_934_ttt,f_934_nhj            |
| 4    | f_934_a1,f_934_t5trt           |
+------+--------------------------------+

I want to get all user ids who has any of form listed in form table. So here the output will be:

+------+
| u_id |
+------+
| 1    |
| 2    |
| 4    |
+------+

Every help will be appreciated.

Best Answer

You can use MySQL INSTR to achieve this:

SELECT U.u_id
FROM Userassigned U
JOIN FormTable F ON INSTR(U.Fname, F.Fname) > 0;

SQL Fiddle for the same: http://sqlfiddle.com/#!9/6f27ce/1

Related Solutions

MySQL Performance – Using Triggers or Transactions

Without a doubt, I would rule out triggers and strictly stay with transactions.

Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.

Combine this with the fact that proper data validation protocols are not implemented in MySQL's Stored Procedure Language. Business Intelligence is OK to have contained in a database provided the Stored Procedure Language can handle a transactional environment. As a MySQL DBA, I have to honestly say that such is not the case with MySQL. Oracle (PL/SQL), PostgreSQL (PL/pgSQL), and SQL Server (T-SQL) have this edge over MySQL.

Concerning transactions, MySQL has InnoDB as its main ACID-compliant storage engine (Deafult storage engine in MySQL 5.5). It has excellent crash recovery and obeys the ACID compliance protocols.

I would choose transacitons over triggers every single time.

Mysql – How to write this self join based on three columns

The problem with your provided query is that you're doing an INNER JOIN. Because there are no rows with a parentid of 5, the subquery would return NULL on those rows and be excluded.

What you want is a LEFT JOIN, something like this (sqlfiddle):

SELECT t.*, parentchildrelation.parentid, childs
FROM tbl_info t
LEFT JOIN 
  (SELECT 
    parentid,
    COUNT(DISTINCT id) AS childs 
  FROM
    tbl_info  
  GROUP BY parentid) AS parentchildrelation 
  ON t.id = parentchildrelation.parentid 
WHERE t.no_daughter != parentchildrelation.childs 
   OR (t.no_daughter > 0 AND parentchildrelation.parentid IS NULL)
GROUP BY t.id, t.parentid;

I included more columns, because where id=5 and parentid=2 has two different values for no_daughter. You should be able to adjust those as needed.

Best Answer

Related Solutions

MySQL Performance – Using Triggers or Transactions

Mysql – How to write this self join based on three columns

Related Question