SQL syntax - Is this Possible?

General
1

Good Day! I have a question, I am not sure how to search for an answer as I think what I am asking may not exist. If you are aware whether it does or does not exist could you let me know? Thanx in advance!!
(I’m working in MySQL)

Is there a way to write a select statement in SQL that will pull data from one column–>> parse the information (select the first (or second) word from the data) then select the matching Row from a neighbor table. Let me see if I can give an example:

Lets say I have a table that holds personal information about people
One of the columns in the table is a multi-part list of tasks(or procedures) completed for the person. The tasks are simply ID numbers referencing a neighbor table that lists ALL tasks/procedures with descriptions.
So Table1.tasklist might look like this in the row,column → ‘2,14,27’ (which are really ID’s from table2
Then each row in Table2 contains the task description, fee, code etc that I really want to use from the Query.
Table2 sample row1: 2, g36.121, description, 95.00
Table2 sample row2: 3, h52.223, description, 0.00
Table2 sample row3: 27, 99233, description, 65.00

If the Table1.column says ‘2,14,27’, I would like the rows with these ID’s 2,14,27 returned in the search results, well, I’d also like to parse the Table2 rows and really only return 1 or 2 columns for use -
So Table1 holds a list of procedures: 1,14,35 as an example
I’d like to use those ID’s and pull rowID1,14,35 column2,column4 from table2 for use

Man, I am really sorry that was so wordy.
Seems awful complex, any idea if this is possible?

2

Sounds like pretty basic SQL.

Select table2.,table2. from table1 left join table2 on table1.AColumn1 = table2.AColumn2

Other joins are available.

Show us an example of Table1. Are there more than 1 rows in it?

Table1

Column1
1,14,27
2,3,4

Table2

Column1 Column2 Column3 Column4
2 g36.121 description 95.00
3 h52.223 description 0.00
27 99233 description 65.00

Is that the sort of thing?

You can use Insert Table in the forum (on the cog) to add a table.

3

You really should have the tasks as a separate table with one row per task rather than a comma separated list.

1 Like
4

You can cope with a comma separated list. but it’s not efficient.

MySQL has a find_in_set function you can join on.

5

So one field holds 1,3,6 ?
Thats bad design.

Ideally, you should have a table for (eg) Person, with a person ID

Another table for completed tasks, holding person ID, one Task ID, and maybe a date

Another table for task description.

Then the quesry is simple:

Select Name, TaskDescription
from person a,completedtasks b, tasks c
where a.personid = b.personid
and b.taskid = c.taskid;

After that, you can consider outer joins, so that you always get a person, even if they do not have any completed tasks.

As it stands, in your data, you need a function or a query that translates the single text field into 3 or more distinct values.

1 Like
6

Interesting, let me investigate the find_in_set function. As i set up this table, and thought it thru, (i still can change things), I knew this would be a sticking point. I still want to pursue it because we are only talking about at most a dozen searches a day with this SQL statement. Thank you for such quick replies all!!

Edit: Does the find_in_set have an “nthfield” type potential?

7

No. But Xojo does. Once you retrieve the data. You don’t even need Table1 to use find_in_set.

select * from table2 where find_in_set( table2.column, "1, 3, 4" ) > 0

I would suggest you use MySQLWorkbench to prototype your SQL before putting it into Xojo.

8

I AM definitely using Workbench!

1 Like