Board index » delphi » Stuck on Query.

Stuck on Query.

I have two tables as follows:

TICKET.DB:      TICKET    FROM   TO   PRODUCT    WEIGHT
                ---------------------------------------
                1001      HGA    TLC  WHEAT       10.22
                1002      BGA    HGA  BARLEY      10.00
                1003      INC    TLC  SUPER       20.24
                1004      TLC    BGA  LENTILS     05.50

RUN.DB          RUN      TICKET      CLIENT
                ----------------------------
                1        1001        TLC
                2        1001        HGA
                2        1002        HGA

What I need to be able to do is select all the records from
the ticket table, for a client, say "TLC" (either in the from OR to
field)
that have not got a matching ticket number & code in the Run table,
so I would have the following answer table:

ANSWER.DB       TICKET   FROM   TO  
                ------------------
                1003     INC    TLC
                1004     TLC    BGA

Can this be done at all? What is the best way to do it? I can't
seem to even be able to get close to doing something like this.

Thanks in Advance
- Adam

 

Re:Stuck on Query.


G'day Adam

Try this

SELECT Ticket, From, To
FROM Ticket
WHERE ((From = 'TLC') OR (To = 'TLC')) AND Not Ticket in ( SELECT DISTINCT
Ticket FROM Run )

It should work :-))

--
Regards

Allan Bentsen
mailto:abent...@4d.dk
http://www.4d.dk/

Adam Hair <h...@netconnect.com.au> wrote in article
<3574CB66.4...@netconnect.com.au>...

Quote
> I have two tables as follows:

> TICKET.DB: TICKET    FROM   TO   PRODUCT    WEIGHT
>            ---------------------------------------
>            1001      HGA    TLC  WHEAT       10.22
>            1002      BGA    HGA  BARLEY      10.00
>            1003      INC    TLC  SUPER       20.24
>            1004      TLC    BGA  LENTILS     05.50

> RUN.DB             RUN      TICKET      CLIENT
>            ----------------------------
>            1        1001        TLC
>            2        1001        HGA
>            2        1002        HGA

> What I need to be able to do is select all the records from
> the ticket table, for a client, say "TLC" (either in the from OR to
> field)
> that have not got a matching ticket number & code in the Run table,
> so I would have the following answer table:

> ANSWER.DB  TICKET   FROM   TO  
>            ------------------
>            1003     INC    TLC
>            1004     TLC    BGA

> Can this be done at all? What is the best way to do it? I can't
> seem to even be able to get close to doing something like this.

> Thanks in Advance
> - Adam

Re:Stuck on Query.


Quote
> SELECT Ticket, From, To
> FROM Ticket
> WHERE ((From = 'TLC') OR (To = 'TLC')) AND Not Ticket in ( SELECT   > DISTINCT Ticket FROM Run )

Thanks for your help Allan. This didn't work, because it didn't
like the field names being FROM and TO, but by changing the field
names to something else everything works great!

Once again Thanks.

Best Regards
- Adam

Other Threads