New one, the combination of the table A and table B-CodePudding

Table A
ID1 id2 ID3
A, B1 C1
A, B2, C2
A B3 C3
Table B
ID1 id4 ID5
A E1 E2
A D1 D1

When the ID in table A and table B in the same field, A want to put in table B content displayed in the table A
ID1 id2 ID3 ID4 ID5
A, B1 C1 NULL NULL
A, B2, C2 E1 E2
A B3 C3 D1, D2

How to write?

Many of the same LEFT after the JOIN

CodePudding user response:

Table A and table B is how associated?

CodePudding user response:

The ID 1 in table A and table B of A phase in the ID1
at the same time
Such as a.A=b.A

CodePudding user response:

 
Select * From A 
Left the Join On A.A B=B.A

CodePudding user response:

No, there will be a repeat of the data, disorderly inserted inside the

CodePudding user response:

reference 4 floor walkjian1 response:

no, there will be a repeat of the data, disorderly inserted inside

According to the data you provide and relationship in what you said, I also don't know what to write, certainly not you try this

 


Select distinct Anderson, D1, id2, ID3, ID4, ID5 From A 
Left the Join On A.A B=B.A

CodePudding user response:

Table A
ID1 id2 ID3
A, B1 C1
A, B2, C2
A B3 C3
Table B
ID1 id4 ID5
A E1 E2
A D1, D2
M M1, M2
The results to be like this:
ID1 id2 ID3 ID4 ID5
A, B1 C1 NULL NULL
A, B2, C2 E1 E2
A B3 C3 D1, D2

With the UPDATE?

CodePudding user response:

refer to 6th floor walkjian1 response:

list AID1 id2 ID3
A, B1 C1
A, B2, C2
A B3 C3
Table B
ID1 id4 ID5
A E1 E2
A D1, D2
M M1, M2
The results to be like this:
ID1 id2 ID3 ID4 ID5
A, B1 C1 NULL NULL
A, B2, C2 E1 E2
A B3 C3 D1, D2

With the UPDATE?

you use my above statement may not perform for you?

CodePudding user response:

refer to 7th floor one tree forest _ response:

Quote: refer to the sixth floor walkjian1 response:

Table A
ID1 id2 ID3
A, B1 C1
A, B2, C2
A B3 C3
Table B
ID1 id4 ID5
A E1 E2
A D1, D2
M M1, M2
The results to be like this:
ID1 id2 ID3 ID4 ID5
A, B1 C1 NULL NULL
A, B2, C2 E1 E2
A B3 C3 D1, D2

With the UPDATE?

you use my above statement may not perform for you?

Still have repeat data in it

CodePudding user response:

refer to the eighth floor walkjian1 response:

Quote: refer to 7th floor one tree forest _ response:

Quote: refer to the sixth floor walkjian1 response:

Table A
ID1 id2 ID3
A, B1 C1
A, B2, C2
A B3 C3
Table B
ID1 id4 ID5
A E1 E2
A D1, D2
M M1, M2
The results to be like this:
ID1 id2 ID3 ID4 ID5
A, B1 C1 NULL NULL
A, B2, C2 E1 E2
A B3 C3 D1, D2

With the UPDATE?

you use my above statement may not perform for you?

There are repeated data inside

 

- whether you want this effect 

If not OBJECT_ID (N 'tempdb for.. # aa ') is null 
Drop table # aa 
Go 
If not OBJECT_ID (N 'tempdb for.. # bb ') is null 
Drop table # bb 
Go 
The create table # aa (
ID1 varchar (20), 
ID2 varchar (20), 
ID3 varchar (20) 
) 
Go 
The create table # bb (
ID1 varchar (20), 
ID4 varchar (20), 
ID5 varchar (20) 
) 
Go 
Insert into # aa values (' A ', 'B1, C1') 
Insert into # aa values (' C ', 'B4', 'C4) 

Insert into # bb values (' A ', 'D1', 'E1) 
Insert into # bb values (' A ', 'D2', 'E3) 
Insert into # bb values (' B ', 'D3', 'E3) 


- Delete From # aa 
- Delete From # bb 

Select distinct Anderson, D1, ID2, ID3, ID4, ID5 From # aa A 
Left the Join # bb B On Anderson, D1=B.I D1

CodePudding user response:

I mean, A table and B have the same time, it is according to the record number of tables to decide,
For example:
A, B1 C3 A D2 E3
A B2 C4 A D1 E2
A B2 C5

A1 B2 C2 A1 D3 E3
A1 B3 C3 A1 D2 E2
A1 E1 D1
The results should be
A, B1 C3 null null
A B2 C4 D2 E3
A B2 C5 D1 E2
A1 NULL NULL D3 E3
A1 B2 C2 D2 E2
A1 B3 C3 D1 E1

According to A first find out how many different B ID1
Select distinct id1 from (select id1 from a union select id1 from b) into # tmp_id
According to the circulation of the ID1 for each ID again processing
1 to find out the current ID1 A record and B
2 record Numbers
3 extra form separate insert
4 B from the same number to begin A table insert