SQL query (review of medical projects)

l How to write SQL statement ideas:

main query table : Sysuser

Association query Table : USERJD (Supervisory Unit table), Useryy (Hospital table)

SQL queries are divided into:

1. Internal connection

If two tables have a foreign key relationship, you can use an inner join, because each entry through an inner join can only return a single record. In other words, if the field of the main query table can be found by the associated query table (and only one is found in the association table), you can use the inner link.

Example 1:

Table: Dictinfo:

As follows:

The ID in this table is the primary key. Here TypeCode is the foreign key.

Table: Dicttype.

As follows:

Where TypeCode is the primary key.

This way the two tables have a relationship to the primary foreign key (the primary key of table Dicttype TypeCode is the foreign key in table Dictinfo).

Okay, so we can use the inside connection.

Why does it say that you can only return a single record for each entry in the link?

Because: you want Ah, TypeCode is foreign key, what is foreign key? In the table Dicttype TypeCode is the primary key, the primary key is unique, then is TypeCode is unique, then dictinfo.typecode to match dicttype inside the

TypeCode only one record, which means that only a single record can be returned, that is, if the CodeType in the Dicttype is not unique, then it will find out a number of records, then it is not inside the connection.

Example 2:

One more example: do a data query using the internal connection method.

Assuming that there are two tables Sysuser table and Dictinfo table, we check the Sysuser table to find out the UserID and GroupID as follows:

But I want to know what the groupid here is, and we can find out from the Dictinfo table what the 1,2,3,4 stands for, for example, according to the table design.

Okay, after we know the requirements, we're going to use an inner connection to do that.

In fact

This is a result set. We treat it as a table (the Dictcode here is unique and can be considered a primary foreign key relationship)

So according to the inner connection idea, we write the SQL statement:

Select  from Sysuser, (selectfromwhere typecode='s01'  wheregrouptable.dictcode=sysuser.groupid

(where typecode='s01') grouptable is equivalent to finding the result set as a grouptable table.

2. External links

3. Sub-query

SQL query (review of medical projects)