SQL Syntax error in JOIN statement

  • (2 Pages)
  • +
  • 1
  • 2

25 Replies - 1032 Views - Last Post: 06 September 2011 - 08:46 AM

#1 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

SQL Syntax error in JOIN statement

Posted 24 August 2011 - 09:37 AM

Hi,

I am receiving the following error and I cannot seem to figure out the culprit. Any thoughts? I can provide additional code/info if necessary, just lmk. Thanks!

Error output:
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' ))

Code:
...
$bothQuery .= " FROM TimeSheet, Clients, Jobs, BillCat, Users WHERE TimeSheet.TimeBilled = 'no' AND Clients.ClientId = TimeSheet.TimeClient AND Jobs.JobId = TimeSheet.TimeJob AND BillCat.BillCatId = TimeSheet.TimeBillingCat AND Users.Login = TimeSheet.TimeUser AND (Jobs.JobId = '$_POST[job]' OR Jobs.JobId = '$_GET[jobID]') AND TimeSheet.TimeId != (SELECT TimeSheetPending.TimeId FROM TimeSheetPending WHERE TimeSheetPending.TimeBilled = 'no' )";
$bothQuery .= ") UNION (";
...



Is This A Good Question/Topic? 0
  • +

Replies To: SQL Syntax error in JOIN statement

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 10:33 AM

Right, let me just lay that out...
FROM TimeSheet, Clients, Jobs, BillCat, Users 
WHERE TimeSheet.TimeBilled = 'no' 
	AND Clients.ClientId = TimeSheet.TimeClient 
	AND Jobs.JobId = TimeSheet.TimeJob 
	AND BillCat.BillCatId = TimeSheet.TimeBillingCat 
	AND Users.Login = TimeSheet.TimeUser 
	AND (
		Jobs.JobId = '$_POST[job]' OR Jobs.JobId = '$_GET[jobID]') 
	AND TimeSheet.TimeId != (
		SELECT TimeSheetPending.TimeId FROM TimeSheetPending WHERE TimeSheetPending.TimeBilled = 'no' 
	)



You put a raw get in your query, brave man... If either of those posts or gets mess up the SQL syntex you'll get an error and not know why. That union has a ) in front of it; I hope there is a reasonable one above it. Of course, seeing a union already makes me think something has gone horribly wrong...

One thing that jumps out is that != subquery. I really doubt that works; you need to have one and only one record returned from that query. Is this always the case?

Let's try some standard join syntax...
FROM TimeSheet
	INNER JOIN Clients
		ON Clients.ClientId = TimeSheet.TimeClient 
	INNER JOIN Jobs
		ON Jobs.JobId = TimeSheet.TimeJob 
			AND Jobs.JobId IN ('$_POST[job]', '$_GET[jobID]') 
	INNER JOIN BillCat
		ON BillCat.BillCatId = TimeSheet.TimeBillingCat 
	INNER JOIN Users 
		ON Users.Login = TimeSheet.TimeUser 
WHERE TimeSheet.TimeBilled = 'no' 
	AND TimeSheet.TimeId NOT IN (
		SELECT DISTINCT TimeId 
			FROM TimeSheetPending 
			WHERE TimeSheetPending.TimeBilled = 'no' 
		)



Still not happy about that TimeSheet.TimeId, but now at least it won't crash automatically.

Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#3 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 10:46 AM

Hi, baavgai. Thanks for the quick reply.

I've stripped out the UNION for the purpose of it isn't necessarily required to solve this problem, so the entire query is below. With your suggestion, it looks like I am still receiving an error at the same point (the SELECT with the WHERE clause).

$bothQuery = "(SELECT 'timesheet' AS type,";
$bothQuery .= " TimeSheet.TimeId, TimeSheet.TimeUser, TimeSheet.TimeDate, TimeSheet.TimeClient, TimeSheet.TimeJob, TimeSheet.TimeBillingCat, TimeSheet.TimeDescription, TimeSheet.TimeHours, TimeSheet.TimeStatus, TimeSheet.TimeLocked, TimeSheet.TimeBilled, Clients.ClientID, Jobs.JobId, BillCat.BillCatId, BillCat.CatNum, Users.Login, Users.LastName ";
$bothQuery .= " FROM TimeSheet INNER JOIN Clients ON Clients.ClientId = TimeSheet.TimeClient INNER JOIN Jobs ON Jobs.JobId = TimeSheet.TimeJob AND Jobs.JobId IN ('$_POST[job]', '$_GET[jobID]') INNER JOIN BillCat ON BillCat.BillCatId = TimeSheet.TimeBillingCat INNER JOIN Users ON Users.Login = TimeSheet.TimeUser WHERE TimeSheet.TimeBilled = 'no' AND TimeSheet.TimeId NOT IN (SELECT DISTINCT TimeId FROM TimeSheetPending WHERE TimeSheetPending.TimeBilled = 'no')";
$bothQuery .= ") ORDER BY  `CatNum` ,  `TimeDate` , `LastName` ASC";
$bothResult = @mysql_query($bothQuery);
if(!$result){die(mysql_error());}
$bothNumRows = mysql_num_rows($bothResult);


Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 12:46 PM

