Query help

Hello everyone,
I need some help building a query using three tables and I am having
difficulty writing the query.
Here are the tables:

GENERAL:
GID - primary key
PARCEL
EDITDATE
MAPCODE

SALES:
GID - foreign key
SLSDATE
SLSAMOUNT

RESIDENCE:
GID - foreign key
OCCUPANCY
LIVINGAREA

The relationship is one-to-many from the General table to both the Sales and
the Residence tables. I want to allow users to query by every field in each
of these tables. The problem is that this is a Sales search, so there must
be a record in the Sales table before it searches all other criteria. How
do I setup the joins for this?
Thanks!
MVMMVM (nospam@.nospam.org) writes:
> Hello everyone,
> I need some help building a query using three tables and I am having
> difficulty writing the query.
> Here are the tables:
> GENERAL:
> GID - primary key
> PARCEL
> EDITDATE
> MAPCODE
>
> SALES:
> GID - foreign key
> SLSDATE
> SLSAMOUNT
>
> RESIDENCE:
> GID - foreign key
> OCCUPANCY
> LIVINGAREA
>
> The relationship is one-to-many from the General table to both the Sales
> and the Residence tables. I want to allow users to query by every field
> in each of these tables. The problem is that this is a Sales search, so
> there must be a record in the Sales table before it searches all other
> criteria. How do I setup the joins for this?

The standard recommendation for this type of question is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it easy to copy and paste and develop a tested solution.

With incomplete information, it goes down to guessworks, which may be
less accurate.

One thing is not clear to me what the result set should look like. If you
have something like:

SELECT ...
FROM general g
JOIN sales s ON g.GID = s.GID
JOIN residence r ON g.GID = r.GID

and for a certain row in general, there are 13 rows in sales and 7
rows in residence, you will get 91 rows in the result with all
combinations of sales and residence. Since this is probably not what
you want, the query about is not the right one. But since I don't know
what you want, I don't what is the right.

Of course, to only find rows in general + residence that have some
match in sales, you can do:

SELECT ...
FROM general g
JOIN residence ON g.GID = r.GID
WHERE EXISTS (SELECT *
FROM sales s
WHERE s.GID = g.GID
AND s.SLSDATE >= '20050201'
AND s.SLSDATE < '20050301')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So if you only want to search for things with a row in SALES, you can
start there and outer join the other tables. However, since SALES.GID
is a foreign key to GENERAL, you will always have a row in GENERAL as
well -- so you could start there, also.

I think you want something like this:

select ...
from SALE s
join GENERAL g
on s.GID = g.GID
left outer join RESIDENCE r
on g.GID = r.GID
where ...|||Gentlemen,
Thank you. That is exactly what I needed.

Query by Example in Enterprise Manager

Why hasn't Microsoft adopted the same level of excellent QBE in Enterprise Manager that is included with MS Access for building queries? I am just not a die hard fan of using Query Analyzer and templates for building complex queries. I can understand using QA for performance tuning, etc. but for queries like UPDATE queries using RIGHT OUTER JOINS that have many attribute references, I miss my QBE in Access.

When I did my Masters, I wrote a research paper on Query By Example developed in the 70's by Dr. Mosh Zloof of IBM and there are some really good points about it.

1.) Visually seeing the relationships and joins.

2.) Elimination of typo errors.

3.) Eliminates reference ambiguity.

I don't mind keying in a complex query, but a nice visual tool with the relationships/joined mapped out and the elimination of reference ambituity would make me feel a little better before I hit the Execute Query button. Sometimes the Parse Query button does not exactly give me that warm fuzzy feeling but it helps.

Does anyone know if MS ever plans to adopt the level of QBE provided in MS Access or are there any third party tools available in comparison?

I'm not asking for help with keying in queries through Query Analyzer or any arguments about why that is so much greater. I'm just looking for QBE improvements in SQL server's Enterprise Manager.I understand what you are saying about visualizing the query and seeing the relationship. You are right in saying that MS Access does a good job of that, however, as a DBMS, Access just doesn't cut it. SQL Server is great.

If you are looking to visualize in SQL Server, there is a diagram tool under the database that you are working with so you can build relationships... you can also perform a visual query by right clicking on a table and selecting query from the popup menu. From there you can add tables and make relationships all by pointing and clicking... good thing to do if you aren't too sure about sql... it looks just like access's version and runs almost the same way.

But hey, look at it this way... in Access you are not in control... you simply control where you point and click and Access does the rest. In Query Analyzer, you are more than just a user ... you have flexibility and at the same time you are becoming more and more competant and confident in writing SQL and T-SQL... Remember, there is hardly anything that can't be fixed. So don't be afraid to click that button!!!!! Keep going at it and you'll be a SQL pro in no time!!!!!!|||Believe me, I've been working with SQL Server for 2 1/2 years but I still miss the QBE capability of Grid Analyzer in MS Access. For example, in SQL's Query Analyzer you can't add a joined table for an UPDATE query but you can for a SELECT. What's up with that? Seems pretty basic to me.|||Personally, I think it's funny that everyone balks at using QBE in EM for query building. I found that in Access, my percentage of reference ambiguity using Grid Manager was zero. However, when I use QA and templates, it's probably 5 to 25% and then I have to make corrections. To me, it's like comparing BASIC to VISUAL BASIC. Visual tools are awesome! Why people think it's such an insult to use them is beyond me. Just my two cents.

Query building based on user selections

I currently have a form that has different options in a list box such as:

Status Codes

Division Codes

Project Managers

Report Types

The user can select a value from any one of these and hit a search button to run a query. Currently I am running through some IF statements to evaluate which combination of values were selected to run the right SQL code. Example

A user wants to view reports that have a status code of "Active" and a division code of "001". So my if statement would be something like (not in perfect syntax, just trying to get the point across):

if strStats <>"" and strDivision <>"" and strProjMgr ="" and strRptType ="" thenselect ...from ...where status_cd = strStats and division_cd = strDivision

end if

So my question is this: Is there a way to build the query dynamically (1 time) without having to run through all the senarios? 

Yes. you could write a query as:

select ...
from ...
where status_cd = ISNULL(@.strStats,status_cd)

and division_cd = ISNULL(@.strDivision,division_cd )

and ...

pass the values for @.strStats and @.strDivision. You can pass the parameters in any combination.

|||So this basically allows a condition to be null or a value can be passed?|||Yes.