Home / Questions / 7717
Accepted
Juan Pinzón
Asked: 2020-04-14 12:37:45 +0800 CST 2020-04-14 12:37:45 +0800 CST

Select a single value from a many-to-many relationship

  • 772

I have a problem when obtaining the records of a DB through a many-to-many relationship, I have the following tables: PostgreSQL Tables

At the time of extracting the data from the cpu, there are cpu's that have several network cards, so I have a many-to-many relationship, the problem is that I don't know how to obtain the network cards without repeating the cpu.

The query I make is the following:

SELECT cpu.id_cpu, nic.mac, nic.dir_ip FROM cpu 
JOIN activo_fijo ON cpu.id_activo = activo_fijo.id_activo 
JOIN nic_cpu ON nic_cpu.id_cpu = cpu.id_cpu 
JOIN nic ON nic.id_nic = nic_cpu.id_nic;

The query returns the following: sql output

As you can see, the record with id_cpu: 39 is repeated since that cpu has 2 network cards.

What I would like to obtain is this data in a single record, or in any case omit a network card, since I have to list all the existing cpu's and in this case some would be repeated, since there are several cpus with two network cards;

This is what I want to avoid: enter image description here

Is there a way to do it in SQL or PHP?

I am using PHP as programming language and postgresql as DB.

php

3 Answers

  1. Maybe you are looking for this:

    SELECT
  x.id_cpu, x.mac, x.dir_ip
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY t.id_cpu ORDER BY t.mac) AS r,
    t.id_cpu, t.mac, t.dir_ip
  FROM
    (SELECT nic_cpu.id_cpu, nic.mac, nic.dir_ip FROM cpu
     JOIN nic_cpu ON nic_cpu.id_cpu = cpu.id_cpu
     JOIN nic ON nic.id_nic = nic_cpu.id_nic) t) x
WHERE
  x.r = 1;
    • 3
  2. Best Answer

    What I would like to get is that data in a single record

    You can use the function string_aggto concatenate macand dir_ip. Namely:

      SELECT cpu.id_cpu,
         string_agg(nic.mac, ', '),
         string_agg(nic.dir_ip, ', ')
    FROM cpu
    JOIN activo_fijo ON cpu.id_activo = activo_fijo.id_activo
    JOIN nic_cpu ON nic_cpu.id_cpu = cpu.id_cpu
    JOIN nic ON nic.id_nic = nic_cpu.id_nic
GROUP BY 1;
    • 2

  3. What about using DISTINCT 

    Select DISTINCT id_cpu, mac, dir_ip from (
  SELECT cpu.id_cpu, nic.mac, nic.dir_ip FROM cpu 
  JOIN activo_fijo ON cpu.id_activo = activo_fijo.id_activo 
  JOIN nic_cpu ON nic_cpu.id_cpu = cpu.id_cpu 
  JOIN nic ON nic.id_nic = nic_cpu.id_nic
)

    Or Using a Left join

    SELECT cpu.id_cpu, nic.mac, nic.dir_ip FROM cpu 
LEFT JOIN activo_fijo ON cpu.id_activo = activo_fijo.id_activo 
LEFT JOIN nic_cpu ON nic_cpu.id_cpu = cpu.id_cpu 
LEFT JOIN nic ON nic.id_nic = nic_cpu.id_nic;

    Op 3: Group by

    SELECT cpu.id_cpu, max(nic.mac), max(nic.dir_ip) FROM cpu 
JOIN activo_fijo ON cpu.id_activo = activo_fijo.id_activo 
JOIN nic_cpu ON nic_cpu.id_cpu = cpu.id_cpu 
JOIN nic ON nic.id_nic = nic_cpu.id_nic
group by id_cpu
    • 1