person/alias query

[Brett SQL]

I'm trying to construct an ASP query in VBScript. The query should take a
string (queryLastName) and match all aliases (PerAlso.LastName) and real
names (Per.LastName). The results should be only real names (Per.LastName,
etc) with *ALL* aliases of real names in HTML small type.

My problem is trying to construct the subquery, join, union or whatever,
and putting together a queryRS recordset that I can loop thru and output
HTML. Plus getting all aliases for a record that only matches queryLastName
with Per.LastName.

THANKS!!!


'works but no PerAlso
'strSQL = "SELECT Per.FirstName, Per.LastName FROM Per WHERE Per.LastName
LIKE '" & queryLastName & "%' ORDER BY Per.LastName, Per.FirstName"

'works but only PerAlso matches, doesn't search Per column
'strSQL = "SELECT Per.PerID, Per.FirstName, Per.LastName, PerAlso.AlsoFirstName,
PerAlso.AlsoLastName FROM PerAlso, Per WHERE PerAlso.AlsoLastName LIKE '"
& queryLastName & "%' AND PerAlso.PerID = Per.PerID"

'doesn't work, invalid union
strSQL = "SELECT Per.PerID, Per.FirstName, Per.LastName FROM Per WHERE Per.LastName
LIKE '" & queryLastName & "%' UNION SELECT Per.PerID, Per.FirstName, Per.LastName,
PerAlso.AlsoFirstName, PerAlso.AlsoLastName FROM PerAlso, Per WHERE PerAlso.AlsoLastName
LIKE '" & queryLastName & "%' AND PerAlso.PerID = Per.PerID ORDER BY 3"