Solved: SubSelect on Same Table

[stanl]

I'm brain dead. I need to query workorders [w] and customer accounts [c] between a date field range [d] from table [t]. However, if [c] appears in the table more that once (either inside or outside the date range) then don't include [w]

I'm thinking something like {pseudo-code}

[vba]
SELECT [w],[c],[d] FROM [t] WHERE ([d]>=#mm/dd/yyyy# AND [d]
<#mm/dd/yyyy#) AND [c] IN
( SELECT [c],Count([c]) AS n from t2 [t] WHERE n=1)
[/vba]

This is a very huge and very active multi-user table so I don't get to play around... so I'd like to be pretty sure the query works first time. Thanks for any pointers. Stan

[darthobra]

Stan,

Try this...

SELECT T.W, T.D, T.C
FROM T
WHERE (((T.C) In (SELECT T.C
FROM T
GROUP BY T.C
HAVING (((Count(T.C))=1)))))
GROUP BY T.W, T.D, T.C
HAVING (((T.D) Between #mm/dd/yyyy# And #mm/dd/yyyy#));

If I understand correctly... the goal is to show only W records where C does not have multiple records. within the table T, regardless of dates. This means that if C (bob) has a W in Jan and Mar and you query Mar and Apr, then C (bob) will not appear in the result set.

That is how this query functions. If I have misinterpretted the directive, I can rework it.

Good luck!

Darth

[Mavyak]

I think you need a group by in the sub-query along with a "having count('a') = 1"

(Edit: anachronistic post)

[darthobra]

Mavyak,

This is true. I probably should have spaced this SQL better. The Group By is in the IN sub-query which returns the Cs that have a count of 1. The problem with this query, although it works, is that if the table is sizable, the sub-query will take a long time to run to return the IN subset. It would probably be advantageous to have a VIEW (Query) that returns only records of distinct C with a count of occurances and join that with the Table between the desired dates.

< qrySingleOccurance >
SELECT DISTINCT [C]
FROM T
GROUP BY T.C
HAVING (Count(T.C))=1

Sub Query Option
SELECT T.W, T.C, T.D
FROM T
WHERE (((T.C) In (SELECT C FROM qrySingleOccurance)));

Inner Join Option
SELECT T.W, T.C, T.D
FROM T INNER JOIN qrySingleOccurance ON T.C = qrySingleOccurance.C;

By creating the query (view) the DBMS remembers the SQL and performs it faster which lessens the impact on the database as the table is queried.

Darth

[Mavyak]

I meant that my post was anachronistic. I didn't expect you to get a post in before me. My post was in reference to the first post.

[darthobra]

LOL... sorry.

[stanl]

Thanks;

Missed HAVING, and also assumed you have to alias a table if your subselect references the same table. I have a window where I will be able to test this on Sunday. The table has ~ half million rows.

Stan

[stanl]

P.S.

just tested... removed your 2nd GROUP BY...HAVING which cut the query time by about 90%... the data goes from a table into an Excel template, I picked a 2 week date range and total time was 3 minutes.

Closing thread... thanx again Stan