[SQL] Subqueries met meerdere rijen en kolommen?

[Trucker Her]

Goedenavond,

Ben vandaag bezig gegaan met een database ontwerp voor een advertentie website.
Heb de database naar mijn weten redelijk in elkaar geknoopt. Nu ben ik aan het proberen een view te maken.

Hierbij een afbeelding van de huidige database structuur.
http://2v2.nl/uploads/13a...e966005d433c-Untitled.png

Insert queries;

SQL:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Table `category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `category` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Primary key\n',
  `name` VARCHAR(45) NOT NULL COMMENT 'Catecory name',
  `active` TINYINT(1) NOT NULL COMMENT 'Is category active?',
  `description` LONGTEXT NULL COMMENT 'Description about category',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `advertisement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `advertisement` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `category_id` INT NOT NULL,
  `active` TINYINT(1) NULL,
  `CREATED_ON` TIMESTAMP NULL,
  `LAST_UPDATED` TIMESTAMP NULL,
  `DELETED_ON` TIMESTAMP NULL,
  PRIMARY KEY (`id`, `category_id`),
  INDEX `fk_advertisement_category1_idx` (`category_id` ASC),
  CONSTRAINT `fk_advertisement_category1`
    FOREIGN KEY (`category_id`)
    REFERENCES `category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `fields`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `fields` (
  `id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  `active` TINYINT(1) NULL,
  `CREATED_ON` TIMESTAMP NULL,
  `LAST_UPDATED` TIMESTAMP NULL,
  `DELETED_ON` TIMESTAMP NULL,
  PRIMARY KEY (`id`, `category_id`),
  INDEX `fk_fields_category1_idx` (`category_id` ASC),
  CONSTRAINT `fk_fields_category1`
    FOREIGN KEY (`category_id`)
    REFERENCES `category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `field_value`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `field_value` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `field_id` INT NOT NULL,
  `advertisement_id` INT NOT NULL,
  `value` LONGTEXT NULL,
  `active` TINYINT(1) NULL,
  `CREATED_ON` TIMESTAMP NULL,
  `LAST_UPDATED` TIMESTAMP NULL,
  `DELETED_ON` TIMESTAMP NULL,
  PRIMARY KEY (`id`, `field_id`, `advertisement_id`),
  INDEX `fk_field_value_fields1_idx` (`field_id` ASC),
  INDEX `fk_field_value_advertisement1_idx` (`advertisement_id` ASC),
  CONSTRAINT `fk_field_value_fields1`
    FOREIGN KEY (`field_id`)
    REFERENCES `fields` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_field_value_advertisement1`
    FOREIGN KEY (`advertisement_id`)
    REFERENCES `advertisement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Nu als volgt; het probleem.

Ik heb in de tabel fields de veldnamen staan. Aangezien in elke advertentiecategorie er verschillende eigenschappen kunnen zitten aan een advertentie worden die daarin gedefineerd. Dit ook om ze makkelijk te kunnen toevoegen en verwijderen.
Ik wil dus vervolgens op basis van een categorie alle advertenties in een view kunnen zetten voor die categorie.
In de tabel field_values staan de waardes die bij het betreffende veld horen.

Dus adhv een id van een categorie wil ik alle advertenties selecteren en de velden vervolgens corresponderend met de veldnamen ophalen.

Nu dacht ik dat op deze wijze te doen:

SQL:

CREATE VIEW `advertisements_in_category` AS
    SELECT 
        `ads` . *,
        (SELECT 
                *
            FROM
                `category`
            WHERE
                `category`.`id` = `ads`.`category_id`) AS `category`,
        (SELECT 
                `fv` . *,
                    `fld`.`name` AS `field_name`,
                    `fld`.`description` AS `field_description`
            FROM
                `field_value` AS `fv`
                    INNER JOIN
                `fields` AS `fld` ON `fv`.`field_id` = `fld`.`id`) AS `fields`
    FROM
        `advertisement` AS `ads`
            INNER JOIN
        `category` AS `c` ON `ads`.`category_id` = `c`.`id`
    WHERE
        `category_id` = '1'

Helaas werkt dit alleen niet en krijg ik de error:
#1241 - Operand should contain 1 column(s)

Het idee erachter is dus dat ik een result met subresults terug zou krijgen, alleen dit blijkt dus niet mogenlijk?

Hebben jullie ideeën of nieuwe blikken op mijn aanpak? Ik kom er op deze wijze niet uit. Ik hoop iniedergeval dat mijn verhaal duidelijk genoeg is. Zo niet zal ik mijn bericht hierop nog even moeten aanpassen.

Alvast erg bedankt!

[Gomez12]

Eigenlijk alleen maar 1 vraag : Hoe ben je tot deze data-structuur gekomen?

Want als je bij je 1e view al vastloopt dan zou ik zeggen : Iets minder theorie en iets meer toegeschreven naar je eigen nivo...

Een database-ontwerp waarmee je vanaf dag 1 al problemen hebt om query's te maken gaat bij mij direct de prullenbak in. De problemen zullen later niet minder worden (eerder meer)

[Onbekend]

Volgens mij mag een subselect (dus de select op regel 4 en 10) maar 1 kolom selecteren.

Ik ben het volledig met Gomez12 eens.
Dit wordt al een complexe view, die misschien al voor elk request een relatief hoge belasting van de database gaat vragen. Als je vaak zo'n overzicht wil, raad ik je aan om gewoon een nieuwe tabel aan te maken, en die te vullen met de data. Je werkt die data gewoon bij als er iets met een advertentie of artikel gebeurd.

[Douweegbertje]

Ik vind je SQL statement een beetje raar, maar misschien is het omdat het laat is en ik hem gewoon niet snap.

Je moet even zelf gaan voorstellen wat je nu wilt doen. Wat jij denkt kan namelijk helemaal niet (of in elk geval niet zo)

Wat je moet verwachten als result is dit:

code:

category | name | active | description | id (fields) | name | description | id(field_value) | etc

Wat jij aan het doen bent, maar niet echt kan is dit:

code:

category | name | active | description | id (fields) | name | description | id(field_value) | etc
                                                        | field_id 1   |
                                                        | field_id 2   |
                                                        | field_id 3   |
                                                        | field_id 4   |
                                                        | field_id 5   |

En daarom gaat je code op zijn gat


Wat je misschien beter kunt doen is een view maken op basis van:

code:

SELECT * FROM field_value as fv
LEFT JOIN fields as f ON fv.field_id = f.id

Dan kun je vervolgens als je de category weet, daar een query op doen om alle field values te pakken.

[ Voor 12% gewijzigd door Douweegbertje op 13-12-2013 21:12 ]