CODEWITHSUNDEEP
mysql
Evelyn1986
Evelyn1986

Reputation: 159

Add a column to a table according to some query results

I have these two tables, say faq_categories and faq_category_relations. First I do a query in tabel faq_category_relations, like

SELECT category_id 
FROM faq_category_relations
WHERE faq_id = 2;

And the result_a is 

--------------
|category_id |
--------------
|          2 |
--------------
|          3 |
--------------

Then I want to query table faq_categories and add one column checked, the value of which is set according to result_a(that is, if faq_categories.id is one of the values in result_a, set the checked to be true, else false), to the query results.

Till now I only come up with:

SELECT *, IF((faq_categories.id is one of result_a), 'true', 'false') AS checked
FROM faq_categories

And have no idea how to continue..

I know I can do that trick in PHP after retrieving the query data, but there must be some simple way to directly return the query results I needed.

EIDT:

The result should come out like this:

---------------------------
|category_id |    checked |
---------------------------
|          1 |      false |
---------------------------
|          2 |       true |
---------------------------
|          3 |       true |
---------------------------
|          4 |      false |
---------------------------
|          5 |      false |
---------------------------

Upvotes: 0

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Assuming there are no duplicates in faq_category_relations, then you can do this with a left outr join and a case expression:

SELECT c.*,
       (case when cr.category_id is not null then 'true' else 'false' end) as checked
FROM faq_categories c left outer join
     faq_category_relations cr
     on c.category_id = cr.category_id and
        cr.faq_id = 2;

I am also assuming that you just need the value in a query and not as a new column in the table.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425033

The simplest way is:

select c.*, cr.category_id is not null checked
from faq_categories c
left join faq_category_relations cr
  on c.category_id = cr.category_id
where cr.faq_id = 2

This works because with left joins, the values in the joined columns will be null if there's no join, so testing for not null will tell you if the category was listed in the other table.

If you want you can move the condition on faq_id into the join condition, which can be done by changing where to and in the above query.

Upvotes: 0

Related Questions