Complex queries

for the linear mind...

[grandpa]

Writing complex queries has never been my forte. I'm passable with simpler queries. Now I have some data that will require either a complex query, and/or multiple tables. Please let me explain.

I capture (non-identifiable) visitor data, including referral strings. The referal gives me search terms, SE's, and advertising codes. My desire is extract specific data based on all three of those criteria. I'm sure that it's possible, but is it more efficient to form a single query on the same field using multiple criteria? Right now I've created multiple tables, populating them with subsets of the original table, and I run my simpler queries over those tables. But.. I know that I can get better results than what I have now. The idea of creating another batch of tables doesn't appeal to me. If I can get what I want from one table I'd be happy.

(There are about 400,000 rows in this table.)

[victor]

One idea (depends on the database you are using): think about creating views rather than separate tables.

That way, your mental model remains the same, and the query optimizer has a chance to improve yhe way it tackles the queries.

[grandpa]

mental model remains the same
there's a scary thought..

I've spent the night working cleaning up the data. Cleaned data is going into separate tables for search terms, SE's, and codes. The record id field is common for each table. I can't think of any better way to do it.

I was ahead of myself trying to write any kind of query until I can work with clean records. ..and I really wanted to be out of town this weekend :(