Jump to content

Archived

This topic is now archived and is closed to further replies.

gterre

LEFT JOINS and regular joins

Recommended Posts

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?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
$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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.