CODEWITHSUNDEEP
mysqlsqljoin
Zoran
Zoran

Reputation: 1371

chaning the join in mysql

I have 2 tables, faq and faq_categories...i have a join that work, and so far, I was a happy camper.

But...requirements change, and i have to change the join, but i don't know how to do it

Here is the current code that works just fine:

SELECT faq.* , faq_categories.categoryname 
                                  FROM faq 
                                  JOIN faq_categories
                                  ON ( faq.catid = faq_categories.catid)

So far, all faq belongs to one category...but from now on, there will be faq which will not belonng to any category....and that complicate things, at least for me.

How should I change this code in order to display the faq which does not have catid?

Here are my tables: 

CREATE TABLE IF NOT EXISTS `faq_categories` (
`catid` int(11) NOT NULL AUTO_INCREMENT,
`parentid` int(11) DEFAULT NULL,
`categoryname` varchar(255) NOT NULL,
`categoryname_en` varchar(255) DEFAULT NULL,
`description` text,
`description_en` text,
`metatags` text,
`metatags_en` text,
`sorder` int(11) NOT NULL,
`visible` tinyint(4) NOT NULL,
`categoryphoto` varchar(255) DEFAULT '',
PRIMARY KEY (`catid`),
KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=204 ;


CREATE TABLE IF NOT EXISTS `faq` (
`faqid` int(11) NOT NULL AUTO_INCREMENT,
`catid` int(11) DEFAULT NULL,
`question` text NOT NULL,
`question_en` text NOT NULL,
`answer` text,
`answer_en` text,
`metatags` text,
`metatags_en` text,
`sorder` tinyint(4) DEFAULT NULL,
`visible` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`faqid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

Upvotes: 1

Views: 60

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

For those that have no category, we assume you mean faq.catid will be NULL. Your table definitions don't need to change at all. That will only require changing yourINNER JOINto aLEFT JOIN. The FAQs with no category will show aNULLforfaq_categories.categoryname` in the output:

SELECT 
  faq.* , 
  faq_categories.categoryname 
FROM
  faq 
  LEFT JOIN faq_categories  ON ( faq.catid = faq_categories.catid)

I would encourage you now to anticipate the time when a FAQ must belong to more than one category, however. To do that, you need to create a joining table which contains a faqid and catid. There can be many rows per faqid:

CREATE TABLE faq_in_categories (
  faqid INT(11) NOT NULL,
  catid INT(11) NOT NULL,
  PRIMARY KEY (faqid, catid),
  FOREIGN KEY (faqid) REFERENCES faq (faqid),
  FOREIGN KEY (catid) REFERENCES faq_categories (catid)
);

Under this model, you would remove the faq.catid column because membership in a category is defined in the joining table. This is a many-to-many relationship.

Queried by:

SELECT 
  faq.*
  categories.*
FROM
  faq
  JOIN faq_in_categories ON faq.faqid = faq_in_categories.faqid
  JOIN categories ON faq_in_categories.catid = categories.catid
WHERE faq.faqid = 'some faqid'

Upvotes: 1

Related Questions