why "where id in" does not work here?

[Rommeo]

Hello,

 

I m a beginner level about writing mysql queries. Shortly and simply my tables are as follows;

userTable
userid-specs
1-1,2,3
2-10,11

specsTable
specid-label
1-football
2-basketball
3-tennis

and my query is as follows;

SELECT
*
FROM
userTable ut, specsTable st
WHERE
ut.userid = 1 AND
st.specid IN (ut.specs)

and it gives me the wrong result like;

userid_specs_specid_label
1_1,2,3_1_football

where i need an output like this;

userid_specs_specid_label
1_1,2,3_1_football
1_1,2,3_2_basketball
1_1,2,3_2_tennis

actually output like this would be better (if it can be done) (At the end I m gonna print it like this)

[userid:1] can play football,basketball,tennis

So my question is, what is it i m missing and it gives me the wrong result?

 

Thank you in advance for your help.

[Barand]

IN () takes a series of values separated by commas. Your specs field is a single string value which happens to contain commas.

 

Those "specs" should be in a separate table with each value in a separate record with the owning userid

userid | spec
-------+--------
  1    |   1
  1    |   2
  1    |   3
  2    |   10
  2    |   11

[Rommeo]

 

IN () takes a series of values separated by commas. Your specs field is a single string value which happens to contain commas.

 

Those "specs" should be in a separate table with each value in a separate record with the owning userid

userid | spec
-------+--------
  1    |   1
  1    |   2
  1    |   3
  2    |   10
  2    |   11

Instead of creating a seperate table, is there any mysql-side solution? Like an explode function or something like that?

[Barand]

Why don't you just correctly normalize the data and use SQL as it was designed to be used? Either that or stick with spreadsheets or text files.

[Rommeo]

Why don't you just correctly normalize the data and use SQL as it was designed to be used? Either that or stick with spreadsheets or text files.

That's what i m planning to do for long term, but for now I need to solve this by sql codes (if possible) (or by php which i don't prefer) since I have very limited time now and normalizing will take time.

[Barand]

Because you didn't normalize you are running into problems like this, and that is a situation that won't go away. If only you had spent the time more wisely at an earlier stage.

 

It shouldn't take you long to explode the values in PHP then write out to a normalized version of the table. You could have done that while you were posting here.

 

EDIT:

$sql = "SELECT userid, specs
        FROM userTable";
$res = $pdo->query($sql);

$stmt = $pdo->prepare("INSERT INTO user_specs
                       (userid, specid)
                       VALUES (? ?)";

foreach ($res as $rec) {
   $specs = explode(',', $rec['specs]);
   foreach ($specs as $sp) {
       $stmt->execute( [ $res['userid'], $sp ] );
   }
}

Edited February 12, 2018 by Barand

[Rommeo]

Because you didn't normalize you are running into problems like this, and that is a situation that won't go away. If only you had spent the time more wisely at an earlier stage.

 

It shouldn't take you long to explode the values in PHP then write out to a normalized version of the table. You could have done that while you were posting here.

Well Barand thank you for your suggestions but I need to know if it can be done by sql for now. It's not that easy as you say, it will take time since the tables are in use by other scripts and applications created by other developers. So as you can guess any update in the structure can cause problems for other applications to work.

[Barand]

mysql> SELECT * FROM user;
+--------+-------+
| userid | name  |
+--------+-------+
|      1 | Peter |
|      2 | Paul  |
+--------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM spec;
+--------+------------+
| specid | label      |
+--------+------------+
|      1 | Football   |
|      2 | Basketball |
|      3 | Tennis     |
+--------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM user_spec;
+--------+--------+
| userid | specid |
+--------+--------+
|      1 |      1 |
|      1 |      2 |
|      1 |      3 |
+--------+--------+
3 rows in set (0.00 sec)

mysql> SELECT u.userid
    -> , u.name
    -> , us.specid
    -> , s.label
    -> FROM user u
    -> JOIN user_spec us USING (userid)
    -> JOIN spec s USING (specid);
+--------+-------+--------+------------+
| userid | name  | specid | label      |
+--------+-------+--------+------------+
|      1 | Peter |      1 | Football   |
|      1 | Peter |      2 | Basketball |
|      1 | Peter |      3 | Tennis     |
+--------+-------+--------+------------+
3 rows in set (0.03 sec)

This is the normalized solution^^^.

 

I'll see what I can come up with for your current design disaster.

[Barand]

Found something that should help

mysql> SELECT * FROM usertable;
+--------+-------+
| userid | specs |
+--------+-------+
|      1 | 1,2,3 |
|      2 | 10,11 |
+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM spec;
+--------+------------+
| specid | label      |
+--------+------------+
|      1 | Football   |
|      2 | Basketball |
|      3 | Tennis     |
+--------+------------+
3 rows in set (0.02 sec)



mysql> SELECT ut.userid
    ->      , s.specid
    ->      , s.label
    -> FROM usertable ut
    ->      JOIN spec s ON FIND_IN_SET(s.specid, ut.specs);
+--------+--------+------------+
| userid | specid | label      |
+--------+--------+------------+
|      1 |      1 | Football   |
|      1 |      2 | Basketball |
|      1 |      3 | Tennis     |
+--------+--------+------------+
3 rows in set (0.00 sec)

[Rommeo]

 

Found something that should help

mysql> SELECT * FROM usertable;
+--------+-------+
| userid | specs |
+--------+-------+
|      1 | 1,2,3 |
|      2 | 10,11 |
+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM spec;
+--------+------------+
| specid | label      |
+--------+------------+
|      1 | Football   |
|      2 | Basketball |
|      3 | Tennis     |
+--------+------------+
3 rows in set (0.02 sec)



mysql> SELECT ut.userid
    ->      , s.specid
    ->      , s.label
    -> FROM usertable ut
    ->      JOIN spec s ON FIND_IN_SET(s.specid, ut.specs);
+--------+--------+------------+
| userid | specid | label      |
+--------+--------+------------+
|      1 |      1 | Football   |
|      1 |      2 | Basketball |
|      1 |      3 | Tennis     |
+--------+--------+------------+
3 rows in set (0.00 sec)

Thank you so much Barand! I really appreciate it, I was about to start solving it by php and you saved my life. I'll definitely normalize the table as soon as possible. Thanks

[Psycho]

Well Barand thank you for your suggestions but I need to know if it can be done by sql for now. It's not that easy as you say, it will take time since the tables are in use by other scripts and applications created by other developers. So as you can guess any update in the structure can cause problems for other applications to work.

 

Which is why the application layer should not 'talk' directly to the database layer. In other words, a developer shouldn't need to write queries in the functional code in order to interact with the database. There should be a Database Abstraction Layer (class(es), functions, whatever) that handles all the database interactions and the application code calls that. If there is a need to modify the database structure and the way data is accessed it becomes a fairly easy process to update just the DAL and all the application functionality doesn't have to change.

Edited February 14, 2018 by Psycho