SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Mysql-Query

  1. #1
    SitePoint Member
    Join Date
    Sep 2003
    Location
    Bonn
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql-Query

    Hey,
    I am using a MYSQL-database and got following question:

    There are following tables:

    person (Table)
    -------------
    Fields:
    person_id
    person_name
    -------------

    not_available (Table)
    -------------
    not_available _id
    not_available _person_id
    not_available _start_date
    not_available _end_date
    -------------

    Now I got two dates:
    project_start_date and project_end_date

    I want a query to find all persons,
    who are available over the whole time
    between project_start_date and project_and_date,
    and who are NOT not_available at least one time
    in this time range.

    For example:
    project_start_date 10.10
    project_end_date 30.10.

    All persons who are not_available in this time range
    shall not be shown.
    I just want to see all persons who are available in between
    project_start_date and project_end_date.

    I need the Mysql_Query to find all persons who
    are available between 10.10 and 30.10.

    Thank you,

    Ashton

  2. #2
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EDIT: Scrub that, it doesn't work.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Gent, Belgium, Europe, World, Univers
    Posts
    397
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have the newest MySQL version ? i.e. does it allow subqueries ?

    If that is the case ...

    select person_id, person_name from person where person_id not in
    (select not_available_person_id from not_available
    where not_available_start_date < project_end_date
    and not_available_end_date > project_start_date)

    This one sould do it I think.
    Last edited by kajakske; Sep 30, 2003 at 05:19.

  4. #4
    SitePoint Addict
    Join Date
    Dec 2002
    Location
    Gent, Belgium, Europe, World, Univers
    Posts
    397
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Untested:
    select person_id, person_name from
    person left outer join not_available
    on person_id = not_available_person_id
    where not_available_start_date < project_end_date
    and not_available_end_date > project_start_date
    and not_available_id is null

    Without the subqueries ...

  5. #5
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is driving me nuts!

    This query will return a 2 column result. One column is the name, the other is a number that will be 0 of the person can do the project, and a positive integer if they are not available.

    Code:
    SELECT
    	person.name ,
    	SUM (
    		IF ( not_available.start_date BETWEEN '2003-10-10' AND '2003-10-30' , 1 , 0 )
    	) +
    	SUM (
    		IF ( not_available.end_date BETWEEN '2003-10-10' AND '2003-10-30' , 1 ,	0 )
    	) AS available
    FROM
    	person
    LEFT OUTER JOIN
    	not_available
    	USING ( person_id )
    GROUP BY
    	person.person_id
    But as we cannot use aggregate functions in the where clause, I can't filter on the availability.

    I think we need MattR, rudy, or one of the other SQL gurus to help in this one.

    Matt.

  6. #6
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kajakske
    Untested:
    select person_id, person_name from
    person left outer join not_available
    on person_id = not_available_person_id
    where not_available_start_date < project_end_date
    and not_available_end_date > project_start_date
    and not_available_id is null

    Without the subqueries ...
    BTW, this approach doesn't work because it doesn't account for people that have bookings both in your specifid time frame, and outside it.

    Matt.


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
  •