Select info from a table referencing info from another table

Question

I have a main product table with different products. Different products have different specs, so I've created separate specs tables for each product (there will be more than ten of them). What I want to do is to show individual product's specs on a product_page.php whenever the product is clicked.

My product page has columns:

id - SKU - prod_name - prod_desc....

My specs table columns

id - SKU - prod_specs....

So I want to take SKU from first table and search the rest of the table with this UNIQUE sku and wherever it is show the rest of the field from that table

What I do is

SELECT SKU FROM products AS p 
INNER JOIN cpu_specs AS cs ON cs.SKU = p.SKU
INNER JOIN hdd_specs AS hs ON hs.SKU = p.SKU
WHERE p.SKU = $productSKU  "

But it gives me an error.

If I do SELECT * then it fetches all the info from both tables

mysql_fetch_assoc() expects parameter 1 to be resource, look like the query stops if it doesn't find the sku in a table — tempteh, Jan 30 '14 at 00:37
http://stackoverflow.com/questions/7916381/mysql-error-mysql-fetch-assoc-expects-parameter-1-to-be-resource — loveNoHate, Jan 30 '14 at 00:46
If I tweak the query i get the results from both tables, but I don't realy need the results from the product table, just all the fields from the specs table for particular product. — tempteh, Jan 31 '14 at 07:26

score 0 · Answer 1 · answered Jan 30 '14 at 03:27

0

Try this:

SELECT p.id, p.prod_name, p.SKU, IFNULL(cs.prod_desc, hs.prod_desc)
FROM products AS p 
LEFT JOIN cpu_specs AS cs ON cs.SKU = p.SKU
LEFT JOIN hdd_specs AS hs ON hs.SKU = p.SKU
WHERE p.SKU = $productSKU

answered Jan 30 '14 at 03:27

Saharsh Shah

28,687
8
48
83

score -1 · Accepted Answer · edited Jun 30 '22 at 22:38

I didn't find any proper SELECT query that would find just one result from the tables, but came up with a workaround

In my table products each product has a subcategory (sub_cat),which is cpu, hdd etc. So I've named the secification tables for each product like so hdd_specs, cpu_specs and so on.

So I store the sub_cat as a variable and then SELECT everything from the table called like my variable and it works smoothly.

$query = "SELECT sub_cat FROM products WHERE SKU = $productSKU";

$select = mysql_query($query);

$result_1 = mysql_fetch_assoc($select);

$sub_cat = $result_1['sub_cat'];

$sub_cat = $sub_cat.'_specs';

 
            

$get = " SELECT * FROM $sub_cat
         WHERE SKU = $productSKU
         ";

Select info from a table referencing info from another table

2 Answers2