undefinedfix
Sign in

SQL statement problems

zlodes edited in Tue, 24 May 2022

There are two tables, a document table and a case table

文档表
document (id,case_num,document_name)

案件表
case (id,case_num,case_name,status)

Case of document table_ Num corresponds to the case of multiple case tables_ Case of num document table_ Num format is separated by "'", for example:

A001,A002

The problem is to add the contents of the two tables to the third table. The judgment condition is the case of the document table_ The status of all the records in the corresponding case table in the num field is' 1 '

What's the point of this sentence

2 Replies
Amer
commented on Tue, 24 May 2022

This is not easy to write. If you insist on writing it, you need to first write the case in the document table_ Num string out, in the program traversal, traversal of each to query the case table.

If possible, restructure the table. Generally, this kind of relationship is expressed in the middle table.

document(id,document_name)
case(id,case_name,status)
# 中间表
document_case(id,document_id,case_id)

And then query like this

SELECT * FROM document d JOIN document_case dc ON d.id = dc.document_id JOIN `case` c ON dc.case_id = c.id WHERE d.id not in (SELECT dc2.document_id FROM `case` c2 JOIN document_case dc2 ON c2.id = dc2.case_id WHERE c2.status = 0)
user164049
commented on Tue, 24 May 2022

It is suggested to adjust the table structure. When you store multiple values in one field, it is against the principle of the first paradigm of database design. If the field itself does not participate in any operation, but simply stores, then there is no big problem with the anti normal form here.

But you are obviously involved in the calculation here. Therefore, if the performance and data consistency is relatively large, it is suggested to adjust the table structure as suggested by the brother upstairs.

lock This question has been locked and the reply function has been disabled.