HELP- MySQL Query

[Ik]

Hi guys, I need your help generating a mySQL query.

I have the following DB tables:

AUTHORS:
- Id
- Name

PRODUCTS:
- Id
- AuthorId
- Title

ORDERS:
- OrderId
- CustomerId

ORDERDETAILS:
- OrderId
- ProductId
- Price

What I need to do is to generate ONE single query so that I can display the results on a web page like this:
1- Author's Name (from the authors table)
2- Product title (from the products table)
3- Number of purchases made for this product
4- The total sales for this product

Pls help ASAP

 

[pixelbypixel]

Do you not have any link between Author and Product? How will you know which Author belongs to which Product? Without that link you will not be able to get that information..

I would suggest putting an AuthorID field in the PRODUCTS table which contains the ID of the Author.

If you do have a link somewhere that's not clear could you let me know?

 

[Ik]

Do you not have any link between Author and Product? How will you know which Author belongs to which Product? Without that link you will not be able to get that information..

I would suggest putting an AuthorID field in the PRODUCTS table which contains the ID of the Author.

If you do have a link somewhere that's not clear could you let me know?

Hey, yes it's there in the products table but I forgot to mention it. I will update my post now.

What do you suggest? Thanks

 

[jdk]

Why does it have to be one query and not two?

 

[Ik]

Why does it have to be one query and not two?

Hi,

I'm going to generate a report, so I thought it had to be one query.

 

[pixelbypixel]

This is a quick example in PHP using an SQL statement. I have tested the SQL in sqlyog and it seems to work (mind you I only entered about 4 sales, so I can't guarantee it'll work). It might work for you, if not, i apologise, I just did it quickly.

First off, 'authors' is a reserved word, so for the example below i renamed it to 'authors2'

$sql = "SELECT products.*, authors2.*, orderdetails.ProductId, orderdetails.Price FROM authors2, orderdetails, products WHERE products.AuthorId = authors2.Id AND orderdetails.ProductId = products.Id ORDER BY products.title"
$result = mysql_query($sql);
$purchases = 0;
$sales = 0;
$lastTitle = "";
$lastAuthor = "";

while($row = mysql_fetch_array($result))
{
	

	if($lastTitle!="")
	{
		if($lastTitle == $row['Title'])
		{
			$sales += $row['Price'];
			$puchases++;
		}
		else
		{
			echo "1- ".$lastAuthor."<br />";
			echo "2- ".$lastTitle."<br />";
			echo "3- ".$purchases."<br />";
			echo "4- ".$sales."<br /><hr />";
			$purchases = 1;
			$sales = $row['Price'];
		}
	}
	else
	{
		$sales += $row['Price'];
		$purchases++;
	}
	
	$lastAuthor = $row['Name'];
	$lastTitle = $row['Title'];

}

Untested, let me know if it works/any problems..

This also assume 'Title' is unique to an author, as in Two authors can't have products with the same Title.

 

[Ik]

Hey Storm,

The script works, but 2 problems:

1- The author name repeats, can we group by author?
2- Can we sort by sales desc?

Thanks

 

[pixelbypixel]

The author name will repeat, if they are the author of more than 1 product..

Can you send me a PM with some example output that is repeating?

 

[Ik]

Thanks man, I got it to work. Here is the query:

SELECT products.*, sum(orderdetails.price) as sales, count(orderdetails.orderid) as purchases FROM authors, orders, orderdetails, products WHERE products.authorid = authors.id AND orderdetails.productid = products.id AND orders.orderid = orderdetails.orderid GROUP BY products.title ORDER BY sales desc