Questions about mysql associated multi-table query-CodePudding

Assuming that there is a table, structure for

 create table user_info {
Id int primary key, 
The name varchar (20) 
} 

The create table order_info {
Id int primary key, 
Order_user_id int, 
Examine_user_id int 
}

User_info is a user table, order_info through order_user_id, examine_user_id associated user table, now my demand is that the query only once user_info, to query the order_user_id and examine_user_id respectively by the corresponding user name
Similar

 select 

The from 
User_info user_info, order_info order_info

Could you tell me how to write SQL to?

CodePudding user response:

Demand is not clear, 111

CodePudding user response:

reference 1/f, James 灬 Gosling response:

demand not clear 111

Is the query order_user_id and examine_user_id corresponds to the user name

CodePudding user response:

reference 1/f, James 灬 Gosling response:

demand not clear 111

But user_info can only be used once

CodePudding user response:

Associative table is sure as a business driver table, two join affiliate corresponding order_user_id and examine_user_id respectively

CodePudding user response:

reference geek, 4/f, the poet's response:

watch must be associated as a business driver table, two join association respectively corresponding order_user_id and examine_user_id

Specific to how to write? User_info can only be used once

CodePudding user response:

SELECT
A.n ame
The FROM
User_info AS a
INNER JOIN order_info AS b ON Anderson, d=b.o rder_user_id
The or Anderson, d=b.e xamine_user_id

CodePudding user response:

The

refer to 6th floor 灬 James Gosling response:

SELECT
A.n ame
The FROM
User_info AS a
INNER JOIN order_info AS b ON Anderson, d=b.o rder_user_id
The or Anderson, d=b.e xamine_user_id

Well, I mean to get two list
User_info. Name as' orderUserName,
User_info. Name as' examineUserName,
such

CodePudding user response:

refer to 7th floor Lao wang is my response:

Quote: refer to the sixth floor, James 灬 Gosling response:
SELECT
A.n ame
The FROM
User_info AS a
INNER JOIN order_info AS b ON Anderson, d=b.o rder_user_id
The or Anderson, d=b.e xamine_user_id

Well, I mean to get two list
User_info. Name as' orderUserName,
User_info. Name as' examineUserName,
The

So I just said that you are not clear, need you these six years is how to CSDN, requirements description not clear

CodePudding user response:

refer to the eighth floor James 灬 Gosling response:

Quote: refer to 7th floor Lao wang is my response:

Quote: refer to the sixth floor, James 灬 Gosling response:
SELECT
A.n ame
The FROM
User_info AS a
INNER JOIN order_info AS b ON Anderson, d=b.o rder_user_id
The or Anderson, d=b.e xamine_user_id

Well, I mean to get two list
User_info. Name as' orderUserName,
User_info. Name as' examineUserName,
The

So I just said that you are not clear, need you these six years is how to CSDN, requirements are described not clear

Were detected, rather than find out together

CodePudding user response:

SELECT
B.n ame, citigroup ame
The FROM
Order_info AS a
INNER JOIN user_info AS b ON b.i d=a.o rder_user_id
INNER JOIN user_info AS c ON c.i d=a.e xamine_user_id

Only used once user_info seems not very good do you see if I can meet this

CodePudding user response:

Brother do you have any interest in QQ group

CodePudding user response:

10th floor is the correct term, but you want to just want to play, also can write, just want to use oracle

 
The select t2. *, 
T1. The name, 
T1. Name2 
The from (
Select a t1. Id, 
T1. The name, 
The prior t1. Id id2, 
The prior t1. The name name2 
The from (
Select a t1. *, 
Count (1) over (cot) 
The from user_info t1 
) t1 
Connect by level & lt; Cot 
) t1, 
Order_user t2 
Where a t1. Id=t2. Order_user_id 
And t1. Id2=t2. Examine_user_id

CodePudding user response:

The SELECT c.i d, Max (ame), Max (c.e xamname) FROM (
Select
Anderson, d,
Case the when a.o rder_user_id=b.i d then b.n ame end as the name,
Case the when a.e xamine_user_id=b.i d then b.n ame end as examname
The from order_info a
Left the join user_info b on a.o rder_user_id=b.i d or a.e xamine_user_id=b.i d) AS c GROUP BY c.i d

I also have this kind of SAO writing you see which one do you like to use

CodePudding user response:

refer to the 13th floor James 灬 Gosling response:

SELECT c.i d, Max (ame), Max (c.e xamname) FROM (
Select
Anderson, d,
Case the when a.o rder_user_id=b.i d then b.n ame end as the name,
Case the when a.e xamine_user_id=b.i d then b.n ame end as examname
The from order_info a
Left the join user_info b on a.o rder_user_id=b.i d or a.e xamine_user_id=b.i d) AS c GROUP BY c.i d

I also have this kind of SAO writing you see which one do you like to use

Subqueries or check a user table, I don't think efficiency is too low to do (to tell the truth, the main is too low), but I asked a circle, as if only two kinds of writing, or associated query to check one more time, either in the select a subquery

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull