Combine SubQuery and Left Join

[gray]

Hi All

I need to combine two SQL statements which will be used as the Recordsource for a form (but also as the rowsource of comboboxes) but I'm strggling with the syntax.

My principle table has 4 signiticant columns-

Unique_no - Autonumbered PK
ID - long number - represents a 'family' of records within any given table
Version - Integer - represents the version of a record within its 'family'
Media_Unique_no - a foriegn table's Unique Autonumbered PK

e.g.
Unique_no ID Version Media_Unique_no
732 21 1 28
812 21 2 107
847 21 3 13
901 56 1 41
905 56 2 210

The first Statement looks for the highest versions within any particular family within a table using a subquery:-

SELECT Tgt_Tbl.* FROM Tracks AS Tgt_Tbl
,(SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results WHERE Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version

The second joins my principle table to a foriegn table so that it can be sorted by one of the columns of the foriegn table:-

SELECT Tgt_Tbl.* FROM Tracks As Tgt_Tbl LEFT JOIN Media ON Tgt_Tbl.Media_Unique_No = Media.Unique_No WHERE Tgt_Tbl.Deleted=False ORDER BY Media.Name

On their own both work perfectly but I get syntax errors whichever way I try to join combine them.

I build the statements programatically for a dozen
different forms and comboboxes which is why I need just one statement.

Can anyone help me please?

 

[namliam]

SELECT Tgt_Tbl.* FROM Tracks AS Tgt_Tbl
,(SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results WHERE Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version

The second joins my principle table to a foriegn table so that it can be sorted by one of the columns of the foriegn table:-

SELECT Tgt_Tbl.* FROM Tracks As Tgt_Tbl LEFT JOIN Media ON Tgt_Tbl.Media_Unique_No = Media.Unique_No WHERE Tgt_Tbl.Deleted=False ORDER BY Media.Name

On their own both work perfectly but I get syntax errors whichever way I try to join combine them.

Your error probably says " ambigious joins " or something simular, your first query isnt joined 'properly' thus combining the 2 will make problems.

 

[gray]

Hi Namlian

Sadly, I've had numerous attempts with numerous syntax errors - but I thought I should be able to do something like:

SELECT Tgt_Tbl.* FROM Tracks AS Tgt_Tbl
,(SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results

LEFT JOIN Media ON Tgt_Tbl.Media_Unique_No = Media.Unique_No

WHERE Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version

I know I am close but, alas, with this iteration I get a Syntax Error in Join Operation...

 

[namliam]

SELECT Tgt_Tbl.* FROM Tracks AS Tgt_Tbl
,(SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results WHERE Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version

Yes and you are 'stuck' because of this one query, it is not properly joined

Something joined like below should return the same thing

SELECT Tgt_Tbl.* FROM Tracks AS Tgt_Tbl 
Join (SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results on Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version

Then merging the two will probably be less of a problem

 

[gray]

Hi Namilan

Fantastic! Thanks... using 2 joins instead of one subquery and one join has worked! My successful query reads :

SELECT Sort_Tbl.name, Sort_Tbl.description, Tgt_Tbl.* FROM ((Tracks AS Tgt_Tbl)

LEFT JOIN Media As Sort_Tbl ON Tgt_Tbl.Media_Unique_no = Sort_Tbl.Unique_no )

INNER JOIN (SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results on Tgt_Tbl.ID=Max_Results.ID AND Tgt_Tbl.Version=Max_Results.Max_Version

WHERE Tgt_Tbl.Deleted = FALSE

Thanks again!

P.S.
For anyone else trying this, watch out for brackets (parentheses) when joining Access tables. Also, make sure you specify the type of Join (i.e. LEFT JOIN, INNER JOIN in my case), as Access does not seem to have a default Join as some other sytems do.

 

[namliam]

Having no brackets should work too ...