kalle volkov wrote:
>
> hi!
>
> probably not the best list but i'll try ;)
>
> i have cross-table like:
> ---
> | first | second | third | fourth
> first | 5 | 4 | 3 | 2
> second | 1 | 2 | 7 | 2
> third | 4 | 1 | 3 | 4
> fourth | 5 | 8 | 12 | 10
> ---
>
> and so on for 200+ rows...
>
> have to build it on MySQL...
>
> what is the best solution seeing that there might be 1000+ lines to
> cross :(
>
> the result should be:
> ---
> SELECT number WHERE first_cross = 'second' AND second_cross = 'fourth';
> ---
> ... which will result the number in the cross of first and second...
> first clause is ALWAYS the upper line and second clause is ALWAYS the
> first column...
>
> TIA
>
> regs,
> cal6, in trouble :(
Hi Kalle
I would use a table with three fields:
CREATE TABLE
cross
(first_cross VARCHAR(30) NOT NULL
, second_cross VARCHAR(30) NOT NULL
, value INTEGER
, PRIMARY KEY (first_cross,second_cross)
)
Your example data would look like this:
INSERT INTO
cross
(first_cross
, second_cross
,value
) VALUES
('first','first',5)
,('first','second',1)
,('first','third',4)
,('first','fourth',5)
,('second','first',4)
,('second','second',2)
,('second','third',1)
,('second','fourth',8)
,('third','first',3)
,('third','second',7)
,('third','third',3)
,('third','fourth',12)
,('fourth','first',2)
,('fourth','second',2)
,('fourth','third',4)
,('fourth','fourth',10)
This will give you full flexibility on the number of crosspoints currently used.
You select with:
SELECT
value
WHERE
first_cross = 'second'
AND second_cross = 'fourth'
Tschau
Christian
