031 Medical Project-Module III: Pharmaceutical supplier Catalogue Module--SQL Supplemental Knowledge

This supplemental knowledge has a point that is necessary, as the teacher on the screen raises a point:

Internal links related query:

If Table A and table B have a foreign key association, you can use a foreign key to make an internal link query

Select Dictinfo.*, Dicttype.typename

From Dictinfo, Dicttype

where Dictinfo.typecode = Dicttype.typecode

-- not through the foreign key, through GroupID Enquiry User-type code result set, only one record can be queried, with the link inside

Select Sysuser.*, Dictinfo.info

From Sysuser,

(select Dictcode, TypeCode, info from dictinfo where typecode = ' S01 ') dictinfo

where sysuser.groupid = Dictinfo.dictcode

Summary: If the main query table field is queried only one record from the associated table, this field can be used as the inner link associated field

-- An example of an error within a link that duplicates a record by correlating the query

-- Use GroupID from Select Dictcode, TypeCode, info from Dictinfo multiple records can be found, cannot be used within links, duplicate records may occur

Select Sysuser.*

From Sysuser, (select Dictcode, TypeCode, info from Dictinfo) dictinfo

where sysuser.groupid = Dictinfo.dictcode

Note: If you use the internal link query to duplicate records, first of all to consider whether the SQL is wrong, you can not go directly to use distinct to remove duplicate records.

There are some special cases where you need to use distinct to remove duplicate records, such as complex statistical analysis of SQL.

Look at this project on the screen when I happened to be looking at 50 query series, write there SQL query statement I ran into this problem. I also used the distinct function, the original is not used AH.

External Link Association query:

Table A, table B has only a subset of the data and table a match, you cannot use the inner link.

The main query is table A and can only use external links.

-- To query the user's owning organization , Sysid the corresponding three-sheet ID

Select SYSUSER.*,USERYY.MC from Sysuser left join useryy on sysuser.sysid = useryy.id

SELECT * from Useryy right join sysuser on sysuser.sysid = useryy.id

-- the above needs cannot be used within the link

Select SYSUSER.*,USERYY.MC from Sysuser, useryy where sysuser.sysid = Useryy.id

Summary:

Table A can only query part of the data from table B, only use external links

Sub-query

Select Sysuser.*,

(SELECT * from useryy where id = sysuser.sysid)

From Sysuser

A subquery can return only one column, otherwise:

Subqueries allow only one row to be returned, otherwise:

The correct sql:

-- Sub-query

-- according to Sysid Remove Unit name

-- according to GroupID Query the name of the user type code

Select Sysuser.*,

(Select MC from useryy where id = sysuser.sysid) SYSMC,

(select info from dictinfo where Dictcode = sysuser.groupid and TypeCode = ' S01 ') groupname

From Sysuser

Nested tables

A SQL query result can be made into a virtual table, queried in the same way as querying an entity table.

The Composed Virtual table field is not allowed to be duplicated, otherwise:

031 Medical Project-Module III: Pharmaceutical supplier Catalogue Module--SQL Supplemental Knowledge