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.

Alternative to Creating View with Union in two databases?

I attempted to create a view in SQL Server 2000 that Unions two
queries. The first part of the query gets data from the local server,
the second part gets info from a linked server. (The query works fine
in Query Analyzer.)

I received this error when I tried to save the query:

ODBC error: [Microsoft][ODBC SQL Server Driver] The operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.

[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB provider
returned message: New transaction cannot enlist in the specified
transaction coordinator.]

After a little reading I discovered the "Database limitation":
"A view can be created on a table only in the database the view
creator is accessing".

That's my problem... is there a simple solution or alternative to
creating a view?

Thanks,
Mattmattd@.semiconductor.com (Matt D) wrote in news:7bd0381c.0310070503.4d416d94@.posting.google.c om:

> That's my problem... is there a simple solution or alternative to
> creating a view?

A stored procedure will likely do the trick.

--
Ross Presser -- rpresser AT imtek DOT com
... seeking a new quote ...|||Matt D (mattd@.semiconductor.com) writes:
> I attempted to create a view in SQL Server 2000 that Unions two
> queries. The first part of the query gets data from the local server,
> the second part gets info from a linked server. (The query works fine
> in Query Analyzer.)
> I received this error when I tried to save the query:

I had no problems to create a view spanning two servers.

Since you talk about saving query, I get the idea that you might
be using Enterprise Manager for the task. Try running the CREATE VIEW
statement from Query Analyzer instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp