query which compares values within a table

From: <deann15_at_yahoo.com>
Date: 22 Mar 2006 18:30:32 -0800
Message-ID: <1143081032.074098.161350@j33g2000cwa.googlegroups.com>

I have a table temp which stores data like this:

aid	bid	cid	txt
1	100	900	e
1	100	901	f
1	200	918	a
1	300	915	g
1	300	918	a
1	300	916	k
2	100	901	p
2	200	915	j
2	200	918	b

aid resides in different bids. I need to write a query to compare values from txt column for every aid and cid from source where bid=200 and every target bids, in this case, with bid = 300 and bid=200 and returns conflicting values between source and target for the aid/bid

The query will return something like this: aid srcbid cid srcValue targetbid targetValue

1    200    900                 100        e
1    200    901                 100        f
1    200    915                 300        g
1    200    916                 300        k
1    200    918   a            100
1    200    918   a            300
2    200    901                 100         p
2    200    915   j             100
2    200    915   j             300

......

I have one query to compare values between source and target as long as the rows exist in the table, but for non-existent values from source or target, I have to write a query to compare source with every targetid then union the results together. but the problem is there can have many targetids, then I will have build the query dynamically and have many unions.

what's the alternative to get the correct results? thanks in advance. Received on Wed Mar 22 2006 - 20:30:32 CST