Help with a View

**John Bell** · Jul 20 '05, 04:11 AM

Re: Help with a View

Hi

It is always better to post DDL ( CREATE TABLE statements etc..) and example
data (as INSERT statements) with the expected output from that data. This
will remove any ambiguities from your descriptions and helps everyone try
out their replies. There is no definition for tblFile which does not help!!

It is not clear if you want one or all records. But this may help

SELECT F.Filenumber, F.DataofFile, E.status, E.date, E.comments, E.qualifier
FROM tblFile F
join tblElectronic E on F.filenumber = E.filenumber
WHERE E.qualifier = 100
OR ( E.qualifier = 101
AND NOT EXISTS ( SELECT 1 FROM tblElectronic T
WHERE T.filenumber = E.filenumber
AND T.qualifier = 100 )
)

John

"Rubia 078" <rubia078@yahoo .com> wrote in message
news:40ec7b0c$0 $16462$c397aba@ news.newsgroups .ws...[color=blue]
> I have a table tblElectronic that recieves data electronically. It
> contains fields: filenumber, status, date, qualifier and comments. The
> qualifier field is either 100 or 101, meaning the file is confirmed or
> estimated, respectively. I then join tblElectronic to another table
> (tblFile) in a view. I am having trouble building the view. The field
> Qualifier in tblElectronic can have the value 100 or 101.
>
> filenumber status Date Qualifier Comments
> 1111 xxxx 01/01/01 100 Comments
> 1111 xxxx 01/01/01 101 Comments
> 1112 xxxy 01/01/01 101 Comments
> 1113 xyxy 01/01/01 100 Comments
>
> I want to use the record where qualifier = 100 in my view, except in the
> case where 101 is the only qualifier that exists, meaning it has not
> been confirmed yet.
>
> So my view should pull the rows:
> filenumber status date qualifier comments
> 1111 xxxx 01/01/01 101 Comments
> 1112 xxxy 01/01/01 101 Comments
> 1113 xyxy 01/01/01 100 Comments
>
>
> I have tried case statements but to no avail. Here is my view:
>
> SELECT tblFile.Filenum ber, tblFile.DataofF ile, tblElectronic.s tatus,
> tblElectronic.d ate, tblElectronic.c omments
> FROM tblFile inner join
> tblElectronic on tblFile.filenum ber = tblElectronic.f ilenumber
> where tblElectronic.q ualifier = ??????
>
> Not sure what to put here, since I want it to be where qualifier = 100
> unless that doesn't exist, in which case I want it to be where qualifier
> = 101.
>
> Thanks for the help.
>
> Rubia
>
>
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

**Rubia 078** · Jul 20 '05, 04:11 AM

Re: Help with a View

John,

Thank you, I believe that will work. I won't be able to test it until
tomorrow, but it would appear to be what I needed.

Thank you!

Rubia

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!