|
select a.name, b.category_id cat, b.name category, e.category_id jcncat,
b.typecattype
from tblCompany a, tblCategory b,jcnCategoryCompany e
where ((e.company_id = a.company_id and b.category_id = e.category_id and
b.type <> 'NONE')
or b.type = 'NONE') and a.company_id
<> 1
group by a.company_id;
Can anyone see what is wrong with that?
We are trying to pull data for a search in our php program but we get odd
results.
mysql> select a.name, b.category_id cat, b.name category, e.category_id
jcncat, b.type cattype from tblCompany a, tblCategory b, jcnCategoryCompany e where ((e.company_id = a.company_id and b.category_id = e.category_id and b.type <> 'NONE') or b.type = 'NONE') and a.company_id <> 1 group by a.company_id; +--------------------+-----+----------+--------+---------+ | name | cat | category | jcncat | cattype | +--------------------+-----+----------+--------+---------+ | A Typical Business | 1 | | 3 | NONE | | A supplier | 1 | | 3 | NONE | +--------------------+-----+----------+--------+---------+ 2 rows in set (0.00 sec) However, look at the following table: mysql> select * from jcnCategoryCompany; +---------------+-------------+------------+ | category_type | category_id | company_id | +---------------+-------------+------------+ | COMPANY | 1 | 2 | | COMPANY | 3 | 3456 | +---------------+-------------+------------+ And tblCategory: mysql> select * from tblCategory; +------------+-------------+-----------------+ | type | category_id | name | +------------+-------------+-----------------+ | NONE | 1 | | | CAMPAIGN | 1 | Campaign Cat 1 | | CAMPAIGN | 2 | Campaign Cat 2 | | CASE | 1 | Case Category 1 | | CASE | 2 | Case Category 2 | | OPP | 1 | Opp Category 1 | | OPP | 2 | Opp Category 2 | | COMPANY | 1 | Company Cat 1 | | COMPANY | 2 | Company Cat 2 | | C_INDUSTRY | 1 | Company Ind 1 | | C_INDUSTRY | 2 | Company Ind 2 | | CONTACT | 1 | Contact Cat 1 | | CONTACT | 2 | Contact Cat 2 | | COMPANY | 3 | Supplier | +------------+-------------+-----------------+ And abbreviated tblCompany: mysql> select company_id, name from tblCompany; +------------+--------------------+ | company_id | name | +------------+--------------------+ | 1 | | | 2 | A Typical Business | | 3456 | A supplier | +------------+--------------------+ 3 rows in set (0.00 sec) The strange behaviour seems to stem from the query ALWAYS selecting the "b.type = 'NONE'" case! At the very least, b.category_id should equal e.category_id in the output though it's not! I don't see anything logically wrong with the query. Do you see anything? |
