CODEWITHSUNDEEP
mysqlsql
M.H Mighani
M.H Mighani

Reputation: 198

how to select items in mysql based on their foreign keys

I have two tables named all_categories and all_words that they are designed by this schema:

all_categories table:

create table all_categories(
    name varchar(20) not null default "others",
    primary key(name)
);

all_words table:

create table all_words(
    id integer not null auto_increment,
    english_word varchar(30),
    meaning varchar(100),
    full_english_meaning varchar(500) default null,
    primary key(id),
    category varchar(20) not null default "others",
    foreign key(category) 
        references all_categories(name)
        on delete cascade
);

as you see I have a foreign key in my all_words table that connects it to my all_categories table. In my app I want to make a query to retrieve my words by their categories in this jason form for example:

{
   medical_words:[epilepsy,...],
   political_words:[diplomacy,...],
   ...
}

what query should I write to get my words categorized by their categories?

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you just want group by:

select w.category, group_concat(w.english_word) as words
from all_words w
group by w.category;

Normally, the category would have a numeric id and a join would be necessary to get the category name. However, you are using the name for the foreign key reference, so you don't need all_categories in the query.

Upvotes: 3

Related Questions