Help with select query 2

[MuttleyDiXiE]

Hi,

I'm new to SQL and can't seem to figure out how to create a SQL query that does want I want it to do.

I have a table "Nodedate" like this;

Node Date
TDCXTC1445 30-11-2003
TDCXTC1445 01-12-2003
TDCXTC1445 31-12-2003
ABN001111 30-11-2003
ABN001111 01-12-2003
ABN001111 31-12-2003
ABN001111 31-01-2004
TDCTDR1350 31-12-2003
TDCTDR1350 31-01-2004


What query will output all Node's with the date 30-11-2003 but not 31-12-2004? So from the table sample above the Node/Nodes returned would be TDCXTC1445.

I just can't get it!!!!! Someone please help...

Rune

 

[hneal98]

select * from Nodedate where [Date] = #30-11-2003# and [Date]<>#31-12-2004#

 

[r937]

hneal98, think about that for a second -- on any given row, if [Date]=#30-11-2003# is true, then [Date]<>#31-12-2004# will be true too, i.e. every row where [Date]=#30-11-2003# is true will be returned, regardless of whether any other rows with [Date]=#31-12-2004# exist

here's one way to get the required answer, using a NOT EXISTS correlated subquery

[tt]select Node, [Date]
from Nodedate ZZ
where [Date] = #30-11-2003#
and not exists
( select 1
from Nodedate
where Node = ZZ.Node
and [Date] = #31-12-2004# )[/tt]

and here's another way, using a LEFT OUTER join with a check for unmatched rows (null key):

[tt]select t1.Node
from Nodedate t1
left outer
join Nodedate t2
on t1.Node = t2.Node
and t2.[Date]=#31-12-2004#
where t1.[Date]=#30-11-2003#
and t2.Node is null[/tt]

rudy

http://r937.com/

SQL Consulting

 

[TonyJollans]

Hi MuttleyDiXiE,

Try ..

[blue]

SELECT DISTINCT [NodeDate].[Node] 
FROM   [NodeDate]
WHERE  NOT EXISTS (SELECT [NodeDate].[Node] 
                   FROM   [Nodedate] AS NodeDate2
                   WHERE  [NodeDate].[Node] = [NodeDate2].[Node] 
                   AND    [NodeDate2].[Date] = #01-31-2004#);

[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[r937]

tony, you have to have two date checks altogether

rudy

http://r937.com/

SQL Consulting

 

[TonyJollans]

You are correct, Rudy
I should have read the question properly.


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[MuttleyDiXiE]

Hi Rudy,

Thanks for your help...unfortunately it isn't working =(

Your first query just returns all nodes with the date 31-11-2003 like it's not validating on this last part
and not exists
( select 1
from Nodedate
where Node = ZZ.Node
and [Date] = #31-12-2004# )

I'm using an access database and it doesn't support left outer =(

Do U have any clue to why it seems to ignore the last part in your first query??

Thanks

Rune

 

[r937]

huh? i have access 97 and there is no problem with LEFT OUTER

what do you have, access 2?


can i see your query? and what did it produce?

rudy

http://r937.com/

SQL Consulting

 

[TonyJollans]

Hi MuttleyDiXiE,

I don't entirely follow your post but a couple of points:

1. The date in your sample data is 31-[red]01[/red]-2004. the date in your description, and Rudy's SQL is 31-[red]12[/red]-2004. Might that affect your results?

2. To make the join work with a constant in Access needs some parentheses ..

left outer
  join Nodedate t2
    on

[red]

(

[/red]

t1.Node = t2.Node 
   and           t2.[Date]=#31-

[red]

01

[/red]

-2004#

[red]

)

[/red]

 where t1.[Date]=#30-11-2003#
   and t2.Node is null

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[MuttleyDiXiE]

Hi Rudy,

My mistake... I didn't see the date difference that Tony pointed out... After I corrected the date it worked like a charme =)


I'm using access2K and when I cut/paste your outer join query it says, join function is not supported...Anyways it doesn't really matter now that it's working.

Thank you all for the big help =)


Rune