SQL - Stumped on a SELECT - please help!

Question

I'm trying to achieve the following in SQL Server 2005:

SELECT (IF EITHER EXISTS) usr.username, pro.email
FROM table1 AS usr, table2 AS pro
WHERE usr.username = 'existing_username'
AND / OR
pro.email = 'existing_email'

I can't figure out how to write something like that. Basically I want it to return the username if it finds an existing one, and return the email if it finds one.

So it would return to me: username, email, both or none

Is this possible???

Answer 1

Certainly it's possible. If you have two tables, Usernames with all the taken user names, and Emails with all the taken emails, this query would return all the rows that either have the same username or the same email.

SELECT * FROM Usernames, Emails WHERE Usernames.Name = 'username' OR Emails.Email = 'email'

Answer 2

Two tables or two columns in one table?

If the latter, would this work?

"SELECT * from the_table WHERE email = '".$input_text."' OR username = '".$input_text."'";

If it is the former, you would probably need to write two queries, checking the input text against the two separate columns and using the code to check the length of each result set (if q1 is 1 OR q2 is 1)

Unless the two tables have a fk relationship in which case you could write one query and use a JOIN statement.

Answer 3

i think you should write Boolean logic for this query....

refer this post may be it helps you. Boolean logic

Answer 4

select usr.username, pro.email
from table1 as usr
inner join table2 as pro on
  usr.id = pro.userId --I'm guessing on the relationship here
where 
  usr.usrname = 'existing_username' or
  pro.email = 'existing_email'

Alternately, you could union results from both tables together:

(select username, null as email from table1)
union
(select null as username, email from table2)

The union will give you results if you don't have a relationship between the tables.

Answer 5

The WHERE clause allows you to specify OR.

SELECT username, email FROM table WHERE username = @username OR email = @email

Answer 6

Its not really clear what you want. Since you're not joining the tables I'm assuming you really want the union of the two

SELECT 
       usr.UserName foo
FROM
     table1 AS usr
WHERE
    usr.username = 'existing_username'
UNION ALL SELECT 
       pro.email foo
FROM
     table2 AS pro
WHERE
    pro.email = 'existing_email'

If you want to know where it came from you could do

SELECT 
       usr.UserName foo,
       'usr' source
FROM
     table1 AS usr
WHERE
    usr.username = 'existing_username'
UNION SELECT 
       pro.email foo
      'email' source
FROM
     table2 AS pro
WHERE
    pro.email = 'existing_email'