SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,011
    Mentioned
    56 Post(s)
    Tagged
    0 Thread(s)

    subQuery bug help.

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

    Code:
    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...

    Code:
    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.

    PHP Code:
    <?
    $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. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    You've got limit 1 in your sub query!!!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mal Curtis View Post
    You've got limit 1 in your sub query!!!
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Ah, now I see.

    Yes, that's a problem.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you can't change the db,

    Code:
    WHERE FIND_IN_SET(code.recordid, (...your scalar subquery...))
    http://dev.mysql.com/doc/refman/5.1/...ubqueries.html

  6. #6
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Would that need to be FIND_IN_SET(...) > 0 or will it assume a 0 as false?

  7. #7
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,011
    Mentioned
    56 Post(s)
    Tagged
    0 Thread(s)
    Further normalization of the tables would take time I don't have for now. The comma lists will have to do for now.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mal Curtis View Post
    Would that need to be FIND_IN_SET(...) > 0 or will it assume a 0 as false?
    both null and 0 evaluate to false, so it should be safe to do without the comparison.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •