SQL Query for matching sub query's

Question

I am creating a website like Stack Overflow.
When we post new questions on "stack-overflow", we give some tags which are related to our question.

I have one field named as q_related_tag_ids (question related tag ids) in table question.

I use this field to store all tag_ids separated by commas (,) which are related to posted question.

Table Question >>

q_id | q_title | q_ralated_tag_ids
1    | title1  | 4,5,8
2    | title2  | 6,8,1
3    | title3  | 2,81,13
4    | title4  | 8
3    | title3  | 2,87
4    | title4  | 83

Table Answer >>

t_id | t_name | t_description
1    | java   | java is ...
2    | php    | php is ...
3    | ajax   | ajax is ...
4    | c++    | c++ is ...
5    | perl   | perl is ...
8    | java8  | java8 is...
...

Now the problem is >> I want to get/select all question details which are related to tag-id = 8, or related to the java8 tag

For that, I have created a query >>

select * from question where (
                  q_related_tag_ids like '8' 
                  or q_related_tag_ids like '%,8,%'
                  or q_related_tag_ids like '%,8')

Does anyone have any better approach?

Fix your data structure so you are not storing numeric foreign key references as a delimited string. That is simply the wrong way to structure data in a database. — Gordon Linoff, Apr 19 '18 at 12:27
The *better approach* is to create a table for tags, and a many-to-many table linking posts to tags. Unless tags are merely *decorative*, IOW, you would never search by tags, that is the best way to go. Better performance - faster searches, more maintainable, etc. — Sloan Thrasher, Apr 19 '18 at 12:34
I am reading this answer to figure out if your method is that BAD or not! [delimited list column vs foreign keys](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Ibrahim Mohamed, Apr 19 '18 at 12:43
You all are right i have did big mistake using varchar over new table but now i can't change structure because i have created many queries and php pages on it -thanks i'll take care of it next time — Alvis Vadaliya, Apr 19 '18 at 13:04

Cid · Answer 1 · 2018-04-19T12:45:34.163

I would create a table that makes the relation between both tables.

Table Question >>

q_id | q_title
1    | title1
2    | title2
3    | title3
4    | title4
3    | title3
4    | title4

Table Answer >>

t_id | t_name | t_description
1    | java   | java is ...
2    | php    | php is ...
3    | ajax   | ajax is ...
4    | c++    | c++ is ...
5    | perl   | perl is ...
8    | java8  | java8 is...

Table Relation >>

t_id | q_id
4    | 1
5    | 1
8    | 1
6    | 2
8    | 2
1    | 2

The primary key of Relation table is created using concatenation between both foreign keys and is unique

SELECT * FROM question WHERE q_id IN (SELECT q_id FROM relation WHERE t_id = 8)

I suggest you to take a look at Merise method

score 0 · Answer 2 · answered Apr 19 '18 at 12:40

0

SELECT question.q_title,
       tags.t_name
FROM   question
INNER JOIN tags ON  ',' + question.q_ralated_tag_ids + ',' LIKE '%,' + CAST(tags.t_id AS NVARCHAR(20)) + ',%'

answered Apr 19 '18 at 12:40

Ghazni

826
8
16

1

Simply posting code without explanation is frowned upon on SO – Mazhar Apr 19 '18 at 15:33

score 0 · Answer 3 · answered Apr 19 '18 at 12:43

I think you should use another table to store question tags.
The table contains id, question_id, tag_id
That will be more faster and maintainable
Please check the example

id | quesion_id | tag_id
1  | 1          | 4
2  | 1          | 5
3  | 1          | 8
4  | 2          | 6
5  | 2          | 8
8  | 2          | 1

I think Now you are using varchar or text to store tags Searching over the datatypes takes more time. There will be far better performance in case of using another table to store question_tags. here you are searching in integer field

You are right i have did big mistake using varchar over new table but now i can't change structure because i have created many queries and php pages on it -thanks — Alvis Vadaliya, Apr 19 '18 at 13:00

score 0 · Answer 4 · edited Apr 19 '18 at 15:04

0

SELECT q.*
FROM questions q 
INNER JOIN answer a ON q.q_ralated_tag_ids = a.id or a.t_description LIKE '%java8%'

edited Apr 19 '18 at 15:04

SherylHohman

16,580
17
88
94

answered Apr 19 '18 at 13:26

Ankit Agrawal

2,426
1
13
27

score 0 · Answer 5 · answered May 10 '18 at 01:00

0

Avoiding commalists is better, but here is a way to do what you were trying:

FIND_IN_SET('8', q_related_tag_ids)

answered May 10 '18 at 01:00

Rick James

135,179
13
127
222

SQL Query for matching sub query's

5 Answers5