* [EMAIL PROTECTED] aka Catalin
> I have 6 tables of type (client character, amount numeric)
> and i want to join them in a single table containing the client
> and all the amounts (A1, A2, ..., A6) - 6 rows. A client can be
> in a single table or in many but is distinct in a table.
>
> T1 (C, A1)
> T2 (C, A2)
> T3 (C, A3)
> T4 (C, A4)
> T5 (C, A5)
> T6 (C, A6)
>
> and the result must be
>
> T (C, A1, A2, A3, A4, A5, A6)
>
> Which is the query?
If you don't allready have a client table, with every client once, you will
need one.
There should be an index on the C column of the tables T1-T6.
You say your client column is character, below I assume it is varchar(30),
change it to what you need:
# make client table
CREATE TABLE Cli (C varchar(30) NOT NULL PRIMARY KEY);
# populate client table
INSERT IGNORE INTO Cli SELECT C FROM T1;
INSERT IGNORE INTO Cli SELECT C FROM T2;
INSERT IGNORE INTO Cli SELECT C FROM T3;
INSERT IGNORE INTO Cli SELECT C FROM T4;
INSERT IGNORE INTO Cli SELECT C FROM T5;
INSERT IGNORE INTO Cli SELECT C FROM T6;
# make new table
CREATE TABLE T (
C varchar(30) NOT NULL PRIMARY KEY,
A1 NUMERIC, A2 NUMERIC, A3 NUMERIC,
A4 NUMERIC, A5 NUMERIC, A6 NUMERIC);
# populate new table:
INSERT INTO T SELECT Cli.C,A1,A2,A3,A4,A5,A6
FROM Cli
LEFT JOIN T1 ON T1.C = Cli.C
LEFT JOIN T2 ON T2.C = Cli.C
LEFT JOIN T3 ON T3.C = Cli.C
LEFT JOIN T4 ON T4.C = Cli.C
LEFT JOIN T5 ON T5.C = Cli.C
LEFT JOIN T6 ON T6.C = Cli.C
Your columns are maybe not named A1..A6, but they are all named 'amount'? In
that case, you can use something like this for the last step:
INSERT INTO T SELECT Cli.C,
T1.amount A1,T2.amount A2,T3.amount A3,
T4.amount A4,T5.amount A5,T6.amount A6
FROM Cli
LEFT JOIN T1 ON T1.C = Cli.C
LEFT JOIN T2 ON T2.C = Cli.C
LEFT JOIN T3 ON T3.C = Cli.C
LEFT JOIN T4 ON T4.C = Cli.C
LEFT JOIN T5 ON T5.C = Cli.C
LEFT JOIN T6 ON T6.C = Cli.C
HTH,
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
