Help with nested SQL statement - Probably Very Easy?

I have two tables.  One is called popularity.  Table popularity contains two fields:  PopularID, which is simply an ascending number system, where 1 = the best.  The second field is ProductID, which is primary key in the product table.  The productID number will give me the Name of product from the product table.

I think I've given all the information needed here.  I hope I did.  If you have any questions, please ask.  And THANK YOU all in advance.
mcgeorge40Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please provide more details as to exactly what are you trying to do here.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with jimhorn: you described what you have, but not what you want to get returned as data?
0
mcgeorge40Author Commented:
Okay.  Sorry.  What I want to do is display a top ten list of flower products.

So I need to display the numbers 1 through 10, from the popular table.  The PopularID is a unique ID that starts with 1 and goes to whatever.  These are the most popular flowers.  Also in this table is the ProductID.

With the ProductID, I can query the product table and actually display the name of the number one flower, next to its rank (aka PopularID).

I started to to something, but I think it's very bulky and wrong.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
how do you identify the "product" of type flowers? I assume you have some other "kind" of products?
0
mcgeorge40Author Commented:
yes, but that's not important.  I wrote flowers, but I should have written gifts.

So I'll try to do an even better job of explaining.  I am probably screwing this up pretty good, because it is probably a simple join query.  I think...  I've been out of school a while.

I want to display a top ten list of products.  The first task is displaying the numbers one through ten.  This is the PopularId, the primary key in the popular table.  So, you know:  Select PopularId, ProductID from popular LIMIT 10

Then, I have to attach the names of the products next to the numbers 1 through 10.  (Remember, the numbers are simply from most to least popular from the popular table.)

To get the names of the product too line up next to their PopularID, I have to Select Name from product where ProductId = value from previous query.

Is that better?  I am sorry.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's see:

select top 10 po.popularid, po.productid, pr.name
from popularity po
join product pr
  on pr.productid = po.productid
order by po.PopularID asc

Open in new window

0
Jinesh KamdarCommented:

SELECT A.popularid, B.name
FROM popularity A, products B
WHERE A.productid = B.id
AND A.popularid <= 10
ORDER BY A.popularid;

Open in new window

0
Jinesh KamdarCommented:
I guess i got it wrong. By Top 10, u meant 10 products having the best popularity rating or all products having a popularity rating of 10 or less? In any case, is it possible that 2 or more products can have the same popularity rating?
0
mcgeorge40Author Commented:
The PopularId is the primary key of the popular table.  No 2 products can have the same PopularID (or popularity rating).

So there is only one "number one" and that has a PopularID of 1, and a ProductID to match to "number one."  Then we query the product table with the ProductId to get the name of the product, maybe even a photo.

0
mcgeorge40Author Commented:
angellll, tried it, but got an error.  

See anything wrong?

$query3 = "select top 10 po.PopularId, po.ProductId, pr.Name
from popular po
join product pr
on pr.ProductId = po.ProductId
order by po.PopularID asc";
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what error?`
0
Jinesh KamdarCommented:
In that case, TOP 10 is not required and my query should work. Can u pls try it?
0
mcgeorge40Author Commented:
jinesh kamdar-- I am sorry I've been away.  I was sick.

Your SQL statement runs without error.  However, I cannot get data to display.

Would you mind looking at this code?

$link = mysql_connect($DatabaseServer, $Username, $Password) or die('Could not connect: ' . mysql_error());

mysql_select_db($DatabaseName) or die('Could not select database');

$ustring = $_GET['ProductID'];


$query = "SELECT * FROM product WHERE ProductID = '" . mysql_real_escape_string($ustring) . "'";
$result = mysql_query($query, $link) or die(mysql_error() . " in $query");


$query2 = "SELECT ProductID, Name, ThumbNailImage, Price, Type FROM product WHERE Pcategories LIKE '%99%' ORDER BY rand() LIMIT 0,1";
$result2 = mysql_query($query2, $link) or die(mysql_error() . " in $query2");

$query3 = "SELECT A.PopularId, B.Name
FROM popular A, product B
WHERE A.ProductId = B.ProductId
AND A.PopularId <= 10
ORDER BY A.PopularId";



$result3 = mysql_query($query3, $link) or die(mysql_error() . " in $query3");


// Retrieve the recordset

   while ($row = mysql_fetch_assoc($result)){
         $flowers[] = $row;
   

   while ($row2 = mysql_fetch_assoc($result2)){
         $flowers2[] = $row2;
   

   while ($row3 = mysql_fetch_assoc($result3)){
         $flowers3[] = $row3;
}
}
}
?>


Throughout the page, I can display results from the first query with $flowers[0]["Name"].

The second query ($query2) result can be displayed with  $flowers2[0]["Name"].

But  $flowers3[0]["Name"] does not give me anything.  The query seems to work, (no errors), but the row data seems to be empty.  

Any idea why?

Thank you in advance.
0
Jinesh KamdarCommented:
I'm not sure if this solves the problem, but try writing it in same way that other queries are written.
$query = "SELECT * FROM product WHERE ProductID = '" . mysql_real_escape_string($ustring) . "'";
 
$result = mysql_query($query, $link) or die(mysql_error() . " in $query");
 
$query2 = "SELECT ProductID, Name, ThumbNailImage, Price, Type FROM product WHERE Pcategories LIKE '%99%' ORDER BY rand() LIMIT 0,1";
 
$result2 = mysql_query($query2, $link) or die(mysql_error() . " in $query2");
 
$query3 = "SELECT A.PopularId, B.Name FROM popular A, product B WHERE A.ProductId = B.ProductId AND A.PopularId <= 10 ORDER BY A.PopularId";
 
$result3 = mysql_query($query3, $link) or die(mysql_error() . " in $query3");

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcgeorge40Author Commented:
Thank you very, very much.
0
Jinesh KamdarCommented:
Glad to be of help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.