Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11

Thread: Help with Query

  1. #1
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Query

    I have a table that has Name,PickID,Week.

    I want to run 1 query that will pull into 1 result set the following

    Name, PickID for Week1, PickID for Week2.

    Can anyone help? Thanks,

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    pull the two rows you need and then format them in your front end application if you specifically need them displayed that way.

    what have you tried so far for the query?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    I dunno GuelphDad. From the way he wrote that, I think he wants to get *ALL* the names with their picks from week1 and week2. You could do that via MySQL/PHP, but I suspect it would be "neater" to do it all in SQL
    Code:
    SELECT t1.name, t1.pickid AS week1pick, t2.pickid AS week2pick
    FROM yourtable AS t1, yourtable AS t2
    WHERE t1.name = t2.name
    AND t1.week = 1
    AND t2.week = 2
    ORDER BY t1.name
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried this: I use Alias instead of name. This pull the info I am looking for, but does not put Week 2 in the recordset as an additional column.

    I was trying to avoid programming on the front end application because I thought it would be easier with a mysql query, but maybe not.


    (select m.Alias,m.PickID,m.Week,n.TeamID,n.Name from nflteams as n inner join margin m on m.PickID=n.TeamID where m.Week='1')
    union
    (select m.Alias,m.PickID,m.Week,n.TeamID,n.Name from nflteams as n inner join margin m on m.PickID=n.TeamID where m.Week='2')

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    No, a UNION is *NO HELP* for this situation. Period.

    Why did you not try my query?

    Anyway, you never mentioned that another table was involved.

    But that just means you need to extend the concept of my query.
    Code:
    SELECT m1.Alias,
           m1.Week, n1.TeamID, n1.Name,
           m2.Week, n2.TeamID, n2.Name
    FROM margin AS m1, margin AS m2, nflteams as n1, nflteams AS n2
    WHERE m1.Alias = m2.Alias
    AND m1.week = 1
    AND m1.PickID = n1.TeamID
    AND m2.week = 2
    AND m2.PickID = n2.TeamID
    ORDER BY m1.Alias
    And *PLEASE* don't put apostrophes around NUMBERS! (as you did with m.Week='1')
    MySQL is sloppy enough to allow it in non-strict mode, but it is a BAD HABIT to get into.
    Treat numbers as numbers and strings as strings.

    And there is no reason to SELECT both PickID and TeamID since the conditions of your WHERE clause *guarantee* that they will be the same!
    Last edited by Old Pedant; 09-18-2012 at 10:47 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That worked. Thanks,

    I did try yours and forgot to post the results, sorry. Since I did not state that there were 2 table (sorry, I thought I did) that query did not work.

    I am very new at this so I appreciate your help. I used apostrophes because the field is a string, I did not setup the database for this, just trying to query it.

    Thanks again,

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Whoops...sorry about making an assumption about the data type for week. Bad of me. Not sure why that field needs to be a text field, but since it is you certainly did right.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I really appreciate the help. One more question. I am going to have 17 weeks so is there a more efficient way to do this than what I am doing? Is there a better way to design the tables to make this function better?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    WHOOPS! With 17 weeks. I would *NOT* do this with SQL.

    I would definitely do the consolidations in the PHP code.

    I wouldn't go past 2 or 3 weeks with the above scheme.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, good to know. It buys me some time to work on the PHP portion.

    I was actually thinking that just having a table with Week1, Week2, Week3, etc. might actually work better. Might not be the most efficient in terms of database design, but with only at the most 50 records it would eliminate a lot of code.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    USUALLY it's better not to do that. It eliminates the possibility of making some really interesting queries that can easily be made with your current design.

    Honest, the PHP code to do this is trivial. I don't even use PHP, but I could write it (probably with some PHP syntax errors, but the logic of it is trivial).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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