Compare Multiple Rows in a table with multiple row

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)

Compare Multiple Rows in a table with multiple row

Author

Topic

ekla_pathik
Starting Member

2 Posts

Posted - 2009-07-17 : 16:11:42

Dears,

I am using SQL SERVER 2005.

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

)

Scenario Explanation:

Each Accounts_ID will relate to a value of a table. This id is basically General Ledger 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 “Product” Table’s PK consists of the following structure:

Product_Type TINYINT
Product_ID INT

For Accounts_ID 1,
2 rows will be present for Product M (in Table A)

1,1,' Product,' Product_Type’,1
1,2,' Product,' Product_ID’,1

For Accounts_ID 2,
2 rows will be present for Product N (in Table A)

1,1,' Product,' Product_Type’,1
1,2,' Product,' Product_ID’,2

Here one row is absolutely common among them.
Thats why, i need exact comparison of rows.

So I will compare Table A with 2 rows to get the related Accounts_ID

Some Related Queries:

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:
---------------------------
1 1 TABLE_1 COLUMN_1 123
1 2 TABLE_1 COLUMN_2 1234
1 3 TABLE_1 COLUMN_3 12345

2 1 TABLE_1 COLUMN_1 123
2 2 TABLE_1 COLUMN_2 1234

3 1 TABLE_1 COLUMN_1 123
----------------------

Passing
1 TABLE_1 COLUMN_1 123
2 TABLE_1 COLUMN_2 1234
3 TABLE_1 COLUMN_3 12345

will return Accounts_ID - 1

-------
Passing
1 TABLE_1 COLUMN_1 123
2 TABLE_1 COLUMN_2 1234

will return Accounts_ID – 2

---------
Passing
1 TABLE_1 COLUMN_1 123

will return Accounts_ID - 3

-----------------------
I have tried 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

With INTERSECT operator, I can get the rows with common fields only, cant find a way to get the extra column Accounts_ID. :-(

Can anyone please help me out??
And if this isn't possible, please suggest any other way to do the query.
Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 01:42:43

[code]SELECT t.Account_ID
FROM YourTable t
INNER JOIN PassingTable p
ON p.Primary_Key_ID =t.Primary_Key_ID
AND p.Primary_Key_Table_Name = t.Primary_Key_Table_Name
AND p.Primary_Key_Column_Name = t.Primary_Key_Column_Name
AND p.Primary_Key_Column_Value = t.Primary_Key_Column_Value
INNER JOIN (SELECT Account_ID,COUNT(Primary_Key_ID) AS Total
FROM YourTable
GROUP BY Account_ID)t1
ON t1.Account_ID=t.Account_ID
GROUP BY t.Account_ID
HAVING COUNT(*)=MAX(t1.Total)
[/code]

ekla_pathik
Starting Member

2 Posts

Posted - 2009-07-18 : 03:04:51

The query is returning multiple rows.

--Passing Table
CREATE TABLE B
(
Primary_Key_ID TINYINT
,Primary_Key_Table_Name NVARCHAR(50)
,Primary_Key_Column_Name NVARCHAR(50)
,Primary_Key_Column_Value INT

)

INSERT INTO B
SELECT 1,'TABLE_1','COLUMN_1',123

INSERT INTO B
SELECT 2,'TABLE_1','COLUMN_2',1234

--------

SELECT t.Accounts_ID
FROM A t
INNER JOIN B p
ON p.Primary_Key_ID =t.Primary_Key_ID
AND p.Primary_Key_Table_Name = t.Primary_Key_Table_Name
AND p.Primary_Key_Column_Name = t.Primary_Key_Column_Name
AND p.Primary_Key_Column_Value = t.Primary_Key_Column_Value
INNER JOIN (SELECT Accounts_ID,COUNT(Primary_Key_ID) AS Total
FROM A
GROUP BY Accounts_ID)t1
ON t1.Accounts_ID=t.Accounts_ID
GROUP BY t.Accounts_ID
HAVING COUNT(*)=MAX(t1.Total)

Is returning 2 and 3 but
the expected result is 2, as A.Accounts_ID 3 has only one row. But my passing table has 2 rows. My output needs exact match of rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources