how do I use sql DISTINCT,UNION with where clause-CodePudding

Dear All,

how do I use sql DISTINCT,UNION with where clause?

column "GDN" is in the database table "GPG".

please recommend and solution.

Thanks

TABEL : GPD

PNM
GPPI12301001
GPPI12301002
GPPI82301001
GPPI82301002

TABEL : GPG

GDN	PNM
A.04.01.002.001	GPPI12301001
A.04.01.002.001	GPPI12301002
A.04.01.008.001	GPPI82301001
A.04.01.008.001	GPPI82301002

desired result

PNM
GPPI12301001
GPPI12301002

 Dim query As String = "SELECT DISTINCT PNM FROM GPD UNION SELECT DISTINCT PNM FROM GPG ORDER BY PNM"

Below where clause I mean in the GPG database table

WHERE GDN = 'A.04.01.002.001'

CodePudding user response：

Each SELECT has its own WHERE clause. And the UNION has an ORDER BY for its final result.

SELECT PNM
FROM GPD

UNION

SELECT PNM
FROM GPG
WHERE GDN = 'A.04.01.002.001'  -- belongs to the UNIONS's 2:nd SELECT

ORDER BY PNM  -- belongs to the whole UNION

CodePudding user response：

SELECT DISTINCT PNMs.PNM 
FROM (
        SELECT PNM FROM GPD
    UNION
        SELECT PNM
        FROM GPG
        WHERE GDN = 'A.04.01.002.001'
) PNMs
ORDER BY PNM

CodePudding user response：

You will never get your desired output so long as you UNION all the PNM values from the GPD table. You will always get all the PNM values instead of the subset related to the given GDN value.

All you need is this:

SELECT DISTINCT PNM
FROM GPG
WHERE GDN = 'A.04.01.002.001'
ORDER BY PNM