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

you 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


Show quoteHide quote

Greetings,

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.

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.

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.

SQL Newbie
Iteration in a stored procedure
How to return error from CLR Stored Procedure
Finding Parameters in SP
update column with cursor