CODEWITHSUNDEEP
sqldatabaseoracle-database
phd
phd

Reputation: 11

multiplication table sql

How is it possible to create a SELECT query that will output multiplication table without using anything like join or subqueries? One may use some new supporting tables.

upd:

select 
x " ",
x*x1 "1", x*x2 "2", x*x3 "3", x*x4 "4", x*x5 "5", x*x6 "6", x*x7 "7", x*x8 "8", x*x9 "9",        x*x10 "10"
from num, res
where rownum < 11;

Upvotes: 1

Views: 4513

Answers (4)

Milliyon Sebhatu
Milliyon Sebhatu

Reputation: 1

CREATE TABLE Multiplctable(c1 int ,c2 int ,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int ,c10 int,c11 int,c12 int)

DECLARE @Loopcon  int;--LOOP CONTROLL
Set @Loopcon=1;

WHILE(@Loopcon<=12)
    BEGIN;

            INSERT INTO Multiplctable 
            VALUES(@Loopcon*1,@Loopcon*2,@Loopcon*3,@Loopcon*4,@Loopcon*5,@Loopcon*6,
            @Loopcon*7,@Loopcon*8,@Loopcon*9,@Loopcon*10,@Loopcon*11,@Loopcon*12);

            SET @Loopcon=@Loopcon+1;
  END;



  SELECT M.c1 [1],M.c2 [2],M.c3 [3],M.c4 [4],M.c5 [5],M.c6 [6],M.c7 [7],M.c8 [8],M.c9 [9],M.c10 [10],M.c11 [11],M.c12 [12]
  FROM Multiplctable M

Upvotes: 0

Sebas
Sebas

Reputation: 21542

SELECT 
    level AS C, 
    1*level AS R1, 
    2*level AS R2, 
    3*level AS R3, 
    4*level AS R4, 
    5*level AS R5, 
    6*level AS R6, 
    7*level AS R7, 
    8*level AS R8, 
    9*level AS R9, 
    10*level AS R10 
FROM dual CONNECT BY level < 11

See? Here there's only a single query. And no dumb dummy table filling is required.

Upvotes: 1

Aspirant
Aspirant

Reputation: 2278

select level*&n from dual connect by level<=10;

here you can give any number for which you want the mutiplicaion table to be.

Upvotes: 0

Chris Cooper
Chris Cooper

Reputation: 5122

Insert the values into a single table, select from the table twice, provide no join between the two tables, and voila a cartesian product.

Upvotes: 0

Related Questions