Re: select query assistance

thanks Hugo.

What you wrote gets me the lines that are Department = 2.

I only want the lines of a ticket where all the items are department = 2.

So records look like:

Ticket, date, ordinal, department
01 , 01/01/2005, 1 , 2
01 , 01/01/2005, 2 , 2

Not
02, 01/05/2005, 1 , 3
02, 01/05/2005, 2 , 2

Because the first item is department 3.

Thanks for the ideas.

"Hugo Kornelis" wrote:

> On Thu, 7 Apr 2005 08:05:04 -0700, AshleyT wrote:
>
> >Perfect! Thanks.
> >
> >What about if I want only tickets where all ITEMS has Department 2. So they
> >only bought Cigarettes.
>
> Hi Ashley,
>
> SELECT (column list goes here)
> FROM YourTable AS a
> WHERE NOT EXISTS (SELECT *
> FROM YourTable AS b
> WHERE b.Ticket_Num = a.Ticket_Num
> AND b.Ticket_Date = a.Ticket_Date
> AND b.Item_Dept <> 2)
> (untested)
>
> Also possible - maybe even faster:
>
> SELECT Ticket_Num, Ticket_Date
> FROM YourTable
> GROUP BY Ticket_Num, Ticket_Date
> HAVING MIN(Item_Dept) = 2
> AND MAX(Item_Dept) = 2
> (also untested)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
.

Relevant Pages

  • Re: Enigme
    ... Le mariage est un ticket garanti pour le ciel, ... monsieur fait de sa merde, madame gagne son ciel et inversément. ... Fusti ... Prev by Date: ...
    (soc.culture.belgium)
  • Re: how much do you practice?
    ... journal might be the ticket to approaching it with more consistency. ... Mark R ... Prev by Date: ...
    (rec.music.makers.guitar.jazz)
  • Re: The Rice Man Cometh - DWTS2
    ... > perfection. ... and his tango... ... > ticket) with him. ... Prev by Date: ...
    (rec.arts.tv.soaps.abc)
  • Re: PlusNet Users - cost of Anti-Spam filtering
    ... Bung a ticket on your contact us thing..breath holding isn't advised, ... Prev by Date: ...
    (uk.people.silversurfers)
  • Re: Hush
    ... >> Let us not forget the case of the chap issued a ticket for doing ... What car was he driving? ... A Maestro driver clocked at 480 mph in Wales. ... Prev by Date: ...
    (uk.rec.sheds)