Selecting results from two tables with same schema

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting results from two tables with same schema

Author

Topic

deniseaddy
Starting Member

12 Posts

Posted - 2009-04-14 : 14:09:31

Hi,

I have four tables:

payments_customers
payments_affiliates
customers
affiliates

payments_customers has a foreign key linking to table customers id
payments_affiliates has a foreign key linking to table affiliates id

Both payments tables have the same schema: id, company, value, date and the only difference is the foreign key linking the payment record to either the affiliate or customer table.

In a single SQL query (depending on parameters passed to sproc) how can I...

1. Select all the data from both tables
2. Or select only data from payments_customer or payments_affiliate
3. Or select only the data where the affiliate/customer id matches the payment_customer/payment_affiliate record

Thanks.

Kokkula
Starting Member

41 Posts

Posted - 2009-04-15 : 03:01:16

Hello,

Try these

payments_customers and customers tables can be linked in the way

SELECT Payments.ID,
Payments.Company,
Payments.Value,
Payments.Date
FROM payments_Customers AS Payments
INNER JOIN Customers AS Cust
ON Cust.ID = Payments.ID
WHERE Cust.ID = @CustID -- Parameter you want

payments_affiliates and affiliates tables can be linked in the way

SELECT Payments.ID,
Payments.Company,
Payments.Value,
Payments.Date
FROM payments_Customers AS Payments
INNER JOIN affiliates AS Aff
ON Aff.ID = Payments.ID
WHERE Aff.ID = @AffID -- Parameter you want

If you want all the data from the two tables then

SELECT Payments.ID,
Payments.Company,
Payments.Value,
Payments.Date
FROM payments_Customers AS Payments
INNER JOIN Customers AS Cust
ON Cust.ID = Payments.ID
WHERE Cust.ID = @CustID -- Parameter you want
UNION
SELECT Payments.ID,
Payments.Company,
Payments.Value,
Payments.Date
FROM payments_Customers AS Payments
INNER JOIN affiliates AS Aff
ON Aff.ID = Payments.ID
WHERE Aff.ID = @AffID -- Parameter you want

Hope its clear and helpful...

Thanks,
Pavan

deniseaddy
Starting Member

12 Posts

Posted - 2009-04-15 : 06:06:56

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources