Checking existance of user id in separate table - T-SQL

Question

Hi,

I have a true dailyWTF on my hands here. We have an account sign up page that dishes out new account numbers in sequential order. The problem we have is two fold. For one we had customers that already have customer IDs sign up for another customer ID. To solve this we added a "Current Customer ID" field to the form so they could enter their current customer ID, the only problem is that they are still assigned a customer ID.

The second problem is that our actual customer database in our order entry software doesn't match up with this database.

What we would like to do is select and test to see if the customer exists in the order entry software database.

The data looks like this:

Signup Table

name    | id | id2
-----------------------
Bill    | 1  | NULL
Jim     | 2  | NULL
Sue     | 3  | NULL
Bob     | 4  | NULL
Author  | 5  | NULL
Bill    | 6  | 1
Sue     | 7  | 3

OrderEntry Table

name    | id
-----------------------
Bill    | 1 
Sue     | 3
Author  | 5

Query Results

name    | id | id2  |id1 Exists | id2 Exists
---------------------------------------------
Bill    | 1  | NULL |Yes        | No
Jim     | 2  | NULL |No         | No
Sue     | 3  | NULL |Yes        | No
Bob     | 4  | NULL |No         | No
Author  | 5  | NULL |Yes        | No
Bill    | 6  | 1    |No         | Yes
Sue     | 7  | 3    |No         | Yes

Answer 1

In SQL Server:

SELECT  s.*,
        (
        SELECT  TOP 1 1
        FROM    orderEntry oe
        WHERE   oe.id = s.id
        ),
        (
        SELECT  TOP 1 1
        FROM    orderEntry oe
        WHERE   oe.id = s.id2
        )
FROM    signup s

In MySQL and PostgreSQL:

SELECT  s.*,
        (
        SELECT  1
        FROM    orderEntry oe
        WHERE   oe.id = s.id
        LIMIT 1
        ),
        (
        SELECT  TOP 1 1
        FROM    orderEntry oe
        WHERE   oe.id = s.id2
        )
FROM    signup s

In Oracle:

SELECT  s.*,
        (
        SELECT  1
        FROM    orderEntry oe
        WHERE   oe.id = s.id
                AND rownum = 1
        ),
        (
        SELECT  1
        FROM    orderEntry oe
        WHERE   oe.id = s.id2
                AND rownum = 1
        )
FROM    signup s

This will return 1 if the row exists, NULL otherwise.

Answer 2

I think you are looking for something like this., since you don't show the software systems table I am not sure, unless you are doing a simple null check on the id2 column. If doing just a null check, the second join here is not needed.

SELECT
    S.Name,
    S.id, 
    S.id2,
    CASE OE.ID
    WHEN NULL THEN 'No'
    ELSE 'Yes'
    AS "ID1Exists",
    CASE OS.id
    WHEN NULL THEN 'No'
    ELSE 'Yes'
    AS "ID2Exists"
FROM Signup S
    LEFT OUTER JOIN OrderEntry OE
    ON (S.id = OE.Id)
    LEFT OUTER JOIN OrderSoftware OS
    ON (s.id2 = OS.id)

This will return a result set just the way you need, if the result for ID 2 is based on null in the id2 column, you would use this query.

SELECT
    S.Name,
    S.id, 
    S.id2,
    CASE OE.ID
    WHEN NULL THEN 'No'
    ELSE 'Yes'
    AS "ID1Exists",
    CASE S.id2
    WHEN NULL THEN 'No'
    ELSE 'Yes'
    AS "ID2Exists"
FROM Signup S
    LEFT OUTER JOIN OrderEntry OE
    ON (S.id = OE.Id)