Jump to content

Kcarson

Members
  • Posts

    207
  • Joined

  • Last visited

Everything posted by Kcarson

  1. Sorry if I scared you off a bit...didn't mean to do that necessarily, but since you broke it down to a smaller chunk I can help with that...in the case that there are just 2 pieces of data such as this, this query would work:SELECT a.user, a.data, b.dataFROM table1 aJOIN table1 b on a.user = b.userWHERE a.data <> b.data /* <> means not equal */ That will take care of 2 pieces of data, but it gets tricker when you add in more.
  2. You know I hadn't even thought of using subqueries, but that would work, I just tried it myself, and it seemed to do the trick, but try it yourself to make sure....here you go:SELECT distinct A.computerName, A.userName, A.Logontime, A.logofftimeFROM log AWHERE A.logontime = (SELECT max(b.logontime) FROM log B WHERE B.computerName = A.computerName) All this should do is pull back the data from the row where the max(logontime) exists for each computerName. I include the B.computerName = A.computerName so that it will return the max for each computerName rather than just the max for the entire table.Let me know if you have any questions.
  3. Kcarson

    SQL On A Web Page

    I think what he wants is to have his page query the database for the product the user clicked on and have it populate the Product Fields (e.g. price, description, etc.) on that same page (right???). That way he would have one page, but it would be able to work for all products. He is afraid right now that he will have to create a separate page for Product A, Product B, etc. and when a user clicks on Product A it will have to take him to the separate page he created for that product.
  4. Alright, let me start off by saying, I am not doing this for you (too much work, and I don't have the time...sorry ), but I can still hopefully guide you along the way (by the way, I am not a SQL expert either so what I tell you may not be the most efficient way, but hopefully it will work for your situation).By the way, you keep referring to this in the future tense as if this table is not currently being used, if so that will simplify things some since you won't have to worry about moving existing data, rather just making sure that all isnerted data ends up in the right location.To continue on though, with all that you are wanting to do, I think you will find it easiest to go ahead and dive into SQL a little further. I suggest learning Transact-SQL, it is not complicated (at least not the basic stuff) and it will add a lot of power to your queries. So for instance, you could include conditional statements, loops, variables, and case statements to perform what you are wanting. At that point, I suggest you think on your own situation and decide the algorithm that will work best for you.Whatever you decide to do with the SQL code, I suggest you wrap it up into a stored procedure, so that it will be easier for you to call again in the future and reuse.Finally, if you are going to be forced to insert into the table as is, and then forced to insert into the table you are creating, I would suggest using triggers to call a stored procedure, that would contain most of the code you generated for the first task. In case you don't know, a trigger can be set to execute everytime either an insert, update, or delete occurs on a given table. The trigger can execute most anything you can write in SQL, including basic select/insert/update/delete statements, or a stored procedure, that may contain multiple statements.I know this may not be much help to you, but as I look over the problem, I see it as something that you will need to learn and do yourself and as you get specific questions on each step you can post those in here and we can help you where we can. Please know that this is POSSIBLE but may be difficult. I am sure there is probably some SQL guru out there who could present an elegant solution, but what I am suggesting is more of a brute force method...may not be pretty but it will get the job done . Please don't hesitate to ask more questions as you continue on and I hope all of this helps a little at least. **Wow I am long winded today **
  5. I haven't actually used their Try-it page before, pretty cool....well have you been able to do any insert/update/delete queries against the table, if not, then they might not give access to it, you might only be allowed to do select statements.Here is another tutorial with an on-line interpreter that you might try as you learn and experiment with SQL:http://sqlcourse.com/
  6. Sorry, I must have been sleeping during the first few posts when you mentioned the other values you wanted returned...sorry :)Well, how will you identify the userid and logofftime that you want? Do you want the ones on the same row as the max(logontime)? If so I imagine you could select the max(logofftime) and that should get you at least one of the values you wanted (since the max logoff time will coincide with the max logon time (just a guess though))...as for the UserID, if you want the max(logontime) and max(logofftime) for each userID on each ComputerName, then you would do, if you just want the one user for that max(logontime) then I am unsure as to how that would be accomplished:SELECT computerName, userName, max(Logontime), max(logofftime)FROM log GROUP By computerName, userName As for the group by statement I made...I stand corrected, I sometimes type before I think :)Anyways, let me know if the above works for what you were looking for.
  7. I am not aware of any ways to do this, and I would be curious as well if there was one. As I mentioned, it is definitely possible to do in SQL, but not in one query to the best of my knowledge, because it would require a conditional statement as well (if/else).Although, since all the telephones in use are in one table, you might be able to do something like this:Insert Into AllTelephones (TelePhoneNum, InUseFlag)Select TelePhoneNum, 1from Alltelephones, TelephonesInUsewhere alltelephones.telephoneNum = TelephonesInUse.Telephonenum Then, Update AllTelephonesSet InUseFlag = 0where InUseFlag <> 1 Still not just 1 query, but it would work..I think at least And you could create a trigger so that anytime you add numbers into the TelePhonesInUse Table it would automatically update the record in the AllTelephones table.That would be done something like this: create trigger PhoneInUse on TelePhonesInUsefor insertas Update AllTelephones Set InUseFlag = 1 From TelephonesInUse Where *Some Condition to distinguish last inserted record (probably a max function)* Hope that helps some at least
  8. Alright, to get what you are wanting try this: SELECT distinct computerName, MAX(logonTime)FROM tablename GROUP BY computerName This will first group all the computerNames and keep just one of each (distinct), then from that group it will find the max logon time. This should take care of the errors you received and give you back the data you are wanting.
  9. Ok, I was able to recreate the problem at work today, and I am not positive of a solution , but I figured if I posted a explanation of the problem, maybe somebody else could help :)ExampleJoin between two tables WarID VCLScores.Flags1 52 73 4 Join between three tables WarID VCLScores.Flags OtherScores.Flags1 5 21 5 22 7 62 7 63 4 13 4 1 Now for the explanation, when the joins are performed between two tables, then the WarID is matched between both and you get the correct sum, but when you perform the join between all three tables it will first do the join between War and VCLScores (but what should it put in the columns for OtherScores? well, it puts includes the values for OtherScores as well) and then it will do the join between OtherScores and War, but once again what should it include in the VCLScores column, and it includes the correct values there as well. So basically, it is including the rows multiple times.In my experiment, I had 46 rows when I performed a join between 2 tables, 6000 when I included the third, needless to say the sums were off the second time
  10. Very strange problem Can you be more descriptive in your explanation of what is happening when you include the sums for both tables? Maybe include what the sums are if done individually and what they are if done together?Also, what happens if you use the same query given earlier, but instead of showing the sum(OtherScores.Flag) you just showed OtherScores.Flag. Would your sum(VCLScores.Flag) still be off, or is it correct now?
  11. Well, if you are willing to create temporary tables and such then there may be a way (so maybe not impossible ). What information do you know about the table? Is it always three responses for each user, or will that vary (is there a max number of responses/data units)? and how large of a table are we talking about....100 row table, or 100,000 row table? Also, are the response fields predictable values, such as Response1, response2, etc. or are they different for each record? Finally, is this something you will need to reuse over and over?To give you some idea how the answers might affect things....you can do a join on a single table, and if for instance you knew you only had three responses to deal with, you could write a query (an ugly one mind you....but hey if it works )that would join the table to itself three times, and could populate the fields correctly. If the number of possible responses is unknown, then that idea goes out the door.If the number of rows in the table is low....I would personally suggest just try to manually fix the problem.Oh, forgot to ask, what kind of SQL are you running...MS SQL, Access, Oracle, MySQL, or something else?I still do not know exactly how this can be done, but since you are being flexible and not trying to do all of this in one query, I can try to be flexible too and think on it for a while and see what I can come up with.
  12. If all that information is stored in the same table then I am afraid what you are asking is impossible (or at least I don't know how to do it, if anyone does, I would be greatly interested)
  13. Good question, I had noticed it before but never thought about it, just kind of went on with things. Well, to answer you, it appears that the reason for some of the duplicate tags are because of a transition from HTML to XHTML (somebody please correct me if I am wrong, since its just a guess ). If you look at the next version of XHTML (2.0), you can see that the <b>, <i>, and <tt> are not there. As far as what the differences are between the rest of the tags, well I think it is purely to help identify what the text is, rather than format it properly. These are just my guesses after thinking about it and looking at it, although I must admit that I look forward to hearing if any others can shed a little more light on the subject
  14. What are you submitting to? And what are you using to submit it there, if you are just using the HTML tags, then there is your first problem. If you want to submit the information to a database then you will need to use a server-side language such as ASP or PHP.If you just want the information e-mailed somewhere, then I would suggest using Javascript.Also, see this thread for e-mailing:http://w3schools.invisionzone.com/index.php?showtopic=657
  15. Skemcin,I don't think the job would be for moderators to come up with the best stance on topics, but rather to summarize the main points brought about by both sides once the issue has been debated thoroughly. Once again, this is based on the assumption that Kaijim may be too busy to read through all of the posts everyday, which he may or may not be, who knows. I guess I base my assumption off the fact that he isn't lurking around here very often, but then again he may just be reading not writing. :)Chocolate570,I guess the question at hand is whether nor not the topic you mention should be invisible or not.Oh and by the way, in case anybody is wondering, I voted that it should remain hidden so that there is less to read in the topics and so that the moderators can freely give examples when needed. Not to mention, it would kind of frustrate me if I saw a post that I disagreed with but couldn't respond to
  16. Can you post your sample code? My guess is that there may be a problem with the location of the image, just a guess, but without seeing anything, can't say for sure.
  17. Good point....I guess I am just one of those people who likes all of the pertinent information in one easy to find place rather than having to search all over for it, and I figured Kaijim would benefit from something like this, but if he hasn't asked then I guess it isn't necessary, but still an idea worth considering in my opinion.And wow, finally a poll whose results are not lopsided but actually spurring debates
  18. Wayne,Welcome, as far as the best tools to learn and trying to keep it to a minimum, that is kind of hard because they all complement each other. For starters, you will need to learn HTML/XHTML, next you need to learn CSS, after that you need to evaluate what your needs are and you can then decide either on a Client-Side Scripting language (probably Javascript) or a Server-Side Scripting Language (either PHP or ASP, your choice). You won't need to learn SQL until you start working with databases, and when you do begin it won't take long to get the information you need. As a whole, the tutorials on this site (http://www.w3schools.com) are very short, yet packed with useful information. None of them should take long to finish (a few hours each probably, maybe more for some of the scripting languages) and they will give you a good overall picture of what each language can do, from there you can decide which points you want to learn more about. I know it may seem daunting with all the languages, but you don't have to be an expert in each in order to create a good website, just start at the beginning and start using the knowledge as you acquire it.Best of luck.
  19. Hmm....not sure what to say . Are there any problems with the data you have stored in the OtherScores table? Are you sure the Flag column is an integer/number type?To check the data I would suggest running a query something like this...SELECT flagsFROM OtherScores ORDER BY flags DESC That way you can see what the largest numbers are in the table and maybe that would explain your high numbers, well its a start at least that might narrow down the field of potential problems.Let me know what you find out.
  20. Perhaps something like this.... SELECT War.WarID, War.Map, War.Mode, War.Time, War.ClanPlayed, Sum(VCLScores.Flags), Sum(OtherScores.Flags)FROM War JOIN VCLScores ON VCLScores.WarID = War.WarIDJOIN OtherScores ON OtherScores.WarID = War.WarIDGROUP BY War.WarID Let me know if you have any questions, or if this doesn't work for some reason.
  21. As per your request http://w3schools.invisionzone.com/index.php?showtopic=838
  22. As all of the suggestions are made on the forum, it will help the admins to get a general summary of what the masses want. To take care of this, the moderators have a topic/thread that they can post to but no one else can. The purpose is so that they can review the suggestions made by everybody and then give the admins (Kaijim) a good summary of the pros/cons and want most everybody wants.Now this thread/topic can be hidden from everybody except moderators and admins or it can be viewable by all but locked so that only admins and moderators can make post. (FYI we already have a topic where everybody can make the post....you are reading it right now ).So here are the pros and cons of each choice (at least as far as I see it)Option 1: Viewable/LockedPros:-All members can know what suggestions are being given highest priority to Kaijim and see his responses.-This might help members feel confident that their opinions have been heard and taken into account, even if the changes have not been made.Cons:-One more thread to read.-Moderators/Admins may not feel free to say what needs to be said (not suggesting they will be talking behind peoples backs by any means, but sometimes it helps to give examples of why certain things need to be done, and they may shy away from giving specific examples if everybody can see it)Option 2: HiddenReverse the pros and cons from above Option 3: Who needs one? For those who think that the admins should just read all the post made in the suggestion topic as well as those made in the general topic (since some suggestions have come from there as well).Well, lets see what everybody thinks and wants....
  23. Nice to meet you Andrew, nobody here judges anybody based on age...just maturity level, and you seem to be very mature and professional thus far. Keep it up and as you begin working on your website please do not hesitate to ask questions here, we are always glad to help.
  24. Skemcin,lol....much, much better :)Jonas,That sounds great, I don't know if it would be better for the moderator/admin thread to be locked or hidden...I see pros and cons both ways, I guess the best way to decide would be to just ask people (...maybe a poll ). By the wayI wanted to let you know that you guys (moderators) have been doing a great job thus far, and I am glad Kaijim chose the three of you.
  25. I am glad to hear that, and it appears that you are trying to learn how to do it yourself, so congrats. You have no idea how many students will come onto forums and try to get answers to their homeworks without doing a single piece of work themselves (haven't seen it on this forum really yet, but on some C/C++ Programming forums it is a frequent problem).Anyways, to help with getting only the top number of rows using a particular query, I believe you can do that using the LIMIT command, you can see varying ways to accomplish what you are trying to do in this thread:http://w3schools.invisionzone.com/index.php?showtopic=652Best of luck and please do not take my previous comments to mean you should not post questions here, I know I am always willing to help those who have well thought out questions and who are genuinely wanting to learn rather than just get the answers, and you appear to be a learner .Well, let me know if the LIMIT command works for you or if you have any other questions.
×
×
  • Create New...