ebmigue

Read about database normalization. Your problem is addressed by that science. Avoid attribute names that are the same across tables/relvars, unless they refer to the same thing. Otherwise, you'll have ambiguity across your queries, and program in general. Hope it helps.

Again, where did you get that? By correctness I do not mean that sort of thing. I mean correctness in the formal sense. Oh no. Someone at MIT will be very mad at reading this. Math. Induction can not prove whether or not a "for loop" will execute. That is its assumption. Does that mean Mathematical Induction is useless? Of course, not.

Please keep the this discussion on topic and sensible. Refrain from such irrelevant remarks.

It is their product. It is their first responsibility. Yeah sure, I can contribute. Give me a lifetime. lol And besides, there have more resources and talents. In the meantime, we have to "tweak" our queries, the way they prefer it.

Where did you get that? Such conclusion cannot be inferred from my posts. Sure. Not all "academic" things can be of use - at least on our time. That is for history to tell. But this - the relational model - is one of them that can be used - relational theory. Precisely! I'm not telling people to know every bits and details of every DBMS. If ones does, one cannot anymore work for clients. I'm saying that, if we want to judge the usefulness, features, capabilities, etc. of a given DBMS, our only basis is its underlying theory, namely, the relational model. No other. That's what makes theory important. It becomes the yardstick for our judgement of a certain DBMS. And people say theory has no place in the real world? Now that's preposterous. Certainly, it is helpful to know implementation details, such as a DBMS's optimizer, parser, etc. But all such knowledge are ephemeral. COme the next version of a DBMS, things might get changed "under the hood." But not so for theory. Yes, there are developments in theory - changes, evolutions. But they are (the authors) as careful and ground their conclusions to the scientific tradition. I'm glad we're agreed in this. Of course. That is your decision. But: The Relational Model is no "arbitrary theory" in the field of databases. In fact, it is the basis of all leading DBMS (oracle, mysql, etc.). If it's fundamentally flawed, then it is fundamentally flawed. Your feelings to such fact won't matter. The fact that it is fundamentally flawed should be enough reason to question the status quo.

I hope your clients will not read this. Consistency and precision goes "hand in hand" so to speak. Of course, a "consistently imprecise" system is a different kind; I won't call it consistent in the strictest sense of the term "consistent." That's good. If we live in a totally different universe. Wow, now who's grounded on reality? Yes, certainly you don't "need" to prove it. You're not bounded by contract. But then again, what is to be done? Nothing? Guys, let's stop working! As a man of science, I'd say, Something that is not proven cannot be depended upon. Certainly. Not all consistent systems are true (say, novels). But all true systems are consistent. Stop buying anything "modern" then. Why educate your kid? Yes, that's true. I won't argue. I can read them novels too. They may "sound true," but, is it, really? Practice not grounded on theory is, let us say, a dangerous enterprise. Theory not grounded on practice is a luxury, but a good investment (for knowledge's sake, as they say). I don't understand that nonsense. I agree. That's why we need to learn more, study more, research more - in short, theorize. =) I agree. Clients won't care whether or not you know something "theoretical". As long as you deliver. I get it. The question is, can you be so sure of what you deliver? If yes, well, that is your opinion. If you don't care of the certainty of the products you deliver, well, then again, that is your professional decision.

Of course, observation is first, then science is second. That's the whole point of empirical knowledge. But there are other forms of knowledge that are not empirical - they call it rational/deductive knowledge. Mathematics and logic (and thus, relational databases) is of this kind. (read epistemology to know more about this.) But come on. Did you really think that "observation" is still the stage in the field of computing? For around 80 years, people are developing programs. For 40 years, people are creating relational databases. Surely, their knowledge accumulated over time, w/c made possible for theorizing. Rather than go through the whole 40 years again, save your time by learning theory. That's what I meant by saying that theory is important. By "theory" I mean not the speculative kind. Not the "Big Bang Theory" or "Relativity Theory". What I mean is the fundamentals, the basic axioms of your field. If you're a mathematician, surely you must be knowledgeable of the fundamental principles of mathematics. If you're a database professional, surely you must be knowledgeable of the fundamental principles of databases and computer science. Otherwise, one's output could not be as precise as one thinks.

You cannot prove that. You cannot prove that either. That story is usually regarded as an anecdote. But how did Pascal created it? He used mathematics - mathematics = theory. Of course, hundred years after Pascal did it, developments of science took place. After the 2nd world war, the stage was in place for a science for computers. True. But how did we became so sure of such methods? Science. How did they realise that? Because people at NASA KNEW THEORY. You cannot prove that. You use your assumption as your conclusion -- fallacious. You cannot prove that. That is true. So why bash theory, when practice could also be bashed? That is true. Let's forget all science because they can be wrong. Let's start tomorrow. That cannot be proved. So that's why. Material gain over knowledge. Hey, I'm not a monk. I understand what you mean. The whole point of what I'm 'ranting' about is this: by using theory, we could have earned more. =)

