select stext, 
(SELECT lfieldtype 
FROM lufieldtype 
WHERE (lfieldtypeid = strTool.lfieldtype)) as hhh, 
loptionid
FROM strtoolinfo
WHERE ltoolinfoid not in (select ltoolinfoid from strlnktooltoolinfo WHERE stoolguid = '{79be4f69-6081-4029-8fe7-802048030b5b}')

quote:

---

Originally posted by insanepaul

I have a select command that should return 2 columns as below but the second column (lfieldtype) references another table 'lufieldtype' which I'm stuck on an error message. Rather than getting the ID I want to get the corresponding sDescription from that table. I can't figure out what i'm doing wrong. If I just select the first column I get the correct results.
Example of the 2 tables:
Table lufieldtype

ID sDescription
1 Readonly
2 Writeonly
3 Text

Table strtoolinfo
stext lfieldtype
sometext... 1
sometext... 2
sometext... 1
etc..

select stext, 
(SELECT lfieldtype 
FROM lufieldtype 
WHERE (lfieldtypeid = strtoolinfo.lfieldtypeid)) as hhh, 
loptionid
FROM strtoolinfo
WHERE ltoolinfoid not in (select ltoolinfoid from strlnktooltoolinfo WHERE stoolguid = '{79be4f69-6081-4029-8fe7-802048030b5b}')

---

quote:

---

Originally posted by insanepaul

wow that was quick and correct...Thanks - was driving me insane

---

select s.stext, l.lfieldtype as hhh, 
s.loptionid
FROM strtoolinfo as s
inner join lufieldtype l on l.lfieldtypeid = s.lfieldtype
WHERE NOT EXISTS  (select 1 from strlnktooltoolinfo WHERE ltoolinfoid=s.ltoolinfoid 
AND stoolguid = '{79be4f69-6081-4029-8fe7-802048030b5b}')