Displaying records from 2 tables.

[theITvideos]

Hi  there.

 

I am working on 2 tables:

• 
  Supplier
  
• Products
   
  

Every supplier has multiple Products under them. I need to display only 1 Product from every Supplier.

 

What query would be better. I am not very good with queries.

 

Do we use some nested queries to display only 1 Product from every Supplier?

 

Any comment or feedback are always welcomed.

 

Thank you!

 

 

 

[Miss_Rebelx]

What 1 product do you want to display? The newest product? The most popular? Or any random 1 product?

[theITvideos]

What 1 product do you want to display? The newest product? The most popular? Or any random 1 product?

 

Any 1 Product from the supplier. How can we use the query.

 

Thanks!

[kickstart]

Hi

 

You really need to specify which one you want returned

 

Just guessing on column names

 

SELECT a.SupplierName, b.ProductName
FROM Supplier a
INNER JOIN (SELECT SupplierId, Max(ProductId) AS MaxProdId FROM Products GROUP BY SupplierId) b ON a.SupplierId = b.SupplierId
INNER JOIN Products c ON b.MaxProdId = c.ProductId

 

That will get a list of supplier names with a product name for each where the product id is the highest one for that supplier.

 

All the best

 

Keith

[Miss_Rebelx]

My best idea at randomizing a product would be:

 

$query = "SELECT SupplierID FROM Suppliers";
$result = mysql_query($query);

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

  $query2 = "SELECT COUNT(*) FROM Products WHERE SupplierID = '".$row['SupplierID']."';
  $result2 = mysql_query($query2);

  $count = mysql_result($result2, 0);
  $number = rand(0, ($count - 1));

  $query3 = "SELECT Supplier.SupplierName, Products.ProductName FROM Supplier INNER JOIN Products ON Supplier.SupplierId = Products.SupplierId WHERE SupplierID = '".$row['SupplierID']."'";
  $result3 = mysql_query($query3);

  $display = mysql_result($result3, $number);
  echo $display;

}

 

This is assuming also that the table Products and Supplier both have a SupplierID which is the Foreign key linking them together. And also assuming that Suppler has SupplierName, which is what you wanted to display.