Can I do this in C#?

[kevin.jennings]

I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:

0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...


The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.

What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.

My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?

Thanks,


Kevin

 

[Arne Vajhøj]

I'm writing a C# program to read from an Access database table called
LocationDetail. This table is made up of two columns: Location,
DiscDate. It looks like this:

0000001234 122590
0000001234 102207
0000001234 000000
1237847623 102207
1237847623 071395
4545454545 031206
4545454545 000000
and so on...


The first column denotes a location, the second a disconnect date.
If
the date is '000000' that means the location is active. So, that means
that location 0000001234 above is a currently active location with two
previous disconnects.

What I'd like to do is go through this table and only 'fetch' the
locations that do not have a DiscDate of '000000'. In this instance,
looking at the data above, I'd only want location 1237847623 because
it is the only location that doesn't have a DiscDate that is equal to
'000000'. It has two entries and both have a valid disconnect date.
That means that this location is still "inactive" and that's what I
want to find: All inactive locations.

My C# program currently loads the entire LocationDetail table into a
dataset in the program. I'm assuming that I have to use SQL-like
commands to act on this dataset and pull only the locations I require
from it. But, I have no idea how to do that. Can someone show me an
example?

Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Arne

 

[kevin.jennings]

Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Arne

Thanks for the response! I'll try out your example. I didn't realize
I could do nested selects in SQL. I've never used it before for
anything other than a straight 'select'.

I appreciate it!

Kevin

 

[Arne Vajhøj]

Thanks for the response! I'll try out your example. I didn't realize
I could do nested selects in SQL. I've never used it before for
anything other than a straight 'select'.

Practically all databases except 10 year old MySQL databases
support that.

Arne

 

[Martin Bonner]

Only load the relevant data into the DataSet.

Something like:

SELECT location,discdate
FROM yourtable
WHERE location NOT IN (SELECT location FROM yourtable WHERE
discdate='000000')

Arne

Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)

 

[Jon Skeet [C# MVP]]

Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)

Some dialects of SQL use <> instead of != but yes, I far prefer using
that to using the nested select, which may (depending on
implementation) be significantly slower.

 

[ManicQin]

Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)

Are you this Sql Query suffice?
I think It'll give you

0000001234 122590
0000001234 102207
1237847623 102207
1237847623 071395
4545454545 031206

If I\m not Mistaken,,,

 

[Tim Jarvis]

Couldn't you also do:

No that wouldn't be correct in this case it wouldn't return what Kevin
wanted, this query would return....

0000001234 122590
0000001234 102207
1237847623 102207
1237847623 071395
4545454545 031206

When what he wanted was those locations that are not current i.e. just

1237847623

I think that Arne's response was the correct one, or in fact maybe even
refined as...

SELECT distinct location from mytable where location not in (select
location from mytable where discdate = "000000")

to simply return a distinct list of locations that are not current.

Regards Tim.

--

 

[Jon Skeet [C# MVP]]

No that wouldn't be correct in this case it wouldn't return what Kevin
wanted, this query would return....

<snip>

Ah, yes... Oops. That'll teach me to read the question more closely
(and/or not try to read news before my first coffee...)

 

[Arne Vajhøj]

Couldn't you also do:

SELECT location,discdate
FROM yourtable
WHERE discdate != '000000'

(or doesn't SQL support a not-equal operator?), in which case
synthesize from
WHERE (discdate < '0000' || '0000' < discdate)

The SQL standard is <>.

But your solution gives a comletely different result.

Arne

 

[Martin Bonner]

The SQL standard is <>.

But your solution gives a comletely different result.

D'oh!

That would be because I didn't read the question carefully enough.
Sorry for the noise :-(