Hey,

I have a online mysql database and it has a table with a few thousand records. Now nothing special there, my question is however. One of the fields of every row has content like this:

2 9 14 119 5 543 1479 35 90 .... <-- this field will now be referenced as "Numberfield"

That can be potentially up to about 2000 of those numbers in one field for each row each seperated by a space. (its basically a memo field with room for short of 10.000 characters)

Now those numbers are all ID's of a second table where on command I want to get all records in my grid where the ID corresponds to the numbers above...

So in short (always have trouble explaining sql querries in words :)

Database 1 - Table 1 (consists of 3000 records)
DB1Table1ID - DB1Table1Title - DB1Table1Author

Database 2 - Table 1 (consists of 2000 records)
Numberfield

After performing the SQL Id like something like this in my DBGrid:
ID - DB1Table1Title - DB1TitleAuthor
9 - xxx - xxx
14 - xxx - xxx
119 - xxx - xxx
5 ......
543 ....
1479 ..
35 .
90

So out of 3000 records in table1 it presents me with 8 records as a results from those based on the 8 numbers listed in the Numberfield in Table 2. (the two tables are in 2 different databases, one is online mysql, the other is the local absolute database.. however both use sql as a language of course and should support whatever sql i need for this... I don't need any help on the databases, I can connect to both online and offline databases fine and fetch data or perform sql querries on both at my leasure... its solely the correct SQL synthax that I need :)

Somthing like:
Select * from DB1Table1 where DB1Table1ID = (any of the numbers in Numberfield of DB2Table1)

Remember they are seperated by a space and in the above example it of course shouldn't recognise "1479" as "1", "4", "7" and "9" but only once as 1479.


This has been bugging me for close to two weeks now and not getting there :(

var
i: 1.000.000
begin
'thanks a ' + inttostr(i*i);
end;
end.

Recommended Answers

All 3 Replies

I think you should read the record, parse it in Delphi, and then read the other table.

If you're connecting to mysql and the record had been separated by a comma instead of a space, then you could've used FIND_IN_SET().

Im in the process of converting all my records to use comma instead of space. I never used this before (find in set), any sql example relevant to my situation ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.