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!

 

[ThomasLafferty]

Few questions:
(my SQL is a bit weak...) What is the present relationship between the tables? Take a look at your relationship window and make sure everything is hunky dory there. My guess is that the tables are not related properly and it is returning one record from the Drawing table for every record in the other table. Failing that, you could try using SELECT TOP 1 at the start of your SQL statement and see what that gets you.

Hope this helps.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Also, I noticed a space in your Table name in the SQL you posted:

tbl_wkpkg_dwg_rev [!]space here[/!] r

And I was wondering if this was intentional since you used underscores in the rest of your table name.

For what it's worth, I haven't had time to try to recreate your scenario, so I'm just making guesses...

Tom

Live once die twice; live twice die once.

 

[PHV]

Tom, r is an alias (the AS keyword is not mandatory).

 

[ThomasLafferty]

PHV: gotcha!

Rather than making any more guesses, could you list all involved field names and their data types? I can deduce them from your SQL above, but looking at what you have listed for return, I am a bit confused.

When I look at your original post:

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

and compare it to your SQL:

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;

Right away I notice that it appears to return [!]dwg[/!]1 and [!]dwg[/!]2 which gets me wondering about your data type and the actual value displayed when you run your query. Is it really [!]dwg[/!]1, or does it actually display a [!]1[/!]? The reason I'm being a little slavish about wanting to know your field names and data types is that it looked like you were including the field name [!]dwg[/!] in the field's data itself, so if you could humor me and list all field names and their data types as well as spelling out the kind of relationship between the tables? I'm betting one to many on wkpkg, but it would sure be nice to get a fully clarified picture so I can set up identical tables and fields with data so I can work on the problem.

Hope you don't mind, and the colored highlights above aren't intended as rants, just visual attention getters.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Is this what you mean that the query returns?

[b]dwg     ID     DrawingTitle     wkpkg[/b]
 1      1          1              1
 1      1          1              1
 1      1          1              1
 1      1          1              1
 2      2          2              2
 2      2          2              2
 2      2          2              2
 2      2          2              2

Live once die twice; live twice die once.

 

[marc2982]

Hi Guys!

Thanks for the responses, 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!