Page 1 of 1

To JOIN or not to JOIN that is the question

Posted: Thu Jan 24, 2008 11:25 pm
by micknc
I have been reading for hours but I am at a stand still now. Help me learn how to accomplish this:

I have a php page that uses a variable passed by url to display information from a table. I have been using a very simple code for that:

Code: Select all

$id=$_GET['id'];
 
$query="SELECT * FROM lineitm WHERE SO_NO=$id";
I can display the correct data from the table but what I want to do is also display data from a secondary table that corresponds to one of the fields in the first table. Here are the fields that we are concerned with:
Table 1 lineitm:
SO_NO
SHIP_ID
other fields

Table 2 custshp:
SHIP_ID
other fields

I have tried to use JOIN and I can list table 1 and the corresponding ship_id in table 2 but it doesn't limit table one to so_no. Here is the code I am trying to edit to limit table 1 to so_no:

Code: Select all

$query = "SELECT lineitm.QTY,lineitm.DESCRIP, custshp.CITY ".
"FROM lineitm LEFT JOIN custshp ".
"ON lineitm.SHIP_ID = custshp.SHIP_ID";
can I put a WHERE SO_NO=$id statement in here somewhere without getting an SQL error?

Thanks for the advice.

Re: To JOIN or not to JOIN that is the question

Posted: Thu Jan 24, 2008 11:56 pm
by micknc
For those who stumble onto this looking for help I found a very useful site:
http://www.developerfusion.co.uk/show/3998/7/

Here is the final code that worked for me:

$query = "SELECT lineitm.QTY,lineitm.DESCRIP, custshp.CITY, custshp.STATE FROM lineitm, custshp WHERE lineitm.SHIP_ID = custshp.SHIP_ID AND lineitm.SO_NO=$id";

Re: To JOIN or not to JOIN that is the question

Posted: Fri Jan 25, 2008 12:28 pm
by mwasif
The preferred way to write this query is

Code: Select all

SELECT lineitm.QTY,lineitm.DESCRIP, custshp.CITY, custshp.STATE FROM lineitm
INNER JOIN custshp ON lineitm.SHIP_ID = custshp.SHIP_ID 
WHERE lineitm.S_NO=$id

Re: To JOIN or not to JOIN that is the question

Posted: Fri Jan 25, 2008 1:16 pm
by califdon
mwasif wrote:The preferred way to write this query is

Code: Select all

SELECT lineitm.QTY,lineitm.DESCRIP, custshp.CITY, custshp.STATE FROM lineitm
INNER JOIN custshp ON lineitm.SHIP_ID = custshp.SHIP_ID 
WHERE lineitm.S_NO=$id
I would be cautious about saying that JOIN syntax is necessarily the "preferred way," as compared with using the WHERE clause. I'm not an SQL expert, but I believe the difference lies in the way the database engine optimizes the query, and there are situations where one or the other syntax might be more efficient. In any case, it would make a difference primarily with very large datasets.