SQL question-- Alternatives to IN statement (nested SQL)
Posted on 2004-08-09
OK, in the absence of a successful mySQL 4.1 installation, I need to know some alternatives to using nested SQL statements.
here's the SQL I have now that is symptomatic of what I need to change:
SELECT field, field, field, field FROM tblcomp WHERE compID IN (SELECT compID_Inv FROM tblinv WHERE mfgID = 56;) ORDER BY CompanyName ASC;
tblcomp = company table
tbl inv = Inventory table
Each company could potentially own SEVERAL machines (held in the tblinv table), but all I WANT is ONE record for each company. But when I use distinct keyword or DISTINCTROW or a LEFT join I still get multiple company records-- In the above example, it is grabbing a list of all companies that own a machine from a specific manufacturer (mfgID). The IN statement works great-- but in mySQL it can't handle the nested query.
Does ANYONE know a way to get the list either with a rewritten single SQL select statement or using a HEAP table?