Re: NEED HELP and GUIDANCE!

Somthing like this maybe:

CREATE TABLE Members (login VARCHAR(10) NOT NULL PRIMARY KEY, membername VARCHAR(30) NOT NULL UNIQUE); CREATE TABLE Movies (cat_no CHAR(10) NOT NULL PRIMARY KEY, title VARCHAR(30) NOT NULL, released INTEGER NOT NULL, UNIQUE (title,released));

CREATE TABLE MembersMovies (login VARCHAR(10) NOT NULL REFERENCES Members (login), cat_no CHAR(10) REFERENCES Movies (cat_no), media CHAR(3) NOT NULL CHECK (media IN ('DVD','VHS')), Memberstatus CHAR(1) NOT NULL CHECK (Memberstatus IN ('H','W') /* "Has" or "Wants" */), PRIMARY KEY (login,cat_no,Memberstatus));

Query: Who has the movies that member 'X' wants?:

SELECT U.membername, M.title, M.released  FROM MembersMovies AS H,
 MembersMovies AS W,
 Movies AS M,
 Members AS U
 WHERE H.cat_no = W.cat_no

  AND H.media = W.media
  AND H.Memberstatus = 'H'
  AND W.Memberstatus = 'W'
  AND W.login = 'X'
  AND W.cat_no = M.cat_no
  AND H.login = U.login ;

> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am

Any of those products should be capable of your specified requirements. I expect your choice will be guided mostly by what you know and the existing environment you have to work with.

-- 
David Portas
SQL Server MVP
--