I will correct what I said. There are acceptable implementations (DBMS) back then and even now. But they are not as popular. But then again popularity does not necessarily mean correctness.

I might just do that. Give me a lifetime. lol. What is to pick from if they are all the same, conceptually speaking? But still one wonders: What happened in the last 40 years w/c didn't see an acceptable implementation of the Relational Model? 40 long years. Big, rich, companies with the best computer engineers at their employment. The best minds in the industry. And still we are stuck with SQL, the language from the 70's. One wonders. Because of lack of appreciation for relational theory, I strongly suspect. The point is, if all DB users/programmers are aware of theory, they will what they expect from products. Thus, there is pressure. If there is pressure, those companies with resources will be forced to cater for the market's demands. It's more efficient that way. Rather than go lone wolf. That would be the last resort I guess.

True or not, this is an empirical matter, w/c involves a lot of variables, and no amount of experience can prove the truth of the case. I don't even know what "partial inserts" mean. Please clarify.

And you have to wonder, "why does he resist?" Because the parser could still be improved. That's Why. From assembly to Java. Notice the "improvement?". More and more, programming languages are turning into natural language. If you have to insist that we have to write 80's style code...wow, that means only that for 30 years parsers have not improved. This is a call to improve the MySQL parser (and optimizer as a consequence) so that we could write queries closer to natural language.

I guess we could that as "One of your opinions in w/c I disagree."

I will pretend that I agreed to your agree claims (why would I bother understanding such nonsense). But this I cannot approve: Computer Science is theory. You wouldn't even have a profession - all of us would not have a profession - if not for theory. Airplanes are impossible w/o the science of physics w/c is theory. Automobiles are impossible w/o the science of mechanics w/c is theory. Synthetic medicine are impossible w/o the science of chemistry w/c is theory. And you say theory has no place in the real world? Wow.

I agree. They are complex. I'd suggest that you use re (http://www.reetudes.com). The interface (the way you will use it) does not require you to instatiate objects. You can also directly issue queries, and retrieve its results. $array = re::_eval("SELECT * FROM some_table WHERE a = ?param2? AND b = ?param1?", array( 'args'=>array('param1'=>1, 'param2'=>'b') ))->toArray(); foreach($array as $tuple){ echo $tuple->get('some_value'); } That's it. Join Tables? $array = re::usevar("table1")->join(re::usevar("table2"))->toArray(); //loop as above. Of course, setting up connection parameters are necessary. But that would be easy. Once its done, you can immediately begin. re is based on the Relational Algebra. Perfect for working with databases that are relational (e.g, MySQL). You notice that the syntax is "OOP style" but the "approach" is the good-old procedural method. Hope it helps.

I suggest that you learn Relational Algebra (or predicate calculus). Then learn SQL. Then learn the "commonality" between the two. That is the only way for you to truly understand what you are doing. Of course, giving you "a quick tip" is good, but I'm thinking "long-term." Hope it helps.

Try: SELECT *, page_url AS parent_url, parent_id FROM pages WHERE id='get_parent_id UNION SELECT *, page_url, parent_id AS get_parent_id FROM pages WHERE page_url='$page' AND child='1' I've simply reversed them. UNION "honors" the "first" SELECT expression for the resulting attributes names of the result.

Just a guess. I've noticed your using LEFT JOIN. That might have generated NULL attribute "values". Thus your usual WHERE clause may not work as expected due to the NULL involved. Check first the NULL columns before doing any restriction (WHERE clause). Hope it helps.

$query = "SELECT DISTINCT u.* FROM todo_list inner join todo_patients on (todo_list.pt_id = todo_patients.id ) INNER JOIN users u ON u.id = todo_patients.user_id" Thay *may* work for you, too.

Add a DISTINCT clause in your select statement. Or better yet, define keys in your tables.

