p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Re: help converting access query to mysql query


Message #1 by "Gellings, C.O." <gellingsco@p...> on Sat, 01 Feb 2003 12:59:59 +0100
To start with, MySQL doesn't use the INNER JOIN like ACCESS, but you can 
use alias' which make things quicker to type.

SELECT b.LabAstecNum, b.LabID, c.JobOrderID,
c.PStage, b.PSize, pstage, psize,
b.OrderID, d.CustomerID, d.OrdDateRcvd,
b.TestClass, d.ProductType, f.CtAvSeeds_Lb,
f.CtAvLbs_Unit, d.IncSeedVar, d.IncSeedsSize,
b.LbsOut, b.UnitsOut, d.AproxShipDate,
e.DC2_perc_Germ

FROM tblOrderInfo


tblOrderInfo as a, tblLabData as b RIGHT JOIN tblJobOrders as c ON 
(b.JobOrderID = c.JobOrderID)
LEFT JOIN tblCount as f ON (b.LabID = f.LabID)
LEFT JOIN tblGermTests as e ON (b.LabID = e.LabID)
LEFT JOIN tblOrderInfo as d ON (d.OrderID = b.OrderID)


WHERE (((b.TestClass)=2 Or (b.TestClass)=3))
[here you'll need to connect the missing links: => you've already linked c, 
d, e, f]
AND a.[fieldname] = b.[fieldname]
ORDER BY 3, 6, 7;

an other option is to skip the join part and create the join in the 
'where-clause'
you start with FROM TABLE1 AS a1, TABLE2 AS a2 TABLE3 AS a3 ( the 'AS' bit 
is not required, but you create an alias for the table name)
then you add the 'WHERE' bit linking the tables with the requirements

WHERE a1.[FIELDNAME]=a2.[FIELDNAME]
AND ax.[FIELDNAME]=ax.[FIELDNAME]
(etc)
AND a1.[FIELDNAME] (= <> > <) [value]
AND ax.[FIELDNAME] (= <> > <) [value]
AND ax.[FIELDNAME] (= <> > <) [value]

finally ORDER BY [column number], [column number], [column number] etc
  if possible with the selection you can even do a GROUP BY after this


h.t.h.

Carl


  Return to Index