Help with Query

mjstehn · 09-17-2012, 02:56 AM

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,

guelphdad · 09-17-2012, 05:58 AM

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?

Old Pedant · 09-17-2012, 06:09 AM

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

mjstehn · 09-17-2012, 11:56 PM

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')

Old Pedant · 09-18-2012, 10:45 PM

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!

mjstehn · 09-19-2012, 12:07 AM

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,

Old Pedant · 09-19-2012, 12:32 AM

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.

mjstehn · 09-19-2012, 05:28 AM

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?

Old Pedant · 09-19-2012, 06:05 AM

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.

mjstehn · 09-19-2012, 06:19 AM

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.

Old Pedant · 09-19-2012, 11:48 PM

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).