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 14 of 14
  1. #1
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subquery returning syntax error but it looks fine...

    Hi, I have simplified my query so that you don't have to go through irrelevant pieces of it so if it appears so simple that I shouldn't be using a subquery, it's because I've removed much of the superfluous code. I am receiving the following error:
    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 'SELECT TimeId FROM TimeSheetPending WHERE TimeSheetPen

    About this code:
    Code:
    $bothQuery = "SELECT t.TimeId  FROM TimeSheet t WHERE t.TimeId NOT IN (SELECT TimeId, TimeBilled FROM TimeSheetPending WHERE TimeSheetPending.TimeBilled = 'no')";
    
    $bothResult = @mysql_query($bothQuery);
    
    if(!$bothResult){die(mysql_error());}
    
    $bothNumRows = mysql_num_rows($bothResult);
    Every reference I have found online and other forums have told me my syntax is correct. Obviously something is wrong, but I'm stuck. I am running on MySQL version 4.1.22 so I believe subqueries are allowed.

    Look forward to your knowledge, cheers!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    When you use IN ( SELECT ... ) (or NOT IN, of course) the SELECT *must* return only *ONE FIELD*. If it returns two fields, the IN() doesn't know which one to use.

    So just remove TimeBilled from the inner SELECT.

    And you don't need the table references in the field selectors, as there's never any ambiguity about which table you mean:
    Code:
    SELECT TimeId  FROM TimeSheet 
    WHERE TimeId NOT IN (
         SELECT TimeId FROM TimeSheetPending 
         WHERE TimeBilled = 'no')
    But you could also do this with a JOIN (seems trickier, but once you get used to it, it's not):
    Code:
    SELECT TS.TimeID
    FROM TimeSheet AS TS LEFT JOIN TimeSheetPending AS TSP
    ON ( TS.TimeId = TSP.TimeId AND TSP.TimeBilled = 'no' )
    WHERE TSP.TimeId IS NULL
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply!

    I used your first SELECT (the one without the JOIN) and received the same error:
    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 'SELECT TimeId FROM TimeSheetPending WHERE TimeBilled = 'no')' a

    I'm staying away from using a JOIN in this part of the SQL because I don't know that it makes sense when I add back the many other elements of this query (many of which have INNER JOINS). I can show you all of it if you'd like, but I think I first must determine why this syntax error persists regardless of what I do. Also, that "a" at the end of the error is part of the message, not sure where it's coming from though.

    Thanks again for taking a look at my problem!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    Well, start by using some DB tool to run *ONLY* inner SELECT.

    That is, *NOT* using PHP code, just try to execute
    Code:
    SELECT TimeId FROM TimeSheetPending WHERE TimeBilled = 'no'
    At this point, I'd guess that your TimeBilled field is not actually a text field of some kind. Maybe it's a BIT (BOOLEAN) field? In which case of course you can't compare it to the string value 'no'

    If you still can't figure it out do a DESCRIBE TimeSheetPending and show us what that shows you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ran query for

    Code:
    SELECT TimeId FROM TimeSheetPending WHERE TimeBilled = 'no'
    ... in phpmyadmin and received the two records that I should have received.

    Also, ran DESCRIBE TimeSheetPending and I've attached a screen shot of the results (export was too large). The table looks as it should imo. TimeBilled is an ENUM field, but I don't think it should matter what TimeBilled is in this case as I can remove that if you'd like and the error still occurs at the start of the subquery SELECT statement.
    Attached Thumbnails Attached Thumbnails Subquery returning syntax error but it looks fine...-screen-shot-2011-08-25-2.20.32-pm.jpg  

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    Weird. Curious why TimeId there is primary key. Shouldn't it be the primary key in TimeSheet table and then a foreign key in TimeSheetPending table?? Though I guess it could be both primary and foreign key in TimeSheetPending.

    Anyway, I duplicated your tables as much as it seemed to matter and tried the query, and it worked fine.
    Code:
    mysql> select * from timesheet;
    +--------+-------+
    | timeid | name  |
    +--------+-------+
    |      1 | Adam  |
    |      2 | Bob   |
    |      3 | Carol |
    +--------+-------+
    3 rows in set (0.04 sec)
    
    mysql> select * from timesheetpending;
    +--------+------------+
    | timeid | timebilled |
    +--------+------------+
    |      1 | yes        |
    |      3 | no         |
    +--------+------------+
    2 rows in set (0.03 sec)
    
    mysql> SELECT TimeId  FROM TimeSheet
        -> WHERE TimeId NOT IN (
        ->      SELECT TimeId FROM TimeSheetPending
        ->      WHERE TimeBilled = 'no');
    +--------+
    | TimeId |
    +--------+
    |      1 |
    |      2 |
    +--------+
    2 rows in set (0.04 sec)
    So I dunno where the problem is.

    Try doing
    Code:
    SELECT TimeId  FROM TimeSheet WHERE TimeId NOT IN ( 1, 3, 37 );
    (where you pick TimeId's that actually exist, of course).

    What does that get you?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried the SELECT you recommended with some values and that seemed to work. I'm not sure what is going on, but I've put together the more detailed version of my query so you can see exactly what I'm trying to do. Basically I want to display all results from the TimeSheet table where TimeBilled is set to 'no' AND where the TimeJob (ID) = $_GET['jobID'] (I've just placed an example job ID in my code below as I'm testing this directly in phpmyadmin and not using php. The big caveat is that if the TimeSheet.TimeID exists in the TimeSheetPending table, I want to show the TimeSheetPending entry instead of the TimeSheet entry. This should allow for multiple records to be displayed and keep the order parameters (less concerned about order right now). If this doesn't make sense, I can explain in more detail. The code below outputs the same error message at the beginning of the subquery SELECT statement.


    Code:
    SELECT 'notInPendingTable' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
     FROM TimeSheet t, Jobs j
      WHERE t.TimeBilled = 'no' AND j.JobId = 2226 AND t.TimeId NOT IN (SELECT TimeId FROM TimeSheetPending tp)
    UNION
    SELECT 'inPending' AS type, tp.TimeJob, tp.TimeId, tp.TimeBilled, j.JobId
     FROM TimeSheet t, Jobs j
      WHERE tp.TimeBilled = 'no' AND j.JobId = 2226 AND tp.TimeId IN (SELECT TimeId FROM TimeSheet t)
    Last edited by bradley1983; 08-26-2011 at 12:21 AM.

  • #8
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not sure how/if I am on the right path, but thought I'd share... The SQL below returns the records from each of the two main tables (t and tp) that have matching TimeId. Maybe there is a way to use this information and PHP manipulation to go a step further to my desired result? I used the SELECT '' AS type b/c I was thinking maybe there is a way to say If ($Row['type'] == 'NOTpending') then ... but it was just a thought.

    Code:
    "SELECT 'NOTpending' AS type, tp.TimeId, tp.TimeJob, tp.TimeBilled, j.JobId
     FROM TimeSheetPending tp
     INNER JOIN Jobs j ON j.JobId = tp.TimeJob AND j.JobId IN ('2226')
     JOIN TimeSheet t ON tp.TimeId = t.TimeId
      WHERE t.TimeBilled = 'no' GROUP BY t.TimeId
    UNION
    SELECT 'INpending' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
     FROM TimeSheet t
     INNER JOIN Jobs j ON j.JobId = t.TimeJob AND j.JobId IN ('2226')
     JOIN TimeSheetPending tp ON t.TimeId = tp.TimeId
      WHERE t.TimeBilled = 'no' GROUP BY t.TimeId";

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    This version makes no sense:
    Code:
    SELECT 'notInPendingTable' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
     FROM TimeSheet t, Jobs j
      WHERE t.TimeBilled = 'no' AND j.JobId = 2226 AND t.TimeId NOT IN (SELECT TimeId FROM TimeSheetPending tp)
    Unless TimeBilled appears in *both* the TimeSheet table and in the TimeSheetPending tables, that is.

    But even if it does, you have no JOIN condition there between TimeSheet and Jobs.

    At a minimum, you would need *something* like AND t.TimeJob = j.JobId

    Maybe you need to show the fields in all 3 tables??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    TimeBilled does appear in both tables. If a record from TimeSheet is edited for the first time, the original stays in TimeSheet and then the updated record is stored in TimeSheetPending. I want to display all records from TimeSheet and TimeSheetPending when TimeBilled = 'no' AND JobId = 'someJobID' AND (show only the TimeSheetPending record when the TimeSheetPending.TimeId matches TimeSheet.TimeID). Does that help to better explain?

    For your second point about the JOIN, I've come up with the following query using a JOIN and it retrieves all records from both within the parameters. I think I'm getting close, but not sure how to strip out the matching records so the matching TimeSheet records do NOT appear. Any thoughts?

    Code:
    SELECT 'NOTpending' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
     FROM TimeSheet t
     INNER JOIN Jobs j ON j.JobId = t.TimeJob AND j.JobId IN ('2226')
     JOIN TimeSheetPending tp ON t.TimeId != tp.TimeId
      WHERE t.TimeBilled = 'no' AND t.timeID != tp.TimeId GROUP BY t.TimeId
    UNION
    SELECT 'INpending' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
     FROM TimeSheet t
     INNER JOIN Jobs j ON j.JobId = t.TimeJob AND j.JobId IN ('2226')
     JOIN TimeSheetPending tp ON t.TimeId = tp.TimeId
      WHERE t.TimeBilled = 'no' GROUP BY t.TimeId
    Results Screen shot attached (notice the two INpending records match the ID of two of the NOTpending records, I want the two INpending records to appear in this query along with the other ten NOTpending records).
    Attached Thumbnails Attached Thumbnails Subquery returning syntax error but it looks fine...-screen-shot-2011-08-26-8.50.03-am.jpg  

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    I showed you one way:
    Code:
    SELECT 'NOTpending' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
    FROM TimeSheet t
    INNER JOIN Jobs j ON j.JobId = t.TimeJob AND j.JobId IN ('2226')
    LEFT JOIN TimeSheetPending tp ON ( t.TimeId = tp.TimeId AND tp.TimeBilled = 'no' )
    GROUP BY t.TimeId
    HAVING tp.TimeId IS NULL
    Or something along those lines.

    It seems weird to do a LEFT JOIN looking for matches and then reject the ones with no matches (the HAVING clause), but it does work.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I use this query, I get the following error:
    #1054 - Unknown column 'tp.TimeId' in 'having clause'

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,542
    Thanks
    77
    Thanked 4,382 Times in 4,347 Posts
    SORRY SORRY SORRY!

    My mind was *NOT* functioning. We need WHERE, not HAVING!
    Code:
    SELECT 'NOTpending' AS type, t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
    FROM TimeSheet t
    INNER JOIN Jobs j ON j.JobId = t.TimeJob AND j.JobId IN ('2226')
    LEFT JOIN TimeSheetPending tp ON ( t.TimeId = tp.TimeId AND tp.TimeBilled = 'no' )
    WHERE tp.TimeID IS NULL
    Sheesh!

    Can't use HAVING like that.

    And if you think you need a GROUP BY, then you need to GROUP BY *all* fields. But for that, why not just use DISTINCT, instead?

    It's never good to use GROUP BY on just one or two of the SELECTed fields out of many. It works (though *ONLY* in MySQL), but it never gives you the results you expect.
    Last edited by Old Pedant; 08-26-2011 at 11:42 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just want to share the working solution in case anyone comes across this issue in the future...
    Code:
    SELECT (case when b.PTimeId is null then 'NOTpending' else 'INpending' end) AS type, a.*, b.*, c.*, j.*, u.*, bc.* 
    	FROM TimeSheet a
    		INNER JOIN Clients c ON c.ClientId = a.TimeClient
    		INNER JOIN Jobs j ON j.JobId = a.TimeJob AND j.JobId IN ('$jobId1', '$jobId2')
    		INNER JOIN BillCat bc ON bc.BillCatId = a.TimeBillingCat
    		INNER JOIN Users u ON u.Login = a.TimeUser
    			LEFT OUTER JOIN TimeSheetPending b
    				ON a.TimeId = b.PTimeId 
    				WHERE a.TimeBilled = 'no'
    				ORDER BY  bc.CatNum, a.TimeDate, u.LastName
    Then I added an if...else for the type within the while loop. Thanks for the help Old Pedant!


  •  

    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
    •