compare 2 tables and display which was not in second table

[rpv_sen]

Hi Friends

i am working for association management project. my database is mssql i am having 2 tables as below

1. table name is community_username

  ASCN_NAME     COM_NAME    URFNAM  Email_id    URAC

    Name1       Commname1    Name1   Email1     Yes
    Name1       Commname1    Name2   Email2     Yes
    Name1       Commname1    Name3   Email3     Yes
    Name2       Commname1    Name1   Email1     Yes
    Name2       Commname1    Name2   Email2     Yes
    Name2       Commname1    Name3   Email3     Yes

2. Table Name is log_list

Ascn_name    Com_name     Request_no    Reviewer_id    Status    Log content      Email_id

  Name1     Commname1    2013/mar/001      Name1       Denied      Content         Email1
  Name1     Commname1    2013/mar/001      Name2       Approved    Content         Email2
  Name1     Commname1    2013/mar/001      Name3       Approved    Content         Email3

  Name1     Commname1    2013/mar/002      Name1       Denied      Content         Email1
  Name1     Commname1    2013/mar/002      Name2       Approved    Content         Email2

Now i want to compare 2 table and i want to display only the URFNAM from community_username which is not in log_list table for ascn_name.

Can any help me to find the solution

[pixelsoul]

Which column are you wanting to compare against in the tables? ASCN_NAME?

[rpv_sen]

Thanks for your reply

i want to compare URFNAM,Ascn_name,Comm_name from community_username and Ascn_name,Comm_name, Reviewer_id from log_list

[pixelsoul]

meh ignore me, I just saw the last comment on your post....

[rpv_sen]

if URFNAM!=Reviewer_id then it has to print URFNAM from community_username.

for example log_list table have name1,name3 for ascn_name1 and Com_name1 when we compare both name1 and name2 in community_username. but name3 is not available in log_list. it should print 'name3' from community_username

[pixelsoul]

I don't have a way to test this right now, but something like this may work.

SELECT DISTINCT URFNAM FROM community_username
WHERE (ASCN_NAME NOT IN (SELECT ASCN_NAME 
FROM log_list))

[pixelsoul]

This may work also

SELECT
a.URFNAM

FROM
community_username a

LEFT JOIN log_list b ON (a.ASCN_NAME = b.ASCN_NAME)

WHERE
b.ASCN_NAME IS NULL

[rpv_sen]

Thanks for your work.

its showing like below

UFNAME   ASCN_name

name3      name1
name1      name2
name2      name2
name3      name2

But we need a output as below only

UFNAME   ASCN_name

name3      name1

[pixelsoul]

It is showing that way from the first query or the second query I posted?
Try the second query if you haven't yet.

[Caeon]

What is your primary key for each? These table header names are pretty confusing.

If you want to compare two table for related information you need to have a primary key that interconnects the two tables together.

Example: There are two tables. One with all users' information and a second with a list of people's cars. Trying connect the username of the person for each car.

Table 1: users
Columns: id, username, password

Table 2: cars
Columns: id, user_id, model, make, year, color

In this case we'll use the "user_id" from table 2 and match it with "id" from the "users" table.

_______________________________

Now to take a stab in the dark for your version... I would use the ASCN_name from the logs to find the URFNAM, assuming the ASCN_name is the same in each and is your primary key.

If you have the ASCN_name then just do a search for that in the community table and pull the URFNAM.

Hope this helps. If not, let me know.

[rpv_sen]

Thanks for your Help my friend. atlast i got the result