Simple SQL Statement

benc007
benc007 used Ask the Experts™
on
I have 2 tables:
User -> userId *, userName, userEmail
Chemical -> chemicalId *, chemicalUserName, chemicalUserEmail
* = primary key

Each user can appear more than once in the User table (ie. 2 or more users can have the same userEmail).
Each chemical can appear more than once in the Chemical table (ie. 2 or more chemicals can have the same chemicalUserEmail).

I would like a SQL statement to return UNIQUE emails from both tables, TableSourceName, with the rest of the information for each table.  This means all emails returned in "userEmail / chemicalUserEmail" below are unique.

eg.
TableSourceName, userId / chemicalId, userName / chemicalUserName, userEmail / chemicalUserEmail:
UserTable, UserID225, userName225, joe@joe.com
UserTable, UserID226, userName225, bob@bob.com
ChemicalTable, ChemID123, chemUserName123, ryan@ryan.com
ChemicalTable, ChemID525, chemUserName525, sara@sara.com
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This will work on SQL Server 2005 and 2008

SELECT UserTable TableSourceName, userId, userName, userEmail
FROM (
SELECT UserTable TableSourceName, userId, userName, userEmail, row_number() over ( partition by userEmail order by userId) rnum
from (
SELECT UserTable TableSourceName, userId, userName, userEmail
FROM User
UNION ALL
SELECT ChemicalTable TableSourceName, chemicalId, chemicalUserName, chemicalUserEmail
FROM Chemical ) temp ) temp1
WHERE rnum = 1

Commented:
SELECT 'UserTable' AS TableSourceName, userId as id, userName as Name, userEmail as Email
FROM User
UNION ALL
SELECT 'Chemical', chemicalId , chemicalUserName, chemicalUserEmail
FROM Chemical
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Small Mistake
-- This will work on SQL Server 2005 and 2008
 
SELECT UserTable TableSourceName, userId, userName, userEmail
FROM (
SELECT UserTable TableSourceName, userId, userName, userEmail, row_number() over ( partition by userEmail order by userId) rnum
from (
SELECT 'UserTable' TableSourceName, userId, userName, userEmail
FROM [USER]
UNION ALL
SELECT 'ChemicalTable' TableSourceName, chemicalId, chemicalUserName, chemicalUserEmail
FROM Chemical ) temp ) temp1
WHERE rnum = 1

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
racek - I am getting an error:
Invalid column name 'userName'.

rrjegan17 - I am using SQL Server 2000, and I am getting errors:
'row_number' is not a recognized function name.
Line 9: Incorrect syntax near 'temp'.

Author

Commented:
Can anyone help?
Top Expert 2012

Commented:
>>I am using SQL Server 2000, and I am getting errors:
'row_number' is not a recognized function name.<<
That is because ROW_NUMBER() is not supported in SQL Server 2000
Top Expert 2012

Commented:
>>Invalid column name 'userName'.<<
That is the name you have it listed in your original question.  

Why don't you post the structure (CREATE TABLE) of both your tables.

Author

Commented:
'userName' is valid ... it was a typo on my end.  The table structure above is correct.

The code by racek is returning records, but I need it to return all records from both tables with unique email addresses.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
benc007,
     That's why I mentioned earlier that my query will work in 2005 and 2008.
I analyzed your requirement many times for SQL Server 2000 and you need to go with a temp table approach and not in a single query.

Try this one and hope this helps.
-- Create temp table 
 
create table #temp (TableSourceName varchar(100), userId int, userName varchar(100), userEmail varchar(100);
 
-- Inserting Distinct Email Id's
 
insert into #temp (userEmail)
SELECT userEmail FROM [USER]
UNION 
SELECT chemicalUserEmail FROM Chemical 
 
-- Update records into Temp table
 
update #temp
set TableSourceName = t2.TableSourceName ,
userId = t2.userId,
userName = t2.userName
from #temp t1, (
SELECT 'UserTable' TableSourceName, userId, userName, userEmail
FROM [USER]
UNION ALL
SELECT 'ChemicalTable' TableSourceName, chemicalId, chemicalUserName, chemicalUserEmail
FROM Chemical ) temp
where t1.userEmail = t2.userEmail
 
-- Fetch results with Distinct Email id
 
select * from #temp

Open in new window

Author

Commented:
rrjegan17, I am getting an error:
Incorrect syntax near the keyword 'insert'.

Commented:
create table #temp (TableSourceName varchar(100), userId int, userName varchar(100), userEmail varchar(100);

SELECT 'UserTable'  , userId  , userName , userEmail  
FROM User
UNION ALL
SELECT 'Chemical', chemicalId , chemicalUserName, chemicalUserEmail
FROM Chemical;

Author

Commented:
You are missing ) so I added it to:

create table #temp (TableSourceName varchar(100), userId int, userName varchar(100), userEmail varchar(100));

But the select does not return unique email addresses.  Keep in mind both the user and chemical tables have duplicate userEmail's and duplicate chemicalUserEmail's.  Only unique email addresses (and the information for the record) for both tables should be returned.

Author

Commented:
When I try to run the select again, I am getting:
There is already an object named '#temp' in the database.

Commented:
if you want to see user_id, which one of duplicates) first (min?) last(max) ?
drop table #temp ;
create table #temp (TableSourceName varchar(100), min_userId int, max_userId int,   userName varchar(100), userEmail varchar(100));
 
