Jump to content


Photo

WHERE clause problem


  • Please log in to reply
8 replies to this topic

#1 esm

esm
  • New Members
  • Pip
  • Newbie
  • 5 posts
  • LocationAtlanta GA

Posted 06 May 2003 - 11:11 PM

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...?

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 06 May 2003 - 11:21 PM

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)

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 esm

esm
  • New Members
  • Pip
  • Newbie
  • 5 posts
  • LocationAtlanta GA

Posted 07 May 2003 - 02:34 AM

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





#4 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 07 May 2003 - 03:14 PM

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\')

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#5 esm

esm
  • New Members
  • Pip
  • Newbie
  • 5 posts
  • LocationAtlanta GA

Posted 07 May 2003 - 05:00 PM

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.

#6 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 07 May 2003 - 06:11 PM

So what is the problem u face can u be clear?
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#7 esm

esm
  • New Members
  • Pip
  • Newbie
  • 5 posts
  • LocationAtlanta GA

Posted 07 May 2003 - 08:26 PM

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.

#8 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 07 May 2003 - 09:44 PM

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;

	}



}



?>

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#9 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 08 May 2003 - 08:34 AM

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://searchdatabas...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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users