July 15, 2009 at 9:04 am
Dears,
I am using SQL SERVER 2005.
I have a table to store row wise PKs' of different tables against ID.
CREATE TABLE A
(
Accounts_ID INT
,Primary_Key_ID TINYINT
,Primary_Key_Table_Name NVARCHAR(50)
,Primary_Key_Column_Name NVARCHAR(50)
,Primary_Key_Column_Value INT
)
INSERT INTO A
SELECT 1,1,'TABLE_1','COLUMN_1',123
INSERT INTO A
SELECT 1,2,'TABLE_1','COLUMN_2',1234
INSERT INTO A
SELECT 1,3,'TABLE_1','COLUMN_3',12345
INSERT INTO A
SELECT 2,1,'TABLE_1','COLUMN_1',123
INSERT INTO A
SELECT 2,2,'TABLE_1','COLUMN_2',1234
INSERT INTO A
SELECT 3,1,'TABLE_1','COLUMN_1',123
Data In Tubular Format:
---------------------------
11TABLE_1COLUMN_1123
12TABLE_1COLUMN_21234
13TABLE_1COLUMN_312345
21TABLE_1COLUMN_1123
22TABLE_1COLUMN_21234
31TABLE_1COLUMN_1123
----------------------
Now I want to get Accounts_ID by comparing dynamics PKs'
Say For Getting Accounts_ID "2"
I will pass two rows for two PKs'
-----------
I have tried with the following without any success:
SELECT DISTINCT Accounts_ID FROM A
INNER JOIN (
SELECT Primary_Key_ID,Primary_Key_Table_Name,Primary_Key_Column_Name,Primary_Key_Column_Value
FROM A
INTERSECT
SELECT Primary_Key_ID,Primary_Key_Table_Name,Primary_Key_Column_Name,Primary_Key_Column_Value
FROM
(
SELECT 1 AS Primary_Key_ID,'TABLE_1' AS Primary_Key_Table_Name,'COLUMN_1' AS Primary_Key_Column_Name,123 AS Primary_Key_Column_Value
UNION ALL
SELECT 2 AS Primary_Key_ID,'TABLE_1' AS Primary_Key_Table_Name,'COLUMN_2' AS Primary_Key_Column_Name,1234 AS Primary_Key_Column_Value
) AS i
) AS t
ON A.Primary_Key_ID=t.Primary_Key_ID AND A.Primary_Key_Table_Name=t.Primary_Key_Table_Name
AND A.Primary_Key_Column_Name=t.Primary_Key_Column_Name AND A.Primary_Key_Column_Value=t.Primary_Key_Column_Value
Can anyone please help me out??
And if this isn't possible, please suggest any other way to do the query.
Thanks in Advance.
July 15, 2009 at 9:55 am
Given your source data when you pass a 2, what do you want as output?
July 15, 2009 at 9:59 am
can you post what you would like the output to look like because from your description it just sound like you would like the two rows that have an accounts_id = 2 but that can't be what you are looking for.
July 15, 2009 at 10:00 am
I will Pass the following two rows
1,'TABLE_1','COLUMN_1',123
2,'TABLE_1','COLUMN_2',1234
to get "2" (Accounts_ID, 1st column in table A)
July 15, 2009 at 10:20 am
I have to ask is the sample data you provided an accurate reflection of your actual data. Because if you are using the values:
1,'TABLE_1','COLUMN_1',123
then there are four rows that would match, two with accounts_id 1 and two with accounts_id 2.
I am wondering, are you wanting the accounts_id if the data that is passed matches exactly and the accounts id does not have more or less rows associated with it
ie
1,'TABLE_1','COLUMN_1',123 would return 3
and
1,'TABLE_1','COLUMN_1',123
1,'TABLE_1','COLUMN_2',1234
1,'TABLE_1','COLUMN_3',12345 would return 1
July 15, 2009 at 10:29 am
Exactly Bob. Thats what i want.
Actually Each Accounts_ID will be associated with value from different tables. And Some tables has 1 PK, Some 2 or more. So i will pass the PKs' (1 or More rows) to match exactly with this table to get Acounts_ID.
---------------------------
Bob Says:
I have to ask is the sample data you provided an accurate reflection of your actual data. Because if you are using the values:
1,'TABLE_1','COLUMN_1',123
then there are four rows that would match, two with accounts_id 1 and two with accounts_id 2.
I am wondering, are you wanting the accounts_id if the data that is passed matches exactly and the accounts id does not have more or less rows associated with it
ie
1,'TABLE_1','COLUMN_1',123 would return 3
and
1,'TABLE_1','COLUMN_1',123
1,'TABLE_1','COLUMN_2',1234
1,'TABLE_1','COLUMN_3',12345 would return 1
Bob
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Edited: Today @ 5:21:01 PM by Robert klimes
July 15, 2009 at 10:48 am
Actually Each Accounts_ID will be associated with value from different tables.
if this is the case then your sample data is incorrect. it should be something like this.
INSERT INTO A
SELECT 1,1,'TABLE_1','COLUMN_1',123
INSERT INTO A
SELECT 1,2,'TABLE_1','COLUMN_2',1234
INSERT INTO A
SELECT 1,3,'TABLE_1','COLUMN_3',12345
INSERT INTO A
SELECT 2,1,'TABLE_2','COLUMN_1',678
INSERT INTO A
SELECT 2,2,'TABLE_2','COLUMN_2',6789
INSERT INTO A
SELECT 3,1,'TABLE_3','COLUMN_1',10
and if this is correct then something like this would suffice
select Accounts_ID from A
where (Primary_Key_ID = 1
and Primary_Key_Table_Name = 'TABLE_2'
and Primary_Key_Column_Name = 'COLUMN_1'
and Primary_Key_Column_Value = '678')
union
select Accounts_ID from A
where (Primary_Key_ID = 2
and Primary_Key_Table_Name = 'TABLE_2'
and Primary_Key_Column_Name = 'COLUMN_2'
and Primary_Key_Column_Value = '6789')
group by Accounts_ID
July 15, 2009 at 11:41 am
Bob,
If u insert the following data
INSERT INTO A
SELECT 4,1,'TABLE_2','COLUMN_1',678
INSERT INTO A
SELECT 4,2,'TABLE_2','COLUMN_2',6789
INSERT INTO A
SELECT 4,3,'TABLE_2','COLUMN_3',67890
Your Query will return both 2 and 4.
But actually the expected return is "2", as Accounts_ID - 2 has 2 PKs' (2 rows) and Accounts_ID - 4 has 3 PKs' (3 Rows)
Thanks.
July 15, 2009 at 2:59 pm
You had stated
Actually Each Accounts_ID will be associated with value from different tables.
which , by inserting those extra rows, would make that statement incorrect. so one accounts_ID can have the same values as another.
in that case give this a try.
with cte as(
select Accounts_ID,count(*) as NoKeys from A
group by Accounts_ID),cte2 as(
select Accounts_ID,count(*)as cnt from A
where Primary_Key_ID in ('1','2')
group by A.Accounts_ID)
select cte2.Accounts_ID from cte2
inner join cte
on cte2.cnt = cte.NoKeys
and cte2.Accounts_ID = cte.Accounts_ID
and cte2.cnt = (select MAX(cnt) from cte2)
July 17, 2009 at 2:29 am
Bob,
Thanks for your great support.
Actually, i couldn't state the problems perfectly.
Each Accounts_ID will relate to a value of a table. This will be basically GL_Accounts_ID for generating Auto Voucher. Whenever a transaction will be made, I will search the related Chart of Accounts Head and insert a voucher.
Say,
Accounts ID 1 will relate to Product M
Accounts ID 2 will relate to Product N
If My Product Table consists of the following structure :
Product_Type TINYINT
Product_ID INT
Then For Product M,
I have to Pass Product_Type 1 and Product_ID 1
For Product N,
I have to Pass Product_Type 1 and Product_ID 2
In Table A,
For Accounts_ID 1, 2 rows will be present for Product M
Product_Type 1
Product_ID 1
For Accounts_ID 2, 2 rows will be present for Product N
Product_Type 1
Product_ID 2
So, one row is absolutely common among them.
Thats why, i need exact comparison.
With INTERSECT operator, I can get the rows with common fields only, cant find a way to get the extra column Accounts_ID. 🙁
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply