Java linux HTML qmail C++ PHP Debian MySQL ASP JavaScript perl Delphi NetBSD Solaris etc etc etc [...]






Page: 1 2 3


Comments:
<Xgc> If you're looking for the latest reply, that's different. I don't think that inner select provides that.
<rwc> It does at the moment, somehow
<Xgc> You'd have to SELECT the greatest post_date from related t1 or t2 records.
<rwc> I'll send you a link to a page, so you can see the results of the query
<Xgc> max(greatest(t1.post_date, t2.post_date)), but I think this still needs work.
<rwc> hmmm
<Xgc> I wouldn't make this dependent on t1 or t2.
<rwc> I see
<Xgc> rwc: If this is a hierarchical representation of threads, I don't see how you're finding the latest post_date. I don't think your model provides this.
<Xgc> rwc: If you have a reply to a reply to a reply to an original thread, you can't get that post_date from a simple 2 way (or even 2 way) JOIN as you have.
<Xgc> Sorry, I meant 2 or 3.
<rwc> well, all posts in a thread have a post_thread field which will be the same for all of them
<rwc> except the root post which is indicated by a post_thread of 0
<Xgc> You could do that if all the messages in a subthread refer to the outer most thread_id or post_id.
<rwc> they refer to the outermost post_id through post_thread
<rwc> sorry, should have mentioned that before



