SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested Select Statement MySQL ASP.net Application

    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;

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your post says you're using mysql, but GetDate() is a microsoft sqlserver function
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •