[web] LEFT-JOIN in MYSQL
Author
now we have three table:
client(cle_client,client_name),
shopping-list(cle_shopping_liste,cle_client,cle_product,date)
product(cle_product, product_name)
what the custoemr expects is that:
display a table with two columns
one is about the client, the other is about the product that this client bought recently
even though the client do not buy any product, his name must be also displayed.
therefore, what my sql query is :
select distinct cle_client, client_name, product.product_name from client
left join shopping_liste on shopping_liste.cle_client = client.cle_client
left join product on shopping_liste.cle_product = product.cle_product
but the problem is,
this sql will always get the oldest infomation in shopping-liste. that means, the product fetched out, is always the fisrt product this client bought
oh.... cle = id in french
do any of you have ideas about that? Thx very very much....
I'm not sure you can do that, exactly... You could instead nest a SELECT query on another SELECT from shopping-list left joined with product, order by date DESC, with the outer SELECT query distinct on cle_client. Since this will only get you the clients that have purchased something, you will have to UNION this with the query you have, WHERE cle_product IS NULL
Why do you have to join the two tables? If all you're displaying is the clients name and his most recent purchase, then just echo his name, and pull his most recent purchase with a single query.
EDIT: Ok, nevermind. You have it set up a little differently than I first thought. What you probably want to do is this:
Select * from client join `shopping-list` on (client.cle_client = `shopping-list`.cle_client) join product on (`shopping-list`.cle_product = product.cle_product) order by `shopping-list`.date desc limit 0,1
This query should return all the information that I've seen you looking for (ie, clients name and his most recent purchase).
[Edited by - Cygnus_X on December 12, 2006 8:24:02 AM]
EDIT: Ok, nevermind. You have it set up a little differently than I first thought. What you probably want to do is this:
Select * from client join `shopping-list` on (client.cle_client = `shopping-list`.cle_client) join product on (`shopping-list`.cle_product = product.cle_product) order by `shopping-list`.date desc limit 0,1
This query should return all the information that I've seen you looking for (ie, clients name and his most recent purchase).
[Edited by - Cygnus_X on December 12, 2006 8:24:02 AM]
Cygnus_X: that query will pull all shopping list items and the clients they are associated with, which is fine except that any clients that haven't purchased something won't show up in the results, which is part of what jaguar_Lee wants to get, which is why I suggested that you UNION your query with something that will give you all the clients that haven't ordered something.
So, I think the final query will be:
(Select * from client join `shopping-list` on (client.cle_client = `shopping-list`.cle_client) join product on (`shopping-list`.cle_product = product.cle_product) order by `shopping-list`.date desc limit 0,1)
UNION
(SELECT cle_client, client_name, product.product_name FROM client LEFT JOIN shopping-list USING (cle_client) WHERE shopping-list.cle_client IS NULL)
So, I think the final query will be:
(Select * from client join `shopping-list` on (client.cle_client = `shopping-list`.cle_client) join product on (`shopping-list`.cle_product = product.cle_product) order by `shopping-list`.date desc limit 0,1)
UNION
(SELECT cle_client, client_name, product.product_name FROM client LEFT JOIN shopping-list USING (cle_client) WHERE shopping-list.cle_client IS NULL)
Quote:Original post by Jaguar_Lee
now we have three table:
client(cle_client,client_name),
shopping-list(cle_shopping_liste,cle_client,cle_product,date)
product(cle_product, product_name)
what the custoemr expects is that:
display a table with two columns
one is about the client, the other is about the product that this client bought recently
even though the client do not buy any product, his name must be also displayed.
therefore, what my sql query is :
select distinct cle_client, client_name, product.product_name from client
left join shopping_liste on shopping_liste.cle_client = client.cle_client
left join product on shopping_liste.cle_product = product.cle_product
but the problem is,
this sql will always get the oldest infomation in shopping-liste. that means, the product fetched out, is always the fisrt product this client bought
oh.... cle = id in french
do any of you have ideas about that? Thx very very much....
select cle_client, client_name, product.product_name from client
left join shopping_liste on shopping_liste.cle_client = client.cle_client
left join product on shopping_liste.cle_product = product.cle_product
order by shopping_liste.date desc limit 1
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
