Query Help: Problem with exclusion logic

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query Help: Problem with exclusion logic

Author

Topic

Lienne
Starting Member

11 Posts

Posted - 2006-12-05 : 12:36:08

Hi all,

I have a question with (Most likely) a very simple answer. I'm trying to build a query that will run on a table full of house addresses. Each element is in it's own separate field, such as house number, street suffix, street name, etc.

What I need the query to do, is select all rows which match with a user-provided address (minus the house number) essentially giving us all houses on that street. I then need to run the process a second time automatically removing that street from the results (Don't worry about thinking of another way to do this. It has to be done this way).

The problem I'm running into is that when I try to append the select statement for the second pass, since the fields are separate, I'm essentially getting all items where street name aren't the same, and street suffix aren't the same etc. rather than where (as a whole) they are not the same.

Thanks a bunch, and let me know if I need to clarify!

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 13:00:58

You need to post your SQL and table structure if you want to get meaningful help, but something like this will do it

SELECT ...
FROM houses h
LEFT JOIN (SELECT h1.houseid
FROM houses h1
WHERE h1.street = 'something' AND h1.city = 'something' AND h1.zip = 'something') ON h1.houseid = h.houseid
WHERE h1.houseid IS NULL

Lienne
Starting Member

11 Posts

Posted - 2006-12-05 : 13:23:17

Ok, here's a bit of it. I'm running this in PHP.

"SELECT * FROM contacts WHERE deleted='1' AND id='' AND x_coord<>'' AND y_coord<>'' AND community_name='$comm_name' AND street_name='$street_name' AND prefix_directional='$prefix_directional' AND street_suffix='$street_suffix' AND post_directional='$post_directional' AND house_number <= '$hn'$not_string ORDER BY house_number DESC"

the $not_string is where, after each time it loops, I add the last processed street.

$not_string .= " AND (street_name<>'$street_name' AND prefix_directional<>'$prefix_directional')"

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 14:14:07

I'd recommend that you get it working in Query Analyzer first and then put it into your PHP code, but that's up to you.

So take the query template I gave you and use contacts instead of houses and contactid instead of houseid.

Lienne
Starting Member

11 Posts

Posted - 2006-12-05 : 14:52:55

ok, I'll try that. Unfortunately, I'm not versed enough in sql to know what most of those commands do. What is the dot syntax for? What is LEFT JOIN? And what is ON? Thanks!

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:13:15

That seems to be an advanced query. Maybe over your head.
Post some sample data here and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 15:15:48

quote:
Originally posted by Lienne

ok, I'll try that. Unfortunately, I'm not versed enough in sql to know what most of those commands do. What is the dot syntax for? What is LEFT JOIN? And what is ON? Thanks!

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

Look up SELECT in Books Online, and these sites will teach you about those things, you should take the time to learn them now or you won't get very far
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lienne
Starting Member

11 Posts

Posted - 2006-12-05 : 15:33:10

I have no problem with select statements in general; I'm just trying to get my head around what that meant. I have an SQL book I'm looking at at this moment and am still having trouble understanding what you wrote.

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:43:30

That is because we have to guess what you need, bevause you hav not provided enough information.
Provide some sample data, your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

EDIT: some emphasis

Lienne
Starting Member

11 Posts

Posted - 2006-12-05 : 15:47:45

I appreciate your help, but I don't understand why the above data isn't enough. I'm just not sure what you're asking for.

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 15:57:22

quote:
I have no problem with select statements in general; I'm just trying to get my head around what that meant. I have an SQL book I'm looking at at this moment and am still having trouble understanding what you wrote.

quote:
What is the dot syntax for? What is LEFT JOIN? And what is ON?

Your book should help you with this pretty quickly - what I gave you is "select statements in general".

Dot syntax is absolutely standard, you'll see it in most SQL queries, its the table name or alias (the h and h1 in the query I gave you are table aliases) followed by the column nmae. That allows you to refer to columns in different tables that have the same name. But you can also have names like
databasename.schemaname.tablename
See - http://www.sql-tutorial.net/SQL-Aliases.asp for example

LEFT JOIN is a left outer join, see joins in any standard SQL SELECT statement reference including Books Online.
See http://www.sql-tutorial.net/SQL-JOIN.asp for example

ON is the join condition (you may be used to putting the tables in the join after the FROM keyword and then putting the join conditions in the WHERE clause - if so learn to do it this way, its the ANSI SQL standard and is much better supported and more readable than the other way).
See http://www.sql-tutorial.net/SQL-JOIN.asp for example

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:58:08

You must have a table today full of house addresses you want to parse, right?
Post some 10 records of information here and what you would like yor query to return when working on the records you provided.
Unless you do this, all suggestions we make are just more or less advanced guesses!
And we are tired of guessing...

Are you data a state secret or something so you cannot post the information we require to help you?

Another guess

select * from yourtablenamehere where yourcolumnnamehere like '%' + yourtemplatestreetaddresshere + '%'

Peter Larsson
Helsingborg, Sweden

Lienne
Starting Member

11 Posts

Posted - 2006-12-05 : 19:59:21

actually, yeah the program I'm working on IS for the US gov't so (funny that you mention it) I can't post any of it here.

I just figured out a way around this issue so I don't need to do it this way anymore. I'm sorry for the trouble, and thank you for your help though!

----------------------------
> SELECT * FROM users WHERE clue > 0
0 rows returned

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 01:22:24

Good luck!

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-12-06 : 05:42:08

"actually, yeah the program I'm working on IS for the US gov't so (funny that you mention it) I can't post any of it here."....

I get amazed by this ..... have people ever heard of the process of inventing data....or even simpler lifting 10 lines out of the phone book, and changing the names (key details) to hide the innocent?

Who ever said that test data had to be live?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 05:50:07

Is there a skill level that must be satisfied to work for the US government?

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-12-06 : 09:24:46

Well the top job got filled by an idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources