Help with Distinct statement

[DebHanleyRI]

I have two tables I need to join to populate an excel spreadsheet. The following statement works, but, now I need to modify to select only distinct oldack.trans_recordid.

The oldack.date is the primary key.

Here is my select statement:

SELECT oldack.[Date], oldack.ack, oldack.visit_id, oldack.trans_recordid, transcripts.trans_three, Transcripts.Recordid, transcripts.visit_id, transcripts.transcript
FROM oldack
INNER JOIN
Transcripts ON (LTRIM(oldack.trans_recordid)) = Transcripts.Recordid
WHERE (oldack.date > "dateVariable" ) and (oldack.ack LIKE '%MSA|AE%')

Thanks,
Deb

not all who wander are lost....

 

[mwolf00]

If you want just unique trans_ids returned from the query, you will need to tell the database what other information you want for that ONE record - I'm not explaining this too well...

Lets say that the table has
Trans_id visit_ID
1 1
1 2
1 3
1 3

SELECT DISTINCT trans_id FROM myTable
Trans_id
1

BUT when you add a second column with multiple value to the query you get..
SELECT DISTINCT trans_id, visit_ID FROM myTable
Trans_id visit_ID
1 1
1 2
1 3

In this case you need to tell the DB that you want just one visit_id (let's say the first one)

SELECT DISTINCT m.trans_Id, d.visit_id FROM myTable m
JOIN (SELECT distinct trans_id, min(visit_id) as visit_id FROM myTable GROUP BY trans_id) d ON m.trans_id = d.trans_ID

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[DebHanleyRI]

Thanks for the quick response - Let me mention I am a newbie. I believe that I have followed your instructions correctly, but the error I get is:

Line 3: Incorrect syntax near '.'.
Line 3 is: as Transcripts.Recordid...

I tried the following:
SELECT DISTINCT oldack.trans_recordid, Transcripts.Recordid FROM oldack
JOIN (SELECT distinct oldack.trans_recordid, min(Transcripts.Recordid),oldack.[Date], oldack.ack, oldack.visit_id, transcripts.trans_three, Transcripts.Recordid, transcripts.visit_id, transcripts.transcript
as Transcripts.Recordid FROM transcripts GROUP BY trans_id)
ON (LTRIM(oldack.trans_recordid)) = Transcripts.Recordid
WHERE (oldack.date > '02/05/04' ) and (oldack.ack LIKE '%MSA|AE%')

I am not sure that I have added the additional fields I need in the correct spot.

not all who wander are lost....

 

[mwolf00]

If you REALLY want to call it that (you already have a column called recordid from transcripts) then put brackets around it ([])

SELECT DISTINCT oldack.trans_recordid, Transcripts.Recordid FROM oldack
JOIN (SELECT distinct oldack.trans_recordid, min(Transcripts.Recordid),oldack.[Date], oldack.ack, oldack.visit_id, transcripts.trans_three, Transcripts.Recordid, transcripts.visit_id, transcripts.transcript
as [Transcripts.Recordid] FROM transcripts GROUP BY trans_id)
ON (LTRIM(oldack.trans_recordid)) = Transcripts.Recordid
WHERE (oldack.date > '02/05/04' ) and (oldack.ack LIKE '%MSA|AE%')


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)