// C - Categories, P - Parameters, CP_Link - linkage table between C and P
// Basically, it is a list of features belonging to a particular category.

DROP TABLE [C];
CREATE TABLE [C] (
  ID_C	Autoinc,
  Name 	varchar(32),
  PRIMARY KEY( ID_C )
);

DROP TABLE [P];
CREATE TABLE [P] (
  ID_P	Autoinc,
  Name  varchar(32),
  PRIMARY KEY( ID_P )
);

DROP TABLE [CP_Link];
CREATE TABLE [CP_Link] (
  ID		Autoinc,
  FK_ID_C	integer,	// foreign keys
  FK_ID_P	integer,
  PRIMARY KEY( ID )
);

INSERT INTO [C] ( Name ) VALUES ( 'boys' );	// 1
INSERT INTO [C] ( Name ) VALUES ( 'girls' );	// 2
INSERT INTO [C] ( Name ) VALUES ( 'snakes' );	// 3


INSERT INTO [P] ( Name ) VALUES ( 'leg' );	// 1
INSERT INTO [P] ( Name ) VALUES ( 'hand' );	// 2
INSERT INTO [P] ( Name ) VALUES ( 'beard' );	// 3
INSERT INTO [P] ( Name ) VALUES ( 'tail' );	// 4


INSERT INTO [CP_Link] ( FK_ID_C, FK_ID_P ) VALUES ( 1,1 );  // boys
INSERT INTO [CP_Link] ( FK_ID_C, FK_ID_P ) VALUES ( 1,2 );
INSERT INTO [CP_Link] ( FK_ID_C, FK_ID_P ) VALUES ( 1,3 );

INSERT INTO [CP_Link] ( FK_ID_C, FK_ID_P ) VALUES ( 2,1 );  // girls
INSERT INTO [CP_Link] ( FK_ID_C, FK_ID_P ) VALUES ( 2,2 );

INSERT INTO [CP_Link] ( FK_ID_C, FK_ID_P ) VALUES ( 3,4 );  // snakes


//-------------------------------------------------------


SELECT P.Name 
FROM C,P,CP_Link
WHERE (FK_ID_C=ID_C) AND (FK_ID_P=ID_P) AND (C.Name='girls');
// this will result in:
// leg
// hand
// we have category 'girls' selected, which has two features linked. All works fine.

// now a bit more complex:
SELECT DISTINCT P.Name 
FROM C,P,CP_Link
WHERE (FK_ID_C=ID_C) AND (FK_ID_P=ID_P) AND (C.Name IN ('boys','girls'));
// so we have more than one category as input parameter, this will result in:
// leg
// hand
// beard
// so this is a joint list of all features listed.

SELECT 	P.Name 
FROM 	C
	INNER JOIN CP_Link L	ON	L.FK_ID_C	= C.ID_C
	INNER JOIN P		ON	L.FK_ID_P	= P.ID_P
WHERE 	C.Name IN ('boys','girls')
GROUP BY P.Name
HAVING COUNT(*) = 2

SELECT p.name
FROM   p, cp_link
WHERE  cp_link.fk_id_p = p.id_p
   AND cp_link.fk_id_c IN ( 1,2 )
GROUP BY p.name
HAVING Count(p.id_p) = 2;