帮助选择sql语法 [英] help with sql select syntax
问题描述
我很困惑，非常遗憾。
了解如何使FriendID与其关联的选择语句UserID
>
正如你可以看到FriendID涉及到我的用户表的UserID，所以如果FriendID = 2，那么显示UserID 2的细节。 b
$ b
即时消息试图做的是如果即时消息用户ID 1我想显示所有我的朋友，我的用户ID由会话设置，
我需要做的是找出我的朋友是谁这样从朋友中选择FriendID，其中SessionID = UserID，然后在User表中找出friendsID属于谁，并显示他的名字，第二名和他的图片路径。
非常令人困惑...
我的表结构如下所示：
p>
因此，在我的选择命令中总结一下我需要的内容：
要找出哪些朋友与（currentsession UserID具有FriendID's）
显示与其自己的用户ID有关的friendID的名字，姓氏，图片路径。例如：
（p）$（
） userID = 1）FriendID 2 = UserID 2，所以选择用户的第一名，第二名，其中UserID = 2
我可能与我的表结构有关系问题不确定。 / p>
我希望你能理解我的疑惑：
编辑我的数据库的图像
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`gymwebsite2` DEFINULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE`gymwebsite2`;
- -------------------------------- ---------------------
- 表`gymwebsite2`.`User`
- - -------------------------------------------------- -
CREATE TABLE if NOT EXISTS`gymwebsite2`.`User`（
`UserID` INT NOT NULL AUTO_INCREMENT，
`Email` VARCHAR（245）NULL，
`FirstName` VARCHAR（45）NULL，
`SecondName` VARCHAR（45）NULL，
`DOB `VARCHAR（45）NULL，
`Location` VARCHAR（45）NULL，
`Aboutme` VARCHAR（1045）NULL，
` VARCHAR（45）NULL，
`password` VARCHAR（45）NULL，
PRIMARY KEY（`UserID`））
ENGINE = InnoDB;
- ------------------------ -----------------------------
- 表`gymwebsite2`.`WallPosting`
- ------------------------------------------- ----------
CREATE TABLE if NOT EXISTS`gymwebsite2`.`WallPosting`（
`idWallPosting` INT NOT NULL AUTO_INCREMENT，
`UserID` INT NOT NULL，
`Wallpostings` VARCHAR（2045）NULL，
PRIMARY KEY（`idWallPosting`），
INDEX``fk_WallPosting_User`（`UserID` ASC），
CONSTRAINT`fk_WallPosting_User`
FOREIGN KEY（`UserID`）
参考文献``user``（`UserID`）
在删除不行动
在更新不行动）
ENGINE = InnoDB;
- ------------------------ -----------------------------
- 表`gymwebsite2```Pictures`
- ------------------------------------------- ----------
CREATE TABLE if NOT EXISTS`gymwebsite2`.`Pictures`（
`idPictures` INT NOT NULL AUTO_INCREMENT，
`UserID` INT不为NULL，
`picturepath` VARCHAR（1045）NULL，
PRIMARY KEY（`idPictures`），
INDEX`fk_Pictures_User1`（`UserID` ASC），
CONSTRAINT`fk_Pictures_User1`
FOREIGN KEY（`UserID`）
参考文献``user``（`UserID`）
在删除不行动
在更新不行动）
ENGINE = InnoDB;
- ------------------------ -----------------------------
- 表gymwebsite2`.`Friends`
- ------------------------------------------- ----------
CREATE TABLE if NOT EXISTS`gymwebsite2`.`Friends`（
`idFriends` INT NOT NULL AUTO_INCREMENT，
`UserID` INT NOT NULL，
`FriendID` INT NOT NULL，
PRIMARY KEY（`idFriends`），
INDEX`fk_Friends_User2`（`FriendID` ASC），
INDEX`fk_Friends_User1`（`UserID` ASC），
UNIQUE INDEX`FriendID_UNIQUE`（`FriendID` ASC） ，
CONSTRAINT`fk_Friends_User2`
FOREIGN KEY（`FriendID`）
参考`gymwebsite2`.`User`（`UserID`）
ON删除无操作
ON UPDATE NO ACTION，
CONSTRAINT`fk_Friends_User1`
FOREIGN KEY（`UserID` ）
REFERENCES`gymwebsite2`.`User`（`UserID`）
ON DELETE NO A CTION
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;
select u .FirstName，u.SecondName，p.picturePath
来自用户u
加入好友f
对f.FriendId = u.UserId
加入图片p
在p。 UserId = u.UserId
其中f.UserId = SessionId（< - sessionId是你的id）
Here he comes to confuse his day! (should be my handle from now on)
Im confused and very much lost.
I need to figure out how to make a select statement for getting FriendID to relate to there UserID
As you can see FriendID relates to the UserID of my User table, so if FriendID = 2 then display UserID 2 details.
What im trying to do is if im UserID 1 I want to display all my friends, my userid is set by a session,
What I need to do is find out who my friends are so Select FriendID from freinds where SessionID = UserID then some how figure out who friendsID belongs to in the User table and display his firstname, secondname and his picturepath.
Very confusing...
My table structure looks like this:
So to sum it up what I need in my select command:
To find out who im friends with (currentsession UserID has FriendID's) Display FirstName, SecondName, picturepath of friendID related to his own UserID
Example:
I (userID=1) have FriendID 2 = UserID 2 so select firstname, secondname from User where UserID=2
I could have relational problems with my table structure tho not sure.
I hope u can understand my confusion:
EDIT for those that cant see the images of my db
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 `gymwebsite2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `gymwebsite2` ;
-- -----------------------------------------------------
-- Table `gymwebsite2`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gymwebsite2`.`User` (
`UserID` INT NOT NULL AUTO_INCREMENT ,
`Email` VARCHAR(245) NULL ,
`FirstName` VARCHAR(45) NULL ,
`SecondName` VARCHAR(45) NULL ,
`DOB` VARCHAR(45) NULL ,
`Location` VARCHAR(45) NULL ,
`Aboutme` VARCHAR(1045) NULL ,
`username` VARCHAR(45) NULL ,
`password` VARCHAR(45) NULL ,
PRIMARY KEY (`UserID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gymwebsite2`.`WallPosting`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gymwebsite2`.`WallPosting` (
`idWallPosting` INT NOT NULL AUTO_INCREMENT ,
`UserID` INT NOT NULL ,
`Wallpostings` VARCHAR(2045) NULL ,
PRIMARY KEY (`idWallPosting`) ,
INDEX `fk_WallPosting_User` (`UserID` ASC) ,
CONSTRAINT `fk_WallPosting_User`
FOREIGN KEY (`UserID` )
REFERENCES `gymwebsite2`.`User` (`UserID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gymwebsite2`.`Pictures`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gymwebsite2`.`Pictures` (
`idPictures` INT NOT NULL AUTO_INCREMENT ,
`UserID` INT NOT NULL ,
`picturepath` VARCHAR(1045) NULL ,
PRIMARY KEY (`idPictures`) ,
INDEX `fk_Pictures_User1` (`UserID` ASC) ,
CONSTRAINT `fk_Pictures_User1`
FOREIGN KEY (`UserID` )
REFERENCES `gymwebsite2`.`User` (`UserID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gymwebsite2`.`Friends`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gymwebsite2`.`Friends` (
`idFriends` INT NOT NULL AUTO_INCREMENT ,
`UserID` INT NOT NULL ,
`FriendID` INT NOT NULL ,
PRIMARY KEY (`idFriends`) ,
INDEX `fk_Friends_User2` (`FriendID` ASC) ,
INDEX `fk_Friends_User1` (`UserID` ASC) ,
UNIQUE INDEX `FriendID_UNIQUE` (`FriendID` ASC) ,
CONSTRAINT `fk_Friends_User2`
FOREIGN KEY (`FriendID` )
REFERENCES `gymwebsite2`.`User` (`UserID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Friends_User1`
FOREIGN KEY (`UserID` )
REFERENCES `gymwebsite2`.`User` (`UserID` )
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;
select u.FirstName, u.SecondName, p.picturePath
from User u
join Friends f
on f.FriendId = u.UserId
join Pictures p
on p.UserId = u.UserId
where f.UserId = SessionId ( <-- sessionId is your id)
这篇关于帮助选择sql语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
