alternative to union

hi, i have tables like sells, byus, rents, credits
they all have a FK to table partners - partner_guid
how do i get all sells, buys ... for a given partner w/ 1 query?
i`ve been thinking about UNION but it will require(will it ?) me to
write all where and join clauses 4 times
TIAyou can try a similar one as shown below
select * from
(
select col1,col2.. from table 1
union all
select col1,col2.. from table2
union all
select col1,col2.. from table3
)
x
inner join partners p on
x.partnerid = p.partnerid
where
p.PartnerName = 'xxx'
- Sha Anand
"Freddie" wrote:

> hi, i have tables like sells, byus, rents, credits
> they all have a FK to table partners - partner_guid
> how do i get all sells, buys ... for a given partner w/ 1 query?
> i`ve been thinking about UNION but it will require(will it ?) me to
> write all where and join clauses 4 times
> TIA
>|||Greetings,
"Freddie" <sure@.spam.com> wrote in message
news:uQhe9o2mGHA.1208@.TK2MSFTNGP03.phx.gbl...
> hi, i have tables like sells, byus, rents, credits
> they all have a FK to table partners - partner_guid
> how do i get all sells, buys ... for a given partner w/ 1 query?
> i`ve been thinking about UNION but it will require(will it ?) me to write
> all where and join clauses 4 times
Assuming a simple schema and that you want totals, here's one without UNION.
select
p.partner_guid,
sum(isnull(s.sale,0)) as total_sales,
sum(isnull(b.buy,0)) as total_purchases,
sum(isnull(r.rent,0)) as total_rents,
sum(isnull(c.credit,0)) as total_credits
from partners p
left outer join sells s
on s.partner_guid = p.partner_guid
left outer join buys b
on b.partner_guid = p.partner_guid
left outer join rents r
on r.partner_guid = p.partner_guid
left outer join credits c
on c.partner_guid = p.partner_guid
group by
p.partner_guid
Just add a WHERE clause to get the row for your input partner.
Regards,
Neale NOON|||>> hi, I have tables like sells, buys, rents, credits they all have a FK to
table partners - partner_guid <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>From your personal narrative, however, it sounds like you have
serioius attribute splitting problems. You have taken the values of an
attribute and made them into tables. Would you also split Personnel
into Male and Female?
And why did you think that a GUID is a key? How do you verify it?
Validate it? But, boy, it was quick and easy to code so you did not
have to think of RDBMS design, wasn't it?
You clearly have no idea what you are doing and really need to get more
help than you will find on a Newsgroup.