| |
| |
| |
|
Page: 1 2 3
Comments:
<0> how would you check to see if a table is empty?
<1> how would you check to see if theres something in it
<0> exactly
<0> if I could figure that out, then I'd just do a not
<2> lol
<2> wel there are 2 ways
<2> select count(*) from tablename
<2> or if you know that a specific column will never be null - like say the primary key
<2> youc ould say if not exists select column from tablename where pk not null
<0> well the thing is that I have select company.name, sum(case when topic.deleteddate is null then 0 else 1 end) from company left join topic on company.id = topic.companyid group by company.name; <---
<0> nos the problem is that if there's nothing in the topic that matches companyid with company.id, then the result is one for some reason
<0> when it should be 0
<0> i'm sorry ih ave that switched, it should be then 1 else 0
<0> and it should be is not null
<0> I want to do something like sum(case when topics.deleteddate is null and [topics table is not empty] then 1 else 0 end)
<0> if I do a count() then it'll complain about using aggregate function
<3> hi. let's say i have a table GiveAway with cols GiveAwayID, Prize1, Prize2, Prize3, where the prize fields would be PersonID's of people who won them
<3> how could i query for the PersonID who got the most total prizes
<3> (this is not a homework question, heh)
<4> Is there a more efficient way of doing parent/child relations on id in a table because there is no single way to get the tree structure without a stored procedures?
<0> sum(case when topics.deleteddate is not null then 0 else 1 end) displays 1 if the topics table contains no data
<1> neat
<0> Well I want it to display 0... do you know of a way?
<2> lol
<0> I want it to display 1 if the topics table contains one item with deleteddate set to null
<1> this is cute, the sa user on a box still exists from a 6.5 install
<1> like, when i go to recreate it, even though its coming from a 2000 install i still have to use 'skip_encryption_old'
<1> i think its 6.5... i dont recall ever having 7.0 on here
<1> Pilabyte: really?
<0> yeah... really
<1> select case when count(*) > 0 then 'hi' else 'don''t cry' end from blahblah
<1> <3
<2> lol whoa
<0> I can't use count in an aggregate function :(
<0> my situation is a weird one
<0> I promise I've tried to read for a good amount of time and been stuck. I've pasted the code at http://sql-servers.com/nopaste/?show=112
<0> when I have select like that it complains about one aggregate function being contained in there
<1> join to a derrived table maybe
<1> my head hurts
<0> good idea
<0> you're brilliant...
<2> no actually he's phargle
<5> << brilliant
<0> lol
<0> all right fine... you can be brilliant too
<0> now every other channel you're in, people are probably wondering what's going on
<2> lol
<0> I have to stop drinking :-/
<0> WOrking at a liquor store for 13 hours straight every saturday never helps
<6> Particularly when you sample the goods.
<0> My dad always wonders why the inventory count on the reports are off
<0> "Hey man... you can never trust computers"... he's old fashioned so he buys that
<0> I"ve recently gotten attached to this Lindeman's merlot wine
<0> it's like crack
<2> hahahah
<0> BAAM!
<0> I knew that if I got drunk enough i coyld fixed it!
<0> I just created another field called createddate and then I did sum(case when topics.deleteddate is null and topics.createddate is not null then 0 else 1)
<0> I've learned more in this channel about SQL than anywhere else!
<0> sqlcourse.com was the only "tutorial" i took... and I only completed it halfway :-/
<7> ...
<2> ..
<7> Pilabyte: what was the initial issue?
<7> I don't see the necessity for the second column ...
<7> nor the need to place the column in the case statement
<0> The original issue was that I'm building a forum, I was left joining company.companyid with topic.companyid where company.deleteddate is null and topic.deleteddate is null
<0> the problem of course being that if all of the topics of that company were deleted, then the company itself wouldn't show int he select statement that I just typed
<0> I had to have a left join because I had select company.name, count(topic.topicid) <--- but then I changed that to select company.name, sum(case when topic.deleteddate is null then 0 else 1 end) from ...
<0> I'm sorry, correction: sum(case when topic.deleteddate is null then 1 else 0 end)
<0> but then if topic didn't have any data in it whatsoever relating to the specific company, then it would display 1, because deleteddate would be null (beacuse there IS no data to begin with)
<0> so I had to find a way for it to display 0 when topic didn't have any data in it, so I created a field called topic.createdate, and I did the following: sum(case when topic.deleteddate is null and topic.createdate is not null then 1 else 0 end)
<0> means only add one if there is data in topic and deleteddate is set to null
<0> As you can tell... my experience with SQL is extremely limited
<0> 'cause I'm sure there's a much better way of acheiving what I'm trying to acheive
<0> or spelling what I'm trying to spell for that matter
<0> my girlfriend told me today, "If you're not dumb... then NOBODY is dumb"
<0> she was trying to compliment me
<2> eithe way you pout it
<2> lmao its not good
<2> s/pout/put/
<2> but
<0> yeah I know...
<2> at least you 2 are a match made in heaven
<2> may you have much bliss
<0> LOL
<0> I'll smack her in the face and then tell her to go wash the dishes
<2> that was totallyuncalled for
<0> and then MAYBE there will be a trace of us in heaven
<0> yeah I know :)... I almost got fired for joking like that at my job
<0> is there anyway of viewing the code of a trigger that I made?
<4> any1 have code (vb.net/C#) for updating the tree after an insert from preorder tree traversal table ?
<2> i usually8 dont update tree traversal table in .net
<2> ill usually do that in the sql
<4> but it's an update a recursive function?
<2> that depends
<2> for me i have a method to keep track of it so that it doesnt need to be a recursive function
<2> it simply queries and pulls all nodes that belong to a parent node - at all levels
<4> ok
<2> differnet ways to do it
<4> hmm..
<4> so you get the entire tree then decide what to do
<2> each with their own Advatnages and disadvantages
<4> ?
<2> not the whole tree
<2> just all nodes belonging to a node
<2> at any level
<4> is the code in sql a stored procedures?
<4> I not use that.. for database compatible.. that's why I doing it in .net
<1> updating the tree? w***a tree?
<0> Gents, thank you much for all of your help today... good night
<4> it's updating the tree/hierarchy structure in the database
<7> hrm ...
<7> did pilabyte get a non-case solution to his issue?
<7> cause I'm pretty sure:
<7> select c.name, count(t.topicid) from company c left join topics t on c.companyid = t.companyid; ... would return 0 for companies containing no topic
<7> without doing all the extra work of a case statement
<1> he had a max() in the select as well so it got all pissed at the count()
<8> any female cam2cam chat with me 2/m/here
<8> any female cam2cam chat with me 24/m/here
<9> that's a pretty harsh welcome message you have there...
<10> how do i save the structure and attributes of an access database?
<10> say i want to recreate a blank one, how would i do it without messing up the autocounts and stuff?
<11> copy the table, remove all the data ,change the auto number field name, save, and rename to old name.
<11> or
<11> copy the table with ctrl pressed
<11> and then paste table as stracture only
<11> wazoo
<10> isnt there a better way hms?
<10> like sql server?
<11> better then what ?
<11> hey upb
<12> I want to go trough all the records, what do i write to go to the next record? While oRSka.eof=true then
<12> oRSka.Next?
<13> In MS SQL 2000, how do I store hashed p***words in a column, if I don't want to store users' p***words in clear text?
<14> afternoon
<12> will INSERT INTO overwrite data?
<14> no
<14> 'insert' means 'add data'
<14> at least, as far as sql is concerned
<14> you're welcome
<12> ok, so Update is what i'm looking for :=)
<2> lmao
<12> UPDATE kunder SET knavn='', weblink='', notat='' WHERE knr='2' < is this a valid statement?
<12> Do i first have to SELECT before i can UPDATE?
<13> Is there no function/stored procedure in SQL 2000 like the "P***WORD()" in MySQL, that hashes a p***word?
<14> JMO : no
<14> aryx : (1) depends (2) no
<11> http://sql-servers.com/nopaste/?show=113
<11> i use mssql, how is it possible that i get in SiteID few times the same number ?
Return to
#sql
or
Go to some related
logs:
#freebsd
#freebsd
simonscan ftp
irssix terminal
#netbsd
#politics
is america ethical
#politics
#mirc
#dsl
|
|