MySQL query.

[Jazztronik]

Please I need your help to create a SELECT query that retrieves the information of all the fields underlined in red in the diagram below.

http://i7.photobucket.com/albums/y280/Jazztronik/SQL-1.jpg

- A row in ad-pepper might have, in customers, no coincidence, or just one.

- A customer may have 0, 1 or more orders. An order is made by a customer only.

- Each order has three orders_total, but I have to retrieve the value of the one in orders_total whose field type is type="ot_total".

I hope the diagram and the explanations help you understand what I want to get.

[NogDog]

I think something like this is what you're after:

SELECT
o.date_purchased,
ot.value,
c.customers_firstname,
c.customers_lastname,
ap.email,
ap.phone
FROM
orders AS o
INNER JOIN orders_total AS ot ON o.orders_id = ot.orders_id
INNER JOIN customers AS c ON o.customers_id = c.customers_id
LEFT JOIN ad_pepper AS ap ON c.customers_email_address = ap.email
WHERE ot.type = 'ot_total'

[Jazztronik]

That's not exactly what I want NogDog because I forgot to mention some more info. Out of all those results, I just want to list the orders made by the users whose mail is in ad_pepper

Your query retrieves all the orders having that type="ot_total", and fields email and phone are always NULL. These rows aren't important for me.

[NogDog]

So just add an "AND o.customers_id=<xxx>" to the end of the WHERE clause, where "<xxx>" is replaced with the ID of interest. Without knowing what programming language and database API you are using, I don't know exactly how you would want to enter the ID into the query string.)

[Jazztronik]

So just add an "AND o.customers_id=<xxx>" to the end of the WHERE clause, where "<xxx>" is replaced with the ID of interest. Without knowing what programming language and database API you are using, I don't know exactly how you would want to enter the ID into the query string.)

I think that would be good in case I wanted to retrieve the orders made only by one person. But what I want is to get all the "ot_total" orders made by the people who is registered (their email) in the table ad_pepper

I'm using MySQL 5 programmed with PHP 5.

[NogDog]

Sorry, I guess I misread your previous post. If you only want results for people with ad_pepper entries, change that "LEFT JOIN" to another "INNER JOIN".

[Jazztronik]

That's it! You got it! Thank you very much NogDog! :) :)

By the way, do you know about any website or tutorial for dummies with very easy to understand explanations about MySQL and exercises? However much I understand what the MySQL clauses mean, I don't know how to use some of them in practice.:o

[NogDog]

I don't know about MySQL-specific sites, but there are a couple good general database and SQL tutorials at http://www.geekgirls.com/menu_databases.htm that can help you work with pretty much any DBMS.

[Jazztronik]

Thanks again!! I bookmarked that website.

Now another question has come up: I have to group the obtained rows so that every customer appears only once with a total sum of all the orders made by him/her.

I tried to use the GROUP BY clause and sum(ot.value), but I always got a syntax error.

[Jazztronik]

I solved it. I had to add at the end:

GROUP BY ap.email

and change ot.value to sum(ot.value) as val

Now, this is more difficult to me:

How to list from orders, the ones whose customer has made more than one order,

or,

all the customers who have made more than one order.