Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    11

    Unanswered: UNION with WHERE Clause?

    I have 2 tables(Customer,Staff) with the same column name 'email'
    I would like to join both tables together where email="xxx@xx.com" to check if email exist already in the database
    I have tried

    Code:
    Select email from (select email from staff union select email from customer) as 'CombinedEmail' where email = 'xxx@xx.com'
    But it does not work at all. Is there anything wrong with my codes?
    Thanks

  2. #2
    Join Date
    Jun 2012
    Posts
    11
    Got my answer from
    HTML Code:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109040
    select *
    from
    (select Col1,Col2 from table1
    Union
    select Col1,Col2 from table2
    )t
    where yourconditions

    select Col1,Col2 from table1
    where somecondition
    Union
    selection Col1,Col2 from table2
    where somecondition2

    NOTE: The first condition got a 't' after '(select Col1,Col2 from table1
    Union select Col1,Col2 from table2)' I do not know why. Is there anyone who can tell me why? Thanks

  3. #3
    Join Date
    Dec 2014
    Posts
    1
    The "t" is just a variable for the temporary table.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    Select email 
    from (select email 
    	from staff 
    		union 
    	select email 
    	from customer) as CombinedEmail
    where CombinedEmail.email = 'xxx@xx.com'
    What this SQL script basically does is: create a virtual table with the name CombinedEmail, and then go look for all records in that virtual table that meet a certain requirement (the value in the column email = 'xxx@xx.com').

    In your original code you had quotes around CombinedEmail
    as 'CombinedEmail'
    giving SQL Server the instruction to consider CombinedEmail as a string, and not as the name of the virtual table. Something that makes no sense at that place to SQL Server.
    Last edited by Wim; 12-16-14 at 22:17.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think it would be more efficient to repeat the where clause inside the subquery:
    Code:
    SELECT email
    FROM   (
            SELECT email
            FROM   staff
            WHERE  email = 'xxx@xx.com'
            
            UNION ALL
            
            SELECT email
            FROM   staff
            WHERE  email = 'xxx@xx.com'
           ) As CombinedEmail
    Alternative solution:
    Code:
    SELECT 'Exists!' As result
    WHERE  EXISTS (
            SELECT email
            FROM   staff
            WHERE  email = 'xxx@xx.com'
           )
    OR     EXISTS (
            SELECT email
            FROM   staff
            WHERE  email = 'xxx@xx.com'
           )
    Another alternative solution:
    Code:
    DECLARE @email_address varchar(256) = 'something@example.com';
    
    IF EXISTS (
         SELECT email
         FROM   staff
         WHERE  email = @email_address
       )
    OR EXISTS (
         SELECT email
         FROM   customer
         WHERE  email = @email_address
       )
      BEGIN
        SELECT 'Email already exists';
      END
    ELSE
      BEGIN
        SELECT 'Email doesn't exist';
      END
    ;
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •