Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    Mar 2004
    Posts
    17
    Member #
    5091
    I'm having trouble with a (simple?) concept in MySQL join. This query gives me the correct data from all 3 tables, but I need to sort by the date from 2 of the tables:

    SELECT det.CalendarDetailsID, dai.StartDate, wee.DisplayStart, det.Title, det.Details
    FROM phpCalendar_Details det
    LEFT JOIN phpCalendar_Daily dai ON det.CalendarDetailsID = dai.CalendarDetailsID
    LEFT JOIN phpCalendar_Weekly wee ON det.CalendarDetailsID = wee.CalendarDetailsID

    Is there a way I combine dai.StartDate and wee.DisplayStart dates fields into a new (alias?) field that I can sort with?

    Or is there a way that I can simply sort the two fields within each other without combining them?

    I've done a lot of reading and can't figure this out. Any help will be greatly appreciated. Thank you.

  2.  

  3. #2
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    The string combination of the two? What do you mean by "combine" ?
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  4. #3
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    You can't join two different columns. If they however, contain the same info, you need only select one (or none) of them. The columns can still be processed, even though they are not selected.

    Ex:
    Two tables fruits and country.

    fruits:
    id | type | color
    1 | banana | yellow
    2 | apple | red or green
    3 | orange | orange
    4 | grape | red or green

    country:
    id | fruit_id | countryName
    1 | 2 | norway
    2 | 2 | sweden
    3 | 1 | costa-rica
    4 | 1 | brazil

    Query:
    SELECT c.countryName AS Country, f.type AS Fruit FROM fruits AS f INNER JOIN country AS c ON c.fruit_id=f.id ORDER BY Country

    This query will return:
    Country | Fruit
    brazil | banana
    costa-rica | banana
    norway | apple
    sweden | apple

    Notice that even though fruits.id and country.fruit_id are used to join the two tables, they are not selected. What you select, is what you want to display. You don't need to select columns you have no intention of displaying!

    In the above example, I've sorted on Country (which, as you can see, is an alias). You could also sort on a column you didn't select! That doesn't matter. The selected columns are just to tell MySQL which columns you want returned after the join/order-by and what else, have done their job.

    In case you didn't know how to rename a selected column, I've used the AS syntax to show you, both on the tablenames themselves (to make the query less verbose), and on the selected columns to make them display a shorter more descriptive name.
    I've highlighted the new aliases in red, and the SQL syntax in blue.
    S. Rosland

  5. #4
    Junior Member
    Join Date
    Mar 2004
    Posts
    17
    Member #
    5091
    I understand your explanation. But when I'm dealing with the three tables, it keeps the results from phpCalendar_Daily and phpCalendar_Weekly separate. I need the results from both tables to be listed together, ordered by their date fields (dai.StartDate and wee.DisplayStart).

    Right now, when I use my query, I get:

    CalendarDetailsID | StartDate | DisplyDate | Title | Details
    60 | 04-25-2005 | NULL | Yoga Class | 7 p.m. at...
    61 | 04-27-2005 | NULL | Heart Health | 7:30 p.m. at...
    62 | 04-30-2005 | NULL | Aerobics | 4 p.m. at...
    63 | NULL | 04-26-2005 | Eating Right | 5 p.m. at...

    I need:

    CalendarDetailsID | StartDate | DisplyDate | Title | Details
    60 | 04-25-2005 | NULL | Yoga Class | 7 p.m. at...
    63 | NULL | 04-26-2005 | Eating Right | 5 p.m. at...
    61 | 04-27-2005 | NULL | Heart Health | 7:30 p.m. at...
    62 | 04-30-2005 | NULL | Aerobics | 4 p.m. at...

    - or -

    CalendarDetailsID | NEW DATE COMBINED FIELD/ALIAS? | Title | Details
    60 | 04-25-2005 | Yoga Class | 7 p.m. at...
    63 | 04-26-2005 | Eating Right | 5 p.m. at...
    61 | 04-27-2005 | Heart Health | 7:30 p.m. at...
    62 | 04-30-2005 | Aerobics | 4 p.m. at...

    Thank you for your help!

  6. #5
    Senior Member visualAd's Avatar
    Join Date
    Jan 2003
    Location
    Slough, UK
    Posts
    201
    Member #
    434
    Yes all you need is an IF statement in your query. If the start date field is NULL you want to use the dispaly date instead right? This should do it:
    Code:
    SELECT det.CalendarDetailsID, 
           dai.StartDate, 
           wee.DisplayStart, 
           det.Title, 
           det.Details, 
           IF(dai.StartDate IS NULL, wee.DisplayStart, dai.StartDate) combined
    
        FROM phpCalendar_Details det
    
        LEFT JOIN phpCalendar_Daily dai ON det.CalendarDetailsID = dai.CalendarDetailsID
    
        LEFT JOIN phpCalendar_Weekly wee ON det.CalendarDetailsID = wee.CalendarDetailsID
        
        ORDER BY combiend ASC;


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:27 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com