hi,

i have:

select distinct P.ID, P.INVR, P.LVL3,
D.MO, H.hSTAT,
D.PIDESC, PD.DESC, RPC.CON
from
LM.dbo.PIP AS P

join LM.dbo.Inv AS H ON
(P.INVR = H.INVR)

join LM.dbo.tblProd AS PD ON
(P.ID = PD.ID)

join LMRe.dbo.mini AS RPC ON
(P.ID = RPC.Acct)

join CT.dbo.tblLVL AS D ON
(P.LVL3 = D.LVL3)
WHERE D.MO =
(select max(MO) from CT.dbo.tblLVL)


I would like to add this one to above, assuming P can be joined by CD column
to it:
select distinct CD, TX
from LMRe.dbo.mini A
Join dev.dbo.Ref B
On A.Wcode = b.acancd
where aciscm = 8
and typCODE In(822,23)


thanks in advance,
geebee

Without saying us why you are using the statement DISTINCT in both cases and
how they could possible complement or compete each other, it's not really
possible to tell you for sure how to do this.

The brut case method would be to transform both queries as subqueries and
join them on the CD column; however, this might not be the most optimal way
of joining these two queries.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"geebee" <geraldjr30 (AT) hotmail (DOT) com(noSPAMs)> wrote

You can try this:

SELECT DISTINCT
P.ID,
P.INVR,
P.LVL3,
D.MO,
H.hSTAT,
D.PIDESC,
PD.DESC,
RPC.CON,
T.TX
FROM LM.dbo.PIP AS P
JOIN LM.dbo.Inv AS H
ON P.INVR = H.INVR
JOIN LM.dbo.tblProd AS PD
ON P.ID = PD.ID
JOIN LMRe.dbo.mini AS RPC
ON P.ID = RPC.Acct
JOIN CT.dbo.tblLVL AS D
ON P.LVL3 = D.LVL3
JOIN (SELECT DISTINCT CD, TX
FROM LMRe.dbo.mini AS A
JOIN dev.dbo.Ref AS B
ON A.Wcode = b.acancd
WHERE aciscm = 8
AND typCODE IN (822,23)) AS T
ON P.CD = T.CD
WHERE D.MO = (SELECT MAX(MO)
FROM CT.dbo.tblLVL);

--
Plamen Ratchev
http://www.SQLStudio.com