Jump to content


Photo

LEFT JOINS and regular joins


  • Please log in to reply
9 replies to this topic

#1 gterre

gterre
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 17 September 2006 - 01:01 AM

I'm trying to do a select query with a left join and regular join and keep getting a mysql error. Is this even possible?

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 17 September 2006 - 01:45 AM

I'm trying to do a select query with a left join and regular join and keep getting a mysql error. Is this even possible?

It's possible. Although, the results may not be what you expect. Post the query and the error.

#3 gterre

gterre
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 17 September 2006 - 01:52 AM

$result = @mysql_query('SELECT TT_number, datetimeend, service, MINUTE(datetimestart) AS startminute, HOUR(datetimestart) AS starthour, DAYOFMONTH(datetimestart) AS startday, MONTH(datetimestart) AS startmonth, YEAR(datetimestart) AS startyear, category, status, priority.order, priority, description, datetimestart FROM troubleticket, priority, category, services WHERE TT_number="'.$tt.'" LEFT JOIN troubleticket ON lawid=law.lawid AND troubleticket.pid=priority.pid AND troubleticket.catid=category.catid AND services.sid=troubleticket.sid ');

$row = mysql_fetch_array($result);


error is:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\NetTicket\editticket.php on line 16


I want to join my law table to my troubleticket table, my trouble ticket table is the main table.


#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 17 September 2006 - 01:57 AM

Change the mysql_query call to the following and post the query and error shown.

$query = 'SELECT TT_number, datetimeend, service, MINUTE(datetimestart) AS startminute, HOUR(datetimestart) AS starthour, DAYOFMONTH(datetimestart) AS startday, MONTH(datetimestart) AS startmonth, YEAR(datetimestart) AS startyear, category, status, priority.order, priority, description, datetimestart FROM troubleticket, priority, category, services WHERE TT_number="'.$tt.'" LEFT JOIN troubleticket ON lawid=law.lawid AND troubleticket.pid=priority.pid AND troubleticket.catid=category.catid AND services.sid=troubleticket.sid ';
$result = mysql_query($query) or die($query."<br />\n".mysql_error());


#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 17 September 2006 - 02:00 AM

One error that I now see is that you have a LEFT JOIN coming after your WHERE clause. The WHERE should come after all the JOINS.


#6 gterre

gterre
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 17 September 2006 - 02:06 AM

i get the same error.

#7 gterre

gterre
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 17 September 2006 - 02:08 AM

but the query still works if i was to take the left join out, which means that the where clause came before the other joins but it still works... I will try moving the where clause to the end and see what happens

#8 gterre

gterre
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 17 September 2006 - 02:11 AM


ok i moved the where clause to the end of the query and it still doesn't work.. Do i need to declare my law table in the from clause before declaring the join?

#9 gterre

gterre
  • Members
  • PipPip
  • Member
  • 27 posts

Posted 17 September 2006 - 02:13 AM

that doesn't work either

#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 17 September 2006 - 02:18 AM

Using the following syntax to do the joins should make things clearer.
SELECT
col1, col2 ...
FROM
table1
INNER JOIN
table2
ON
table1.col = table2.col
INNER JOIN
table3
ON
table2.col = table3.col
LEFT JOIN
table4
ON table3.col = table4.col
...
...
WHERE
...

If you're still having trouble post the query you're currently using in addition to the error shown when you use the method shown in the earlier post to make the mysql_query call.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users