Querying id by having criteria in two lines

[ahunter488]

Need help creating a query to display id's where users have both L & R (bilateral).

[Rod]

This should work for you:

SELECT TabR.Field1
FROM (
SELECT sampledata.Field1
FROM sampledata
WHERE sampledata.Field2="R") AS TabR
INNER JOIN sampledata ON TabR.Field1 = sampledata.Field1
WHERE sampledata.Field2="L";

[ahunter488]

When I use this in my actual database,
I receive multiple listings when
listed items
are
R
L
R
or
L
R
L
Also, I get one bad result at the first entry for one
L
L
L
The rest of inquiry appears to be working correctly, however if I can prevent the incorrect entry I would be more certain that this is pulling the correct results.
Anything I can do to prevent this (and thank you very much for the help)

Also, if I wanted to get the opposite data, users that do not have and L & R only L or R (or multiples of the same), how could this be acheived? Is there a subtraction query that would remove the results of this query from the original query?

SELECT TabR.QueryBilateralImplant.PatientId
FROM [SELECT QueryBilateralImplant.PatientId
FROM QueryBilateralImplant
WHERE QueryBilateralImplant.AudSide="R"]. AS TabR
INNER JOIN QueryBilateralImplant ON TabR.PatientId = QueryBilateralImplant.PatientId
WHERE QueryBilateralImplant.AudSide="L"
ORDER BY QueryBilateralImplant.PatientId;

This should work for you:

SELECT TabR.Field1
FROM (
SELECT sampledata.Field1
FROM sampledata
WHERE sampledata.Field2="R") AS TabR
INNER JOIN sampledata ON TabR.Field1 = sampledata.Field1
WHERE sampledata.Field2="L";

[Rod]

Bit pushed for time right now but off the top of my head you need to use the DISTINCT predicate to avoid duplicates in the result set.

SELECT DISTINCT TabR.QueryBilateralImplant.PatientId
FROM [SELECT DISTINCT QueryBilateralImplant.PatientId
FROM QueryBilateralImplant
WHERE QueryBilateralImplant.AudSide="R"]. AS TabR
INNER JOIN QueryBilateralImplant ON TabR.PatientId = QueryBilateralImplant.PatientId
WHERE QueryBilateralImplant.AudSide="L"
ORDER BY QueryBilateralImplant.PatientId;

I haven't tested this!

Can't think why you get your bad result.

Post back if it still doesn't work.