problem of using LEFT JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem of using LEFT JOIN

Author

Topic

fengqidan
Starting Member

3 Posts

Posted - 2010-07-19 : 00:16:04

i have 2 tables T and M,T table for all the guests and M only for whom has already registed, i want to select all the guests who havent registed and tables content as shown as follow

T (guests)
ID Email UserName
1 A B
2 G XYZ
3 GG G
4 G G
5 GG XYZ

and
M (members)
ID TID Email MemberName
1 5 GG XYZ

I want to write a query to find out the ppl in T table who has neither same Email nor MemberName in M table: query like follow

select t.* from dbo.T t
left outer join M m on t.Email = m.Email
left outer join M m2 on t.UserName = m2.MemberName
where m.Email is null and m2.MemberName is null;

Results:
ID Email UserName
1 A B
4 G G

NOW i want to get the result of only
ID Email UserName
1 A B,

because 4 G G is actually redundent coz Email G and UserName G may already exist in the T table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-19 : 01:13:40

[code]
select *
from @T t
where t.Email <> t.UserName
and not exists
(
select *
from @M m
where m.Email = t.Email
or m.MemberName = t.UserName
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

fengqidan
Starting Member

3 Posts

Posted - 2010-07-19 : 01:38:39

quote:
Originally posted by khtan

select *
from @T t
where t.Email <> t.UserName
and not exists
(
select *
from @M m
where m.Email = t.Email
or m.MemberName = t.UserName
)

KH
[spoiler]Time is always against us[/spoiler]

i think ur answer is not what i want, firstly, not to show the 4th record
ID Email UserName
4 G G

is not because the email and username are same, is because from either
these two propertities we can infer that it may registed by the same ppl(because it has the same Email with the 2nd record in Guest table, also it has the same username with the 3 record in Guest table)

ID Email UserName
1 A B
2 G XYZ
3 GG G
4 G G
5 GG XYZ

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-07-19 : 02:37:49

--DROP TABLE #T
--DROP TABLE #M
CREATE TABLE #T (ID INT , Email Varchar(100), UserName varchar(100))
INSERT INTO #T VALUES (1,'A','B')
INSERT INTO #T VALUES (2,'G','XYZ')
INSERT INTO #T VALUES (3,'GG','G')
INSERT INTO #T VALUES (4,'G','G')
INSERT INTO #T VALUES (5,'GG','XYZ')
CREATE TABLE #M (ID INT ,TID INT , Email Varchar(100), MemberName varchar(100))
INSERT INTO #M VALUES (1,5,'GG','XYZ')

--Here is a Complex query.
--Not Advisable to use this query since your requirement is also not normalized.

--But will work, but not recommended for more data in the table will take long time.

--Please test and let me know the issue

Select * FROM #T WHERE ID NOT IN
(
Select ID FROM #T
WHERE UserName IN
(
Select Email FROM #T
WHERE UserName IN
(
SELECT Email FROM #M
UNION
SELECT MemberName FROM #M
)
UNION
Select UserName FROM #T
WHERE UserName IN
(
SELECT Email FROM #M
UNION
SELECT MemberName FROM #M
)
)
UNION

SELECT ID FROM #T
WHERE Email IN
(
Select Email FROM #T
WHERE UserName IN
(
SELECT Email FROM #M
UNION
SELECT MemberName FROM #M
)
UNION
Select UserName FROM #T
WHERE UserName IN
(
SELECT Email FROM #M
UNION
SELECT MemberName FROM #M
)
)
)

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources