|
|
Posted by Erland Sommarskog on 12/05/36 11:29
MVM (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/techinfo/productdoc/2000/books.asp
[Back to original message]
|