Query help

[MVM]

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!
MVM

[Erland Sommarskog]

Re: Query help

MVM (nospam@nospam. org) writes:[color=blue]
> 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?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[ZeldorBlat]

Re: Query help

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 ...

[MVM]

Re: Query help

Gentlemen,
Thank you. That is exactly what I needed.