SQL Query for Multiplication

Question

I have two relations(tables in SQL Database) having purely numbers. These two relations are to be imagined as Matrices.

The Question is to write an SQL query to multiply there two relations as we do with normal matrix multiplication.

I have been racking my brains for this.... But to no use :O

Can anyone please help me out?????

Table number 1 :

@ Aaron Bertrand :

I am using SQL Server 2008 R2 The Screenshots of the tables are :

Table 1 :

Table 2 :

I used the Query :

select t1.A,t2.B,SUM(t1.C*t2.C)
from Table_1 as t1 join Table_2 as t2 on t1.B=t2.A 
group by t1.A,t2.B   
order by t1.A

The Answer :

This way is quite Easy rather than my original Matrix way as suggested by @Marcelo Cantos and @ypercube...

Answer 1

Assuming the following structure:

A (row INT, col INT, value FLOAT)
B (row INT, col INT, value FLOAT)

You can write this:

SELECT A.row, B.col, SUM(A.value * B.value)
  FROM A JOIN B ON A.col = B.row
 GROUP BY A.row, B.col

EDIT: Using the tabular layout of the table to mimic a matrix structure makes this problem much more difficult, for at least two reasons:

1. You can't deal with rows and columns in a symmetrical fashion.
2. You can't portably refer to the row an element comes from.

The best work-around would be to add a row number column to each table thus...

table1 (row, A, B, C)
table2 (row, A, B, C)

...then synthesise the structure I suggest above as a view on each table...

CREATE VIEW A
    SELECT row, 1, A FROM table1 UNION
    SELECT row, 2, B FROM table1 UNION
    SELECT row, 3, C FROM table1 UNION

-- Ditto VIEW B

...and finally use the SQL above to evaluate the answer. If necessary, you could write another view that converts the result back into a tabular structure.

Frankly, however, I consider all this to be very misguided. You will find things work much better with a row/col/value structure (and more logically coherent) than an A/B/C/... structure. Consider, for instance, how easily you can compute the transpose of a matrix using row/col/value:

SELECT col AS row, row AS col, value
  FROM A

or sum the diagonal:

SELECT SUM(value)
  FROM A
 WHERE row = col

and ask yourself how easy these would be using your structure.

P.S.: On the subject of logical coherence, your tables are not relations in the proper sense of the word, since they define an order on the rows, and must necessarily allow for duplicate rows, neither of which make sense in a relation, which is (loosely) defined as a set of tuples. This conflation of n-dimensional relations with 2-dimensional tables is at the heart of much that is ill with data management these days.

Answer 2

I assume both tables have (i, j, dataij) columns

where i means row and j means column:

SELECT a.i
     , b.j
     , SUM(a.dataij * b.dataij)
FROM table1 AS a
  JOIN table2 AS b
    ON a.j = b.i
GROUP BY a.i
       , b.j

---

With this data structure, operations between matrixes like multiplication are easier. Harder is to show the tables in usual matrix format. One way is to use something like this (check for PIVOT if you have a version of SQL-Server that supports it for other ways to achieve it):

SELECT
    MIN(CASE WHEN j=1 THEN dataij ELSE NULL END) AS column1
  , MIN(CASE WHEN j=2 THEN dataij ELSE NULL END) AS column2
  , MIN(CASE WHEN j=3 THEN dataij ELSE NULL END) AS column3
FROM tableX 
GROUP BY i
ORDER BY i
;