CODEWITHSUNDEEP
php
user3121440
user3121440

Reputation: 3

Help with php mysql query adding 3rd table

I need help w/a php query, and will start by explaining the obvious that I'm over my head with this.

The query, which works (pretty much*), randomly selects from 2 tables products with certain criteria. My issue is I need not just the field products.vendorid, but the actual vendor name which is located in another table, table: vendor. So products.vid is the vendor of the product - and I need to go into the vendor table and lookup the fields vendor.company that matches.

I don't know how to introduce this 3rd table in here that's mainly a lookup. I'm trying to keep in within the same recordset and wondering if a subquery is of use here or something else? The query:

SELECT products.pid, products.vendorid, products.is_visible, 
       product_description.compare1, product_description.title
FROM products, product_description 
WHERE products.pid >= (SELECT FLOOR( MAX(pid) * RAND()) FROM `products` ) 
  and ((product_description.compare1 = 'Cohasset' ) 
  and (products.is_visible='Yes') and (products.pid = product_description.pid))
LIMIT 5

To clarify - I need a field called 'vendor.company', in the table 'vendor', which contains vendor id, called 'vid' which matches up with products.vendorid.

Hope this makes sense and I appreciate the help.

*(not 100%: if the random number generated is more than 5 less than the total products but I can live w/that for now)

Upvotes: 0

Views: 63

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

I would use JOIN instead of WHERE, where possible:

SELECT products.pid
     , products.vendorid
     , products.is_visible
     , product_description.compare1
     , product_description.title
     , vendor.company
FROM products
  JOIN product_description
    ON products.pid = product_description.pid
  JOIN vendor
    ON vendor.vid = products.vendorid
WHERE products.pid >=
        ( SELECT FLOOR( MAX(pid) * RAND())
          FROM products ) 
  and (product_description.compare1 = 'Cohasset') 
  and (products.is_visible = 'Yes') 
LIMIT 5

Upvotes: 0

Frank Farmer
Frank Farmer

Reputation: 39356

SELECT products.pid, products.vendorid, products.is_visible, 
       product_description.compare1, product_description.title,
       vendor.company
FROM products, product_description, vendor
WHERE products.pid >= (SELECT FLOOR( MAX(pid) * RAND()) FROM `products` ) 
  and ((product_description.compare1 = 'Cohasset' ) 
  and (products.is_visible='Yes') and (products.pid = product_description.pid))
  and vendor.vid = products.vendorid
LIMIT 5

To break this down:

I need a field called 'vendor.company',

So we add vendor.company To the end of the SELECT section at the top.

in the table 'vendor',

So we add vendor to the FROM list

contains vendor id, called 'vid' which matches up with products.vendorid.

So we add vendor.vid = products.vendorid to the WHERE

Upvotes: 1

Related Questions