0

I am using VS2005 and SQL Server 2005.

I am trying to execute multiple sql statements on two sql tables, meaning which I need to do more than a single check on the two tables.

The two tables are:

Table1: UserID, Username.

Table2: UserID, Status.

The following are the checks that I need to perform, but I do not know what is the exact SQL query I need.

  1. Users that exist in Table1 should exist in Table2

  2. Users that exist is Table1 should not have STATUS=DELETE in Table2

  3. Users that do not have STATUS=DELETE in Table2 should exist in Table1

May I know how do I form this checks in to SQL queries and execute them in my VS Sqldatasource?

After which store the results in a variable and display them in GridView table.

Thank You very much for the help.

Improve this question
4
  • what "results" are you looking for? All your checks are boolean Commented Nov 15, 2011 at 2:00
  • i am looking for the "result" that does not fulfill the 3 query checks, means I am looking for Users that exist in Table2 but not Table1; Users that exist in Table1 has 'STATUS=DELETE' in Table2; and Users that do not have 'STATUS=DELETE' in Table2 exists in Table1. Commented Nov 15, 2011 at 2:03
  • @RUiHAO Can a UserID have multiple statuses at the same time? Commented Nov 15, 2011 at 3:08
  • @BrandonMoore Good point. My default assumption was that userID was the PK in table2, but if that assumption is wrong then my solution might not be accurate. Commented Nov 15, 2011 at 12:21

2 Answers 2

Reset to default
1

So you have 3 cases you want to catch:

  1. User exists in table1 but not in table2
  2. User is marked as DELETE in table2 but still exists in table1
  3. User is NOT marked as DELETE in table2 but doesn't exist in table1

These three scenarios are covered in the WHERE clause below in that order.

SELECT *
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid
WHERE (t2.userid IS NULL AND t1.userid IS NOT NULL)
OR (t2.status = 'DELETE' AND t1.userid IS NOT NULL)
OR (t2.userid IS NOT NULL AND t2.status <> 'DELETE' AND t1.userid IS NULL)

Edit: In response to the OP's comment, here is a modified version that will insert data into table3 based on case #1. Assume that table3 is a table that has two columns, userID and problem.

INSERT INTO table3
SELECT userID, 'No Matching Table2 Record'
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid
WHERE (t2.userid IS NULL AND t1.userid IS NOT NULL)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks alot, your sql query worked for me. I can now display the result base on the 3 cases. However, if I want to change the query to: For each case, generate an insert statement into a separate database for each issue found (because I want to add a column 'ISSUE' for each problem found, so that it is more user friendly when displaying the data). How can I change one, for e.g., the first query: SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid WHERE (t2.userid IS NULL AND t1.userid IS NOT NULL) into an INSERT INTO statement instead?
0

You want to join table 1 and 2 on UserID and exclude delete status. Then use the results in your grid view.

select t1.UserID, t1.Username
from Table1 t1 
     join Table2 t2 on t1.UserID = t2.UserID
where t2.Status != 'DELETE'
Improve this answer

1 Comment

This only covers his third case (user is not marked as 'DELETE' in table2 but still exists in table 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.