CODEWITHSUNDEEP
mysql
MMMMS
MMMMS

Reputation: 2201

how to make inner join with where condition

I have tables like below,

CREATE TABLE `CreateEvent_tbl` (
  `Event_Id` varchar(10) NOT NULL,
  `State` varchar(25) DEFAULT NULL,
  `District` varchar(35) DEFAULT NULL,
  `School` varchar(150) DEFAULT NULL,
  `Event_Date` date DEFAULT NULL,
  `Created_By` varchar(35) DEFAULT NULL,
  `Created_Date_Time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`Event_Id`)



CREATE TABLE `StudentDetails_tbl` (
  `Student_Id` varchar(15) NOT NULL,
  `Event_Id` varchar(10) DEFAULT NULL,
  `Student_Name` varchar(45) DEFAULT NULL,
  `Parents_Name` varchar(90) DEFAULT NULL,
  `Std_Ph_No` varchar(25) DEFAULT NULL,
  `Ph_No_1` varchar(25) DEFAULT NULL,
  `Ph_No_2` varchar(25) DEFAULT NULL,
  `Email_Id` varchar(50) DEFAULT NULL,
  `Address` varchar(250) DEFAULT NULL,
  `Created_By` varchar(25) DEFAULT NULL,
  `Created_Date_Time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`Student_Id`)



 CREATE TABLE `BuyerDetails_tbl` (
  `Event_Id` varchar(10) DEFAULT NULL,
  `Student_Id` varchar(15) DEFAULT NULL,
  `Call_Buy_Id` varchar(10) DEFAULT NULL,
  `Buyer_Id` varchar(10) NOT NULL,
  `Purchased_Date` date DEFAULT NULL,
  `No_Of_Reference` int(11) DEFAULT NULL,
  `Created_By` varchar(45) DEFAULT NULL,
  `Created_Date_Time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`Buyer_Id`)

My query is :

      select CreateEvent_tbl.Event_Id,CreateEvent_tbl.State,StudentDetails_tbl.Student_Id,StudentDetails_tbl.Student_Name,BuyerDetails_tbl.Purchased_Date
 from CreateEvent_tbl
     inner join  BuyerDetails_tbl on CreateEvent_tbl.Event_Id=BuyerDetails_tbl.Event_Id
     inner join StudentDetails_tbl  on StudentDetails_tbl.Student_Id=BuyerDetails_tbl.Student_Id
     where BuyerDetails_tbl.Buyer_Id="B045";

when i search by Buyer_Id from BuyerDetails_tbl, display StudentDetails and EventDetails from StudentDetails_tbl and CreateEvent_tbl where Student_Id and Event_Id in BuyerDetails_tbl.

But above query not working,shows nothing.

where i am wrong?

Note : i am new for joining queries.

Upvotes: 0

Views: 53

Answers (1)

DRapp
DRapp

Reputation: 48139

Since you are looking for a specific buyer, I would start with THAT table as the first in the list, then join to the other two. Also, to shorten readability of the query, notice the "aliasing" of the table names (via "ce", "sd" and "bd" ) vs the long table names for all the column names and joins.

select 
      ce.Event_Id,
      ce.State,
      sd.Student_Id,
      sd.Student_Name,
      bd.Purchased_Date
   from 
      BuyerDetails_tbl bd
         inner join CreateEvent_tbl ce
            on bd.Event_Id = ce.Event_Id
         inner join StudentDetails_tbl sd 
            on bd.Student_Id = sd.Student_Id
   where 
      bd.Buyer_Id = "B045";

I would ensure you have proper indexes based on the query criteria and join conditions..

BuyerDetails_tbl -- index on (buyer_id) to optimize your WHERE criteria.

Upvotes: 1

Related Questions