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.
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.
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.
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.
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.
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.
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..
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";
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.
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).
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.
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.
__________________
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.
Last edited by Old Pedant; 08-26-2011 at 11:42 PM..
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!
Before you post, read our: 
