How to Group the results of a Select into two tables -- SQL

Asked

Viewed 66 times

0

Well I have two tables the Tbinfo and the table Tbprograma

CREATE TABLE TBInfo(
    Ip VARCHAR(20),
    HostName VARCHAR(50),
    Mascara VARCHAR(15),
    Gataway VARCHAR(15),
    Win VARCHAR(50),
    Versao VARCHAR(8),
    Hd VARCHAR(11),
    Memoria VARCHAR(8),

);

CREATE TABLE TBProgramas(
    Ip VARCHAR(20),
    NomePrograma VARCHAR(255),
);

And I wish that when I wore a SELECT I’d look like this

Ip           | HosteName | Programas
192.255.3258 | USER-PC   | VisualStudio,Google Chrome
sql
  • 1

    Use the GROUP BY clause with an aggregation function that concatenates the program names, type string_agg(Expression, delimiter) or the equivalent of your DBMS at the junction of your tables.

1 answer

2

One possibility is to use the Inner Join clause:

SELECT
  TBInfo.ip,
  HostName,
  NomePrograma
FROM TBInfo
INNER JOIN TBProgramas
  ON TBProgramas.ip = TBInfo.ip

Another possibility is to group and concatenate the Program Name:

Mysql - 5.1.73

SELECT
  TBInfo.ip,
  TBInfo.HostName,
  GROUP_CONCAT(TBProgramas.NomePrograma) AS Programas
FROM TBInfo
INNER JOIN TBProgramas
  ON TBProgramas.ip = TBInfo.ip
GROUP BY (TBInfo.ip);

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.