Please ignore my "proof" I'd given at post http://www.phpfreaks.com/forums/index.php?topic=338280.msg1595479#msg1595479. That "proof" is ambiguous, not exact. But except such "proof", I stand by everything that I've said regarding NULLs. I have given that proof in the attempt to explain to DavidAM (and others) what NULLs are. ----------- Using DavidAM's "objections"/"observations", here is my take on NULL (w/c I hope is clear enough and final): No I don't agree. Such statement confuses NULL and the UNKNOWN truth value. Now, what is a value? A value is "an individual constant (say, the boolean TRUE)." What is NULL? NULL is difficult to define. What is certain is that NULL is not a value. It does not belong to some type (say,integer). NULL is conventionaly understood/considered as a "special mark/flag" that indicates that "some value is unknown," but only as that -- not values. Clearly, if "A" is a table attribute declared of type INT, then the possible values of "A", formally and physically speaking, are the integers supported by the computer system in question (say the integers 0 to (2^32)-1). Formally, NULL is not one of them. Now, when we introduce NULL in an existing 2-valued logical system, the consequence will be the so-called 3-valued logic: there becomes three truth-values: TRUE, FALSE, and UNKNOWN. DECLARE A INT; A = NULL; A == 1; //evaluates to the UNKNOWN truth value. A == A; //evaluates to the UNKNOWN truth value. NULL == A; //evaluates to the UNKNOWN truth value. DECLARE B BOOL; B = (NULL == 1); //initialise to the UNKNOWN truth value. B == ((NULL == 1)); //evaluates to TRUE since both values have the UNKNOWN truth-value. NULL is not a truth-value, by definition; while UNKNOWN is a truth-value. UNKNOWN is merely a consequence of the usage of a 'flag' called NULL. UNKNOWN is a possible value of the type BOOLEAN (in 3-valued logic), while NULL does not even belong to a type. Clearly, both concepts, NULL and UNKNOWN, "are not the same thing." "But SQL thinks it is," w/c makes things matter worse. Yes, you are correct. They are NULL to you. You flag them as NULL. Therein lies the problem! Relational Model's intention is to be precise. There is no "maybe". Either we know it or not. Period. There is no "maybe we know it. maybe not." Precision is achieved by two-valued logic already, why shift to three-valued logic, where uncertainty is introduced? If "UKNOWN" is taken to mean here as NULL, w/c I defined above as a mere flag, yes you are correct. The context here is like this: You have two "things" A and B, and you declare/flag both of them as UNKNOWN/NULL; obviously, if you test their equality, you cannot say TRUE or FALSE. What you will know, is that the result of your comparison is UNKNOWN. What it means is this: "it is known that the result of your comparison is UNKNOWN". Obviously this result is not anymore the NULL we are taking about. Now we know the value (i.e., that the comparison yields to UNKNOWN); while before (when we assumed that A and B where NULL) there is no such knowledge of some value. The result of comparing variables (or attributes) that are NULL is a BOOLEAN value called UNKNOWN, and is a valid value in 3-valued logic. (Note: Using the term "a NULL" is incorrect. There is only one NULL "value.") Well, using the explanation above, the comparison will not return TRUE, but rather UNKNOWN. So I don't see any problem there. Now, if you want to retrieve "those that have the same fav colors", obviously ebmigue's and fenway's will not be in the result since their favorite colors are marked as NULL. Or is it? They could have the same favorite colors, too (since they are unknown). So that is the resulting problem. There is no straightforward way of answering simple queries. But I see the dilemma here. This is the case of "missing information" w/c is encountered in the real world. So you use empty strings, for instance, instead of NULL. But the comparison above will return TRUE! So, problem is not solved. This is the consequence of failure to support user-defined types and type operators overloading. Remember that equality testing is dependent on the type of the operands being tested as equal. "1" == 1 must return FALSE because the operands are of different types. If only in SQL (MySQL, in particular) we could define our own types, say, COLOR, and then override the equality operator ("=") for type COLORs, the problem is solved. First we declare a type called COLOR, defined to have "the usual ROYGBV colors" and the empty string (as a value in case there is missing information). We could then easily freely "customise" the equality operator for type COLOR. In the implementation of the overloaded equality operator, we could code that if we encounter empty strings for both operands (our special value for the missing information) the comparison will result to UNKNOWN. It is also possible to return the FALSE value when the missing color value is encountered during equality testing, in order to avoid problems involving the UNKNOWN truth-value. We might want to document such decisions. In the documentation, the truth-table for comparisons might be necessary for values of type COLOR. Basically, what we are doing is we want to utilise special values for missing information, and at the same time avoiding NULL and the UNKNOWN value, thus avoiding three-valued logic. That is what I meant by saying that in the real world there is no NULL. Special values already capture what NULL intended to achieve. If type definition and type operator overloading is supported in major DBMS products, then using NULL becomes needless. Problem solved. In fact, there are many more methods, aside from the "special value strategy", to deal with missing information w/o using NULL and is theoretically sound. But this is not yet the reality of existing major DBMS products. That is why, NULL and 3-valued logic, with their imprecision, are here to stay with us. But further, the nagging question is: Do relations, by formal definition, have NULL attribute values? The answer is no. By definition, NULL is not a value. But relational attributes necessarily contain a value. So if we "combine" both definitions, contradiction arises. So this "dilemma" is impossible in the first place. And besides, if we encounter missing information, chances are, it is due to bad database design, w/c can be resolved by an alternate better database design. That better database design will not anymore need NULL, w/c is consistent to my claim that NULL is needless, and is not really useful in the real world. Of course, in the real world there is missing information. But when we encounter such instances, we do not use the concept of NULL. Instead we use special values that indicates that there is missing info (say, N/A, or TBA (to be announced, etc.). If we have an attribute named WEIGHT_OF_PRODUCT, and supposing we could define our own types, we could create a user-defined type w/c we might call WEIGHT. If we represent the type WEIGHT with integers, then the possible values of type WEIGHT are the INTEGERS + the special value to represent missing information. And NULL is not anymore necessary. Yep. If your data/program is critical, then use precise methods. Use science, mathematics, and logic. That is the only way. -------------- I will try not to post again on this thread. IMO, enough is said on my part. If you do not agree, doubtful, etc, about such claims I cannot anymore do anything about it. What I can do is to refer you to the leading authors on these matters (e.g., Date, Darwen, McGoveran, Pascal, et. al). They can explain better. (You can find a wealth of materials here: http://thethirdmanifesto.com/) Refer to them to relieve all doubts and possible counter-arguments lingering in your mind. Research and critical study is the only way to clarify what you already know. Thank you and Hope it helps.

