I am having a problem with querying two tables with different foreign key relations… My first query works fine but when adding another table with a different set of relations I am having a problem… Can some one point me in the right direction.
First Query works:
select `title`,`txt`,`img`
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%';
Second query that I am stuck when adding another table:
select `title`,`txt`,`img`
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%' OR (
select `firstname`,`lastname`,`email`,`mi`,`dName`,`phnumber`
from `staff`
inner join `departments`
on `staff`.`department` = `departments`.`did`
inner join `phones`
on `staff`.`phone` = `phones`.`pid`
where `staff`.`keywords` like '%test%');
Best Answer
If you need to combine result sets from different tables, use the UNION operator with two queries. Since the sets have different and unrelated column names, you will need to select a placeholder (usually NULL) in each query for columns in the other query: