Re: select query assistance

On Thu, 7 Apr 2005 13:13:03 -0700, AshleyT wrote:

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

Hi Ashley,

Did you test it?

Using your sample data and a greatly simplified table declaration (no
keys, etc):

create table YourTable (Ticket int, date datetime, ordinal int,
department int)
go
insert YourTable
select 01 , '20050101', 1 , 2 union all
select 01 , '20050101', 2 , 2 union all
select 02, '20050101', 1 , 3 union all
select 02, '20050101', 2 , 2
go

-- My first query
SELECT *
FROM YourTable AS a
WHERE NOT EXISTS (SELECT *
FROM YourTable AS b
WHERE b.Ticket = a.Ticket
AND b.date = a.date
AND b.department <> 2)

-- My second query
SELECT Ticket, date
FROM YourTable
GROUP BY Ticket, date
HAVING MIN(department) = 2
AND MAX(department) = 2

go
drop table YourTable
go

Check the output - both return only ticket 01, not ticket 02.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • Need Help getting kinks out
    ... public Ticket(String name, int speed, int speedLimit, char response) ... } // end Ticket constructor ... public void schoolZone ... //This method changes the speed ...
    (comp.lang.java.help)
  • RE: Insert SP from multiple tables
    ... (authors int, ... > in the customer table, winner, and ticket table? ...
    (microsoft.public.sqlserver.programming)
  • Re: Consolidating Code
    ... >DECLARE @super_user int ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.programming)