-- This is included by gregslist.sql
-- This is modeled on interest.sql, but it didn't go as smoothly when building
-- the seeking table.
SELECT "Create seeking" AS "COMMENT";

DROP TABLE t_seeking;
DROP TABLE t_temp_seeking;
CREATE TABLE t_temp_seeking
(
	id			INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	seeking	VARCHAR(128)
) AS
	SELECT seeking FROM t_gregslist WHERE seeking IS NOT NULL
	GROUP BY seeking
	ORDER BY seeking;
SELECT * FROM t_temp_seeking;

ALTER TABLE t_temp_seeking
    ADD COLUMN seeking1 VARCHAR(64),
    ADD COLUMN seeking2 VARCHAR(64);
SELECT id, seeking1, seeking2, seeking FROM t_temp_seeking;

SELECT "Now build temporary seeking table..." AS "COMMENT";
-- --------------------------------------------------------------------------
-- Now, update the new columns with information from the old, single column
-- "seeking", page 290 (solution page 341).
UPDATE t_temp_seeking SET seeking1 = SUBSTRING_INDEX( seeking, ',', 1 );
SELECT seeking1, seeking2, seeking FROM t_temp_seeking;
UPDATE t_temp_seeking
	SET seeking = TRIM( RIGHT( seeking, ( LENGTH( seeking ) - LENGTH( seeking1 ) - 1 ) ) );
SELECT seeking1, seeking2, seeking FROM t_temp_seeking;

UPDATE t_temp_seeking SET seeking2 = SUBSTRING_INDEX( seeking, ',', 1 );
SELECT seeking1, seeking2, seeking FROM t_temp_seeking;

UPDATE t_temp_seeking
	SET seeking = TRIM( RIGHT( seeking, ( LENGTH( seeking ) - LENGTH( seeking2 ) - 1 ) ) );
SELECT seeking1, seeking2, seeking FROM t_temp_seeking;

SELECT "Now build new seeking table..." AS "COMMENT";

CREATE TABLE t_seeking
(
	id			INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
   seeking	VARCHAR(64)
) AS
	SELECT seeking1 AS seeking FROM t_temp_seeking WHERE seeking1 IS NOT NULL
		AND LENGTH( seeking1 ) <> 0
	GROUP BY seeking1 ORDER by seeking1;

INSERT INTO t_seeking ( seeking )
	SELECT seeking2 AS seeking FROM t_temp_seeking WHERE seeking2 IS NOT NULL
		AND LENGTH( seeking2 ) <> 0
	GROUP BY seeking2 ORDER by seeking2;

SELECT * FROM t_seeking;

DROP TABLE t_temp_seeking;

# This is built by Java code...
#CREATE TABLE t_contact_seeking
#(
#	contact_id	INTEGER,
#	seeking_id	INTEGER
#);
#DESC t_contact_seeking;