SELECT EXISTS() AS has_record FROM table1

[jluost1]

In Access, I can run the query like:

SELECT username, EXISTS(SELECT * FROM member WHERE username='abc') AS has_record FROM table1

I am trying to do the same in SQL7 database:

SELECT username, "has_record" = EXISTS(SELECT * FROM member WHERE username='abc') FROM table1

But this doesn't work.

Can anyone tell me how to make it work (store testing result as a column)?

 

[Bernadette]

Hi There

Try the following ................

SELECT Username 'Has Record'
FROM Table1
WHERE EXISTS (SELECT * FROM Member WHERE username='abc')

Hope this helps


Bernadette

 

[jluost1]

This is not what I need because I would like to see those users who have no record also. The ideal output should be like this:

username has_record
userA 1
userB 0
userC 1
userD 0

 

[tlbroadbent]

Try this.
[tt]
SELECT username,
"has_record" =
CASE
WHEN EXISTS
(SELECT *
FROM member
WHERE username=table1.Username)
THEN 1 ELSE 0 END
FROM table1[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jluost1]

This is exactly what I want. Thank you.