Left Join WHERE

Databases
1

Hi all, I’m having a few problems! I have two tables: orders - contains details of the customer such as their name, address, delivery address, receipt number, etc.

The other table: order_products - contains the product details of their order such as the item, price, serial number, etc.

I have created a LEFT JOIN which seems to be working, kind of anyway. I can’t get the details to match the id= in the URL. Any ideas as to how this can be achieved? My query is below:


$url = $_GET['id'];
$recall = mysql_query("SELECT * FROM orders LEFT JOIN order_products ON orders.receipt_id=order_products.receipt_id AND orders.cust_ref=order_products.customer_id AND orders.cust_ref='$url'");
	$ready = mysql_fetch_array($recall);
		echo ''.$ready['del_method'].'';
2

My initial thoughts are that you’re going to have to sort out some of the relationships in your schema. :slight_smile:

Are you wanting to ‘bodge’ it up, or are you open to advice here?

3

Hey dude, sure thing, open to advice, only way to learn from my mistakes! Fire away bud :smiley:

4

Is $url the order id?

5

Yeah it is :slight_smile:

6

You’ll have to bear with me, SQL doesn’t come naturally to me. :stuck_out_tongue:

I’ve tested the following, mainly because of the former caveat, but it can do doubt be improved.

Let’s say we have this…

You should be able to get what you need with…


SELECT
    o.id,
    o.created,
    o.status,
    ol.product_id,
    ol.product_value,
    ol.product_quantity,
    c.fname,
    c.sname
FROM
    orderline ol
LEFT JOIN
    `order` o ON o.id = ol.order_id
LEFT JOIN
    `customer` c ON c.id = o.customer_id
WHERE
  o.id = 1

7

Hey Anthony, that’s done the trick perfectly! Thank you so much, you really go out of your way to help, I really do appreciate it bud. If your ever down in Devon I owe you a pint! :cool:

It’s good to understand how to do things the best way :slight_smile:

8

can i make a comment?

suppose you do what the title of this thread suggests…


  FROM orderline ol
LEFT OUTER
  JOIN `ORDER` o 
    ON o.id = ol.order_id
 WHERE o.id = 1

the problem with this construction in general is that the WHERE condition destroys the “left-outer-joinedness” of the join

any row of the left table which had no matching row in the right table will be thrown away, because the NULL that is entered into all the columns of the right table will never meet the WHERE condition

hence it operates as an inner join

that’s the problem with using a WHERE clause on any columns of the right table in a left outer join

having said all that, lets take a closer look at these particular tables

under what circumstances would you expect to find a row in the orderlines table that had no matching row in the order table?

never, right? (unless the app itself is broken)

so it should actually be written as an INNER JOIN from the get-go

make sense?

9

Cheers Rudy!

So, if I’m following you correctly; we should use an INNER JOIN because a LEFT JOIN is implicitly designed to ‘throw away’ any non-matching rows but we’ll never have any anyways?

Thanks again Rudy.

10

close but not quite

a LEFT OUTER JOIN is implicitly designed to keep non-matching rows