Complicated table join with subquery

[marc2982]

Hello,

This db I'm working on tracks the latest revisions of drawings. Multiple drawings fall under a work package, and multiple revisions of a drawing are present (listed 1, 2, 3, ...). I want to pull information about the latest revision of a drawing, including the drawing number, the tracking ID from the tbl_wkpkg_dwg_rev table, and the corresponding dwgTitle from tbl_dwg. The tables are joined through the field wkpkg.

I have my select statement partially working; it works on one table. This is what it returns:
dwg1 3 id1
dwg2 1 id2
dwg3 5 id3

Here is the statement:

SELECT r.dwg, r.revision, r.trackingID
FROM tbl_wkpkg_dwg_rev r, (SELECT MAX(revision) as maxrev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) MaxResults
WHERE r.dwg = MaxResults.dwg
AND r.revision = MaxResults.maxrev;

So I would like to join it to tbl_dwg and get:
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg3 5 id3 DrawingTitle3 wkpkg2

However, when I try and join it with tbl_dwg to get the other information, it returns this:
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg1 3 id1 DrawingTitle1 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg2 1 id2 DrawingTitle2 wkpkg1
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2
dwg3 5 id3 DrawingTitle3 wkpkg2

I tried using SELECT DISTINCT, with no luck. I'm out of ideas, so any help would be appreciated!

Thank you!

 

[Liv Manto]

Have you tried:

SELECT r.dwg, r.revision, r.trackingID
FROM tbl_wkpkg_dwg_rev r, (SELECT MAX(revision) as maxrev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) MaxResults
WHERE r.dwg = MaxResults.dwg
AND r.revision = MaxResults.maxrev
group by r.dwg, r.revision, r.trackingID

 

[marc2982]

Hello!

Thanks for the response, sorry I didn't reply earlier, I was away.

I figured it out; for some reason I assumed that the tables were joined through the field wkpkg (I didn't design the database), but they actually weren't! It turns out I was doing things right, and once I used the correct join everything worked out. For reference, here is the code I used:

SELECT r.wkpkg, r.dwg, r.revision, r.trackingID, d.dwgTitle, d.dwgTitle_1st_3_lines
FROM tbl_wkpkg_dwg_rev AS r, data_dwg AS d, (SELECT MAX(revision) AS MaxRev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) AS m
WHERE r.dwg=d.dwg And m.dwg=r.dwg And r.revision=m.MaxRev
GROUP BY r.wkpkg, r.dwg, r.trackingID, r.revision, d.dwgTitle, d.dwgTitle_1st_3_lines;

Thanks again for responding!