Take that "(" out of the front of the select and where it is in the order by.

Since I can see what's going on now... try this:
$jobId1 = mysql_real_escape_string($_POST[job]);
$jobId2 = mysql_real_escape_string($_GET[jobID]);
$bothQuery = "SELECT 'timesheet' AS type,"
	. " t.TimeId, t.TimeUser, t.TimeDate, t.TimeClient, t.TimeJob, t.TimeBillingCat, t.TimeDescription, t.TimeHours,"
	. " t.TimeStatus, t.TimeLocked, t.TimeBilled, "
	. " c.ClientID, j.JobId, b.BillCatId, b.CatNum, u.Login, u.LastName "
	. " FROM TimeSheet t"
	. " INNER JOIN Clients c ON c.ClientId = t.TimeClient"
	. " INNER JOIN Jobs ON j.JobId = t.TimeJob AND j.JobId IN ('$jobId1', '$jobId2')"
	. " INNER JOIN BillCat b ON b.BillCatId = t.TimeBillingCat"
	. " INNER JOIN Users u ON u.Login = t.TimeUser"
	. " WHERE t.TimeBilled = 'no'"
	. "    AND t.TimeId NOT IN (SELECT DISTINCT TimeId FROM TimeSheetPending WHERE TimeSheetPending.TimeBilled = 'no')";
	. " ORDER BY  b.CatNum, t.TimeDate, u.LastName"
	;


Was This Post Helpful? 0
  • +
  • -

#5 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 01:06 PM

Looks like the same error message:
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 DISTINCT TimeId FROM TimeSheetPending WHERE TimeSheetPen

With Code:
$jobId1 = mysql_real_escape_string($_POST[job]);
$jobId2 = mysql_real_escape_string($_GET[jobID]);
$bothQuery = "SELECT 'timesheet' AS type,"
	. " t.TimeId, t.TimeUser, t.TimeDate, t.TimeClient, t.TimeJob, t.TimeBillingCat, t.TimeDescription, t.TimeHours,"
	. " t.TimeStatus, t.TimeLocked, t.TimeBilled, "
	. " c.ClientID, j.JobId, b.BillCatId, b.CatNum, u.Login, u.LastName "
	. " FROM TimeSheet t"
	. " INNER JOIN Clients c ON c.ClientId = t.TimeClient"
	. " INNER JOIN Jobs ON j.JobId = t.TimeJob AND j.JobId IN ('$jobId1', '$jobId2')"
	. " INNER JOIN BillCat b ON b.BillCatId = t.TimeBillingCat"
	. " INNER JOIN Users u ON u.Login = t.TimeUser"
	. " WHERE t.TimeBilled = 'no'"
	. "    AND t.TimeId NOT IN (SELECT DISTINCT TimeId FROM TimeSheetPending WHERE TimeSheetPending.TimeBilled = 'no')"
	. " ORDER BY  b.CatNum, t.TimeDate, u.LastName";
$bothResult = @mysql_query($bothQuery);
if(!$bothResult){die(mysql_error());}
$bothNumRows = mysql_num_rows($bothResult);


This post has been edited by bradley1983: 24 August 2011 - 01:07 PM

Was This Post Helpful? 0
  • +
  • -

#6 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 01:36 PM

Let me also explain what the SQL is supposed to be retrieving, maybe I'm going about it the wrong way in general...

I have a TimeSheet table and a TimeSheetPending table, both with the same fields. If a record in the TimeSheet is ever edited, the original record stays in the TimeSheet, but the updated record is stored as a new record in the TimeSheetPending table. The mentioned SQL is supposed to pull in records from TimeSheet and if the record exists in TimeSheetPending, then the TimeSheetPending record should appear and NOT the TimeSheet record. The closest I was able to get was for all records to show from both tables (of course limited by the WHERE clause in the SQL above).

Does that make sense? If not, let me know and I can explain in more detail. Thanks!
Was This Post Helpful? 0
  • +
  • -

#7 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 02:15 PM

Hi, I don't see a syntax error but one thing I did notice is
INNER JOIN Jobs ON j.JobId = t.TimeJob AND j.JobId IN ('$jobId1', '$jobId2')


You don't alias jobs to j.
Was This Post Helpful? 0
  • +
  • -

#8 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 24 August 2011 - 02:29 PM

Okay, updated that, thank you.

It seems like no matter what I simplify this query down to, the same problem occurs at the SELECT statement within the subquery. Any ideas why the problem is always at this point? It seems like the syntax is all right so I'm really confused.
Was This Post Helpful? 0
  • +
  • -

#9 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 25 August 2011 - 09:14 AM

Slightly new approach. How can I cross reference the two foreach loops and strip out the duplicate records from $arr when $display['ID'] = $Penddisplay['ID'] so that only $Penddisplay['ID'] shows within the list. Can they all remain ordered together (in one foreach) by date? Should I go back to trying to reach my goal with SQL or is this a better route? Open to any and all expertise. Cheers!

