1

i have some strange searching criteria. first let me clarify it,

i have a table like this 

     acct_no |   name  | connected_acct
  ----------------------------------
  1          |  name_1 | 5,6,7
  2          |  name_1 | 11,12,13
   ---------------------------------

1) here person "name_1" is the holder of all the account of "5,6,7"

what i want is, if i search for "acct_no" 1, then all the details of other connected accounts should also be displayed in a grind or whatever.

Thanks

--EDIT-- (1) i fount this question but i don't this it can solve my problem SQL: Display all the records related to common id

(2) i forgot to mention that the database will store some of the fields in UTF format (some local language !)

Community
  • 1
  • 1
Advitiya
  • 13
  • 3
  • Is normalizing your database an option? – Mureinik Dec 01 '14 at 06:37
  • yes, any solution can be implemented. actually it a half-completed project and i need to redesign it. so i can changes if necessary. :) – Advitiya Dec 01 '14 at 06:39
  • Then I strongly recomnend to normalize your tables. You could have an n-to-m connecting table for `[acct_no, connected_acct]` - that will make your life sooo much easier in the future. – Mureinik Dec 01 '14 at 06:41
  • thanks, Mureinik , i am also thinking for a new table for connected accounts. – Advitiya Dec 01 '14 at 06:45

1 Answers1

0

Use FIND_IN_SET function:

Try this: 

SELECT b.acct_no, b.name 
FROM accounts a 
INNER JOIN accounts b ON FIND_IN_SET(b.acct_no, a.connected_acct) 
WHERE a.acct_no = 1;
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • 1
    thanks Saransh, i am trying both the tips from you and mureinik . i think i can now solve it. whenever i completed it i will post the sample code and the detailed solution. :) – Advitiya Dec 01 '14 at 06:46