Using a view to omit data from another table

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

Using a view to omit data from another table

Author

Topic

Ernie57
Starting Member

27 Posts

Posted - 2007-03-21 : 10:54:21

Where it's select in red I want to be able to look in another table with a listing of email domains (@express.com,@express.net,@example.org), and when the are query that I don't view them. I don't know if I need some kind of varible or just lookup in another table. I have windows 2000, and same for SQL. I am trying to write it using SQL Query Analyzer.

Thank You,

Ernie

SELECT FirstName, LastName, Street, city, State, zip, Country, Providence, TypeOfAccount, RemoteComputerName, Timestamp1, PresentEmail, NewEmail,
Password, Age, Account_Active
FROM dbo.newusers
WHERE (FirstName <> ' NULL') AND (LastName <> ' NULL') AND (PresentEmail <> ' LOOKUP in another table') AND (NewEmail <> ' NULL') AND (Password <> ' NULL') AND
(Age <> ' NULL') AND (Account_Active <> 'yes') AND (Account_Active <> 'reject') AND (Account_Active <> 'spammer')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 11:00:04

[code]Select * from
(
select 'Table Structure' as [Please post following] union all
select 'Sample Data' union all
select 'Expected Output'
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 11:02:20

[code]SELECT u.FirstName,
u.LastName,
u.Street,
u.City,
u.State,
u.Zip,
u.Country,
u.Providence,
u.TypeOfAccount,
u.RemoteComputerName,
u.TimeStamp1,
u.PresentEmail,
u.NewEmail,
u.Password,
u.Age,
u.Account_Active
FROM dbo.NewUsers AS u
WHERE ISNULL(u.FirstName, 'NULL') <> 'NULL'
AND ISNULL(u.LastName, 'NULL') <> 'NULL'
AND ISNULL(u.PresentEmail, 'NULL') <> 'NULL'
AND ISNULL(u.NewEmail, 'NULL') <> 'NULL'
AND ISNULL(u.Password, 'NULL') <> 'NULL')
AND ISNULL(u.Age, 'NULL') <> 'NULL'
AND ISNULL(u.Account_Active, 'yes') <> 'yes'
AND ISNULL(u.Account_Active, 'reject') <> 'reject'
AND ISNULL(u.Account_Active, 'spammer') <> 'spammer'
AND NOT EXISTS (SELECT * FROM RejectedEmailDomains AS d WHERE ISNULL(u.PresentEmail, 'NULL') LIKE '%' + d.Col1)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 11:05:01

quote:

Originally posted by harsh_athalye

Select * from
(
	select 'Table Structure' as [Please post following] union all
	select 'Sample Data' union all
	select 'Expected Output'
) t

Need... more... oxygen... Can't... breath... because... laughing...

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 18:30:08

quote:
Originally posted by harsh_athalye

Select * from
(
select 'Table Structure' as [Please post following] union all
select 'Sample Data' union all
select 'Expected Output'
) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I like it

Ernie57
Starting Member

27 Posts

Posted - 2007-03-22 : 12:10:24

quote:

Originally posted by Peso

SELECT		u.FirstName,
		WHERE		ISNULL(u.FirstName, 'NULL') <> 'NULL'
		AND ISNULL(u.LastName, 'NULL') <> 'NULL'
		AND ISNULL(u.PresentEmail, 'NULL') <> 'NULL'
		AND ISNULL(u.NewEmail, 'NULL') <> 'NULL'
		AND ISNULL(u.Password, 'NULL') <> 'NULL')
		AND ISNULL(u.Age, 'NULL') <> 'NULL'
		AND ISNULL(u.Account_Active, 'yes') <> 'yes'
		AND ISNULL(u.Account_Active, 'reject') <> 'reject'
		AND ISNULL(u.Account_Active, 'spammer') <> 'spammer'
		AND NOT EXISTS (SELECT * FROM RejectedEmailDomains AS d WHERE ISNULL(u.PresentEmail, 'NULL') LIKE '%' + d.Col1)

Peter Larsson
Helsingborg, Sweden

Thank you,

That help me a lot! I have another question, see statement below.

SELECT FirstName, LastName, Street, city, State, zip, Country, Providence, TypeOfAccount, RemoteComputerName, Timestamp1, PresentEmail, NewEmail,
Password, Age, Account_Active
FROM dbo.newusers

WHERE

NOT EXISTS (SELECT * FROM Blacklist AS d WHERE ISNULL(PresentEmail, 'NULL') LIKE '%' + BadDomains)

Unfortunately, all records are removed. The table BadDomains only contains after the @ sign.
Example:

youarebad.com

So, if an email were tom@youarebad.com I only want to filter out all domains with "youarebad.com", not all records that are not of that domain! The LIKE looks like it's going to work, but fails for some reason!

Thank you,

Ernie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-22 : 12:14:57

[code]...WHERE ISNULL(PresentEmail, 'NULL') LIKE '%@' + BadDomains)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-23 : 06:59:44

Should not matter...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources