Join returning too many records 1

[schase]

Howdy folks.
(using mySQL 5.x as database)

This is a tad embarrassing, I've got my main table that I need to get info from two supporting tables. It seems to return ok, but I want all records from the main, and 1 each from the supporting tables.

tblCustomer is the main table
tblHistory returns all matching records for the LeadID instead of just the top one.
tblSalesPerson would only have one match anyway.

mySQL="Select tblCustomer.LeadID,tblCustomer.fldTitle,tblCustomer.fldFirstName,tblCustomer.fldLastName,tblCustomer.fldEmail, tblHistory.fldDate,tblHistory.fldType,tblHistory.fldFollowUp,tblHistory.fldFollowUpDate,tblHistory.fldFollowedUp,tblHistory.fldSalesPerson,tblSalesPerson.fldSalesSignature from tblCustomer "
mySQL=mySQL & " LEFT OUTER JOIN tblHistory ON (tblCustomer.leadID = tblHistory.fldLeadID)"
mySQL=mySQL & " LEFT OUTER JOIN tblSalesPerson ON (tblCustomer.fldSales1 = tblSalesPerson.fldUserName)"
mySQL=mySQL & " where (tblCustomer.fldEmail<>"" "" AND tblCustomer.fldEmail is NOT NULL) AND tblCustomer.fldOptOut=0 AND tblCustomer.fldNewsletter=0 order by tblCustomer.leadID asc, tblHistory.historyID desc Limit " & BDQuotes(request("txtAmount")) & ";"
set rs=Conn.execute(mySQL)

What am I missing to only return one matching from tblHistory?

Thank you in advance.

Stuart

 

[Tarwn]

I have not done much with MySQL queries recently, so this syntax could be way off.

The problem you have right now is that your JOINing to all of the records in tblHistory without filtering for the top one for each lead. Perhaps the easiest way to manage this would be to use a nested query to provide a list of the most recent history records for each lead, then add that to the filter for your join. Something like:

mySQL="Select tblCustomer.LeadID,tblCustomer.fldTitle,tblCustomer.fldFirstName,tblCustomer.fldLastName,tblCustomer.fldEmail, " & _
	"tblHistory.fldDate,tblHistory.fldType,tblHistory.fldFollowUp,tblHistory.fldFollowUpDate,tblHistory.fldFollowedUp," & _
	"tblHistory.fldSalesPerson,tblSalesPerson.fldSalesSignature " & _
"FROM tblCustomer " & _
"LEFT OUTER JOIN tblHistory ON (tblCustomer.leadID = tblHistory.fldLeadID [highlight]AND tblHistory.historyId IN (SELECT MAX(th2.historyId) FROM tblHistory th2 GROUP BY th2.LeadId))[/highlight] " & _
"LEFT OUTER JOIN tblSalesPerson ON (tblCustomer.fldSales1 = tblSalesPerson.fldUserName) " & _
"WHERE (tblCustomer.fldEmail<>"" "" AND tblCustomer.fldEmail is NOT NULL) " & _
"AND tblCustomer.fldOptOut=0 AND tblCustomer.fldNewsletter=0 " & _
"ORDER BY tblCustomer.leadID asc, tblHistory.historyID desc Limit " & BDQuotes(request("txtAmount")) & ";"

I highlighted the relevant addition. Now I am only accepting history records who are in a set of all the maximum history records for each lead. You may have been looking for a different way to tell the one history record to use, so you would change that subquery based on your needs.

-T

Best MS KB Ever:

http://support.microsoft.com/kb/555375

 

[schase]

Thank you Tarwn,

And sorry for the lengthy delay in responding - Roadrunner still has not graced me with their presence to repair my darn connection.

I've never done a nested select, and I seem to have coded myself a heckova loop or something that shoots CPU up to 100%.

Select tblCustomer.LeadID,tblCustomer.fldTitle,tblCustomer.fldFirstName,tblCustomer.fldLastName,tblCustomer.fldEmail, tblHistory.fldDate,tblHistory.fldType,tblHistory.fldFollowUp,tblHistory.fldFollowUpDate,tblHistory.fldFollowedUp,tblHistory.fldSalesPerson,tblSalesPerson.fldSalesSignature from tblCustomer LEFT OUTER JOIN tblHistory ON (tblCustomer.leadID = tblHistory.fldLeadID AND tblHistory.historyId IN (SELECT MAX(tblHistory.historyId) FROM tblHistory GROUP BY tblHistory.fldLeadId)) LEFT OUTER JOIN tblSalesPerson ON (tblCustomer.fldSales1 = tblSalesPerson.fldUserName) where (tblCustomer.fldEmail<>" " AND tblCustomer.fldEmail is NOT NULL) AND tblCustomer.fldOptOut=0 AND tblCustomer.fldNewsletter=0 order by tblCustomer.leadID asc, tblHistory.historyID desc Limit 10;

If you can spot where I may be going awry I would greatly appreciate it.

Stuart

 

[schase]

well pretty close Tarwn,
I asked for help on aspmessageboard too and over there they showed this bit

Try changing your tblHistory JOIN to this: 
LEFT OUTER JOIN tblHistory ON 
( 
tblCustomer.leadID = tblHistory.fldLeadID 
AND 
tblHistory.historyID = (SELECT MAX(historyID) FROM tblHistory WHERE fldLeadID = tblCustomer.leadID) 
)

Worked great.

Stuart

 

[Tarwn]

Doh...Sorry, I must have been thinking of two methods at once, using the nested select for a comparison value and just joining directly to a nested select, so i missed a piece. Sorry about that, hope your PC will recover

Best MS KB Ever:

http://support.microsoft.com/kb/555375