CODEWITHSUNDEEP
c#asp.netmysqlsqlhtml
G Gr
G Gr

Reputation: 6090

error in mysql syntax?

Problem in my sql syntax I get the error:

ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.5.9]Not unique table/alias: 'WallPosting'

Code:

{

    string theUserId = Session["UserID"].ToString();
    using (OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite2; User=x; Password=x;"))
    {
        cn.Open();
        using (OdbcCommand cmd = new OdbcCommand("SELECT WallPosting.Wallpostings FROM WallPosting LEFT JOIN WallPosting ON User.UserID = WallPosting.UserID WHERE User.UserID=" + theUserId + "", cn))
// problem in select statement

        using (OdbcDataReader reader = cmd.ExecuteReader())
        {
            var divHtml = new System.Text.StringBuilder("<div id=mysqlcontent>");
            while (reader.Read())
            {
                divHtml.Append(String.Format("{0}", reader.GetString(0)));
            }

            divHtml.Append("</div>");
            test1.InnerHtml = divHtml.ToString();

        }
    }
}

My sql script and table structure: enter image description here

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;

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

Upvotes: 0

Views: 315

Answers (3)

Furqan Hameedi
Furqan Hameedi

Reputation: 4400

correct you query 

      SELECT WallPosting.Wallpostings FROM WallPosting LEFT JOIN USER ON User.UserID = WallPosting.UserID WHERE User.UserID=" + theUserId + "", cn

what you did was 

              WallPosting LEFT JOIN WALLPOSTING ON User.UserID = WallPosting.UserID

Upvotes: 1

Stefan Gehrig
Stefan Gehrig

Reputation: 83642

Shouldn't it be

"SELECT WallPosting.Wallpostings FROM WallPosting LEFT JOIN User ON User.UserID = WallPosting.UserID WHERE User.UserID=" + theUserId + ""

You're joining to the WallPosting table again (which would require you to assign aliases) but you should join on the User table instead if you're gonna use columns from the User table.

Upvotes: 1

geekosaur
geekosaur

Reputation: 61449

When you do a self-join (joining a table to itself), you need to give at least one of the table instances an alias so SQL knows which instance you are talking about at any given time. Something like ... LEFT JOIN WallPosting AS a ON ..., then use a.WallPosting when you mean the join table.

Upvotes: 2

Related Questions