help with sql select syntax

Question

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

enter image description here

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:

enter image description here

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;

your images did not loaded. please describe your table structure. — Farzin Zaker
– Farzin Zaker, Commented Apr 2, 2011 at 17:20

ingo · Accepted Answer · 2011-04-02 17:30:04Z

2

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)

edited Apr 2, 2011 at 17:30

answered Apr 2, 2011 at 17:24

ingo

5,5991 gold badge26 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ingo Over a year ago

Note that changing the join on Pictures to a left join would be preferred if not all users have pictures.

Brian Roach · Accepted Answer · 2011-04-02 17:27:41Z

1

SELECT u.firstName, u.secondName, p.picturepath
FROM User as u, Friends as f, Pictures as p
WHERE f.UserID = $sessionID 
AND u.UserID = f.FriendID
AND p.UserId = f.FriendID

answered Apr 2, 2011 at 17:27

Brian Roach

77k12 gold badges140 silver badges164 bronze badges

2 Comments

Brian Roach Over a year ago

You wanted the picturepath ... you need to join the Pictures table as well. The as is optional. It's fairly old school, I just think it reads well.

G Gr Over a year ago

yeah sorry brian your fast at replying once i noticed i deleted my comment, I thought i was seeing double at first

Farzin Zaker · Accepted Answer · 2011-04-02 17:32:06Z

1

do like this :

    SELECT User.*, Pictures.PicturePath
           FROM (User INNER JOIN Friends 
                ON User.UserId = Friends.FriendId) table
                INNER JOIN ON table.UserId = Pictures.UserId
           WHERE Friends.UserId = 1 // this is your id (SessionId)

this will return full information of your friends

edited Apr 2, 2011 at 17:32

answered Apr 2, 2011 at 17:25

Farzin Zaker

3,6043 gold badges27 silver badges35 bronze badges

Comments

KP Taylor · Accepted Answer · 2011-04-02 17:38:34Z

1

If I understand your question and data model correctly, what you are trying to do is to query a many-to-many recursive relationship between users which are connected by being friends to each other (i.e, through the Friends table which acts like the intersecting entity in a many-to-many relationship).

You can do this by joining the User table in your query twice with 2 different aliases, like this:

select 
    u2.UserID,
    u2.FirstName,
    u2.SecondName,
    p.picturepath
from User u1 -- This gets me
join Friends f on u1.UserID = f.UserID -- This gets my friends
join User u2 on f.FriendID = u2.UserID -- This gets my friends info
join Pictures p on p.UserID = u2.UserID -- This gets my friends pics
where u1.UserID = 1  -- ...or whatever; don't actually hardcode "1"!

edited Apr 2, 2011 at 17:38

answered Apr 2, 2011 at 17:23

KP Taylor

2,1001 gold badge17 silver badges15 bronze badges

2 Comments

G Gr Over a year ago

what about picturepath in a different table?

KP Taylor Over a year ago

@Garrith: Sorry, an oversight! Corrected now

famagusta · Accepted Answer · 2011-04-02 17:42:44Z

1

    SELECT x.FirstName,
           x.SecondName,
           x.Email,
           z.picturepath
      FROM ( Friends AS x,
             User AS y
           )
 LEFT JOIN Pictures AS z
        ON x.FriendID = z.UserId
     WHERE x.UserID = 1  # <- this is determined by the session ID
       AND x.FriendID = y.UserID

edited Apr 2, 2011 at 17:42

answered Apr 2, 2011 at 17:37

famagusta

676 bronze badges

Comments

burning_LEGION · Accepted Answer · 2012-07-30 21:25:20Z

0

SELECT 
   u.FirstName, 
   u.SecondName, 
   p.picturepath
FROM User as u 
  INNER JOIN Friends as f
  ON u.UserId = f.FriendId
    INNER JOIN Pictures as p
    ON u.UserId = p.UserId
WHERE f.UserId = 1

edited Jul 30, 2012 at 21:25

burning_LEGION

13.5k8 gold badges42 silver badges52 bronze badges

answered Nov 2, 2011 at 16:11

Bucsa Lucian

1335 bronze badges

Collectives™ on Stack Overflow

help with sql select syntax

6 Answers 6

1 Comment

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related