Nested Select?

Hi all,
I have the following table
id (autonumber)
category1 (int)
category2 (int)
booking_month (int)
booking_year (int)
I have records in the table for booking_year = 2004 and booking_year = 2005,
for example
id, category1, category2, booking_month, booking_year
1, 20, 30, 4, 2004
1, 20, 31, 10, 2004
1, 20, 30, 4, 2005
I need a SQL statement there lists all those records that are in 2004 but no
in 2005 for a particular category1.
Any ideas?
Thanks,
IvanYou could use a nested sub-query, however, you can also just select from
[booking] as B04 for booking_year = 2004 and then left join [booking] as B05
on booking_year = 2005. Only include records where B05.id is NULL.
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> I have records in the table for booking_year = 2004 and booking_year =
2005,
> for example
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004 but
no
> in 2005 for a particular category1.
> Any ideas?
> Thanks,
> Ivan
>|||"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:

> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004
> but no in 2005 for a particular category1.
SELECT * FROM [tablename]
WHERE (booking_year <> 2005) AND (category1 = particular_value)
HTH|||This does not work:
SELECT o1.category2 FROM offline o1
LEFT JOIN offline o2
ON o2.booking_year = 2005
WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
2004)
and o2.id_no is null
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> You could use a nested sub-query, however, you can also just select from
> [booking] as B04 for booking_year = 2004 and then left join [booking] as
B05
> on booking_year = 2005. Only include records where B05.id is NULL.
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> 2005,
but
> no
>|||Or like this
SELECT * FROM [tablename]
WHERE booking_year =2004 AND category1 not in (select category1 from
[tablename] t1 where year = 2005)
Hth
"Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:
>
> SELECT * FROM [tablename]
> WHERE (booking_year <> 2005) AND (category1 = particular_value)
> HTH|||The compare between category1 and category2 should be in the join.. on..
clause. Try this:
select
B04.booking_year,
B04.category1,
B04.category2
from
offline as B04
left join
offline as B05
-- join both aliases of offline on category1 and category2. also
filter B05 on 2005.
on B05.booking_year = 2005 and
B05.category1 = B04.category1 and
B05.category2 = B04.category 2
where
B04.booking_year = 2004 and
B05.id is NULL -- Doesn't matter which B05 column is null
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
> This does not work:
> SELECT o1.category2 FROM offline o1
> LEFT JOIN offline o2
> ON o2.booking_year = 2005
> WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
> 2004)
> and o2.id_no is null
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> B05
> but
>|||The original B04 records total 112.
The original B05 records total 24.
Logic and simple math tell me that I should get 112-24 = 88!!
When I run your suggested statement I get 85 records.
When I run this statement:
SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2004 AND category2 NOT IN
(SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2005)
I get 87 records.
Strange indeed!!!
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
> The compare between category1 and category2 should be in the join.. on..
> clause. Try this:
> select
> B04.booking_year,
> B04.category1,
> B04.category2
> from
> offline as B04
> left join
> offline as B05
> -- join both aliases of offline on category1 and category2. also
> filter B05 on 2005.
> on B05.booking_year = 2005 and
> B05.category1 = B04.category1 and
> B05.category2 = B04.category 2
> where
> B04.booking_year = 2004 and
> B05.id is NULL -- Doesn't matter which B05 column is null
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
from
as
=
2004
>|||This was my original statement that I tried but I get always 1 less than the
expected result :(
"AM" <shahdharti@.gmail.com> schrieb im Newsbeitrag
news:e8PTwj3DFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Or like this
> SELECT * FROM [tablename]
> WHERE booking_year =2004 AND category1 not in (select category1 from
> [tablename] t1 where year = 2005)
> Hth
>
> "Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
> news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
>|||Ivan,
Can you be more precise about what you want? What does
"in 2004 but no in 2005" mean? I assume it means
Find all rows where booking_year = 2004 but for which
there is not a matching row with booking_year = 2005.
But ... you haven't made it clear what "matching row" means. Does
a matching 2005 row need to have the same category1, category2,
and booking_month, or just some of those columns? And your
data here says id is "autonumber", but you show three identical id
values - does the matching 2005 row have to have the same id value?
You said you think you should get a number of rows that is
the number of 2004 rows minus the number of 2005 rows, but
how do you know every one of the 2005 rows in your table
matches exactly one 2004 row? Maybe some 2004 rows appear
twice, and maybe some 2005 rows have no corresponding 2004
row.
You may know what it means for a 2004 row to be in 2005 as
well, but unless you describe it clearly in terms of the columns of
this table, you can't expect to be able to write a query that will
give you what you want.
Steve Kass
Drew University
Ivan Debono wrote:

>Hi all,
>I have the following table
>id (autonumber)
>category1 (int)
>category2 (int)
>booking_month (int)
>booking_year (int)
>I have records in the table for booking_year = 2004 and booking_year = 2005
,
>for example
>id, category1, category2, booking_month, booking_year
>1, 20, 30, 4, 2004
>1, 20, 31, 10, 2004
>1, 20, 30, 4, 2005
>I need a SQL statement there lists all those records that are in 2004 but n
o
>in 2005 for a particular category1.
>Any ideas?
>Thanks,
>Ivan
>
>|||Perhaps you should be joining on booking_month too ?
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OzdKJc4DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> The original B04 records total 112.
> The original B05 records total 24.
> Logic and simple math tell me that I should get 112-24 = 88!!
> When I run your suggested statement I get 85 records.
> When I run this statement:
> SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2004 AND category2 NOT IN
> (SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2005)
> I get 87 records.
> Strange indeed!!!
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
=
> from
[booking]
> as
booking_year
> =
> 2004
>