coxdabd — 2011-01-14T02:56:28-05:00 — #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);
anthonysterling — 2011-01-14T03:14:38-05:00 — #2
My initial thoughts are that you're going to have to sort out some of the relationships in your schema.
Are you wanting to 'bodge' it up, or are you open to advice here?
coxdabd — 2011-01-14T03:19:03-05:00 — #3
Hey dude, sure thing, open to advice, only way to learn from my mistakes! Fire away bud
anthonysterling — 2011-01-14T03:37:47-05:00 — #4
coxdabd — 2011-01-14T03:39:03-05:00 — #5
Yeah it is
anthonysterling — 2011-01-14T03:44:49-05:00 — #6
You'll have to bear with me, SQL doesn't come naturally to me.
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...
`order` o ON o.id = ol.order_id
`customer` c ON c.id = o.customer_id
o.id = 1
coxdabd — 2011-01-14T04:08:35-05:00 — #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!
It's good to understand how to do things the best way
r937 — 2011-01-14T06:54:57-05:00 — #8
can i make a comment?
suppose you do what the title of this thread suggests...
FROM orderline ol
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
anthonysterling — 2011-01-14T07:03:20-05:00 — #9
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.
r937 — 2011-01-14T07:05:00-05:00 — #10
close but not quite
a LEFT OUTER JOIN is implicitly designed to keep non-matching rows