Multi-column query with Max(date) and join

Sailing_12
Sailing_12 used Ask the Experts™
on
I have the below query which works fine until I uncomment the 2 lines for the join at which point I get the error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cem.new_medicationid" could not be bound.

How do I join a table to this query?
SELECT cem.new_medicationid, cem.new_dose, cem.new_datestarted
FROM new_chaenteredmedication cem WITH (NOLOCK),
   (SELECT max(new_datestarted) as maxdate, new_medicationid
     FROM new_chaenteredmedication WITH (NOLOCK)
	 WHERE new_contactid = '3045FFED-B3C2-DC11-9149-0003FF947908' COLLATE database_default
	 AND new_datestopped is null
     GROUP BY new_medicationid) maxresults
--JOIN meds_master..ndc_pkg_product pp
--ON cem.new_medicationid = pp.pkg_product_id COLLATE database_default
WHERE cem.new_medicationid = maxresults.new_medicationid
AND cem.new_datestarted = maxresults.maxdate

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try

SELECT       cem.new_medicationid,
      cem.new_dose,
      cem.new_datestarted

FROM new_chaenteredmedication cem WITH (NOLOCK),

inner join (SELECT       max(new_datestarted) as maxdate,
                  new_medicationid
                FROM new_chaenteredmedication WITH (NOLOCK)
             WHERE new_contactid = '3045FFED-B3C2-DC11-9149-0003FF947908' COLLATE database_default
                   AND new_datestopped is null
                 GROUP BY new_medicationid
      ) maxresults on cem.new_medicationid = maxresults.new_medicationid
                  AND cem.new_datestarted = maxresults.maxdate

JOIN meds_master.dbo.ndc_pkg_product pp
ON cem.new_medicationid = pp.pkg_product_id COLLATE database_default

or

SELECT       cem.new_medicationid,
      cem.new_dose,
      cem.new_datestarted

FROM new_chaenteredmedication cem WITH (NOLOCK),

inner join (SELECT       max(new_datestarted) as maxdate,
                  new_medicationid
                FROM new_chaenteredmedication WITH (NOLOCK)
             WHERE new_contactid = '3045FFED-B3C2-DC11-9149-0003FF947908' COLLATE database_default
                   AND new_datestopped is null
                 GROUP BY new_medicationid
      ) maxresults on cem.new_medicationid = maxresults.new_medicationid
                  AND cem.new_datestarted = maxresults.maxdate

inner join meds_master..ndc_pkg_product pp
ON cem.new_medicationid = pp.pkg_product_id COLLATE database_default

Sailing_12Pirate

Author

Commented:
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial