kleine sql abfrage Frage

[Mark110]

Hallo,

ich möchte die tabelle teams_user gerne ausgeben mit dem namen des users und dem teamnamen. Wie muss ein solches statement lauten?
Hoffe bin hier richtig ansonsten bitte verschieben.


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';

CREATE SCHEMA IF NOT EXISTS `ls` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `ls`;

-- -----------------------------------------------------
-- Table `ls`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`user` (
`user_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`vorname` VARCHAR(45) NOT NULL ,
`nachname` VARCHAR(45) NOT NULL ,
`alter` SMALLINT NOT NULL ,
`email` VARCHAR(45) NOT NULL ,
`registriert` BOOLEAN NOT NULL ,
PRIMARY KEY (`user_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ls`.`teams`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`teams` (
`team_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`gruendung` VARCHAR(45) NOT NULL ,
`punkte` SMALLINT NOT NULL ,
PRIMARY KEY (`team_id`) )
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `ls`.`matches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`matches` (
`match_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`team_id_a` SMALLINT UNSIGNED NOT NULL ,
`team_id_b` SMALLINT UNSIGNED NOT NULL ,
`ergebnisa` SMALLINT NOT NULL ,
`ergebnisb` SMALLINT NOT NULL ,
PRIMARY KEY (`match_id`) ,
INDEX teams_team_id_fk1 (`team_id_a` ASC) ,
INDEX teams_team_id_fk2 (`team_id_b` ASC) ,
CONSTRAINT `teams_team_id_fk1`
FOREIGN KEY (`team_id_a` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `teams_team_id_fk2`
FOREIGN KEY (`team_id_b` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `ls`.`teams_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`teams_user` (
`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`team_id` SMALLINT UNSIGNED NOT NULL ,
`user_id` SMALLINT UNSIGNED NOT NULL ,
INDEX teams_team_id_fk (`team_id` ASC) ,
INDEX user_user_id_fk (`user_id` ASC) ,
PRIMARY KEY (`id`) ,
CONSTRAINT `teams_team_id_fk`
FOREIGN KEY (`team_id` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `user_user_id_fk`
FOREIGN KEY (`user_id` )
REFERENCES `ls`.`user` (`user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `ls`.`ligen`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`ligen` (
`liga_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(50) NOT NULL ,
`beschreibung` TINYBLOB NULL ,
PRIMARY KEY (`liga_id`) )
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `ls`.`teams_ligen`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`teams_ligen` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`team_id` SMALLINT UNSIGNED NOT NULL ,
`liga_id` SMALLINT UNSIGNED NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX team_team_id_fk (`team_id` ASC) ,
INDEX ligen_liga_id_fk (`liga_id` ASC) ,
CONSTRAINT `team_team_id_fk`
FOREIGN KEY (`team_id` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `ligen_liga_id_fk`
FOREIGN KEY (`liga_id` )
REFERENCES `ls`.`ligen` (`liga_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `ls`.`spieltage`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`spieltage` (
`spieltags_id` SMALLINT NOT NULL ,
`team_id_a` SMALLINT NOT NULL ,
`team_id_b` SMALLINT NOT NULL ,
PRIMARY KEY (`spieltags_id`) ,
INDEX team_team_id_1_fk (`team_id_a` ASC) ,
INDEX team_team_id_2_fk (`team_id_b` ASC) ,
CONSTRAINT `team_team_id_1_fk`
FOREIGN KEY (`team_id_a` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `team_team_id_2_fk`
FOREIGN KEY (`team_id_b` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = MyISAM;



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

 

[masta // thomas]

SELECT
	tu.*, 
	u.vorname, 
	u.nachname, 
	t.name 
FROM
	team_user tu,
	user u,
	team t
WHERE 	
	tu.user_id = u.user_id
AND
	tu.team_id = t.team_id

alternativ

SELECT 
	tu.*, 
	u.vorname, 
	u.nachname, 
	t.name 
FROM 
	team_user tu
LEFT JOIN 
	user u 
ON 
	tu.user_id = u.user_id 
LEFT JOIN
	teams t 
ON 
	tu.team_id = t.team_id

 

[Mark110]

vielen dank probier ich sofort aus

 

[Mark110]

also die abfrage war genau das was ich gesucht habe.
Vielen Dank!


Nun wollte ich einen schritt weiter gehen und die on delete funtkion aufrufen.

-- -----------------------------------------------------
-- Table `ls`.`teams_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ls`.`teams_user` (
`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`team_id` SMALLINT UNSIGNED NOT NULL ,
`user_id` SMALLINT UNSIGNED NOT NULL ,
INDEX teams_team_id_fk (`team_id` ASC) ,
INDEX user_user_id_fk (`user_id` ASC) ,
PRIMARY KEY (`id`) ,
CONSTRAINT `teams_team_id_fk`
FOREIGN KEY (`team_id` )
REFERENCES `ls`.`teams` (`team_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `user_user_id_fk`
FOREIGN KEY (`user_id` )
REFERENCES `ls`.`user` (`user_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = MyISAM;


Aber egal ob ich aus teams_user oder aus teams bzw user einen datensatz lösche, so wird der referenzierte nicht mtigelöscht.

Mache ich etwas falsch? oder ist ein dnekfehler meiner seits?

wenn enin, kann mir vielleicht jemand sagen, was in diesem fall passieren sollte.

 

[Gast]

Was für eine Datenbank hast du den drunter liegen?

Wenn es MySQL ist, wird soweit ich weiß (kann überholt sein) FOREIGN KEY zwar angeboten, aber nicht wirklich unterstützt.

 

[Mark110]

ich bentuze mysql mit der innodb engine foreign keys werden unterstützt.

ob da smit dem on delete auch dazu gehört... vielleicht weiss da einer mehr?

Edit: muss unterstützt werden.. hab ich ja mit der mysql workbench erstellt... also warum klappt e snicht

achso nutze mysql server version 5