Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: subquery

  1. #1
    New Coder
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subquery

    Hello everyone

    I used this query to find the count in second table.

    SELECT e.scid, e.executionid, e.service, e.clientid, e.clientname, e.invoiceno, e.execstatus, e.sysdate, e.cuname,
    e.upuname, e.canreason, e.udate, e.enquiryno,
    (
    select count(dm.gid) from datamodification dm where dm.status=0 and dm.scid=e.scid and dm.department='Fumigation' and dm.scid=e.scid
    ) AS cnt
    FROM execution e
    WHERE e.scid = 'BR001'
    AND e.service = 'Container'
    AND e.clientname LIKE '%%'
    AND e.executionid LIKE '%%'
    ORDER BY sysdate, executionid
    limit 0,30


    I get an error when i run this query.
    MySQL said:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

    When i remove the where condition from subquery it works fine, but this is not what i want.

    Kindly verify my sql syntax and suggest me what should be my query.

    Thanks,
    Last edited by Fou-Lu; 03-30-2009 at 09:52 AM.
    -------------------------
    Shailesh Patil
    -------------------------

  • #2
    New Coder
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up solved

    I changed the sequence of the query.


    Code:
    SELECT  (
    select count(dm.gid) from datamodification dm where dm.status=0 and dm.scid=e.scid and dm.department='Fumigation' and dm.scid=e.scid
    ) AS cnt , 
    e.scid, e.executionid, e.service, e.clientid, e.clientname, e.invoiceno, e.execstatus, e.sysdate, e.cuname, 
    e.upuname, e.canreason, e.udate, e.enquiryno
    FROM execution e
    WHERE e.scid = 'BR001'
    AND e.service = 'Container'
    AND e.clientname LIKE '%%'
    AND e.executionid LIKE '%%'
    ORDER BY sysdate, executionid
    limit 0,30
    I took the subquery first and then normal select.
    It worked.
    Thanks for your time
    -------------------------
    Shailesh Patil
    -------------------------


  •  

    Tags for this Thread

    Posting Permissions

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