SQL JOIN Problem

Hi, I've created a recordset in DMX but needed to manually edit it as I need
more flexibility, but I am stuck. The following SQL joins 3 tables to give me
a list of Links from the Links table. It only gives the links if they have
transactions present in the Transactions table. I need to do a kind of LEFT
OUTER JOIN but there are three tables and this is MS ACCESS and vbscript. Can
anyone help me reformat this SQL so that I also get records from table Links
even if there are no matching records in Transactions. SELECT
Transactions.LinkID,Links.LinkName,Count(Transacti ons.LinkID) AS NumberLinks,
Min(Transactions.TranDate) AS FromDate, Max(Transactions.TranDate) AS
ToDate,Links.LinkDateAdded, LogTotals.Total FROM Transactions,Links, (SELECT
LinkLogs.LogLinkID, Sum(LinkLogs.LogCost) AS Total FROM LinkLogs Group BY
LinkLogs.LogLinkID) AS LogTotals WHERE Transactions.LinkID=Links.LinkID and
Transactions.LinkID=LinkLogs.LogLinkID AND Transactions.SiteID=1 Group By
Transactions.LinkID,Links.LinkName, LogTotals.Total,Links.LinkDateAdded; Many
thanks

Hi yea,
I have had to do complex queries like this a few times before but I used the
Query extension frmo http://www.interaktonline.com , ok it is a commercial
extension but it can save you loads of time.

twocan's




"Red Bull" <webforumsuser (AT) macromedia (DOT) com> wrote

Hi, I've created a recordset in DMX but needed to manually edit it as I
need
more flexibility, but I am stuck. The following SQL joins 3 tables to
give me
a list of Links from the Links table. It only gives the links if they have
transactions present in the Transactions table. I need to do a kind of
LEFT
OUTER JOIN but there are three tables and this is MS ACCESS and vbscript.
Can
anyone help me reformat this SQL so that I also get records from table
Links
even if there are no matching records in Transactions. SELECT
Transactions.LinkID,Links.LinkName,Count(Transacti ons.LinkID) AS
NumberLinks,
Min(Transactions.TranDate) AS FromDate, Max(Transactions.TranDate) AS
ToDate,Links.LinkDateAdded, LogTotals.Total FROM Transactions,Links,
(SELECT
LinkLogs.LogLinkID, Sum(LinkLogs.LogCost) AS Total FROM LinkLogs Group BY
LinkLogs.LogLinkID) AS LogTotals WHERE Transactions.LinkID=Links.LinkID
and
Transactions.LinkID=LinkLogs.LogLinkID AND Transactions.SiteID=1 Group By
Transactions.LinkID,Links.LinkName, LogTotals.Total,Links.LinkDateAdded;
Many
thanks

Plug the SQL into an Access query and then use the visual tools to alter the
properties of the join.

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004