Start Free Trial

asked on

Simple SQL Statement

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

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

SELECT 'UserTable' AS TableSourceName, userId as id, userName as Name, userEmail as Email
FROM User
UNION ALL
SELECT 'Chemical', chemicalId , chemicalUserName, chemicalUserEmail
FROM Chemical

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

ASKER

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'.

ASKER

Can anyone help?

Anthony Perkins

>>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

Anthony Perkins

>>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.

ASKER

'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.

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

ASKER

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

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;

ASKER

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.

ASKER

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

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

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

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

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

ASKER

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.

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


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

ASKER

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.

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.

ASKER

rrjegan17 - yes the table column names are correct.

ASKER

racek - can you help?

>> 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.

ASKER

>> 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?

Anthony Perkins

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...

Anthony Perkins

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.

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Points increased. Please help.

ASKER

Points increased. Please help.

ASKER

-

benc007,
Have you tried the recent script provided by me