Sign in to follow this  
Jaguar_Lee

[web] LEFT-JOIN in MYSQL

Recommended Posts

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....

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this