The query is not bringing the result as it should

[BittenApple]

Hello team,


Hello team
I have a table
ID
MemberID
DateOfService
EndDate
HospitalName

I put this query:
SELECT *
From TableA
WHERE(((TableA.[MemberID]) IN SELECT(MemberID From TableA Where DateOfService<=EndDate and HospitalName ="xyn")));

In the table I have : DateOfService 08/08/2017 and EndDate 07/08/2017 and their data type is date.

When I run query this line of the code is in the result while DateOfService is not less than or equal to EndDate.

I can't find any logic for this.

Please advise me!
BittenApple

[HansV]

The query will return all MemberIDs for which there is at least one record that has DateOfService<=EndDate AND HospitalName="xyn", but it will also return ALL other records for those MemberIDs.

If you only want to return the records for which DateOfService<=EndDate AND HospitalName="xyn", use

SELECT *
FROM TableA
WHERE DateOfService<=EndDate AND HospitalName="xyn";

[BittenApple]

Hans,
Yes, correct. This brings the result; I can't find why this
SELECT *
From TableA
WHERE(((TableA.[MemberID]) IN SELECT(MemberID From TableA Where DateOfService<=EndDate and HospitalName ="xyn")));
doesn't bring the result. This shouldn't be bring when DateOfService is > than EndDate, since we stated clearly in where clause. Why is that working like that?
Regards,
BittenApple

[HansV]

Let's take a simplified example:

S1795.png

The query

Code: Select all

SELECT *
FROM TableA
WHERE MemberID IN
   (SELECT MemberID 
    FROM TableA
    WHERE HospitalName="xyz")

This SQL will return ALL records for those MemberIDs that have at least one record with HospitalName equal to "xyz". But it will not only return records with HospitalName equal to "xyz". It will also return all other records for those MemberIDs.
It will NOT return records for MemberIDs that do not have any record with HospitalName "xyz".

So the above query will return all records for MemberIDs 1 and 3, but not for MemberID 2:

S1796.png

[BittenApple]

Hans,
I might have learned incorrectly or my thought process is different:

This SQL will return ALL records for those MemberIDs that have at least one record with HospitalName equal to "xyz". But it will not only return records with HospitalName equal to "xyz". It will also return all other records for those MemberIDs.

SELECT MemberID
    FROM TableA
    WHERE HospitalName="xyz")
Only brings back 1 xyz and 3 xyx
Then outer query checks if the memberID is in 1 xyz and 3xyz, if it is there, it will be back in the result.
The Select * from can't bring 1 abc, because SELECT MemberID
    FROM TableA
    WHERE HospitalName="xyz")
has a filter and it only brings 1 xyz, 3 xyz
How does the outer query select 1 abc, even if member IDs' are same, but Select * from can't bring 1 abc, because SELECT MemberID
    FROM TableA
    WHERE HospitalName="xyz") doesn't have 1 abc, it only has 1 xyx and 3 xyz.

-How does the outer query select 1 abc when it is filtered by where clause?
Regards,
BittenApple

[HansV]

The outer query is

Code: Select all

SELECT *
FROM TableA
WHERE MemberID IN (...)

The inner query is

Code: Select all

SELECT MemberID 
    FROM TableA
    WHERE HospitalName="xyz"

This inner query returns 1 and 3 for MemberID, because those are the MemberIDs that have a record with HospitalName = "xyz".

So the outer query is equivalent to

Code: Select all

SELECT *
FROM TableA
WHERE MemberID IN (1, 3)

In other words, the outer query returns all records with MemberID either 1 or 3. There is no condition on HospitalName in the outer query.

If you want only records with HospitalName = "xyz", you don't need an inner query. Simply use

Code: Select all

SELECT *
FROM TableA
WHERE HospitalName="xyz"

This query returns only 2 records.

[BittenApple]

Hans,
Hans: "This inner query returns 1 and 3 for MemberID, because those are the MemberIDs that have a record with HospitalName = "xyz"."
and then outer query has 1, 2, 3, and it only picks up 1 and 3 because outer query is matching on 1 and 3 only.
This is correct.
Please remember memberIDs' are not unique.
Member3 might be in hospitalName abc and member3 might be in hospitalName xyz in another row/ record.
I only should see member3 HospitalName=xyz in the result not member3 with hospitalName=abc.
I hope this makes sense.
Regards,
BittenApple

[HansV]

I have already given you the SQL for that. Please read my replies again carefully.
The last SQL in my previous reply will return only the records with HospitalName = "xyz".

[BittenApple]

Hans,
Thanks, I have it now.
Regards,
BittenApple