CODEWITHSUNDEEP
mysql
filimon
filimon

Reputation: 61

How to SELECT in this schema?

I have the following schema

mysql> describe category;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(64)            | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe subcategory;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(64)            | YES  |     | NULL    |                |
| category_id | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe quote;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| content        | text                | YES  |     | NULL    |                |
| category_id    | tinyint(3) unsigned | YES  |     | NULL    |                |
| subcategory_id | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> select * from quote;
+----+---------+-------------+----------------+
| id | content | category_id | subcategory_id |
+----+---------+-------------+----------------+
|  1 | text1   |           1 |              1 |
|  2 | text2   |           1 |              2 |
|  3 | text3   |           2 |              1 |
|  4 | text4   |           1 |              3 |
|  5 | text5   |           2 |              3 |
|  6 | text6   |           2 |              1 |
|  7 | text7   |           2 |              2 |
|  8 | text8   |           3 |              1 |
+----+---------+-------------+----------------+
8 rows in set (0.00 sec)

mysql>

First question is, should I drop the quote.category_id to have a normalized db?

More importantly, I would like to

SELECT quote.content WHERE the subcategory.name='some_name'

How to archive this?

Sorry for the basic question but I am new to SQL. I guess this involves some kind of JOIN?

Upvotes: 0

Views: 48

Answers (4)

filimon
filimon

Reputation: 61

I managed to figure out that a way is:

select content, quote.category_id, quote.subcategory_id from quote, subcategory where subcategory.id=quote.subcategory_id and subcategory.name='some_name';

Upvotes: 1

jawadxiv
jawadxiv

Reputation: 59

select count (1) as total, a.id, b.name
from options a, category b
where a.id =b.id
and a.id = 1

Upvotes: 0

andy
andy

Reputation: 2002

There is plenty of information out there to answer your question. The article on wikipedia has a number of references on DB normalization. From the schema you have, you could drop it to remove redundant information.

You are also correct about the JOIN. There is a nice visual explanation of joins by Jeff Atwood himself.

Upvotes: 0

MiltoxBeyond
MiltoxBeyond

Reputation: 2731

You can drop the category on the quote table since it already is contained in the subcategory table.

The basic syntax of your query would be:

Select q.* from quote q where q.subcategory_id IN (select subcategory_id from subcategory where name='NAME');

You can also do a join like so:

Select q.*, s.name from quote q
Left Join subcategory s USING(subcategory_id)
WHERE s.name='NAME'

Upvotes: 0

Related Questions