select field with condition but not only

[codrutza]

Hi
I am working with SQL Server 2000
Please help me with this.
How should I write in a SP this:

Field1 Field2
11 aa file c
11 fr
11 gr
12 cc file c
12 dd
13 aa
13 bb
13 ff
13 gg
14 aa file c
14 fe
14 eee
14 cc file c

I want to pick only those records – which have the field2 ‘aa file c’ or/and ‘cc file c’ between them, so I should obtain only:
11 aa file c
11 fr
11 gr
12 cc file c
12 dd
14 aa file c
14 fe
14 eee
14 cc file c

 

[gmmastros]

It should be pretty easy to pick out the rows based on your conditions. The problem is... you want all of the rows for the match Field1 when you find a match in field2. To accomplish this, you can use a technique called a derived table.

First, let's build a query that returns just the Field1 values that match your criteria.

[tt][blue]
Select Field1
From YourTable
Where (Field2 = 'aa file c'
Or Field2 = 'cc file c')
[/blue][/tt]

If you actually have many Field2 values you are searching for, you may find it easier to use IN, like this...

[tt][blue]
Select Field1
From YourTable
Where Field2 In ('aa file c','cc file c')
[/blue][/tt]

SQL Server will treat the 2 queries shown above exactly the same way. You'll get the same data, and the performance will be identical.

Notice that you get duplicate Field1 values whenever there are duplicate matches (like 14 which has 'aa file c' and 'cc file c'). For the purposes of the derived table, we don't want duplicates, so let's throw in a distinct clause, like this...

[tt][blue]
Select Distinct Field1
From YourTable
Where Field2 In ('aa file c','cc file c')
[/blue][/tt]

Now we will build an 'outer' query that joins from your table to this derived table. Basically, we can treat the derived table as though it were a table (I'll show you how in a minute). We will basically use this derived table as a filter for the real table so that we only return the rows from the real table that match the Field1 values from the derived table. Like this:

Select YourTable.*
From   YourTable
       Inner Join [!]([/!]
         [blue]Select Distinct Field1
         From   YourTable
         Where  Field2 In ('aa file c','cc file c')[/blue]
         [!]) As IdsICareAbout[/!]
       On YourTable.Field1 = [!]IdsICareAbout.[/!]Field1

Notice the parts in blue are exactly the same as the query we created earlier. By enclosing that query in parenthesis and providing an alias, we are effectively making this a derived table. The alias for the derived table (IdsICareAbout) must be used throughout the outer query so that SQL Server knows you are referring to the Field1 value from the derived table and not the field1 value from the actual table.

The derived table technique is very powerful when used in the right circumstances. I encourage you to learn this technique so that you can apply it to other situations.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

I think I said it wrong. Sorry for my English.
Let assume that I am making a group on Field1 (I’m not making, I am saying that for the purpose of showing what I need).

11
aa file c
fr
gr
12
cc file c
dd
13
aa
bb
ff
gg
14
aa file c
fe
eee
cc file c

I have to exclude group 11, 12 and 14 from report, so I should be left just with 13

13 aa
bb
ff
gg

Let's say that is named "select aaaa";
Second, I have to include "select aaaa" to this procedure:

CREATE PROCEDURE TEST
@BookingCompany varchar(8)

AS
DECLARE @c_result INT

SELECT
FJ.Number,
FJ.Code,
FJ.Partner,
FJ.Via FJVia,
(year(FJ.ReportingDate)) YearRepDate,
Mov.Via FMVia,
Mov.DepartureDate,
Mov.DepartureLocation,
Mov.ArrivalLocation,
FMov.MNo,
MovStatus.Op,
YourTable.Field1,
YourTable.Field2

FROM
(((YourTable WITH(READUNCOMMITTED)
INNER JOIN FJ WITH(READUNCOMMITTED)
ON FJ.Number= YourTable.Field1)
INNER JOIN FMov WITH(READUNCOMMITTED)
ON FJ.Number =FMov.JobNumber)
INNER JOIN Mov WITH(READUNCOMMITTED)
ON FMov.MNo= Mov.Number)
INNER JOIN MovStatus WITH(READUNCOMMITTED)
ON MovStatus.MNo=Mov.Number

WHERE

@BookingCompany=FJ.Code
AND (year(FJ.ReportingDate))>=2010
AND FJ.Partner='AAA'
AND FJ.Via IN ('Air','Sea')
AND Mov.Via IN ('Air','Sea')
AND NOT
(FMov.MNo LIKE 'EA%' OR FMov.MNo LIKE 'ES%@)
AND MovStatus.Op='I'


AND
..........
"select aaaa"


SELECT @c_result=@@ERROR
RETURN @c_result
GO


Can you help?
If I didn't explain right, tell me, please.