Simple Query Help Please

[tclaremont]

I have two related tables. tblVisits, and tblProcedure. Each Visit has one or more procedures, linked on VisitIDNo.

What is the syntax to return ALL the visits, and only the FIRST procedure (CPT4)?

<cfquery name="GetAll" datasource="PaceAce">
SELECT tblVisits.VisitIDNo, tblProcedure.VisitIDNo, tblProcedure.CPT4
FROM tblVisits
JOIN tblProcedure ON tblVisits.VisitIDNo = tblProcedure.VisitIDNo
</cfquery>

[tclaremont]

I solved it by using DISTINCT.

Not sure if that is the textbook way, though.

<cfquery name="GetAll" datasource="PaceAce">
SELECT DISTINCT tblVisits.VisitIDNo, tblProcedure.VisitIDNo, tblProcedure.CPT4
FROM tblVisits
JOIN tblProcedure ON tblVisits.VisitIDNo = tblProcedure.VisitIDNo
</cfquery>

[Dan_Bracuk]

I'm wondering why your business rules don't compel you to identify a primary procedure.

[tclaremont]

Long story, but we are not a typical health organization. I agree that just about every other health care environment would dictate a primary procedure. I have been fighting that battle for years.