Nested Select Statement MySQL ASP.net Application

Databases
1

Hey everyone, I need help creating a nested Select statement in MySQL. Currently I have a query that uses a couple inner joins to return a result set. The query and results are as follows;

SELECT DISTINCT NoAdmit.InquiryID_fk, Patient.Name, Inquiry.DecisionMade, Inquiry.Date, Inquirer.Name, Inquirer.Program from Patient INNER JOIN (Inquiry INNER JOIN NoAdmit on NoAdmit.InquiryID_fk = Inquiry.ID Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk) on
 Inquiry.PatientID_fk = Patient.ID
InquiryID_FK     Name      DecisionMade     Date               Name            Program
1               Nick         Do Not Admit      1/2/2012       John Doe        Program1

Since this query is used in an SQLDataSource I would like to append to it to also include this query;

SELECT * from DecAdmit Where DecAdmit.ActualAdmitDate > GetDate()
Basically saying, that if the ActualAdmitDate field has a date value that is later than todays date that include that in the resultset. I am just unable how to add this as a nested Select statement or another Join.

The join could be used on Inquiry.ID = DecAdmit.InquiryID_fk

Any help would be appreciated.

Thank you,

Nick

2

your post says you’re using mysql, but GetDate() is a microsoft sqlserver function

SELECT Inquiry.ID
     , Patient.Name
     , Inquiry.DecisionMade
     , Inquiry.Date
     , Inquirer.Name
     , Inquirer.Program 
     , DecAdmit.*
  FROM Inquiry 
INNER
  JOIN Patient 
    ON Patient.ID = Inquiry.PatientID_fk
INNER 
  JOIN NoAdmit 
    ON NoAdmit.InquiryID_fk = Inquiry.ID 
INNER 
  JOIN Inquirer 
    ON Inquirer.ID = Inquiry.InquirerID_fk
LEFT OUTER
  JOIN DecAdmit
    ON DecAdmit.InquiryID_fk = Inquiry.ID
   AND DecAdmit.ActualAdmitDate > CURRENT_TIMESTAMP