WHERE clause problem

[esm]

for some reason the following is not working properly:

 

I have a group of numbers (separated by comma) in a field that I would like to use.

 

I know what acctno is beforehand. Let\'s say it is 2306.

 

Lets say that I have the following in the relatedaccts column for the coa table:2206, 2306, 2406, 2506

 

I tried SELECT * FROM coa WHERE acctno IN (relatedaccts)

 

then: WHERE acctno IN (2206, 2306, 2406, 2506)

 

returns false: It does not find the 2306 in the IN clause. Or for that matter the 2406 or 2506

 

But it will find 2206.

 

Weird...

 

Any ideas or help...?

[shivabharat]

SELECT * FROM comp WHERE compno IN (1,2);

 

this displays me the record of 1 and 2

 

SELECT 2 IN (0,3); gives me 0

 

SELECT 3 IN (0,3); gives me 1

 

 

This should work for u

 

SELECT * FROM coa WHERE acctno IN (2206, 2306, 2406, 2506) 

[esm]

thanks for the reply. However, I do need to use the relatedaccts field.

 

SELECT * FROM coa WHERE acctno IN (relatedaccts)

 

here is the abbreviated table structure for a row

field        type         value

acctno       smallint-5   2006

desc         test         Accounts Payable

amount       float(6,2)  -1347.49

relatedaccts text         2007, 2008, 2009

[shivabharat]

Now if u have strored the data

 

u have to fisrt retrieve the data ($relatedaccts) from the table

 

and pass it on

 

 

$relatedaccts = "2007, 2008, 2009";



SELECT * FROM coa WHERE acctno IN (\'$relatedaccts\') 

[esm]

the problem with that is that relatedaccts changes from row to row as the select command loops thru the database.

 

It may be 2007, 2008, 2009 for the first row but blank for the next row and 3049, 3061 for the third row, etc, etc.

[shivabharat]

So what is the problem u face can u be clear?

[esm]

thanks for helping me with this...

 

take a look at my orignal post. the acctno is supplied to the select command which starts the loop thru the rows. I need to be able to select the row if the acctno exists in the relatedaccts column. thus the WHERE clause that read WHERE acctno IN (relatedaccts). which should work but doesn\'t.

 

The IN clause should work if the values are as I have shown in the previous posts.

 

the acctno is an integer type while the relatedaccts is a text type.

[shivabharat]

Are u trying something like this??

 

if found this will retuirn 1 else NULL

 

Sorry if this code is of no use :roll:

 

<?php 



$accno = $_POST[\'accno\'];



$db = mysql_connect("localhost", "admin","admin");



mysql_select_db("test",$db);



$sql = mysql_query("SELECT data FROM temp"); 





while ($row = mysql_fetch_array($sql))

{ 

$related = $row["data"]; 

echo $related;



$sql2 = mysql_query("SELECT * FROM temp WHERE \'$accno\' IN ($related)");

while ($row = mysql_fetch_array($sql2))

 { 

 $found = $row["accno"]; 

 echo $found;

}



}



?>

[pallevillesen]

Ok, to sum up...

 

You want the LIST in the \'IN\' clause to change for each row...

 

So table1

 

Id related

 

1 2,3

2 3,1

3 1,2

4 5,6

5 4,6

6 4,5

 

The you want the select to go

 

select * from table where ID in related; and the value of \'related\' changes for every single row...

 

I don\'t think that is possible. It shouldn\'t be. You could do it using php, loop through everything or something.... a zillion times slower, bad structure etc... But it could be fixed to work....

 

But you basic data structure here is sibling relationship. Small groups of related individuals (accounts).

 

What you want is to store the related accounts in another table, like this

 

id is_related_to

1 2

1 3

2 1

2 3

3 1

3 2

etc...

 

(This table could be halved in size, since the 1 - 2 and 2 -1 is basically the same)... Note that for 1000 accounts, this table will hold a maximum of 1000*999/2 records.... (everybody are related to eachother).

 

Having this table, you would do your seach like

 

select ID\'s from limittable where is_related_to = 2306 (your example)

 

This will give you a list of id\'s you want... Put them somewhere, join them into a variable, whatever....

 

now 2. search...

 

select * from accounts where id IN ($listofidsfromsearch1);

 

ALTERNATIVELY:

 

select id.id, id.name... FROM id, limittable l where id.id = l.id AND l.is_related_to = 2306;

 

What you are trying to do here, is storing a treelike (or GRAPH like ) structure.... The above method is called the adjacency list model - and that\'s NOT the best way...

 

For reading some more on this: (interestin if you\'re in the multimillion DB record business):

 

http://searchdatabase.techtarget.com/tip/1...i537290,00.html

 

If you want help in creating your new table structure and the select statements, let me know... (I\'ll watch this topic).

 

P.

denmark