not equal

[kbreiss]

I have two tables that I am querying. I want to include all records from table A and only those records from table B where the joined fields ARE NOT equal

Here is sort of what I'm trying, but it's not working.


SELECT [table A].DLN, [table B].DUP
FROM [table A] LEFT JOIN [table B] ON ([table A].DUP <> [table B].DUP) AND ([table A].DLN <> [table B].DLN);3

This is putting me no where close.

Thanks in advance,
Kacy
________
Fix ps3

 

[dcx693]

I'd like to learn more about what problem you are trying to solve by doing this type of join, but I will say that usually, non-equi joins cannot be graphically displayed in design view. Usually, when I create non-equi joins there are no lines between the tables in query design view.

Your case might be different, but I already see the LEFT JOIN operator. When you're trying to tell Access to give you items where things don't match, you cannot connect the tables using a common field because that will by definition look for records where the fields have equal values.

Instead, have both tables in your query view, but without any join lines between them. You basically want to eliminate the join operator and replace it with a WHERE like this:

SELECT [table A].DLN, [table B].DUP FROM [table A] WHERE [table A].DUP <> [table B].DUP) AND ([table A].DLN <> [table B].DLN);

Try that expression. You will most likely find a tremendous number of duplicates. You can try using SELECT DISTINCT, but it might not produce the results you want. If that's the case, perhaps we'd better start over again with me asking "What problem are you trying to solve?"

 

[Pat Hartman]

SELECT [table A].DLN, [table B].DUP
FROM [table A] LEFT JOIN [table B] ON ([table A].DUP = [table B].DUP) AND ([table A].DLN = [table B].DLN)
Where [table B].DUP Is Null;

 

[dcx693]

Ah. I misread your post. Pat's solution is right.

 

[kbreiss]

I need to re-stat what I'm trying to do.....I didn't explain it very well. I want to include all records from table A and only those records from table B where the joined fields ARE NOT equal. Maybe the query below we give a better idea of what I'm trying to do. Although I can't get it to work....

Select [table A].dln, [table A].dup
From [table A]
Union
Select [table B].dln, [table B].dup
From [table B]
Where [table A].dln <> [table B].dln
and [table A].dup <> [table B].dup

Hopefully I'm explaining better what I'm trying to attempt.

Thanks guys,
Kacy
________
Edsel Ford Ii History

 

[dcx693]

I think perhaps we're taking it to be more complicated than it actually is.

Say you have TableA and TableB and they have a common field called Field1. You want to see everything on TableA, and you want to see whatever is on TableB, but you also want to see whatever does not match something on TableA.

Since you are asking for what on TableA matches on TableB and you are also asking for what does not match on TableB, are you therefore not asking for everything from TableA?

That query is simply of this form:

SELECT TableA.Field1, TableB.Field1
FROM TableA LEFT JOIN TableB ON TableA.Field1 = TableB.Field1;


Your attempt at the Union query puts together two things (1) everything from tableA and (2) everything from tableB that doesn't match tableA. But that would cause duplication because there are parts of (2) that you've already captured in part (1).

 

[kbreiss]

ok....I tried that, but not getting what I'm looking for. I want to get everything from table A.....even if it does not have a match from table B. And only those records from table B that does not have a match from table A.

Sorry for being a pain

Thanks,
Kacy
________
GENETICALLY MODIFIED FOOD

 

[dcx693]

No, you are not being a pain. I think I might finally understand what you're getting at.

TableB might have records that don't exist at all on TableA. If that's the case, then you do need two separate queries to solve this problem. And you were on the right track with your Union query. Most users (I think) would tend to keep the query results separated because while they do relate to each other indirectly, they have no direct relationship.

Anyway, this will give you everything on TableA:

SELECT TableA.Field1 FROM TableA;


This expression will give you everything on TableB that does not exist on TableA:

SELECT TableB.Field1 FROM TableA RIGHT JOIN TableB ON TableA.Field1 = TableB.Field1
WHERE (((TableA.Field1) Is Not Null));


The union of the two should give you what you need.

 

[Pat Hartman]

Don't you mean:

SELECT TableB.Field1 FROM TableA RIGHT JOIN TableB ON TableA.Field1 = TableB.Field1
WHERE (((TableA.Field1) Is Null));

You want the rows where B has no match with A so the A key would be null.

 

[dcx693]

Thanks Pat. As usual you are correct. Glad we have you on the boards keeping us honest!

 

[kbreiss]

Thanks guys!.........making me look like I actually know what I'm doing here at work.

Thanks,
Kacy
________
Hemp marijuana