LEFT JOINS and regular joins

[gterre]

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?

[shoz]

[quote author=gterre link=topic=108352.msg435841#msg435841 date=1158454873]
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?
[/quote]
It's possible. Although, the results may not be what you expect. Post the query and the error.

[gterre]

$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.

[shoz]

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

[code=php:0]
$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());
[/code]

[shoz]

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.

[gterre]

i get the same error.

[gterre]

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

[gterre]

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?

[gterre]

that doesn't work either

[shoz]

Using the following syntax to do the joins should make things clearer.
[code]
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
...
[/code]

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.