quote:

---

Originally posted by gradstudent2010

I'm using CF8 and I have an SQL issue. I'm trying to display data from three tables using one query. Here is the table information with alias and data examples:

*[pkey] means it's the primary key.


Doc d - [pkey]d.docID, d.docTitle (Holds the doctitle for each docID)

EX:
111 Emp Application
222 Budget Request
333 Vacation Request


ReadData r - [pkey]r.id, r.transactID, r.docID, r.itemID (holds multiple docID for each transactID, and holds multiple itemid for each docID)

EX:
1 12345 111 1
2 12345 111 2
3 12345 111 3
4 12345 222 1
5 73624 111 1


Doctransact dt - [pkey]di.id, dt.docID, dt.transactID
(Holds multiple docID for each transactID)

EX:
1 111
2 222
3 333


I want to display:
1.) all d.docTitles WHERE d.docID = dt.docID AND dt.transactID = 1.
2.) the Max r.itemid (if any) for all rows in the above query results, WHERE r.docID = dt.docID AND r.transactID = dt.transactID

Here is the SQL I have written:

SELECT d.doctitle, r.itemid
FROM (file_doctransact dt INNER JOIN file_doc d ON dt.docID = d.docID) LEFT OUTER JOIN file_readdata r ON dt.docID = r.docID
WHERE dt.transactID = 1

Which displays: ALL r.itemid (if any) for all rows in the above query results, where r.docID = dt.docID. I don’t want to display multiple rows of [the same docTitle and different docID]. I want it to display the one with the highest docID.

I get an error when I try to use the Max() function, such as:

SELECT d.doctitle, Max(r.itemid).

Please help.

---