Query two tables in Mysql

Question

My situation is as follows: I have several tables that have some common information, such as UNIT.

In one of these tables I have all my units and I need to check if this same UNIT also exists in another table, and if it exists, display the information of this other table.

Ex:

Table

unid  -  info
100   -  20
101   -  15
102   -  40
103   -  35

Table

Unid  -  info
101   -  25
103   -  40

what I wish to display:

100 - 20
101 - 25
102 - 40
103 - 40

All searches the units will be individual, I will be consulting only 1 unit at a time, then consulted unit 103, need only information 40.

---

EDIT:

Current Code:

Select cgc, 
       unidade, 
       MAX(velocidade) as vel , 
       circuito, 
       Rot, 
       ip_wan, 
       ip_lan, 
       loopback0, 
       loopback21, 
       loopback22 

From circuitos_rede_2 

Where cgc=457 Not Exists (Select cgc From circuitos_permanentes ) 

Union 

Select cgc, 
       unidade, 
       veloc 

From circuitos_permanentes

Answer 1

Try that, see if it works.

(SELECT unid, info FROM TabelaUM 
 WHERE unid NOT EXISTS ( SELECT Unid FROM TabelaDOIS))
UNION
(SELECT unid, info FROM TabelaDOIS)

---

Editing to fit your need

SELECT cgc, unidade, MAX(velocidade) AS vel , 
       circuito, Rot, ip_wan, ip_lan, loopback0, 
       loopback21, loopback22

FROM circuitos_rede_2 
WHERE cgc=457 NOT EXISTS (select cgc from circuitos_permanentes ) 

UNION 

SELECT cgc, unidade, veloc AS vel, null, null, null, null, null, null, null
FROM circuitos_permanentes

Answer 2

one of the ways to do this query is using UNION between the Selects.

Below follows an example:

SELECT Info FROM Tabela_A
WHERE UId = 103

UNION

SELECT Info FROM Tabela_B
WHERE UId = 103

If you need to sort or group the results just add in the last select the instruction ORDER BY or GROUP BY

SELECT Info FROM Tabela_A
WHERE UId = 103

UNION

SELECT Info FROM Tabela_B
WHERE UId = 103
GROUP BY 1

Note: the number 1 of GROUP BY represents the first column of select

I hope I’ve helped.

Answer 3

I believe it’s a Join. follows an example in sqlserver, but the Join query can be used in several Dbms.

create table REDE1 (COD INT, CAMPO_A VARCHAR(10) NULL, CAMPO_B VARCHAR(10),CAMPO_C VARCHAR(10),CAMPO_D VARCHAR(10),
    CONSTRAINT PK_REDE1 PRIMARY KEY CLUSTERED (COD)
)
GO

create table REDE2 (COD INT, CAMPO_E VARCHAR(10) NULL, CAMPO_F VARCHAR(10)
    CONSTRAINT PK_REDE2 PRIMARY KEY CLUSTERED (COD)
)
GO

create table REDE4 (COD INT, CAMPO_G VARCHAR(10) NULL, CAMPO_H VARCHAR(10),CAMPO_I VARCHAR(10)
    CONSTRAINT PK_REDE4 PRIMARY KEY CLUSTERED (COD)
)
GO

create table REDE5 (COD INT, CAMPO_J VARCHAR(10) NULL
    CONSTRAINT PK_REDE5 PRIMARY KEY CLUSTERED (COD)
)
GO

INSERT INTO REDE1 VALUES
(1,'CAMPO A', 'CAMPO B', 'CAMPO_C', 'CAMPO D'),
(2,'LINHA 2', 'LINHA 2B', 'LINHA 2C', 'LINHA 2D'),
(3,'LINHA 3A', 'LINHA 3B', 'LINHA 3C', 'LINHA 3D'),
(4,'LINHA 4A', 'LINHA 4B', 'LINHA 4C', 'LINHA 4D'),
(5,'LINHA 5A', 'LINHA 5B', 'LINHA 5C', 'LINHA 5D'),
(6,'LINHA 6A', 'LINHA 6B', 'LINHA 6C', 'LINHA 6D')
GO

INSERT INTO REDE2 VALUES
(3,'CAMPO E', 'CAMPO F'),
(5,'CAMPO E', 'CAMPO F'),
(6,'CAMPO E', 'CAMPO F')
GO

INSERT INTO REDE4 VALUES
(1,'CAMPO G', 'CAMPO H', 'CAMPO_I'),
(6,'LINHA G', 'LINHA 2H', 'LINHA 2I')
GO

INSERT INTO REDE5 VALUES
(6,'LINHA 5J')
GO

SELECT R1.*, R2.CAMPO_E, R2.CAMPO_F, R4.CAMPO_G, R4.CAMPO_H, R4.CAMPO_I, R5.CAMPO_J 
FROM REDE1 R1
JOIN REDE2 R2 ON R1.COD = R2.COD
JOIN REDE4 R4 ON R2.COD = R4.COD
JOIN REDE5 R5 ON R4.COD = R5.COD