By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Help with query syntax please

P: n/a
Using A2K. I have a table with the columns ClientID, Program, and
EnterDate. Each client can have multiple records in this table. Each
record in the table has an ID and a program but not necessarily an
enter date. How do I pull out only those clients who do not have an
enter date for any possible program?

If my data looks like this:

ID Program EnterDate
-- ------- ---------
59 AAA <null>
59 DDD 10/01/03
60 AAA <null>
60 DDD <null>
61 AAA 09/01/03
61 DDD 10/01/03

I want the query results to look like this:

60 AAA <null>
60 DDD <null>
Thanks for any help or advice.

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
SELECT ID, Program, EnterDate FROM Table
WHERE EnterDate Is Null

Should work ;)

Nov 13 '05 #2

P: n/a
Tried that before posting and that gives me:

59 AAA <null>
60 AAA <null>
60 DDD <null>

I want records for 60 only.

Nov 13 '05 #3

P: n/a


SELECT yourTable.ClientID, yourTable.Program, yourTable.EnterDate from
yourTable WHERE (yourTable.EnterDate is null);

<ma**********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Using A2K. I have a table with the columns ClientID, Program, and
EnterDate. Each client can have multiple records in this table. Each
record in the table has an ID and a program but not necessarily an
enter date. How do I pull out only those clients who do not have an
enter date for any possible program?

If my data looks like this:

ID Program EnterDate
-- ------- ---------
59 AAA <null>
59 DDD 10/01/03
60 AAA <null>
60 DDD <null>
61 AAA 09/01/03
61 DDD 10/01/03

I want the query results to look like this:

60 AAA <null>
60 DDD <null>
Thanks for any help or advice.

Nov 13 '05 #4

P: n/a
I get the same results as before. I'm curious how is your syntax
different from above?
John Welch wrote:
SELECT yourTable.ClientID, yourTable.Program, yourTable.EnterDate from
yourTable WHERE (yourTable.EnterDate is null);

<ma**********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Using A2K. I have a table with the columns ClientID, Program, and
EnterDate. Each client can have multiple records in this table. Each
record in the table has an ID and a program but not necessarily an
enter date. How do I pull out only those clients who do not have an
enter date for any possible program?

If my data looks like this:

ID Program EnterDate
-- ------- ---------
59 AAA <null>
59 DDD 10/01/03
60 AAA <null>
60 DDD <null>
61 AAA 09/01/03
61 DDD 10/01/03

I want the query results to look like this:

60 AAA <null>
60 DDD <null>
Thanks for any help or advice.


Nov 13 '05 #5

P: n/a
The reason why both solutions give the same result is that they answered the
question you asked. The result that you want was not explained in your
question. You asked for records which had EnterDate without a value, not ID
= 60 and EnterDate IS NULL. The samplw data you'd given had a record with ID
= 59 and the EnterDate with out a value so the solutions given to you
question were correct.

Try:
SELECT ID, Program, EnterDate
FROM Table
WHERE (EnterDate Is Null) AND (ID = 60);

Jeff

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I get the same results as before. I'm curious how is your syntax
different from above?
John Welch wrote:
SELECT yourTable.ClientID, yourTable.Program, yourTable.EnterDate from
yourTable WHERE (yourTable.EnterDate is null);

Nov 13 '05 #6

P: n/a

<ma**********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Using A2K. I have a table with the columns ClientID, Program, and
EnterDate. Each client can have multiple records in this table. Each
record in the table has an ID and a program but not necessarily an
enter date. How do I pull out only those clients who do not have an
enter date for any possible program?

If my data looks like this:

ID Program EnterDate
-- ------- ---------
59 AAA <null>
59 DDD 10/01/03
60 AAA <null>
60 DDD <null>
61 AAA 09/01/03
61 DDD 10/01/03

I want the query results to look like this:

60 AAA <null>
60 DDD <null>
Thanks for any help or advice.


This is what I've come up with, I think it does what you want.

select id, program from manning
where id not in (select id from manning where enterdate is not null)

HTH,
Randy

Nov 13 '05 #7

P: n/a
What if I don't know what the ID # is? The data scenario I gave is
just a sample.
Jeff Smith wrote:
The reason why both solutions give the same result is that they answered the
question you asked. The result that you want was not explained in your
question. You asked for records which had EnterDate without a value, not ID
= 60 and EnterDate IS NULL. The samplw data you'd given had a record with ID
= 59 and the EnterDate with out a value so the solutions given to you
question were correct.

Try:
SELECT ID, Program, EnterDate
FROM Table
WHERE (EnterDate Is Null) AND (ID = 60);

Jeff

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I get the same results as before. I'm curious how is your syntax
different from above?
John Welch wrote:
SELECT yourTable.ClientID, yourTable.Program, yourTable.EnterDate from
yourTable WHERE (yourTable.EnterDate is null);


Nov 13 '05 #8

P: n/a
Yes, I think that did it. Thanks!

Randy Harris wrote:
<ma**********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Using A2K. I have a table with the columns ClientID, Program, and
EnterDate. Each client can have multiple records in this table. Each
record in the table has an ID and a program but not necessarily an
enter date. How do I pull out only those clients who do not have an
enter date for any possible program?

If my data looks like this:

ID Program EnterDate
-- ------- ---------
59 AAA <null>
59 DDD 10/01/03
60 AAA <null>
60 DDD <null>
61 AAA 09/01/03
61 DDD 10/01/03

I want the query results to look like this:

60 AAA <null>
60 DDD <null>
Thanks for any help or advice.


This is what I've come up with, I think it does what you want.

select id, program from manning
where id not in (select id from manning where enterdate is not null)

HTH,
Randy


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.