// Build SQL query
$Query = "SELECT * FROM BillCat, Clients, Jobs, TimeSheet, Users WHERE Clients.ClientId = TimeSheet.TimeClient AND Jobs.JobId = TimeSheet.TimeJob AND BillCat.BillCatId = TimeSheet.TimeBillingCat AND Users.Login = TimeSheet.TimeUser AND TimeSheet.TimeBilled = 'no' AND (Jobs.JobId = '$_POST[job]' OR Jobs.JobId = '$_GET[jobID]') ";
$Query .= "ORDER BY  `CatNum` ,  `TimeDate` , `LastName` ASC";
$Result = @mysql_query($Query);
$NumRows = mysql_num_rows($Result);

// Build Pending SQL query
$PendQuery = "SELECT * FROM BillCat, Clients, Jobs, TimeSheetPending, Users WHERE Clients.ClientId = TimeSheetPending.TimeClient AND Jobs.JobId = TimeSheetPending.TimeJob AND BillCat.BillCatId = TimeSheetPending.TimeBillingCat AND Users.Login = TimeSheetPending.TimeUser AND Jobs.JobId = '$_POST[job]' AND TimeSheetPending.TimeBilled = 'no' ";	
$PendQuery .= "ORDER BY  `CatNum` ,  `TimeDate` , `LastName` ASC";
$PendResult = @mysql_query($PendQuery);
$PendNumRows = mysql_num_rows($PendResult);


$arr=array();
while($Row=mysql_fetch_assoc($Result)){
     $arr[]=array('ID'=>$Row['TimeId']);
}
foreach ($arr as $display) {
	echo "".$display['ID']."<br />";
}


$Pendarr=array();
while($PendRow=mysql_fetch_assoc($PendResult)){
     $Pendarr[]=array('ID'=>$PendRow['TimeId']);
}
foreach ($Pendarr as $Penddisplay) {
	echo "".$Penddisplay['ID']."<br />";
}


Was This Post Helpful? 0
  • +
  • -

#10 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 26 August 2011 - 08:55 AM

I've made some adjustments as I think I'm getting much closer. Here is where I am at, please let me know if you have questions.

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?

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



Here is an explanation of the output from the above query in relation to my data:
- There are 12 records returned from TimeSheet and 2 from TimeSheetPending.
- The 2 records that have the INpending type match the ID of 2 of the NOTpending type records.
- I want the 2 INpending records to appear in this query along with the other 10 NOTpending records, which means I need the 2 NOTpending records that match the INpending to be dropped somehow.
Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL Syntax error in JOIN statement

Posted 26 August 2011 - 09:17 AM

The t.timeID != tp.TimeId is quirky and many not be doing what you want.

Perhaps:
SELECT (case when b.TimeId is null then 'NOTpending' else 'INpending' end)  AS type,
		a.TimeId, a.TimeJob, a.TimeBilled, a.JobId
	FROM (
		SELECT 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')
			WHERE t.TimeBilled = 'no' 
			GROUP BY t.TimeId
		) a
		LEFT OUTER JOIN (select distinct TimeId from TimeSheetPending) b
			ON a.TimeId = b.TimeId


Was This Post Helpful? 1
  • +
  • -

#12 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 26 August 2011 - 09:41 AM

Back to the same error in the subquery:
#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 'SELECT t.TimeId , t.TimeJob , t.TimeBilled , j.JobId FROM Time
Was This Post Helpful? 0
  • +
  • -

#13 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: SQL Syntax error in JOIN statement

Posted 26 August 2011 - 09:47 AM

Huh?!? Can't believe I missed this:
SELECT t.TimeId, t.TimeJob, t.TimeBilled, j.JobId
-- ...
GROUP BY t.TimeId



Yeah, that's not going to fly. Why the GROUP BY?
Was This Post Helpful? 1
  • +
  • -

#14 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 26 August 2011 - 09:58 AM

At some point, duplicates were showing when I ran this query so I added that to avoid seeing those results, not a good practice? I removed it from your sql and the same error occurs.
Was This Post Helpful? 0
  • +
  • -

#15 bradley1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-August 11

Re: SQL Syntax error in JOIN statement

Posted 26 August 2011 - 10:59 AM

Holy crap, I think I've got it :)... thanks to you! The following query is returning the proper records, I'm going to run through some more tests, but I think this will be the working solution. Much obliged, thank you!

SELECT (case when b.TimeId is null then 'NOTpending' else 'INpending' end),  a.TimeId, a.TimeJob, a.TimeBilled, a.TimeJob, b.TimeId
	FROM TimeSheet a
				INNER JOIN Jobs j ON j.JobId = a.TimeJob AND j.JobId IN ('2226')
				LEFT OUTER JOIN TimeSheetPending b
				ON a.TimeId = b.TimeId 
				WHERE a.TimeBilled = 'no'


Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2