[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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.
0
mcgeorge40
Asked:
mcgeorge40
  • 6
  • 5
  • 4
  • +1
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
mcgeorge40Author Commented:
Thank you very, very much.
0
 
Jinesh KamdarCommented:
Glad to be of help :)
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now