Ali code specification: more than three ban the join table-CodePudding

Ali code specification: more than three tables to ban the join, do you need more table association, how to solve

CodePudding user response:

Both in the ali, this little still cannot solve the problem??

CodePudding user response:

refer to the original poster phn555 response:

, ali code specification: more than three table join is forbidden, could you tell me how to solve need more table association,

Don't have to join and

For example:
Select the form XXX tb1 t1, tb2 t2, tb3 t3, tb4 t4, tb5 t5 where t1. Yyy=t2. Yyy and t2. Z=t3. ZZZ and t3. MMM=t4. MMM and t4. NNN=t5. NNN

CodePudding user response:

This,,,, this is there a difference and join

CodePudding user response:

 IF OBJECT_ID (' tempdb for.. #) IS NOT NULL DROP TABLE # TMP TMP '

The SELECT t.n ame AS tableName, 
C.n ame AS colName, 
P.n ame AS typeName, 
T. object_id AS table_objId, 
Arthur c. olumn_id 
INTO # TMP 
The FROM sys. Tables AS t 
INNER JOIN sys. [columns] AS c 
ON [object_id]=t. c. [object_id] 
INNER JOIN sys. Types AS p 
ON c.s. ystem_type_id=p. ystem_type_id 

SELECT * FROM # TMP AS t LEFT JOIN sys. Extended_properties AS ep ON t.t able_objId=ep. Major_id AND tc olumn_id=ep. Minor_id 

IF OBJECT_ID('tempdb.. #) IS NOT NULL DROP TABLE # TMP TMP '

The temporary table, also can have more good way

CodePudding user response:

Don't let three tables link, then from the table design

CodePudding user response:

Maybe several times is to query,
For example, from the name to find the name of the department, start with the personnel in the table, by name to check the department ID; Check the department table, through the department ID to check the department name,

CodePudding user response:

Associated with multiple factors, the design of the table, the design of the application system, and so on

CodePudding user response:

First of all posted the original:
2. [force] more than three table joins, need to join fields, data types must be absolutely consistent; Multiple tables associated query,
Guaranteed by the correlation index of fields need to be,
Note: even if the double table join also pay attention to the table index, SQL performance,

I baidu join and inner join, the difference between a discovery is the same, according to the statement should give is left the join can be used, because the above mentioned multi-table connection also, how about the

CodePudding user response:

What about when different join, statistics?

CodePudding user response:

Three tables above all JOIN this kind of situation because not enough redundancy, add enough redundancy table can, redundant table can be either a temporary table, can also be generated regularly photographed table,

CodePudding user response:

Join a join b c join d
==& gt;
(a join b) join the join d (c)
This is a few times?

CodePudding user response:

11 references to hit dead Newton's apple's reply:

join the join a join b c d
==& gt;
(a join b) join the join d (c)
This is a few times?

you like a silly force

CodePudding user response:

refer to 12 floor talentsonglee reply:

Quote: 11 references to hit dead Newton's apple's reply:

Join a join b c join d
==& gt;
(a join b) join the join d (c)
This is a few times?

you like a silly force

ha ha

CodePudding user response:

Complex queries can still make the view with the view to solve

CodePudding user response:

refer to 6th floor ZBDZJX response:

maybe several times is to query,
For example, from the name to find the name of the department, start with the personnel in the table, by name to check the department ID; Check the department table, through the department ID to check the department name,

You can do it with a SQL sentence never use two words