Web Analytics Made Easy -
StatCounter Subquery returning syntax error but it looks fine... - CodingForum

Announcement

Collapse
No announcement yet.

Subquery returning syntax error but it looks fine...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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
    Be yourself. No one else is as qualified.

    Comment


    • #3
      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!

      Comment


      • #4
        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.
        Be yourself. No one else is as qualified.

        Comment


        • #5
          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 Files

          Comment


          • #6
            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?
            Be yourself. No one else is as qualified.

            Comment


            • #7
              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; Aug 25, 2011, 08:21 PM.

              Comment


              • #8
                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";

                Comment


                • #9
                  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??
                  Be yourself. No one else is as qualified.

                  Comment


                  • #10
                    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 Files

                    Comment


                    • #11
                      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.
                      Be yourself. No one else is as qualified.

                      Comment


                      • #12
                        When I use this query, I get the following error:
                        #1054 - Unknown column 'tp.TimeId' in 'having clause'

                        Comment


                        • #13
                          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; Aug 26, 2011, 07:42 PM.
                          Be yourself. No one else is as qualified.

                          Comment


                          • #14
                            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!

                            Comment

                            Working...
                            X