Simple Join Issue

**geiger** · Mar 7, 2009 22:11

I have a small issue with the following query. What I'm trying to do is select a row in fe_cars (there is always 1 and 1 only) and any rows in fe_tanks (there may be 0 or more). If there is at least 1 tank in fe_tanks, everything works fine. If there isn't, however, the query doesn't select the row in fe_cars either. How can I ensure the fe_cars row is selected even if fe_tanks returns no rows?

Code MySQL:

SELECT tid, fe_tanks.in_miles, in_gallons, fe_tanks.in_mpg, fe_tanks.calc_gallons, calc_mpg, cpg,
	fe_cars.in_miles AS start_miles, fe_cars.in_mpg AS start_mpg, fe_cars.calc_gallons AS start_gallons
	FROM fe_tanks
	JOIN fe_cars ON fe_tanks.cid=fe_cars.cid
	WHERE fe_tanks.deleted='0' AND fe_tanks.cid='$cid'
	ORDER BY fe_tanks.tid DESC

**SpacePhoenix** · Mar 7, 2009 23:20

I think this is what your looking for.

Code SQL:

SELECT
    tid
    , fe_tanks.in_miles
    , in_gallons
    , fe_tanks.in_mpg
    , fe_tanks.calc_gallons
    , calc_mpg
    , cpg
    , fe_cars.in_miles AS start_miles
    , fe_cars.in_mpg AS start_mpg
    , fe_cars.calc_gallons AS start_gallons
FROM
    fe_tanks
RIGHT JOIN
    fe_cars
ON
    fe_tanks.cid=fe_cars.cid
WHERE
    fe_tanks.deleted='0'
AND
    fe_tanks.cid='$cid'
ORDER BY
    fe_tanks.tid DESC

If that does not work, then it will be:

Code SQL:

SELECT
    tid
    , fe_tanks.in_miles
    , in_gallons
    , fe_tanks.in_mpg
    , fe_tanks.calc_gallons
    , calc_mpg
    , cpg
    , fe_cars.in_miles AS start_miles
    , fe_cars.in_mpg AS start_mpg
    , fe_cars.calc_gallons AS start_gallons
FROM
    fe_tanks
LEFT JOIN
    fe_cars
ON
    fe_tanks.cid=fe_cars.cid
WHERE
    fe_tanks.deleted='0'
AND
    fe_tanks.cid='$cid'
ORDER BY
    fe_tanks.tid DESC

**r937** · Mar 8, 2009 05:31

spacephoenix, you're absolutely right -- it's either a LEFT OUTER JOIN or a RIGHT OUTER JOIN !!!

but which table is the outer table?

the easy way to remember is that the outer table is the one you want all the rows of, whether or not the other table has any matching rows

so the correct answer here is FROM fe_tanks RIGHT OUTER JOIN fe_cars

another correct answer is FROM fe_cars LEFT OUTER JOIN fe_tanks

and one of your answers is wrong

**geiger** · Mar 8, 2009 17:47

Hm. Okay, well still neither is working.

Code SQL:

	SELECT
		tid,
		in_gallons,
		calc_mpg,
		cpg,
		fe_tanks.in_miles,
		fe_tanks.in_mpg,
		fe_tanks.calc_gallons,
		fe_cars.in_miles AS start_miles,
		fe_cars.in_mpg AS start_mpg,
		fe_cars.calc_gallons AS start_gallons
	FROM
		fe_tanks RIGHT OUTER JOIN fe_cars ON fe_tanks.cid=fe_cars.cid
	WHERE
		fe_tanks.deleted='0' AND fe_tanks.cid='$cid'
	ORDER BY
		fe_tanks.tid DESC

A simple test within the fetch loop fails to execute as expected.

PHP Code:


while ($row=mysql_fetch_array($sql))
{
    echo "test"; die();

**r937** · Mar 8, 2009 18:38

try it like this:

Code:

  FROM fe_cars 
LEFT OUTER 
  JOIN fe_tanks  
    ON fe_tanks.cid = fe_cars.cid    
   AND fe_tanks.deleted = 0
   AND fe_tanks.cid = $cid

you mentioned that "there may be 0 or more" fe_tanks, so when this query returns an fe_car without a matching fe_tank, you realize that fe_tanks.tid will be NULL, right? are you sure you want to sort by that column?

**geiger** · Mar 8, 2009 18:45

Hmm, good point. The ORDER BY was actually unnecessary altogether.
In your suggestion above I assume your forgetting the WHERE was a mistake?

I tried the following and I think it worked, though I'm doing more tests. Is this proper syntax?

Code MySQL:

SELECT
		tid,
		in_gallons,
		calc_mpg,
		cpg,
		fe_tanks.in_miles,
		fe_tanks.in_mpg,
		fe_tanks.calc_gallons,
		fe_cars.in_miles AS start_miles,
		fe_cars.in_mpg AS start_mpg,
		fe_cars.calc_gallons AS start_gallons
	FROM
		fe_cars LEFT OUTER JOIN fe_tanks ON fe_tanks.cid=fe_cars.cid 
	WHERE
		(fe_tanks.deleted!=1 AND fe_tanks.cid='$cid') OR fe_cars.cid='$cid'

**r937** · Mar 8, 2009 18:52

please try it with ANDs in the ON clause, not in the WHERE clause, like i had it in post #5

**geiger** · Mar 8, 2009 20:01

It didn't work, and RIGHT OUTER JOIN started pulling in tanks that were for the wrong car.

**r937** · Mar 8, 2009 20:07

would you kindly please show me the last query you ran that did not work

i need to ask because i have seen eleventy-seven variations i this thread so i can't say yet what's causing your issue

**geiger** · Mar 8, 2009 20:15

Code MySQL:

SELECT
		tid,
		in_gallons,
		calc_mpg,
		cpg,
		fe_tanks.in_miles,
		fe_tanks.in_mpg,
		fe_tanks.calc_gallons,
		fe_cars.in_miles AS start_miles,
		fe_cars.in_mpg AS start_mpg,
		fe_cars.calc_gallons AS start_gallons
	 FROM fe_cars 
LEFT OUTER 
  JOIN fe_tanks  
    ON fe_tanks.cid = fe_cars.cid    
   AND fe_tanks.deleted = 0
   AND fe_tanks.cid = $cid

When I ran this it selected tanks that didn't have the CID.

**r937** · Mar 8, 2009 20:19

try it like this, please --

Code:

  FROM fe_cars 
LEFT OUTER 
  JOIN fe_tanks 
    ON fe_tanks.cid = fe_cars.cid  
   AND fe_tanks.deleted = 0   
 WHERE fe_cars.cid = $cid

**geiger** · Mar 8, 2009 20:27

Hey, it worked! Thanks so much.

Can you please explain what you did? I don't understand why .deleted=0 is part of the JOIN syntax.

**r937** · Mar 9, 2009 06:22

okay, here's how it works

with two tables in a LEFT OUTER JOIN, you should get unmatched rows from the left table, where there will be a row in the result with data in the columns that come from the left table, and NULLs in all the columns that come from the right table

conditions for the left table belong in the WHERE clause, while conditions for the right table belong in the ON clause of the join

to demonstrate the difference, run these two versions of the query --

Code:

  FROM fe_cars 
LEFT OUTER 
  JOIN fe_tanks 
    ON fe_tanks.cid = fe_cars.cid  
   AND fe_tanks.deleted = 0   
 WHERE fe_cars.cid = $cid

Code:

  FROM fe_cars 
LEFT OUTER 
  JOIN fe_tanks 
    ON fe_tanks.cid = fe_cars.cid  
 WHERE fe_cars.cid = $cid
   AND fe_tanks.deleted = 0

see if you can spot the difference in the results -- with the first query, there will be unmatched rows, but with the second, it will be as if it were an inner join, as no unmatched rows will be returned

**geiger** · Mar 9, 2009 12:28

Thanks…and an inner join is what again?

**r937** · Mar 9, 2009 12:36

here's an article which describes the various types of join --> http://www.sitepoint.com/article/sim...e-from-clause/

**geiger** · Mar 9, 2009 18:07

Thanks. I have one more quick question if you don't mind. I actually do need to sort the tanks, because if there are some I'm using the query to generate a chart. When I add the following to the end, (DESC) it sorts fine. But if I switch it to ASC, it orders seemingly randomly. Why is this happening?

Code MySQL:

ORDER BY fe_tanks.enddate DESC

Code MySQL:

SELECT
		tid,
		in_gallons,
		calc_mpg,
		cpg,
		fe_tanks.in_miles,
		fe_tanks.in_mpg,
		fe_tanks.calc_gallons,
		fe_cars.in_miles AS start_miles,
		fe_cars.in_mpg AS start_mpg,
		fe_cars.calc_gallons AS start_gallons
		FROM fe_cars LEFT OUTER JOIN fe_tanks ON fe_tanks.cid = fe_cars.cid AND fe_tanks.deleted = 0   
		WHERE fe_cars.cid = $cid
		ORDER BY fe_tanks.enddate ASC

**r937** · Mar 9, 2009 20:16

are you seeing the NULLs first? or last?

after all, you ~did~ want a LEFT OUTER JOIN, so some result rows won't have an fe_tanks.enddate

oh, and by the way, if you're going to sort on a particular column, you really should include that column in the SELECT clause so that you can actually see the sequence

**geiger** · Mar 9, 2009 21:03

Nulls are not the problem, and I'm filtering them out anyway now by using

PHP Code:


if (!empty($row['enddate']))
    {

The problem is that when I use ASC, it's coming out in a random order.

Code MySQL:

SELECT
		enddate,
		tid,
		in_gallons,
		calc_mpg,
		cpg,
		fe_tanks.in_miles,
		fe_tanks.in_mpg,
		fe_tanks.calc_gallons,
		fe_cars.in_miles AS start_miles,
		fe_cars.in_mpg AS start_mpg,
		fe_cars.calc_gallons AS start_gallons
		FROM fe_cars LEFT OUTER JOIN fe_tanks ON fe_tanks.cid = fe_cars.cid AND fe_tanks.deleted = 0
		WHERE fe_cars.cid = $cid
		ORDER BY fe_tanks.enddate ASC

I'm using this query in part to construct a cached chart. The rest of the page below is generated with a similar query.

Code MySQL:

SELECT tid, enddate, in_miles, in_gallons, in_mpg, calc_gallons, calc_mpg, notes, cpg, fueltype, strategy
		FROM fe_tanks
		WHERE deleted='0' AND cid='$cid'
		ORDER BY enddate DESC

http://www.myhybridcar.com/fuel-econ..._car.php?cid=1
username: sitepoint
password: temporary

If you hover over the points, you can see that the last few points on the graph are not the same as the first few points on the tank table. (The first 2 are correct, the next2 are not.) Do you see what I mean?

**r937** · Mar 9, 2009 21:21

that's an absolutely gorgeous chart, how did you build it? how does the hover work?

anyhow, about your sorting...

please put fe_tanks.enddate into the SELECT clause, and show me the raw data results (not via php, but directly from mysql)

and about the nulls...

we went through a lot of trouble to make sure we had the right LEFT OUTER JOIN, and now you're saying your php logic ignores the rows where there's no fe_tank???????

**geiger** · Mar 9, 2009 21:52

Thanks about the charts. It's XML/SWF http://www.maani.us/xml_charts/

The output is attached. It's a bit confusing, but basically the chart uses in_mpg or calc_mpg if in_mpg doesn't exist.

About the nulls, no that's not what I meant. Sorry I was unclear. The only time when I filter out the nulls is when I'm building an array of the MPGs, which is used in the chart.

Code PHP:

while ($row=mysql_fetch_array($sql))
{
	if (!empty($row['enddate']))
	{
		if ($in_mpg>0)
		{
			$mpg=$in_mpg;
		}
		else
		{
			$mpg=$calc_mpg;
		}
 
		array_push($car_miles, $miles);
		array_push($car_mpg, $mpg);
		array_push($car_gallons, $gallons);
	}
// other code
}

$car_mpg is below.

PHP Code:


Array
(
    [0] => 45
    [1] => 47.3
    [2] => 44.5
    [3] => 44.5
    [4] => 44.5
    [5] => 44.5
    [6] => 44.5
    [7] => 49.6
    [8] => 49.1
    [9] => 48.9
    [10] => 50.2
    [11] => 50.5
    [12] => 52.3
    [13] => 48.2
    [14] => 47.4
    [15] => 39.9
    [16] => 47
    [17] => 41.6
    [18] => 45.2
    [19] => 46.1
    [20] => 46.6
    [21] => 48.9
    [22] => 44.2
    [23] => 45.7
    [24] => 45.1
    [25] => 45.1
    [26] => 41.7
    [27] => 41.3
    [28] => 48.8
    [29] => 45.1
    [30] => 46.5
    [31] => 46.1
    [32] => 46.8
    [33] => 47.1
    [34] => 49.5
    [35] => 47.8
    [36] => 41.6
    [37] => 45
    [38] => 32.7
    [39] => 45
    [40] => 45
    [41] => 34.7
    [42] => 32.1
)

**r937** · Mar 9, 2009 22:41

i'm sorry, but the sequence in the txt file you attached is actually correct

**geiger** · Mar 9, 2009 23:01

OHHH you know what it is? There are duplicate enddates! I'm sorting by enddate, tid now and it works just fine. Thanks for all your help

User Tag List

Thread: Simple Join Issue

Thread Tools

Display

Simple Join Issue

Bookmarks

Bookmarks

Posting Permissions