For the interested, here is SQL's deviations on the Relational Model: http://en.wikipedia.org/wiki/Relational_model#SQL_and_the_relational_model There are many more in fact. Research on them.

Yes, the solution posted by DavidAM is appropriate for you. That was also what I suggested, though I didn't provide the actual code, only the "pseudo-code." But I would advise you to transform your attributes to non-NULLable attributes. Avoid LEFT JOINs as much as possible. Use them only when GROUP BYing. As to why I am advising that, look up for books by Chris Date, Hugh Darwen. There you will find the reasons. The conceptual and theoretical reasons. People will advise you otherwise, for performance reasons. They will say, "Ah don't listen to them; their queries are slow and impractical." But I would say, at least they are precise. Before you will launch your product, you would want them precise and reliable. A fast program but is imprecise and unreliable is useless, utterly useless. Of course the ideal is to have a program that is both precise and fast. I do not argue that.

@DavidAM I will try, given the place and appropriateness, to explain point-by-point the incoherence of NULL, w/c you claim otherwise. NULL is not even a VALUE! IS NOT KNOWN (or UNKNOWN) is a value and is therefore not the same thing as NULL. UNKNOWN VALUE != NULL. Proof: UNKNOWN VALUE == UNKNOWN VALUE //returns TRUE NULL == NULL // returns NULL UNKNOWN VALUE == NULL //returns NULL (or: TRUE == NULL //returns NULL) So how can they be the same? That is what I'm saying. NULL introduces "holes" in a two-valued (TRUE and FALSE) system. The point is, if you want to use NULL then don't expect the precision of the two-valued system. Since people, I assume, wants precision in their programs (why wouldn't we?), then I am suggesting to avoid NULL. My intent is clear. Yes, you are correct. But make "favorite_color" a "NULLable" attribute, then comparisons becomes 3-valued (T, F, NULL) instead of being 2-valued (T, F). W/c is simpler and has a rich tradition scientifically? The 2-valued system is. Why? Using empty strings instead of NULL is the correct way. What is your proof that using empty strings would yield incorrect results? Any standard textbook on relational databases would say that it is based on 2-valued predicate logic and set theory. Look up Wikipedia. Or buy a book by Date at you local bookstore. That is achieved even without NULL. A bit of history. When Codd introduced the Relational Model (RM) on 1969 there was no NULL. When he revised RM in the early 80's, he introduced it. And from thereon he met criticism. For 10 years (1969 - 1980), RM managed without NULL. Of course, by then, the 80's saw SQL heavily used in the industry. NULL was with SQL. As I've said, look up the leading authors of the Relational Model. Chris Date and Hugh Darwen are the best in the field. They have worked closely with Codd.

Set theory AND first-order predicate logic, to be precise. In predicate logic there is no NULL. You cannot argue that. From wikipedia: The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by E.F. Codd. While propositional logic deals with simple declarative propositions, first-order logic additionally covers predicates and quantification. A predicate resembles a function that returns either True or False. (http://en.wikipedia.org/wiki/Relational_model) There is no NULL! No it is not. A claim is only preposterous if it has no rigorous basis (e.g., "eval is evil", "DISTINCT is evil", etc., etc.) I think such claim has its basis, in the same respect that 2-valued predicate logic has its reasons for not allowing NULL into its system. I admit though that, and this is just speculation, that in practice there might be cases where predicate logic is silent about, which gives rise to the suspicion that NULL, afterall, is useful. IMO, however, that is just hindsight. NULL can be done away with and is needless, for the same reason that 2-valued predicate logic, from Aristotle to Modern Symbolic Logic, has survived for 2000 years w/o introducing NULL into its system. Again, this claim that NULL is needless is not the same as "NULL is evil", so to speak, not a "religious and dogmatic" claim; it is a scientific, logical, claim on the contrary. I have the backing of the leading authors of relational db theory (read the works Date, Darwen, et. al.).