SELECT 'UserTable'  , MIN(userId)  , MAX(userId)  ,  userName , userEmail  
FROM User GROUP BY userName , userEmail 
UNION ALL
SELECT 'Chemical', MIN(chemicalId) , MAX(chemicalId) ,  chemicalUserName, chemicalUserEmail
FROM Chemical GROUP BY chemicalUserName, chemicalUserEmail ;

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Thanks for correcting Missing Closing parenthesis and drop table #temp command.

What's the result of my query after those corrections?

Commented:
you will get all unique combinations TableSouce +  userName + userEmail and for each combination first and last id. If you add count, even number of combinations ...  
Test SELECT query only and then, when you know what do you want create a temp table and insert result into this table.


SELECT 'UserTable'  , MIN(userId)  , MAX(userId)  ,  count(*),  userName , userEmail  
FROM User GROUP BY userName , userEmail 
UNION ALL
SELECT 'Chemical', MIN(chemicalId) , MAX(chemicalId) ,   count(*), chemicalUserName, chemicalUserEmail
FROM Chemical GROUP BY chemicalUserName, chemicalUserEmail ;

Open in new window

Author

Commented:
rrjegan17 - I get:
The column prefix 't2' does not match with a table name or alias name used in the query.

racek - Duplicate emails are still being returned. How does the min, max, and count work?  If there are duplicate or more emails, I would like to return the last userId and last chemicalId.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps
-- drop temp table
 
drop table #temp
 
-- Create temp table 
 
create table #temp (TableSourceName varchar(100), userId int, userName varchar(100), userEmail varchar(100));
 
-- Inserting Distinct Email Id's
 
insert into #temp (userEmail)
SELECT userEmail FROM [USER]
UNION 
SELECT chemicalUserEmail FROM Chemical 
 
-- Update records into Temp table
 
update #temp
set TableSourceName = t2.TableSourceName ,
userId = t2.userId,
userName = t2.userName
from #temp t1, (
SELECT 'UserTable' TableSourceName, userId, userName, userEmail
FROM [USER]
UNION ALL
SELECT 'ChemicalTable' TableSourceName, chemicalId, chemicalUserName, chemicalUserEmail
FROM Chemical ) t2
where t1.userEmail = t2.userEmail
 
-- Fetch results with Distinct Email id
 
select * from #temp

Open in new window

Commented:


SELECT x.email,  SUM(x.hits_user) hits_user, SUM(x,hits_chemical) hits_chemical
FROM
(SELECT   userEmail   email, count(*) as hits_user, 0 as hits_chemical 
FROM User GROUP BY 1
UNION ALL
SELECT  chemicalUserEmail,0 ,  count(*)  
FROM Chemical GROUP BY 1) AS x 
GROUP BY x.email;

Open in new window

Author

Commented:
rrjegan17 - I get:
Invalid column name 'userName'.

racek: I get:
The SUM function requires 1 arguments.
GROUP BY expressions must refer to column names that appear in the select list.
GROUP BY expressions must refer to column names that appear in the select list.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Do you have column userName in USER table.
Hope that is what you mentioned in your question.

Kindly check whether the column names in your tables matches with the query.

Author

Commented:
rrjegan17  - yes the table column names are correct.

Author

Commented:
racek - can you help?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Invalid column name 'userName'.

This means that column userName is either not present in that particular table or not valid in the context it is used. Kindly specify where it errored out so that I can help you to fix it.

Author

Commented:
>> Invalid column name 'userName'.
Server: Msg 207, Level 16, State 1, Line 10

The column name is right as racek's code in Posting Id 24886140 works except it is not returning records with UNIQUE email addresses.  racek - can you help?
Top Expert 2012

Commented:
You know many days ago I asked that you post the structure of your table.  Unfortunately you have been reluctant to do that and in consequence what should have been a "Simple SQL Statement" has turned into a nightmare as I had predicted.

Incidentally my comment was posted soon after you stated:
racek - I am getting an error:
Invalid column name 'userName'.

And now 3 days later you have the same problem and stating " racek - can you help?"  There is an irony somewhere...
Top Expert 2012

Commented:
I forgot to mention that since it appears you are in very good hands with people that are really prepared to help you, if only you will let them, I am going to unsubscribe.

Good luck.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly post the tables structures as acperkins mentioned for both USER and Chemical tables..
Because it was some problem with the table structure

Author

Commented:
I appreciate everyone's help, but as I mentioned before the table structure is as follows:

User -> userId *, userName, userEmail
Chemical -> chemicalId *, chemicalUserName, chemicalUserEmail
* = primary key

SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Column Name userName is present in all locations.
Hence invalid column name userName should not come in my statements

Anyhow try this one out
-- drop temp table
 
drop table #temp
 
-- Create temp table 
 
create table #temp (TableSourceName varchar(100), userId int, userName varchar(100), userEmail varchar(100));
 
-- Inserting Distinct Email Id's
 
insert into #temp (userEmail)
SELECT userEmail FROM [USER]
UNION 
SELECT chemicalUserEmail FROM Chemical 
 
-- Update records into Temp table
 
update #temp
set TableSourceName = t2.TableSourceName ,
userId = t2.userId,
userName = t2.userName
from #temp t1, (
SELECT 'UserTable' TableSourceName, userId, userName as userName, userEmail
FROM [USER]
UNION ALL
SELECT 'ChemicalTable' TableSourceName, chemicalId, chemicalUserName, chemicalUserEmail
FROM Chemical ) t2
where t1.userEmail = t2.userEmail
 
-- Fetch results with Distinct Email id
 
select * from #temp

Open in new window

Author

Commented:
Points increased.  Please help.

Author

Commented:
Points increased.  Please help.

Author

Commented:
-
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
benc007,
    Have you tried the recent script provided by me

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial