Problems Joining Two Tables

[lrdave36]

Hey guys,

This should be simple, but it is not making sense to me.

I have two tables:

Table A
_______

user id trans cd permission

1080 PRAK C
1080 PR01 null
1080 PR02 A



Table B

_______

user id tran cd permission

1030 PRAK C
1030 PRAG null
1030 PR02 D



I want to create a query that will do the following:


Return records from both tables where the permissions match for the same trans cd.


Return records from both tables where the permissions do not match.


I'm drawing a blank on how to approach this. My query so far:

SELECT   A.*, B.* 
FROM     dbo.morris A, 
         dbo.abbi B 
WHERE    A.USER_TRAN_CD=B.USER_TRAN_CD

It does return the trans codes that exist in both tables, but from those results, I need only records with the same permission flags and vice versa. Can anyone help? Total noob here.

 

[djj55]

Can you give an example of the expected output?

select a.*, b.*
from dbo.morris a 
inner join dbo.abbi b 
on A.USER_TRAN_CD=B.USER_TRAN_CD

select a.*, b.*
from dbo.morris a 
left join dbo.abbi b 
ON A.USER_TRAN_CD=B.USER_TRAN_CD
where b.user_tran_cd

This may not be it.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[lrdave36]

The expect output with my table data would be

1030 PRAK C


as both tables have the same trans cd and permission flag.

I guess I would need to run a separate query to return the permissions that didn't match in both tables.

 

[gmmastros]

Djj,

In the second query, you should replace where with and so that the where clause condition becomes a second condition for the join clause.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

sorry trying to write on the fly as I am watching a process on my other monitor.

The where clause should have been "WHERE b.user_tran_cd IS NULL" which may not be what is being looked for.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!