-2

Is it possible to join table 2 on table 1 using a join as follows?

SELECT *.table2 FROM table1 JEFT JOIN table2 ON table2.id IN( table1.values ) WHERE table1.id = 1

Here is the structure

table1
id    | table2_ids 
______|__________
1     | 2,3,4,5,6

table 2
id   |  name
_____|_______
2    | Joe
3    | John
4    | Bill
5    | Bob

I would like the output to be

id   |  name
_____|_______
2    | Joe
3    | John
4    | Bill
5    | Bob

I would be happy to rephrase my question title if someone can suggest an improvement ( which I don't think would be hard :) )

Ben
  • 377
  • 2
  • 6
  • 26
  • could you please add your desired output in table format – Fahmi Oct 23 '20 at 17:21
  • @fahmi Done - added desired output – Ben Oct 23 '20 at 17:24
  • 1
    If you want to treat the ids as discrete values instead of a string, then you should store them on individual rows in another table, not as a comma-separated string. See [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Oct 23 '20 at 18:32

2 Answers2

1

The operator IN is used with a list of values but what you have is a string with a comma separated list of values, so you can't use it.

You can use FIND_IN_SET():

SELECT t2.* 
FROM table2 t2 INNER JOIN table1 t1
ON FIND_IN_SET(t2.id, t1.table2_ids)
WHERE t1.id = 1

See the demo.
Results:

> id | name
> -: | :---
>  2 | Joe 
>  3 | John
>  4 | Bill
>  5 | Bob
forpas
  • 160,666
  • 10
  • 38
  • 76
0

Based on your question, it would seem that you may be looking for an inner join rather than a left join. A left join might make sense if your table 1 values were individual records rather than a concatenated list of ids.

Unless there is a particular reason to do the left join, you might consider something like this:

Note table1.values is considered a string here (which is still bad design)

SELECT table2.*
FROM table1, table2 
WHERE 
  table2.id IN table1.values
  and
  table1.id=1

A better design would be to break up your table1 to be like this:

id, table2_id
1, 2
1, 3
1, 4 
1, 5 
1, 6

Then you could appropriately use the left join:

SELECT table2.*
FROM table1
LEFT JOIN table2 
ON table1.table2_id=table2.id
WHERE 
  table1.id=1
conmak
  • 1,200
  • 10
  • 13
  • Thanks! Would you still consider it bad design if I as using it to store a list of imported product ids? I'd rather not run 5000 inserts just to keep a record of ids that will only be used to roll back an import ... which will hardly ever happen. – Ben Oct 23 '20 at 18:11
  • @Ben what's wrong with rolling back 5000 rows (it's one insert btw)? I think it's a simple choice between normalising data or simply not bothering with a relational database. – Strawberry Oct 24 '20 at 07:03
  • In performance and design terms, you would almost certainly benefit from switching to a normalized design here. With that said, you might consider adding a third column to sort on in table 2 and avoid the join all together. This however might not be reasonable based on other processes happening in your db. – conmak Oct 28 '20 at 14:44