SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple Join Issue

    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

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,034
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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(); 

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please try it with ANDs in the ON clause, not in the WHERE clause, like i had it in post #5

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It didn't work, and RIGHT OUTER JOIN started pulling in tanks that were for the wrong car.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks…and an inner join is what again?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here's an article which describes the various types of join --> http://www.sitepoint.com/article/sim...e-from-clause/

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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???????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Attached Files Attached Files

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, but the sequence in the txt file you attached is actually correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •