Jump to content
Rommeo

why "where id in" does not work here?

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

 

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 :)

Share this post


Link to post
Share on other sites

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 by Psycho

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.