Retruning specific records where BOTH criteria 1 and 2 are true 1

[PieMan]

I am searching one particular field for all instances of particular values.

Therefore I want to return records where the there is a "Value A" and "Value B" all in one field.

e.g.
Display all jobs where materials were supplied by BOTH Supplier A and Supplier B.

Everthing I have tried so far returns records from either Supplier A or Supplier B.

Thanks

Pieman

 

[jadams0173]

Perhaps a query.

Select partnumber, supplierA, supplierB from sometable where supplierA='" & varA "' and supplierB='" VarB & "'"

I tried to have patience but it took to long! -DW

 

[jsteph]

a "Value A" and "Value B" all in one field

Is this really what you mean? This would suggest a bad design if you had a field that could contain two separate values simulteneously.
--Jim

 

[PieMan]

The Table is structured as so:

JobNumber MaterialSupplier MaterialCode
12345 SMITH 54321
12345 JONES 2468
12346 SMITH 54321

What I need to acheive is to list all Jobs where both SMITH and JONES have both supplied materials.

Currently if I use :
WHERE MaterialSupplier IN ('SMITH','JONES')
I get three records returned BUT what I really need is the first two records only that relate to Job 12345 as BOTH suppliers have been involved in this Job.

I hope this makes sense
Thanks
Pieman

 

[Golom]

Select JobNumber
From myTable
WHERE MaterialSupplier IN ('SMITH','JONES')
Group by JobNumber
Having Count(*) = 2

 

[cheerio]

Golom

Add the following line to the data:
12346 SMITH 21

Your query now returns job 12346 because of the two instances of SMITH even though JONES is not there.

Building on your fine start I would be tempted by:

Select JobNumber
From
(
SELECT DISTINCT JobNumber, MaterialSupplier
From myTable
WHERE MaterialSupplier IN ('SMITH','JONES')
) AS Jobs
Group by JobNumber
Having Count(*) = 2

However, you may have a better solution

 

[Golom]

That's probably the best solution. There is the EXISTS option

Select A.JobNumber

From myTable A

Where EXISTS (Select 1 From myTable X
              Where X.MaterialSupplier = 'SMITH' 
                AND X.JobNumber = A.JobNumber)

  AND EXISTS (Select 1 From myTable X
              Where X.MaterialSupplier = 'JONES' 
                AND X.JobNumber = A.JobNumber)

but I would stick with the "Group and Count" version.

 

[PHV]

Another way ?
SELECT DISTINCT A.JobNumber
FROM myTable AS A INNER JOIN myTable AS B ON A.JobNumber = B.JobNumber
WHERE A.MaterialSupplier = 'SMITH' AND B.MaterialSupplier = 'JONES'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886