Join a table to a subquery which has union in it

[robin thomas]

I tried to write a query in visdata. but its not working. Please tell me what i should do.

select * from customer (select lcfloyaltyid as [loyaltyid],lcfdateofbirth as [lcfdob],lcfanniversary as [anniversary],lcfspousedob as [spousedob], 0 as 'lccdateofbirth' from loyaltycardfamilyinfo where lcfdateofbirth<datevalue('01/01/1910') and lcfdateofbirth<>datevalue('12:00:00 AM') union all select lcfloyaltyid as [loyaltyid],lcfdateofbirth as [lcfdob],lcfanniversary as [anniversary],lcfspousedob[spouse],lccdateofbirth from loyaltycardchildinfo lcc left join loyaltycardfamilyinfo lcf on lcc.lccfid=lcf.lcfid where lccdateofbirth<datevalue('01/01/1910') and lccdateofbirth<>datevalue('12:00:00 AM')) where c.cloyaltyid=lcf.lcfloyaltyid

[russell]

visdata is a pass-through tool. what is the underlying dbms?

[russell]

looking at your query, i'm guessing MS Access is the db? Your syntax is a bit messed up, but I'm guessing that this is what you are after

select *
from customer c
INNER JOIN
( select lcfloyaltyid as [loyaltyid],
lcfdateofbirth as [lcfdob]
lcfanniversary as [anniversary],
lcfspousedob as [spousedob],
0 as 'lccdateofbirth'
from loyaltycardfamilyinfo
where lcfdateofbirth < datevalue('01/01/1910')
and lcfdateofbirth <> datevalue('12:00:00 AM')
union all
select lcfloyaltyid as [loyaltyid],
lcfdateofbirth as [lcfdob],
lcfanniversary as [anniversary],
lcfspousedob[spouse],
lccdateofbirth
from loyaltycardchildinfo lcc
left join
loyaltycardfamilyinfo lcf
on lcc.lccfid = lcf.lcfid
where lccdateofbirth < datevalue('01/01/1910')
and lccdateofbirth <> datevalue('12:00:00 AM')

) as x
On c.loyaltyid = x.loyaltyid