CODEWITHSUNDEEP
sqlpostgresql
Omar
Omar

Reputation: 3040

postgresql WHERE IN (list) multiple tables return data from table 2

The following will return 2 records as expected.

SELECT 
    cds_Prod.ProdID, 
    catid, 
    imgid, 
    mfid, 
    mfpn
FROM 
    public.cds_prod
WHERE
    cds_Prod.mfpn IN ('726722-B21', 'SUP-UFMSDN-4K-1GP')
LIMIT 
    1000

Now I need to combine the a description for each result... This is my attempt

SELECT 
    cds_Prod.ProdID, 
    catid, 
    imgid, 
    mfid, 
    mfpn, 
    cds_Stdnez.Description 
FROM 
    public.cds_prod, 
    public.cds_stdnez
WHERE
    cds_Prod.mfpn IN ('726722-B21', 'SUP-UFMSDN-4K-1GP')
LIMIT 
    1000

That returns over 1000 records that have the same mfpn. What i really want is the first 2 records but with descriptions attached.

Upvotes: 0

Views: 51

Answers (2)

emendez
emendez

Reputation: 440

I believe this is the join key. 

   SELECT 
    cds_Prod.ProdID, 
    catid, 
    imgid, 
    mfid, 
    mfpn, 
    cds_Stdnez.Description 
FROM 
    public.cds_prod a 
    LEFT JOIN public.cds_stdnez b on a.ProdID = b.ProdID
WHERE
    cds_Prod.mfpn IN ('726722-B21', 'SUP-UFMSDN-4K-1GP')
LIMIT 
    1000

Upvotes: 1

forpas
forpas

Reputation: 164089

What you are doing is an old fashioned join but you are missing the link between the 2 tables.
There has to be a column in the table cds_stdnez that references the column ProdID of the table cds_Prod, with a similar name like ProdID, so add an ON clause:

FROM public.cds_prod INNER JOIN public.cds_stdnez
ON cds_Prod.ProdID = cds_stdnez.ProdID

and even better use aliases for the tables and qualify all the columns with these aliases:

SELECT 
    p.ProdID, 
    p.catid, 
    p.imgid, 
    p.mfid, 
    p.mfpn, 
    s.Description 
FROM 
    public.cds_prod AS p INNER JOIN public.cds_stdnez AS s
    ON p.ProdID = s.ProdID
WHERE
    p.mfpn IN ('726722-B21', 'SUP-UFMSDN-4K-1GP')
LIMIT 
    1000

Note that LIMIT without ORDER BY is never a good idea.

Upvotes: 0

Related Questions