SQL - select values from two tables but without duplicates-CodePudding

I have two tables, and I want to select all columns from one table and plus one more column from another table, my code:

SELECT uc.*,ul.NAME FROM UC AS uc
inner JOIN UL AS ul
ON uc.PARTY_ID = ul.PARTY_ID WHERE PID = '33322';

This works but since in the table UL i have multiple value for PARTY_ID, for each of them i ve got extra row, can i somehow disable that, i only want to get one vale, any help?

UC table sample:

ID, PARTY_ID, PID
1, AAA, 33322
2, BBB, 33322
3, CCC, 2222222

UL table sample:

ID, PARTY_ID, NAME
1, AAA, HELLO
2, AAA, GOODBYE
3, BBB, LILA
2, BBB, MULI

The result should be:

ID, PARTY_ID, PID, NAME
1, AAA, 33322, HELLO
2, BBB, 33322, LILA

CodePudding user response：

If you have several repeated rows you could try using DISTINCT

SELECT distinct uc.*,ul.NAME FROM UC AS uc
inner JOIN UL AS ul   ON uc.PARTY_ID = ul.PARTY_ID 
WHERE PID = '33322';

but based on your sample if you want the result of all the names in a rows you could use group_concat

SELECT uc.*, group_concat(ul.NAME)
FROM UC AS uc
inner JOIN UL AS ul   ON uc.PARTY_ID = ul.PARTY_ID 
WHERE PID = '33322'
group by uc.id;

otherwise if the name value is not relevant you coul try using a fake aggregation function

SELECT uc.*, min(ul.NAME)
FROM UC AS uc
inner JOIN UL AS ul   ON uc.PARTY_ID = ul.PARTY_ID 
WHERE PID = '33322'
group by uc.id;

CodePudding user response：

You can use DISTINCT Function to return unique results.

SELECT DISTINCT uc.*,ul.NAME FROM UC AS uc
inner JOIN UL AS ul
ON uc.PARTY_ID = ul.PARTY_ID WHERE PID = '33322';

CodePudding user response：

it can be random – ttyttt

SELECT uc.column1, uc.column2, ... uc.columnN,
       ANY_VALUE(ul.NAME) ul_NAME 
FROM UC AS uc
INNER JOIN UL AS ul USING (PARTY_ID) 
WHERE PID = '33322'
GROUP BY uc.column1, uc.column2, ... uc.columnN;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7d660ca882e19ee391db9c67c6df3705

CodePudding user response：

You can try using sub-query as well:

SELECT uc.*, sq.name
FROM UC as uc
INNER JOIN
(
  SELECT DISTINCT party_id, MIN(name) as name
  FROM UL
  GROUP BY party_id
) as sq
ON uc.party_id=sq.party_id
WHERE uc.PID = '33322'

The sub query will return first name for each party_id