Hi,

At present I have a logic implemented like below code example where I can get a combination of columns cid and ani in 'config' table to which I have to map columns cid and ani present in my 'current' table.

Now, a new column pid is added to both 'config' and 'current' tables, like

alter table config add pid integer 

alter table current add pid integer

Now I can have data in a combination of cid and ani (where pid can be NULL) or cid and pid (where ani can be NULL) or a row where all cid, ani and pid can have values. In this scenario, how should I make the changes in the current implementation given below?

Regards.

create table current (cid integer, ani integer, resetdate datetime, threshold integer)
create table config (cid integer, ani integer, resetdate datetime, threshold integer )



SELECT cid, ani from 

(
	SELECT 	A.cid, 
		A.ani,
		CASE WHEN C.cid is null then B.resetdate ELSE C.resetdate END AS resetdate,
		CASE WHEN C.cid is null then B.threshold  ELSE C.threshold  END AS threshold
	FROM	current AS A

	LEFT JOIN 
		(	
			SELECT cid, ani ,resetdate , threshold  from config
		) AS B
	ON A.cid = B.cid AND B.ani is null

	LEFT JOIN 
		(	
			SELECT cid, ani ,resetdate , threshold  from config
		) AS C
	ON A.cid = C.cid AND A.ani = C.ani WHERE A.ani IS NOT NULL
)AS D 
GROUP BY cid, ani

Good day,

basically you just need to add some more cases in the CASE statement (more options of WHEN... THEN...) acording to the options that you might get with the new column. each option before should be break into 2 options: one for pid is null and one for pid not null

If you post some sample data it will be simpler to show you the way specifically, but this is the basic idea

* by the way, you should not use names like this for tables. For example the name "current" is a reserve name in T-SQL and therefore is a very bad idea. but even using name like "config" is a very bad idea since tomorrow it might be uses for something new. You should names that will never be problematic. The best option IN MY OPINION is using 3 parts name. a name that is combine from (1) unique prefix for example your company short name (like MS used by Microsoft), (2) The table meaningful name, which now can be any word including current (but current what?!? this has no meaning), (3) an object type (for example Microsoft use sp for stored procedure)

* in your case you do not need to use sub-query. this query is the same as yours basically:

SELECT cid, ani 
from (
	SELECT 	A.cid, A.ani,
		CASE WHEN C.cid is null then B.resetdate ELSE C.resetdate END AS resetdate,
		CASE WHEN C.cid is null then B.threshold  ELSE C.threshold  END AS threshold
	FROM [current] AS A
	LEFT JOIN config AS B ON A.cid = B.cid AND B.ani is null
	LEFT JOIN config AS C ON A.cid = C.cid AND A.ani = C.ani WHERE A.ani IS NOT NULL
)AS D 
GROUP BY cid, ani

Hello.

Thanks for the reply. Obviously, no one will name the actual tables like that. This was just a sample representation of the actual tables. For the sake of simplicity, I did not give actual complex table and column names.

Also, I need that subquery because I have some calculated columns (&other joins) there which I have not mentioned to keep the example simpler as those columns are not relevent to my problem statement. But I am sorry about not mentioning it earlier.

Ok, now, I have thought of something like below. All users are wecome to provide their views.

Regards.

SELECT cid, ani , pid FROM 
(
	select  A.cid, 
			A.ani,
			A.pid, 
			case when c.cid is null then b.threshold else c.threshold end as threshold
	from	CURRENT AS A
	left join 
			(
				select	cid,
						ani,
						pid,
						threshold 
				from CONFIG
			)B
	on A.cid = B.cid AND (B.ani is NULL OR B.pid IS NULL)
	left join
			(
				select	cid,
						ani,
						pid,
						threshold 
				from CONFIG
			)C
	on A.cid = C.cid AND ((A.ani = C.ani OR A.ani = C.pid) OR (A.ani = C.ani AND A.ani = C.pid)) WHERE A.ani is NOT NULL  OR A.pid is NOT NULL
)Z GROUP BY cid, pid,ani