quote:

---

Originally posted by MIK_2008

Well i am unable to understand what kind of output do you need. Can you provide the details of the required output and i hope every integer in the stock table represents the ID of "Affiliates"

---

declare @stock table (Artist int, Owner int, Buyer int)

insert @Stock
select 8, 2, 5 union
select 2, 1, 6 union
select 4, 4, 9 union
select 7, 5, 3

declare @Affiliates table (ID int, Affiliate_name varchar(20))
insert @Affiliates
select 1, 'John' union
select 2, 'Peter' union
select 3, 'Michael' union
select 4, 'Khaled' union
select 5, 'Sarah' union
select 6, 'Amy' union
select 7, 'Laura' union
select 8, 'Samantha' union
select 9, 'Aaron' union
select 10, 'Sharon'

select z.Artist, a2.Affiliate_name, z.Buyer
from (
	select a.Affiliate_name Artist, s.Owner, s.Buyer 
	from @Stock s
	join @Affiliates a on s.Artist = a.ID) z
join @Affiliates a2 on z.Owner = a2.ID

quote:

---

Originally posted by Skorch

Is this what you're looking for?

declare @stock table (Artist int, Owner int, Buyer int)

insert @Stock
select 8, 2, 5 union
select 2, 1, 6 union
select 4, 4, 9 union
select 7, 5, 3

declare @Affiliates table (ID int, Affiliate_name varchar(20))
insert @Affiliates
select 1, 'John' union
select 2, 'Peter' union
select 3, 'Michael' union
select 4, 'Khaled' union
select 5, 'Sarah' union
select 6, 'Amy' union
select 7, 'Laura' union
select 8, 'Samantha' union
select 9, 'Aaron' union
select 10, 'Sharon'

select z.Artist, a2.Affiliate_name, z.Buyer
from (
	select a.Affiliate_name Artist, s.Owner, s.Buyer 
	from @Stock s
	join @Affiliates a on s.Artist = a.ID) z
join @Affiliates a2 on z.Owner = a2.ID

Some days you're the dog, and some days you're the fire hydrant.

---

SELECT AffTbl2.Affiliate_Name OwnerName, Z.* 
		FROM (
				SELECT AffTbl.Affiliate_Name ArtistName, StockTbl.* 
				FROM Stock StockTbl 
				JOIN Affiliates AffTbl ON StockTbl.Artist=AffTbl.ID) Z 
		JOIN Affiliates AffTbl2 ON Z.Owner=AffTbl2.ID 
		WHERE Z.VAT_Cat IS NULL 
		ORDER BY Z.Artist ASC