#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0

    Displaying query if date has a value or is null


    Hi,

    Im having problems trying to run a query it will only show the results where the job.start_date has a value and I want to to display all of them even if it's null

    Currently its only showing ones that have a value and when I add job.start_date is NULL nothing comes up so im at a loss.

    Here is my current query, yes I know its not very advanced

    Code:
    SELECT job.ID AS jobid
    , job.job_status_id
    , job.start_date AS startdate
    , job.finish_date AS finishdate
    , job.location AS location
    , job.bore_size AS boresize
    , job.length as length
    , job.current_length AS cmeters
    , job.driller_id
    , job_status.status AS jobstatus
    , users.name AS driller
    FROM job, job_status, users, contractor
    WHERE job.driller_id = users.id
    AND job.contractor_id = contractor.ID
    AND job.job_status_id = job_status.ID
    Any help would be appreciated
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by Stika
    ... ... I want to to display all of them even if it's null
    your query as posted does exactly that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    your query as posted does exactly that
    That's what I thought but it will only show results it the job.start_date had a value in it. If I put a where job.start_date is null nothing comes up when 2 results should. And without it im only getting 1 result instead of 3
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by Stika
    That's what I thought but it will only show results it the job.start_date had a value in it.
    yeah, but i just don't believe that

    try running your query outside of php -- i'm guessing it's your display logic that's at fault
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    another possibility is that jobs without a start date don't have a contractor or driller yet

    when you use inner joins like you did, jobs that don't have a matching row in the other three tables get dropped

    such is the nature of inner joins, my friend
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    another possibility is that jobs without a start date don't have a contractor or driller yet
    Now that you mention it yes they are blank... I can fill in the contractor field but the driller isnt usually know until not long before the job starts.

    Is there any way I can fix it?
  12. #7
  13. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,079
    Rep Power
    4101
    Use proper joins and make the driller join a LEFT JOIN.

    Code:
    SELECT 
    	job.ID AS jobid
    	, job.job_status_id
    	, job.start_date AS startdate
    	, job.finish_date AS finishdate
    	, job.location AS location
    	, job.bore_size AS boresize
    	, job.length as length
    	, job.current_length AS cmeters
    	, job.driller_id
    	, job_status.status AS jobstatus
    	, users.name AS driller
    FROM job
    INNER JOIN job_status ON job_status.ID = job.job_status_id
    INNER JOIN contractor ON contractor.ID=job.contractor_id
    LEFT JOIN users ON job.driller_id=users.id
    By using a LEFT JOIN if no rows match then those columns are just NULL. An INNER JOIN (which is what your current method effectively is) will completely filter out results where there is no matching row.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by Stika
    I can fill in the contractor field but the driller isnt usually know until not long before the job starts.
    like kicken says, make the optional joins LEFT OUTER

    looking more closely at your query, it seems you aren't using any contractor info, so why join to it at all?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Yes after some sleep I realized what I did need and didn't need.
    Kickers code worked, I just need to wrap my head around joins

    Thanks for your help
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    I was wondering if I can only display results where the driller_id/users_id is the same as currently logged in user?
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    disregard....figured it out

IMN logo majestic logo threadwatch logo seochat tools logo