Query on related tables question

[jpkeller55]

Is it possible to run a query on two related tables to return records where the value of a field in the Main table is not null and a value of a different field in the Related table is not null. So by example if I had Table3 and Table4 related by NameID. My desire is to return all records that have a value in the LastName field in Table3 and a value in the City field in Table4. Is this possible?


I have a Left Join query that is returning more records than I want to see. In the attached jpeg (JoinQry.jpg) I am desiring only the records that have a red arrow. Any help is appreciated. Thanks.

SELECT Table3.NameID, Table3.FirstName, Table3.LastName, Table4.City
FROM Table3 LEFT JOIN Table4 ON Table3.NameID=Table4.NameID;

[pbaldy]

Try

SELECT Table3.NameID, Table3.FirstName, Table3.LastName, Table4.City FROM Table3 LEFT JOIN Table4 ON Table3.NameID=Table4.NameID
WHERE Table3.LastName Is Not Null AND Table4.City Is Not Null

[jpkeller55]

Sorry, I attached the wrong screen shot of table4 and the join query results. These are correct.

[jpkeller55]

Paul, that is producing the following result:

[jpkeller55]

What shows in the white section is what I would like to include in the query results while excluding what is in green.

[pbaldy]

It sounds like you want to reverse the test:

WHERE Table3.LastName Is Null AND Table4.City Is Null

[ajetrumpet]

Try

SELECT Table3.NameID, Table3.FirstName, Table3.LastName, Table4.City FROM Table3 LEFT JOIN Table4 ON Table3.NameID=Table4.NameID
WHERE Table3.LastName Is Not Null AND Table4.City Is Not Null

Paul,

I believe this query is running fine, but the LEFT JOIN is causing the problem. I think what it happening is that the nature of the join is trumping everything else, which it has a RIGHT too anyway (no pun intended).

<edit>
I'm not making much sense above...

Jim,

I think what you might be looking for is this:

WHERE 

(Table3.LastName Is Not Null AND Table4.City Is Not Null) OR

(Table3.LastName Is Null AND Table4.City Is Not Null) OR

(Table3.LastName Is Not Null AND Table4.City Is Null)

Aren't you looking for any combination other than two nulls?

[jpkeller55]

Hey Adam, that is what I am lookinmg for. I will give it a shot

[jpkeller55]

Yep, that did the trick...thanks Adam. Attached are the results of the query and the code.

SELECT Table3.NameID, Table3.FirstName, Table3.LastName, Table4.City, Table4.Car
FROM Table3 INNER JOIN Table4 ON Table3.NameID = Table4.NameID
WHERE 
Table3.LastName Is Not Null AND Table4.City Is Not Null OR
Table3.LastName Is Null AND Table4.City Is Not Null OR
Table3.LastName Is Not Null AND Table4.City Is Null
ORDER BY Table3.FirstName;

[ajetrumpet]

good deal Jim. I went ahead and marked this thread as solved...hope that is OK!

[pbaldy]

Looks like you were right Adam; good job. I'm not that good doing SQL in my head.

[jpkeller55]

Yes, thanks for marking it solved Adam!

[ajetrumpet]

Looks like you were right Adam; good job. I'm not that good doing SQL in my head.

I would think that SQL Server would have burned sql statements of any kind into your head by this point...No?

And the credit is shared...I don't do 'exclusive'.