Linking same table

I have a table that looks like this:
Acct Reason
1 MA01
1 MA18
1 MA19
2 MA01
2 MA03
3 MA01
3 MA03
3 MA07
4 MA01
4 MA03
In the above example, if an account has a MA18 or MA07, I DO NOT want the
account. Therefore, the resulting data would be account 2 and 4.
The above table needs to be a parent table (i.e. demographic table and
orders would be a child tables).
How do I create a SQL statement that returns any account that DOES NOT have
a MA18 or MA07? Do I create a view? This is way over my head.
THANKS,
MEGSelect * from account a1
where not exists (select * from account a2 where a1.acct = a2.acct and
(a2.Reason = 'MA18' or a2.Reason = 'MA07'))
Daniel
"MEG" <MEG@.discussions.microsoft.com> a crit dans le message de
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
> The above table needs to be a parent table (i.e. demographic table and
> orders would be a child tables).
> How do I create a SQL statement that returns any account that DOES NOT
have
> a MA18 or MA07? Do I create a view? This is way over my head.
> THANKS,
> MEG|||SELECT *
FROM Table O
WHERE NOT EXISTS( SELECT 1 FROM Table T
WHERE T.Acct = O.acct
AND T.Reason IN('MA18','MA07'))
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
>I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
> The above table needs to be a parent table (i.e. demographic table and
> orders would be a child tables).
> How do I create a SQL statement that returns any account that DOES NOT
> have
> a MA18 or MA07? Do I create a view? This is way over my head.
> THANKS,
> MEG|||A table can join to an aliased version of itself.
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
> The above table needs to be a parent table (i.e. demographic table and
> orders would be a child tables).
> How do I create a SQL statement that returns any account that DOES NOT
have
> a MA18 or MA07? Do I create a view? This is way over my head.
> THANKS,
> MEG|||"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
Something like (untested):
SELECT DISTINCT Acct
FROM AcctTable
WHERE NOT EXISTS (SELECT * FROM AcctTable WHERE reason IN ('MA18','MA07'))
Good Luck,
Jim|||Is a VIEW and an ALIAS similiar other than a VIEW being stored and an ALIAS
created on the fly?
I am using Crystal Reports so I don't think I can create something on the fl
y.
THANKS,
MEG
"JohnnyAppleseed" wrote:

> A table can join to an aliased version of itself.
> "MEG" <MEG@.discussions.microsoft.com> wrote in message
> news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> have
>
>|||A table referenced in a query can be reassigned a new name.
For example select ... from MyTable as MT ..
I'm calling this a table alias. A view is just a method to store a query in
SQL Server, and it can contain a query with an alias, but not necessarily.
Below is a query I wrote for another guy this morning. In it he is wanting
to self join a table called [offline] back to itself and list those records
that exist for 2004 but not 2005. Notice that [offline] is left joined to
[offline] and each is referenced using a different alias (B04 vs. B05).
select
B04.booking_year,
B04.category1,
B04.category2
from
offline as B04
left join
offline as B05
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
When using Crystal Reports, I highly reccomend that you store your queries
in a View or Stored Procedure, making them easier to manage and re-use
across report templates. There is nothing uglier than a project with two
dozen CR templates and each one has a slightly version of the same query.
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:913DD957-E0B3-4592-8FA4-CA3A2EEE92E3@.microsoft.com...
> Is a VIEW and an ALIAS similiar other than a VIEW being stored and an
ALIAS
> created on the fly?
> I am using Crystal Reports so I don't think I can create something on the
fly.
> THANKS,
> MEG
> "JohnnyAppleseed" wrote:
>
the|||Your reply was very beneficial. I agree that the view/stored procedure is
the best method.
In Crystal Reports, I don't know how to pass a parameter to the view/stored
procedure (i.e. in your example if you wanted to have a parameter for 2004
and one for 2005).
Any thoughts?
THANKS,
MEG
"JohnnyAppleseed" wrote:

> A table referenced in a query can be reassigned a new name.
> For example select ... from MyTable as MT ..
> I'm calling this a table alias. A view is just a method to store a query i
n
> SQL Server, and it can contain a query with an alias, but not necessarily.
> Below is a query I wrote for another guy this morning. In it he is wanting
> to self join a table called [offline] back to itself and list those records
> that exist for 2004 but not 2005. Notice that [offline] is left joined to
> [offline] and each is referenced using a different alias (B04 vs. B05).
> select
> B04.booking_year,
> B04.category1,
> B04.category2
> from
> offline as B04
> left join
> offline as B05
> 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
> When using Crystal Reports, I highly reccomend that you store your queries
> in a View or Stored Procedure, making them easier to manage and re-use
> across report templates. There is nothing uglier than a project with two
> dozen CR templates and each one has a slightly version of the same query.
> "MEG" <MEG@.discussions.microsoft.com> wrote in message
> news:913DD957-E0B3-4592-8FA4-CA3A2EEE92E3@.microsoft.com...
> ALIAS
> fly.
> the
>
>|||Calling a SP or paramaterized view from a CR template is cumbersome. When I
worked with CR 8.5 and VB 6.0, I would define an external ADO Recordset,
load the recordset with data, and then bind the recordset to the report
design object. This also involves building a .TTX file that has the same
data structure as your recordset, and then binding the data source of the
template to that TTX file. It sounds complicated, but it's actually less
troublesome once you get everything setup. Using deja.com, search in the
*crystal* newsgroups for keyword "TTX", and you will find several
discussions. Also there are examples on the http://www.businessobjects.com/
support website.
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:0186A86C-82C3-4359-BF93-73FE1262CF02@.microsoft.com...
> Your reply was very beneficial. I agree that the view/stored procedure is
> the best method.
> In Crystal Reports, I don't know how to pass a parameter to the
view/stored
> procedure (i.e. in your example if you wanted to have a parameter for 2004
> and one for 2005).
> Any thoughts?
> THANKS,
> MEG
> "JohnnyAppleseed" wrote:
>
in
necessarily.
wanting
records
to
queries
query.
the
want
and
NOT