CODEWITHSUNDEEP
mysqlsql
Adrian
Adrian

Reputation: 2012

how to query unrelated tables without UNION, JOIN or multiple selection

My issue is that I would like to query several different columns from 2 different tables, it is for a search feature where I want the results to show a mix of categories and products in the array.

Correct me if Im wrong, but I cant use JOIN because the tables are not related in any way.

I cant use UNION because there is no consistency in the amount of columns I want choose from per table.

And I tried simply selecting the combination of columns from the combination of tables but that multiplies everything. However Ill show you the code for the last option as perhaps there is a way of tweaking it to get it to work...

SELECT pd.product_id, pd.name, pd.meta_keyword, pd.description, cd.category_id,
  cd.name AS 'category_name'
FROM oc_product_description AS pd, oc_category_description AS cd 
WHERE pd.name LIKE '%$word%'
  OR cd.name LIKE '%$word%';

This returns the entire list of oc_category_description category_name values for every successful hit of pd.name whereas I ONLY want successful results of either the product name or the category name.

Upvotes: 0

Views: 653

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

If you want UNION, you have to add all columns from the various tables and NULL values in the columns that are not supplied in other tables:

SELECT pd.product_id,                      -- pd columns
       pd.name , 
       pd.meta_keyword, 
       pd.description,

       NULL AS category_id,                -- cd columns
       NULL AS category_name
   FROM oc_product_description as pd 
   WHERE pd.name LIKE '%$word%' 

UNION ALL

SELECT NULL, 
       NULL, 
       NULL, 
       NULL,

       cd.category_id , 
       cd.name AS category_name
   FROM oc_category_description as cd 
   WHERE cd.name LIKE '%$word%' ;

Upvotes: 2

Related Questions