@ Stein maybe you can do that in mysql, but in ms sql you can't group on u.id and select on u.name. hence the 2nd subquery

[Edited on December 7, 2010 at 4:29 PM. Reason : i really don't know. out of convenience i tested mine on mssql not mysql]

12/7/2010 4:27:43 PM

yeah, would do the sort and order, the LIMIT 1 and pull off the top result for each member.

12/7/2010 4:52:37 PM

btw you wouldn't have this problem if you'd implemented the bitwise security scheme i recommended in july 2009

12/7/2010 4:58:28 PM

select
usr."name",
typ."type"
from [user] usr
JOIN [relationship] rel ON rel.user_id = usr.id
JOIN [type] typ ON rel.type_id = typ."level"
where rel.type_id in
(select T.type_id from (select row_number() over (order by type_id asc) as Rno, rel2.type_id from [relationship] rel2 where rel2.user_id = rel.user_id) T where T.Rno = 1)

this works. hope this helps!

[Edited on December 7, 2010 at 5:49 PM. Reason : works for mssql....not sure if that's what you're using...I'm not THAT bored to read the whole convo]

12/7/2010 5:43:56 PM

Quote :

"@ Stein maybe you can do that in mysql, but in ms sql you can't group on u.id and select on u.name. hence the 2nd subquery"

Gotcha. You can do it in MySQL

My tampering with MS SQL is just going from MS SQL -> MySQL in Powershell, so it's about the most awkward real world experience there is.

12/7/2010 6:56:04 PM

Thank you for all of the help! It seems like all of the queries work, but I forgot to mention that I am trying to display the list of people in order of their type priority. Does that make sense? For example, the managers will be listed at the top and then each lesser priority level will be displayed.

12/7/2010 9:51:25 PM

ORDER BY type...assuming you listed the actual data values

12/8/2010 12:34:26 AM

imago log in as

' or '1'='1

12/8/2010 1:38:45 AM

Quote :

" select (select uu.name from [user] uu where uu.id=u.id) , (select tt.type from type tt where tt.level = MIN(t.level) limit 1) from [user] u join relationship r on r.user_id = u.id join type t on t.id = r.type_id group by u.id order by t.priority desc "

This query successfully executes, but the results aren't right. The tables I gave on the other page are just examples - the user table has many more rows and the table is more complex because there are more columns.

When I run the query, the names are mismatched with the type name. Some people who aren't managers are getting a manager designation and some designations aren't showing up at all. In this case, I didn't change the query except for the limit 1 and order by t.priority desc additions.

Also, when I try to pull more than one bit of information from the first subquery - let's say I want to get the user's unique ID in addition to their name, using select uu.id, uu.name - it fails with this error:

Quote :

"Operand should contain 1 column(s)"

Thoughts?

[Edited on December 8, 2010 at 7:37 AM. Reason : Full query]

12/8/2010 7:34:28 AM

Quote :

"I didn't change the query except for the limit 1 and order by t.priority desc additions."

'ORDER BY priority' won't work, because again just because there's a MIN() in there, it doesn't mean that it's selecting the right row.

Quote :

"Also, when I try to pull more than one bit of information from the first subquery - let's say I want to get the user's unique ID in addition to their name, using select uu.id, uu.name - it fails with this error:"

Subqueries can return one column. In some situations they can return multiple rows, but only one column.

12/8/2010 8:43:36 AM

Quote :

"'ORDER BY priority' won't work, because again just because there's a MIN() in there, it doesn't mean that it's selecting the right row."

But it did work. Whether I put in the order by operand or not, the person and their type are mismatched. Putting in the operand does organize them. At least, it looks like it does. I obviously don't have a strong understanding of how this is working.

Quote :

"Subqueries can return one column. In some situations they can return multiple rows, but only one column."

If I add u.id to your query, it will pull up the user's ID just fine. I only get the error when using the query provided by qntmfred. Which makes sense according to your explanation.

Any thoughts as to why the users are being incorrectly paired with their type?

12/8/2010 9:05:40 AM

Here is your working query:

SELECT u.id, u.name, (SELECT tt.type FROM tww_type tt WHERE tt.level=MIN(t.level) LIMIT 1)  FROM tww_type t
JOIN tww_relationship r ON (t.id=r.type_id)
JOIN tww_users u ON (r.user_id=u.id)
GROUP BY u.id ORDER BY MIN(t.level), name;

12/8/2010 9:07:29 AM

That query executes successfully, but the names and types are still mismatched for some reason.

I know this because there is one type for the chairman and there is only one person with that designation. I have double-checked this in the relationship table just to make sure. A different person is coming up with the chairman type.

Also, not all of the different types are represented. There are some other types - supplemental staff, for example - and there are no results with that type, even though they are definitely in the relationship table.

12/8/2010 9:17:05 AM

At the beginning of the query (after the SELECT, before u.id), put GROUP_CONCAT(t.level).

That value will tell you what levels the user should have. See if that explains things a bit more.

[Edited on December 8, 2010 at 9:24 AM. Reason : Also note in my earlier query atype only shows up if the user has that level as their minimum level.]

12/8/2010 9:22:59 AM

Adding GROUP CONCAT gives me a column with values of [BLOB - 1 B] or [BLOB - 3 B] or [BLOB - 5 B].

I have no idea what that means.

12/8/2010 9:29:17 AM

What are you running your queries in? Just command line or in some sort of query browser?

Try: CAST(GROUP_CONCAT(t.level) AS CHAR) instead of just GROUP_CONCAT()

[Edited on December 8, 2010 at 9:33 AM. Reason : .]

12/8/2010 9:31:18 AM

Quote :

"What are you running your queries in? Just command line or in some sort of query browser?"

phpMyAdmin on DreamHost. I believe it's MySQL 5, if that makes any difference.

Quote :

"Try: CAST(GROUP_CONCAT(t.level) AS CHAR) instead of just GROUP_CONCAT()"

Now the column displays a number or several numbers separated by commas, but I don't know what that number means. In the case above regarding the chairman type, the number in this new column is "4". The chairman type in the database has an ID of "12" and this person that is matched to it in the result does not match either a "4" or a "12" (their user ID is paired with a "7" type in the relationship table).

12/8/2010 9:50:34 AM

That column will show all the levels that a user is tied to. So that if a person shows 1,3,4 it means they're a member the ranking at level 1, level 3, and level 4. Therefore the row should show the User's ID, the User's Name, and the Name of the Type at Level 1.

I guess the question is: when looking at your type table, do multiple ids have the same level. That would change the query and explain why you're seeing incorrect information.

12/8/2010 10:19:03 AM

probably overthinking a problem relevant to like 200 records y'all

12/8/2010 10:34:26 AM

^ i realize that being a dick is your natural state, but what difference does the number of records make? you're not exactly contributing anything of value, so what do you care if other people give it a shot?

12/8/2010 10:52:05 AM

Quote :

"denormalize the data by storing the max title on the user table and create an index to match"

Quote :

"btw you wouldn't have this problem if you'd implemented the bitwise security scheme i recommended in july 2009"

who doesn't contribute

12/8/2010 10:57:45 AM

Quote :

"who doesn't contribute"

i didn't say you don't contribute, i said you don't contribute anything OF VALUE

saying "here's what you should have done" (who knows if this is even the same thing) and "you should change your tables to be what i think they should be" (who knows if he has any control over the structure of the tables) is not the same as helping to figure out the answer to his actual question

i get that you don't know the answer...i don't think you have to keep pointing it out

12/8/2010 11:06:04 AM

he does have control over the table structure

the dude's been posting about developing this little project for over a year

the perfect query isn't always the right solution, guy

12/8/2010 11:10:53 AM

The issue with denormalizing is that it may not properly describe the level of granularity you're looking for.

But lets not get into the debate of which has more "value" -- suggesting a different structure in hopes to bypass a problem or bitching about someone suggesting a different structure.

Because that one is pretty cut and dry.

[Edited on December 8, 2010 at 11:26 AM. Reason : .]

12/8/2010 11:19:25 AM

p.s.

select u.[name], t.[type]
from [user] u
join (select u.[id], min(t.[level]) highest_level
from [user] u
join [relationship] r
on u.[id] = r.[user_id]
join [type] t
on t.[id] = r.[type_id]
group by u.[id]) [durp]
on [durp].[id] = u.[id]
join [type] t
on [durp].highest_level = t.[level]

[Edited on December 8, 2010 at 11:21 AM. Reason : ass]

12/8/2010 11:20:42 AM

Is it bad I had no idea you could do subqueries in the FROM?

And what is it going to take for me to get you people to start using backticks instead of this bracket hogwash?

12/8/2010 11:26:46 AM

Brackets look more official, and old people can't tell the difference between backticks and apostrophes.

Anyway, caveat OP that that's for MS SQL.

12/8/2010 11:28:02 AM

^^^ i don't see any fundamental difference between what you've written and what others have already contributed...so if the first ones didn't work, i don't see how yours will

[Edited on December 8, 2010 at 1:06 PM. Reason : could be wrong, of course...in which case, fork me]

12/8/2010 12:51:54 PM

i'm just shutting you down for opening your stupid mouth about me not knowing the answer

12/8/2010 1:21:25 PM

how is posting the same solution in a different format shutting me down?

i didn't realize it would piss you off so much, calling you out for being useless

12/8/2010 1:23:27 PM

when you talk

all i hear is dicks hitting the ground

12/8/2010 1:24:58 PM

that makes no sense...did they chop them off so i could eat them? or are they massive 4-foot dongs?

12/8/2010 1:33:08 PM

i am not the dick expert

12/8/2010 1:36:32 PM

gentlemen

take the dick talk to chit chat

12/8/2010 1:40:46 PM

HE STARTED IT.

anyway, the problem with the solutions so far is that using min() gets the lowest priority level from the subquery and it loses the relationship between the user's id and the type's priority...that's why vertigo is getting users with random types

at least, i think that's what's happening

[Edited on December 8, 2010 at 1:42 PM. Reason : .]

12/8/2010 1:42:16 PM

One more time: The essential problem with trying to wrangle a sensible query around this is that his schema needs work.

12/8/2010 1:45:11 PM

One more time: "change it because i don't know how to do what you're asking, even though i don't know jack shit about the circumstances surrounding your particular problem" is not always the correct answer, no matter how desperately you want it to be.

12/8/2010 1:47:35 PM

One more time: I demonstrated that I know how to do what he's asking, and I am at least a little familiar with the circumstances 'cause I paid attention to his other threads. Quit being a thread-derailing jackass.

12/8/2010 1:50:15 PM

One more time: you demonstrated that you could rewrite someone else's query to do the same thing, and you think you know what's going on because of old posts that may or may not be relevant. quit being a self-important jackass.

12/8/2010 1:53:04 PM

Quote :

"anyway, the problem with the solutions so far is that using min() gets the lowest priority level from the subquery and it loses the relationship between the user's id and the type's priority...that's why vertigo is getting users with random types at least, i think that's what's happening"

That's not the problem at all.

My query, qntmfred's, and FroshKiller's all work based on the criteria outlined. There's something else going on with his data that we can't see in the test case.

Also, I fail to see why you're going to give someone shit for trying to contribute (whether you agreed with his means or not) when you've contributed nothing except a failed attempt to call someone out and criticize a different approach to solving the problem at hand.

12/8/2010 2:00:51 PM

well, if that's the case...then i suggest that vertigo hire a professional to raze their entire database and start over from scratch

now i've contributed AND provided a solution

i am awesome

[Edited on December 8, 2010 at 2:13 PM. Reason : i never said y'all's queries didn't work based on the info provided, btw]

12/8/2010 2:04:04 PM

here:

select
usr."name",
typ."type"
from [user] usr
JOIN [relationship] rel ON rel.user_id = usr.id
JOIN [type] typ ON rel.type_id = typ."level"
where rel.type_id in
(select T.type_id from (select row_number() over (order by type_id asc) as Rno,rel2.type_id from [relationship] rel2 where rel2.user_id = rel.user_id) T where T.Rno = 1)
order by rel.type_id asc

same query as I posted above, just with the sorting you were looking for.

I get these results:
John, manager
Anna, manager
Mary, manager
Will, trainer

This is based on the data you provided. Adding Will in as a manager, I then get:
John, manager
Anna, manager
Mary, manager
Will, manager

Isn't this what you're looking for?

[Edited on December 8, 2010 at 3:20 PM. Reason : ah....mysql, not mssql. guess I should have read. oh well...I don't know mysql. i'm a mssql guy.]

12/8/2010 3:16:54 PM

ok so this page demonstrates why it can be annoying and sometimes useless to bring questions like this here.

afripino, while we all seem to enjoy writing queries that may or may not work for you, just post your question on stackoverflow and you'll probably get some better results.

12/8/2010 3:37:24 PM

Quote :

"ok so this page demonstrates why it can be annoying and sometimes useless to bring questions like this here. afripino, while we all seem to enjoy writing queries that may or may not work for you, just post your question on stackoverflow and you'll probably get some better results."

afripino wasn't the person asking for help.

The answers that vertigo has gotten from at least 3 people do what he's asking for -- the issue is that there's another fact messing things up. Sure, quagmire02 being bored and trying to fuck up a perfectly decent thread is annoying, but it's not actually stopping the problem from being solved.

[Edited on December 8, 2010 at 3:41 PM. Reason : .]

12/8/2010 3:41:18 PM

ooh yeah wrong user

I wanted to say OP but that wouldn't work here

12/8/2010 4:09:47 PM

^Mods.....troll on the loose....BAN HIM!!!! LOL + J/K

Anyway, my query posted works with the id field added too

select
usr.id,
usr."name",....copy and paste all the other stuff.

Yields
1, John, manager
4, Anna, manager
2, Mary, manager
3, Will, manager

[Edited on December 8, 2010 at 4:24 PM. Reason : blah]

12/8/2010 4:20:25 PM

Quote :

"Sure, quagmire02 being bored and trying to fuck up a perfectly decent thread is annoying"

yes, yes, calling out FroshKiller for coming in here and swinging his dick around without actually contributing anything is horrible...bring the banhammer! the difference is that frosh is a better troll than i am...his first instinct is to be an ass and THEN contribute something if the mood so strikes him

Quote :

"Mods.....troll on the loose....BAN HIM!!!! LOL + J/K"

as always, i won't be banned OR suspended because i haven't done anything wrong (at least, nothing more or less "wrong" than frosh, and we both know he's butt buddies and won't be banned for being a dick)...i won't be suspended because all i did was call someone out on making snide comments without contributing to what is (generally) a serious forum, and then just responded to his pathetic "dick" insults

i can, and will continue to, post on the regular

Quote :

"just post your question on stackoverflow and you'll probably get some better results"

ain't that the truth

12/9/2010 8:05:57 AM

Help!

Ive got a table with four columns

ID, Year, weekOfYear, searches

year, weekOfYear and ID are indexed (don't ask why the date is stored this way...)

I need to be able to efficiently select the number of searches between a given date for a given ID

i.e. Select sum(searches) where year = 2010 and weekofyear between 5 and 15.

Obviously this screws up when we transition to a new year (like we did 5 days ago). I can't convert year and weekofyear to a date and then compare that between dates, because then the indexes don't work on those fields and the select drags. Ideas?

[Edited on January 4, 2011 at 5:29 PM. Reason : . I should add, that this will take two vairables @datestart and @dateend that will determine range]

1/4/2011 5:27:53 PM

datepart(week,enddate)/*w1*/ - (weekOfYear/*w2*/ - 53*(year(enddate)-year)) <=datediff(week,enddate,startdate) -1

was my solution.

1/4/2011 6:01:22 PM

With regards to the "week of the year" field -- how is that populated? Just by the MySQL WEEKOFYEAR() function or something else?

1/5/2011 12:33:04 AM