<Xgc> If that's what you have, then you have a simple mechanism for finding max(post_date) based on GROUP BY thread_id
<Xgc> or post_thread
<Xgc> You don't need both. Just a max(date) ... GROUP BY post_thread
<Xgc> rwc: If I reply to some subthread, is my post_thread set to the outer most post_id (the post_thread of the message I'm replying to)?
<rwc> Xgc, to the outermost post_id
<rwc> the nested functionality is actually not implemented yet
<rwc> there there is a post_parent field in there somewhere
<Xgc> rwc: If so, that inner select should be: SELECT post_thread, MAX(post_date) FROM ds_forum_post GROUP BY post_thread
<Xgc> Then JOIN that using post_thread in the JOIN criteria.
<Xgc> You don't really care about post_parent, as far as I can tell.
<rwc> currently, no....
<rwc> I'm having trouble understanding this
<rwc> I will leave the post_parent functionality for when I'm really bored, in any case
<Xgc> *nod* You can still need that generally, just not for this query.
<Xgc> Ok. So you're trying to find the latest post_date for each thread, right?
<Xgc> and you're trying to find the latest post info for that date and thread, right?
<rwc> yes
<rwc> and I want to count the rows returned, without having to retrieve them all
<Xgc> Do you need the original thread post for this query or just the post_id?
<rwc> just the post_id
<Xgc> Right. So you don't need the original thread post data.
<rwc> Yes
<rwc> I don't
<Xgc> Ok. That's fine.
<Xgc> Do you really need that user info for the latest post?
<rwc> my original problem was with counting the results, with them being retrieved in this way
<rwc> user info?
<Xgc> JOIN ds_user ON t1.post_uid = uid
<rwc> I have the username of the thread starter
<rwc> hmmm
<Xgc> Ah. That's the original poster.
<rwc> yes
<Xgc> You still want that?
<rwc> ideally, yes
<Xgc> You don't need that LEFT JOIN for t2. We know that post exists. We can make that a simple INNER JOIN.
<rwc> so I replace "left outer join" with "inner join" ?
<Xgc> Does the top most post x have x.post_thread = x.post_id ?
<Xgc> If not, I think it should.
<rwc> no, it has post_thread = 0, to signal that it is a root post
<rwc> I see
<rwc> maybe it would be a good thing to change
<Xgc> Right. It should point to itself. Then you can drop the LEFT JOIN for an INNER JOIN.
<rwc> I see
<Xgc> That's not a big deal.
<rwc> do you think I should make a new field that indicates if it is a root post?
<rwc> maybe I could use post_parent = 0
<Xgc> x.post_thread = x.post_id is an indication we're a root post.
<rwc> ah, right
<rwc> that makes sense
<Xgc> Sure. You could also look at parent_post.
<mihai941> i have a concrete question. I have these rows in a table http://sql-servers.com/nopaste/?show=377 and i would like to get a result back as following : http://sql-servers.com/nopaste/?show=378. This is what i got now : http://sql-servers.com/nopaste/?show=379. I hope somebody can help me out. I have used alot of hours on this query and i can't seem to figure it out.
<rwc> Xgc, I'll have to head off to bed soon
<Xgc> rwc: Does parent_post currently point to 0 now?
<rwc> it does
<rwc> but for every post
<Xgc> rwc: For the root posts, that is.
<Xgc> For all?
<rwc> incorrectly
<rwc> yes
<Xgc> Ah. Is that going to change?



<rwc> yes
<Xgc> Ok.
<rwc> it should post to the parent :)
<rwc> at some point
<rwc> I could add something in to make it non-zero on replies
<rwc> might be faster to use that?
<rwc> if indexed
<Xgc> It should point to the message you are replying to. Root messages can be seen as replies to 0 (the root of the tree).
<rwc> right
<Xgc> rwc: Not tested, but shows the rough form of the solution: http://hashmysql.org/paste/viewentry.php?id=1529
<Xgc> rwc: I know you have to sleep, so I just tossed what I have together. It could have errors.
<rwc> brilliant, thank you very much for your help
<rwc> I'll play around with that tomorrow
<Xgc> I think the handling of the latest date in the outer query wasn't finished completely.
<Xgc> But the correct date is available in that derived table.
<Xgc> pdate.post_date is the date.
<Xgc> Have a good night (sleep).
<rwc> I'll give it a shot anyway, and see what happens
<rwc> Thanks again for the help
<rwc> Good night.
<Xgc> It also depends on the fixes you said you were going to make to parent_post, etc.
<Xgc> Night.
<Xgc> Ah. t2 does not JOIN with pdate.post_date. It needs to do that so that we grab that lastest post.
<Xgc> http://hashmysql.org/paste/viewentry.php?id=1530
<Xgc> and the LEFT JOIN isn't require anymore. It can be changed to an INNER JOIN
<Xgc> Ooops. Shouldn't try to work so quickly. We need all the t2 posts for the count. Remove the date test.
<mihai941> anyone who could help me ?
<rwc> thanks Xgc - goodnight again
<Xgc> mihai941: SELECT Application, Status FROM mytable;
<Xgc> mihai941: Are you saying that there are N rows for each application?
<Xgc> mihai941: SELECT Application, MIN(Status) FROM mytable GROUP BY Application; -- This returns 0 if any one status is not true. If they're all 1, you'll see 1 as the result.
<mihai399> Xgc: sorry i got disconnected
<Prescriptor> anyone using sql 2005? question: has the syntax changed? right now, i have to go "select * from myusername.mytablename" when i'm querying 2005 server ... but my actual server (i.e. not my development computer i am using right now) is using sql 2000, so that username.tablename syntax is not recognized... any way to get over this?
<Prescriptor> ie. is there any backward compatibility?
<Xgc> mihai941: SELECT Application, MIN(Status) FROM mytable GROUP BY Application; -- This returns 0 if any one status is not true. If they're all 1, you'll see 1 as the result.
<Xgc> mihai399: Based on your query, I assumed Status was 0 or 1.
<mihai399> yes status can be 0 or 1
<mihai399> let me try
<mihai399> I get a operand data type bit is invalid for min operator
<Xgc> mihai399: Right. It doesn't like the boolean.
<mihai399> Any other ideas ?
<Xgc> mihai399: Convert that boolean to an integer. You can use CASE for that.
<mihai399> if i change it to an integer then what select should i run ?
<Xgc> SELECT Application, MIN(CASE WHEN Status=0 THEN 0 ELSE 1 END) as Status FROM mytable GROUP BY Application;
<Xgc> mihai399: Try that.
<mihai399> ok thanks alot. i will try it out. i need to make a test database since i can't mess this one up
<Xgc> mihai399: A SELECT can't *mess* anything up.
<Xgc> Unless you regularly type DROP instead of SELECT.
<Xgc> or DELETE.
<Fr3DBr> hey
<Fr3DBr> how do i select specific fields on my table ?
<Fr3DBr> i know select * what about select field1, field2
<Fr3DBr> ?
<Xgc> Fr3DBr: That's fine.
<Fr3DBr> oh i see
<Fr3DBr> and is that faster than select * ?
<Xgc> Fr3DBr: Avoid * unless you really need it.
<Xgc> Fr3DBr: Speed is really not an issue.
<Fr3DBr> security is so.
<Fr3DBr> or * breaks other stuff?
<Xgc> Ask yourself why you think you need * in any particular instance. If it's required, use it. If not, don't.
<Xgc> It (*) can cause problems, yes.
<Fr3DBr> can you gimme some examples? i just want to know what i am dealing with hehe
<Xgc> It also depends a bit on the API you use. Some don't allow field access by name. In this case, you don't want to assume field order in the result.
<Xgc> SELECT * ...; If you need to use position to access fields, you are risking everything by assuming order of the fields. SELECT f1, f2, f3, f3 ...; makes no such order assumption.
<Xgc> Adding fields to a table can change behavior of (and even break) some queries that use *.
<Xgc> Also, you don't want to send unnecessary fields to the client.
<Xgc> and even more important, always specify an INSERT field list. Don't assume field order here either.
<Xgc> You see this fairly often: INSERT INTO table1 VALUES (1,x,x,x,x,x);
<Fr3DBr> yea
<Fr3DBr> im using this
<Xgc> That's bad form.
<Fr3DBr> whats better?
<mihai399> thank you for your help. it solve my problem


Name:

Comments:

Please enter the result of the sum 63 + 46 (to avoid spam):






Return to #sql
or
Go to some related logs:

sed
x doesn't start with dell optiplex debian
centos ahsay 1011
dodek ubuntu
wusb54g+linux
rmmod snd_intel8x0 is in use ubuntu
Mechanize Connection: keep-alive
ubuntu syslogkd
/dev/dri/card0 gentoo
web