Query two tables in Mysql

Question

Query two tables in Mysql

Navigation

#1 by (7 votes)
#2 by (2 votes)
#3 by (1 votes)

6

My situation is this: I have several tables that have some information in common, 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 there is, display the information in this other table.

Ex:

Table_A

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

Table_B

Unid  -  info
101   -  25
103   -  40

What I want to display:

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

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

php mysql

asked by anonymous 24.11.2014 / 19:11

3 answers

Separate application authentication server How to filter a list asynchronously using LINQ?

score 7 · Answer 1

Try this, 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

score 2 · Answer 2

One of the ways to do this query is to use UNION between SELECTs.

Below is 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 the 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: number 1 of GROUP BY represents the first column of select

I hope I have helped.

score 1 · Answer 3

I think it's a join. follows an example in the sqlserver, but the join query can be used in multiple DBMSs.

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