Q: a field in a table in the other two tables (structure), two table data were found out how to one-time
Example:
The create table ss (int id, name varchar (22));
Create table c1 (int id, name VARCHAR (22), ss_id int);
Create table c2 (int id, name VARCHAR (22), ss_id int);
Tables ss id and c1 or c2 ss_id, associated in table c1 and c2 structure consistent
Expectations: query the ss table id information in all fields of c1 and c2, do not use the union

CodePudding user response:

To the great god, to god

CodePudding user response:

Come on, for discussion

CodePudding user response:

The select c1. *, c2. *
The from ss, c1 and c2
Where ss. Id=c1. Ss_id
The or ss. Id=c2. Ss_id
This mean?

CodePudding user response:

The first kind of
Select * from ss INNER JOIN c1 on ss. Id=c1. Ss_id
Union all
Select * from ss INNER JOIN c2 on ss. Id=c2. Ss_id
The second
Select ss. *, c1. * from ss, c1 where
The EXISTS (select SS. Id from c1 where SS. Id=c1. Ss_id)
Or
The EXISTS (select SS. Id from c2 where SS. Id=c2. Ss_id)
Mean that this data table ss 100 w, c1 and c2 added up to the 100 w, want to look for is there a better way

CodePudding user response:

The first kind of
Select * from ss INNER JOIN c1 on ss. Id=c1. Ss_id
Union all
Select * from ss INNER JOIN c2 on ss. Id=c2. Ss_id
The second
Select ss. *, c1. * from ss, c1 where
The EXISTS (select SS. Id from c1 where SS. Id=c1. Ss_id)
Or
The EXISTS (select SS. Id from c2 where SS. Id=c2. Ss_id)
The third kind of
Select * from ss INNER JOIN (
The select c1. * from c1
Union all
Select the c2. * from c2) TMP
On ss. Id=TMP. Id
A fourth
Select * from ss LEFT JOIN c1 on ss. Id=c1. Ss_id LEFT JOIN c2 on ss. Id=c2. Ss_id
Where c1. Ss_id is not null or c2. Ss_id is not null
5 kinds of
Select SS. *, ifnull (c1. Id, c2. Id) c_id, ifnull (c1) name, c2. Name) c_name, ifnull (c1) ss_id, c2. Ss_id) c_ssid from
Ss LEFT JOIN c1 on ss. Id=c1. Ss_id LEFT JOIN c2 on ss. Id=c2. Ss_id
Where c1. Ss_id is not null or c2. Ss_id is not null
Looks correct, three tables are under the big data, the optimal query

CodePudding user response:

Using union, recommend:
The select c1. * from c1
Where the exists (select 1 from ss where c1. Ss_id=ss. Id)
Union all
Select the c2. * from c2
Where the exists (select 1 from ss where c2. Ss_id=ss. Id)

Do not use union, use your second