subQuery bug help

Databases
1

This doesn’t make sense. The following query returns 1 row only…


SELECT code.recordid AS id,
	code.code AS value 
FROM code 
WHERE code.recordid IN (
	SELECT diagnosis 
	FROM bills 
	WHERE bills.recordid = 231
	LIMIT 1
)

But that isn’t right. If I run the subquery it returns “9622,7995,7811” - and if we run that directly in the in clause I get the expected three rows…


SELECT code.recordid AS id,
	code.code AS value 
FROM code 
WHERE code.recordid IN ( 9622,7995,7811 )

For the moment I can have PHP run 2 queries - but why isn’t this working as a single query?

NOTE: I think the problem is that the field is storing a comma delimited string, but there’s only 1 row for the subquery. Is there a way to get mySQL to consider the literal result of the query rather than the row results?

EDIT: It’s not like the 2 query solution isn’t that unreadable due to my database driver’s flexibility - but it is less efficient. The below works.

<?
$this->output['forms']['procedures']['diagnosis']['options'] =  PAMWF::$db->queryField("
			SELECT code.recordid AS id,
				code.code AS value 
			FROM code 
			WHERE code.recordid IN (".PAMWF::$db->queryFirstItem("
				SELECT diagnosis 
				FROM bills 
				WHERE bills.recordid = {$this->output['forms']['procedures']['parentid']['value']}
				LIMIT 1
			").")
		", 'id');
?>
2

You’ve got limit 1 in your sub query!!!

3

but that’s the problem – his one row contains a column value of ‘9622,7995,7811’

michael, if you want to do it right, read up on first normal form and declare a separate table

4

Ah, now I see.

Yes, that’s a problem.

5

If you can’t change the db,


WHERE FIND_IN_SET(code.recordid, (...your scalar subquery...))

http://dev.mysql.com/doc/refman/5.1/en/scalar-subqueries.html

6

Would that need to be FIND_IN_SET(…) > 0 or will it assume a 0 as false?

7

Further normalization of the tables would take time I don’t have for now. The comma lists will have to do for now.

8

both null and 0 evaluate to false, so it should be safe to do without the comparison.