Jump to content
  • Advertisement

chadsxe

Member
  • Content Count

    547
  • Joined

  • Last visited

Community Reputation

146 Neutral

About chadsxe

  • Rank
    Advanced Member
  1. chadsxe

    SQL question.

    Quote:Original post by WavyVirus There are several ways to approach this. Some depend on the database you are targeting. I've included a few examples here (please excuse any odd mistakes - I don't have a database here to play with. I believe that the best way to write an efficient query is to build it up in pieces, using your database's "explain" functionality throughout to tweak it performance-wise). Oracle: *** Source Snippet Removed *** MySQL: *** Source Snippet Removed *** You should also consider the odd cases which can crop up when writing queries like this - if more than one employee has the max, do you show them all or just an arbitrary one? Some Oracle examples which will show all "top" rows: *** Source Snippet Removed *** *** Source Snippet Removed *** Thank you for the great explination. I helped a lot. I have another question for the sake of it. If you noticed, I have a nested subquery in my query. This can be removed by adding another "AND" with a join. SELECT EMP_ID, SUM(ESTIMATE_LINE_QTY * SERVICE_COST) AS totalSales FROM scf_estimate, scf_estimate_line, scf_service WHERE scf_estimate.ESTIMATE_ID = scf_estimate_line.ESTIMATE_ID AND scf_service.SERVICE_ID = scf_estimate_line.SERVICE_ID AND scf_estimate_line.INVOICE_ID IS NOT NULL GROUP BY EMP_ID; Which version would be the better way to do it?
  2. chadsxe

    SQL question.

    So I tried this SELECT d.EMP_ID, MAX(d.totalSales) FROM ( SELECT EMP_ID, SUM(ESTIMATE_LINE_QTY * SERVICE_COST) AS totalSales FROM scf_estimate, scf_estimate_line, scf_service WHERE scf_estimate.ESTIMATE_ID = scf_estimate_line.ESTIMATE_ID AND scf_service.SERVICE_ID = scf_estimate_line.SERVICE_ID AND scf_estimate.ESTIMATE_ID IN ( SELECT ESTIMATE_ID FROM scf_estimate_line WHERE INVOICE_ID IS NOT NULL ) GROUP BY EMP_ID ) AS d; And it results in this EMP_ID MAX(d.totalSales) 14 60103.35 As you can see it shows the correct totalSales value but not the correct EMP_ID Any thoughts Chad
  3. So I am fairly new to SQL programming and have a questions. I wrote this query SELECT EMP_ID, SUM(ESTIMATE_LINE_QTY * SERVICE_COST) AS totalSales FROM scf_estimate, scf_estimate_line, scf_service WHERE scf_estimate.ESTIMATE_ID = scf_estimate_line.ESTIMATE_ID AND scf_service.SERVICE_ID = scf_estimate_line.SERVICE_ID AND scf_estimate.ESTIMATE_ID IN ( SELECT ESTIMATE_ID FROM scf_estimate_line WHERE INVOICE_ID IS NOT NULL ) GROUP BY EMP_ID; Which returns EMP_ID totalSales 14 5972.2 15 60103.35 16 52888.5 22 8458.5 23 42228.8 I am now trying to figure out how to go about and get a result that only shows the MIN or MAX of the totalSales column. I imagine I have to subquery the table that results from the query above but I am not sure on how to do that. Any advice would be great. Thanks Chad
  4. Hi, In the above ERD I am trying to represent a "chair" relationship. - Each committe has a chair (leader of the committee), and no member can chair more then one commitee. As you can see I am using ENROLLMENT as a composition entity in order to resolve a M:N relationship. My question is do I make the "chair" relationship between COMMITEE and ENROLLMENT, or do I make it directly between COMMITEE and MEMBER. Regards Chad
  5. Quote:Original post by alvaro Quote:Original post by chadsxe Quote:Original post by alvaro Quote:Original post by chadsxe So with that in mine are we agreeing that this would be the fairest representation? No, you still haven't expressed the fact that a town can only have one ruler. The is what the double hash || mark means in Crows Foot notation. That can't be right. Whatever the convention is, your line is decorated symmetrically, but the relationship is not symmetric: A citizen could rule over one town or over none (or perhaps over more than one, but that's not clear to me), but a town always has exactly one ruler. Perhaps the line should look like the line for the other rule, only flipped. From my first post..... "Each Town is ruled by one citizen, and each of those citizens can rule only one Town." After re-reading that a couple of times (and your most recent response)I now feel like the relationship might be better represented by... This states that a town must be ruled by exactly 1 citizen, and a citizen can rule 0 or no more then 1 town. The word "can" would imply that a citizen does not have to rule a town. What do you think? Regards Chad
  6. Quote:Original post by alvaro Quote:Original post by chadsxe So with that in mine are we agreeing that this would be the fairest representation? No, you still haven't expressed the fact that a town can only have one ruler. The is what the double hash || mark means in Crows Foot notation. Chad
  7. Hi, I am using Visio 2010 and have things set to be displayed with Crows Foot notation. The diamond seems to be for Chen notation. The book has not talked about specialization yet, which might mean I am thinking way too far ahead. I guess my best bet is to take things as they are and not try to over think them at this point. So with that in mine are we agreeing that this would be the fairest representation? Thanks Chad
  8. Quote:Original post by alvaro Quote:Original post by chadsxe Quote:Original post by alvaro Quote:Original post by chadsxe None the less, being I am new to database design I was not aware you can set up multiple relationshiops between two entites in an ERD. Are you suggesting something like this? I am not sure what the marks at the connections mean, but yes, basically that. The marks are Crows Foot notation. I am not trying to be rude but do you know what an ERD (Entity-relationship model) is? Or are you making suggestions from a real-world perspective? Yes, I know what you are talking about. I was introduced to entity-relationship diagrams using min-max/ISO notation. And I figure your diagram has some issues, since you don't seem to be indicating in any way that a town can only have one ruler, but I am not familiar with the way you are expressing these things. I do have a certain amount of real-world experience with database design as well, although I don't consider myself an expert. Quote:Again, I am not trying to be rude, and it might be a product of me not knowing ERD theory that well, but I am pretty sure you don't represent attributes with relationships. Attributes are a part of the the entity. Quoting that Wikipedia page: "Entities and relationships can both have attributes." Consider this... Citizen has - house, name, etc... King has - house, name other attributes that only apply to king, etc... Depending on how you define a citizen vs a king, a king can have many more attributes then a citizen. I don't feel like you can simply define this in a relationship of - one citizen rules one town. Hence my inital reasoing that a king needs to be defined in it's own entity. Does that make sense? Again I say this all with open ears. I am learning as I go. Regards Chad
  9. Quote:Original post by alvaro Quote:Original post by chadsxe None the less, being I am new to database design I was not aware you can set up multiple relationshiops between two entites in an ERD. Are you suggesting something like this? I am not sure what the marks at the connections mean, but yes, basically that. The marks are Crows Foot notation. I am not trying to be rude but do you know what an ERD (Entity-relationship model) is? Or are you making suggestions from a real-world perspective? Quote:Original post by alvaro Quote:It still seems a bit weird not to represent a King even though the King is a subset of a citizen. All though a king and citizen might have the same attributes, a king might have an additional set to consider. What do you think? I think a king is a citizen that happens to be the ruler of some town. If you need attributes for kings that other citizens don't have, they are probably attributes of the Rules relationship. Again, I am not trying to be rude, and it might be a product of me not knowing ERD theory that well, but I am pretty sure you don't represent attributes with relationships. Attributes are a part of the the entity. Regards Chad
  10. Quote:Original post by alvaro I would only have two entities: Town and Citizen ("Citizan" is not a word). There would be two relationships between them: Houses and Rules. A Citizen can only be housed in one town, and a city can only have one ruler. Sorry about the spelling error. None the less, being I am new to database design I was not aware you can set up multiple relationshiops between two entites in an ERD. Are you suggesting something like this? It still seems a bit weird not to represent a King even though the King is a subset of a citizen. All though a king and citizen might have the same attributes, a king might have an additional set to consider. What do you think? Regards Chad
  11. Hi, First, sorry if this is the wrong forum. Second, I am new to database design but I figured it was time to learn this skill set. I am currently trying to understand and learn how to use the Entity Relationship Model. One of the books I am reading asks me to conceptualize a basic ERD showing the relationships between a Twon, Citizen, and King. The book offered me up these "business rules"… Each Town has many citizens, but each citizan belongs to one Town. Each Town is ruled by one citizen, and each of those citizens can rule only one Town. This is my first attempt at it... My confusion comes in the form of - if a king is a citizan how do I represent the relationship between the King and Citizen entity. Thanks Chad [Edited by - chadsxe on September 8, 2010 12:51:02 PM]
  12. Hi, Well that was defiantly the problem I was having. Thanks to all for the info and useful tips. Regards Chad
  13. Well, I think I just figured out my issue. I was making a call like so ZeroMemory( &sound, sizeof( Sound ) ); in my LoadSound function. That turned out to be bad news. I made the changes to the orginal post to reflect this, and am now going to do some testing to insure this is correct. Thanks for the info so far. I will report back with the results. Chad
  14. Hi, Thanks to all for the information so far, but I have done what has been suggested and I am still getting the same memory leak. What do you suggest now? I am still mightly confused. Regards Chad
  15. Quote:Original post by Antheus Are any of the classes mentioned higher in call chain singletons or statics? No, I do not use singlteons or a static class at all. Regards Chad
  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!