ebmigue

Yes I am seriously suggesting. The Relational Model is based on classical 2-valued logic (TRUE and FALSE). NULLs (and LEFT JOINS) makes it 3-valued logic. Research on the consequences on such change, I can't mention it here in detail, for this is not the place, and besides there are lot of sites in the 'net that discusses such issue. Well, for starters: SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END That will give you 0, w/c means NULL is not even equal to itself! An "object" not equal to itself? Nonsense. One of the three "laws on thought" dictates: an object is equal to itself. Wow, I just can't comprehend that fact. But I'm forgiving and lives in a non-ideal world, so I try to avoid NULLs as much as possible. If I use LEFT JOIN, only on cases of GROUPING. Besides, there are no NULLS in the real world. Only N/A values, which can be simulated by "special values." Even ANSI "represents" NULLs using a non-NULL value. That clearly indicates the contradiction on using NULL.

Right. As if people who are creating the DBMS - the software used by most programmers - are infallible persons. For 40 years, we only have SQL, while in theory we could have more useful database technology. Is that the fault of the programmer? Of the regular user of SQL? Nope. That is the fault of dbms vendors. They could be at fault too. Marketing of course would tell otherwise. We forget that what we almost always "learn" are their hacks, since in the first place they failed to implement RM properly.

Parsers who have problems like persons do should not be called parsers at all. Parsers who understand like persons should not be parsers, but persons.

You mean like multiple variable assignment? Such thing is not support by SQL, AFAIK. The work around is using transactions. There is very little chance that there would be a disconnect during script execution. Or, you could use a stored procedure. The stored procedure will involve the two (or multiple inserts), but still you will be using transactions. At least, if you use a stored procedure, there is only one DBMS-call in your php script instead of two (or more). But passing the parameters to the stored procedure might be a nightmare. Hope it helps.

Suggestion. Do not subquery, then cartesian product (CROSS JOIN), then restrict (WHERE). Instead: Step 1.) LEFT JOIN them both tables by user id. Step 2.) GROUP BY user id, summarizing by latest date logged in. Also, avoid using NULLS (and LEFT JOINs) as they are not originally part of the relational model. Hope it helps.

Yes there is. Google for "php mysql transactions". The conventional approach to your problem would be: try{ //begin transaction command. if (insert user query) { if (insert address query) { } } //commit transaction command. } catch(Exception $e){ //rollback transaction command. } Hope it helps.

You are in a good start. What you are looking for is an operation that will compute the Transitive Closure of a set (http://en.wikipedia.org/wiki/Transitive_closure). Specifically, Relational Transitive Closure. AFAIK, SQL does NOT support this operation (transitive closure). You can try re (http://www.reetudes.com). Usage would be: $relationships = re::usevar('friends')->tclose()->toArray(); foreach($relationships as $tuple){ //...echo $tuple->get('user_id') . ' ' . $tuple->get('friend_id') } However, if your table is quite large, admittedly, re may not process it efficiently. But there are other techniques to improve on the computation, say, perform paging on the results first. Hope it helps.

You could use WHERE NOT EXISTS, like so: SELECT * FROM main_table a WHERE NOT EXISTS( SELECT * FROM some_table b WHERE a.id = b.id) WHERE NOT EXISTS is easier to understand (notice that your problems wordings are somewhat similar to this solution). Hope it helps.

I really hope the PHP community will heed your advice. Thanks.

Right. As if implementation/performance is always the consideration. Have people considered, that if a query is slow, it is not the query or the programmer that is always at fault? The OPTIMIZER, the DBMS, in short the company whose product you used, could also be at fault. Strive for correctness first; performance should be secondary. Any consideration on performance is meaningless if what is attempted to be achieved is not achieved in the first place. If you have a program that is super fast but gives you 1 + 1 = 3, any discussion on making it faster is meaningless, IMHO.

Ok. I disagree. A variable's current value == A variable's current contents. The integer 1. Are you saying that the integer 1 has no meaning? Wow. Mathematics is in trouble. Which begs the question: why have this discussion, when in the end, "people can do what they want", given "that 20+ years of programming experience" is involved. I rest my case.

But is not the value of a variable the variable's contents? Thus, both things are the same. In my example, 1 represents the integer 1. I'd rather not comment. It is difficult to arrive at an intelligent conclusion, when preference is involved. I would just like to note that, if your suggestion is to be taken seriously, then the PHP community is in horrible error and malpractice. See their parameter names for the built-in PHP functions (e..g, the string functions)? Yep, the parameter names (variable names) are based on the parameters purpose in the function. In that case, they are in error. I do not understand any of this.

Huh? If you use $query, your variables names will not become obsolete. If you use $sql, and $sql is no longer used by the public, your code becomes obsolete. At least in naming conventions. Wow. Should I declare a variable $one, because it will contain the value 1? What if it can contain the value 2? It is untenable. I say, name things according to their purpose.

What is you basis for saying that DISTINCT "has nothing to do with sets"? Tables are sets. SELECT is a set operation. Obviously, since DISTINCT is used with SELECT, then it has "something to do" with sets. I don't even understand this. In the first place, DISTINCT is NOT a work around for GROUP BY. (Where have you gotten that info?) I am arguing on the line of correctness, and not on current implementation of some dbms of the DISTINCT directive. Now, what if some good DBMS actually implemented efficiently DISTINCT? Obviously, your argument would not anymore be true. Right. That is because in SQL a given requirement can be achieved in many ways. I don't see anyhow why this is relevant to the discussion that "DISTINCT is evil". Then shame on the current implementation of MySQL of SQL. Again, if syntactically, DISTINCT is allowed with GROUP BY, would that make necessarily make DISTINCT "evil"? GROUP BY could easily be the culprit for that matter. Further, you use GROUP BY when aggregating data, and not when removing duplicate rows. DISTINCT is for that purpose. You are misusing GROUP BY if you are using it to remove duplicate rows (w/c in the first place should not be existing). We could also say that MySQL's implementation of SQL is incomplete/incorrect. THus, DISTINCT is not "evil." It is the implementation. Yeah I know. You have to explicitly define your keys. That's what sucks. Relational Model explicitly states that by default, all attributes of a given table are members of a composite key. Thus, you don't even have to explicitly define your keys to avoid duplicate rows! Imagine the time saved on that. Further, it is just not about tables where duplicates wreak havoc. SELECT 'A' as 'attr' UNION ALL SELECT 'A' as 'attr' Now you have duplicates. And why would SQL support the "ALL" directive? To introduce duplicates. Why would we want duplicates, where in set theory there are no duplicates? The answer to that question eludes me. For 40 years, the Relational Model has not been completely implemented. ----------- Please, I do not want to engage in this. Just stop telling people that "x is good", "x is bad", "x is evil", etc, etc. Computing is a scientific enterprise. Such categorical claims, and somewhat dogmatic, should be avoided where possible. If you have to have to claim such things, provide a rigorous treatise on the subject matter. The discussion should be logical and scientific. That is the only way. Otherwise, you'd just be feeding misconceptions to the general public. ----------- Hope it helps.

seems that you didn't read neither my first post (Reply # nor my last.... didn't the comment in this line doesn't ring a bell for you? HAVING gp = CONCAT_WS(',',23,45); // or replace here for the attributes that you want ordered asc.... Yeah I read your post many times, that is why I think that I have to provide another answer for the OP to choose from. There is nothing inherently wrong with your solution. However, what if the OP needs to display ALL such products? Obviously having a HAVING clause is not anymore tenable.

I see nothing wrong with it. Save your sql query strings, the same way you are saving, say, html strings. Retrieve those strings, then process them using the applicable library in PHP (say, mysql_*). You might want to perform some "pre-saving process" when saving those query strings, though (e.g., for security considerations). Hope it helps.

And going on 20 years from now, SQL might not be anymore in existence (or its status might be that of COBOL). So use $query instead. IMO, people will still be "querying" databases 20 years from now, but not necessarily using SQL (some other db language might be in use). $query is more general.

I recommend otherwise. Use But if you are "getting duplicate emails", then what is exactly that you want? Do you want the latest date of a given email? Then use: I suggest you review what you really intend to have, then, and only then, write the query. You have it backwards if you write the query, then determine what you want! I disagree. What is evil is the fact that in SQL, tables can have duplicate records. That is what is evil. Why? Because SQL is intended to be an implementation of the Relational Model. The Relational Model is based upon the mathematical idea of relations. And relations are just sets. And sets have no duplicates. How did SQL miss that very fundamental fact, escapes me. That is the evil that is ought to be conquered. DISTINCT is a workaround by the SQL committee so that SQL is a "truly relational language" (i..e, so that tables are indeed relations, no duplicate rows). Are you suggesting that using DISTINCT is evil since it will achieve what the relational model wanted to achieve? I hope you do not intend that. Of course, performance-wise, DISTINCT can be expensive. But: I'd rather have a slow program, rather than have a program that is not as precise as mathematics. I'd sacrifice efficiency for accuracy, as a principle.

SELECT post, user , time FROM posts WHERE user = ( SELECT friend FROM friends WHERE user = 1 UNION SELECT user FROM friends WHERE friend = 1 ) The problem with that query, is that you are comparing a scalar value (the 'user' attribute) with a set (the subquery). Unfortunately, in SQL, instead of notifying you of the true problem with some unambiguous description, you are instead notified with a cryptic message (i.e., "too many subqueries"). ----- To fix that, you only need to change your method of comparison, like so: SELECT post, user , time FROM posts WHERE user IN ( SELECT friend FROM friends WHERE user = 1 UNION SELECT user FROM friends WHERE friend = 1 ) IOW, change the '=' operator to the 'IN' operator, which is a set operator. Hope it helps.

@fenway, @mikosiko The query you both gave works only for rows with attribute values 23 and 45. As I interpreted the OP's requirements, he needs all products that exactly have the same attribute values for each record in w/c they appear. For instance: product attribute A 1 A 2 B 1 B 2 C 2 D 1 E 1 F 1 F 3 As he said: So the query ought to return products A, B, D, and E. A and B, because the records in w/c they appear, they have the same attribute values (1 and 2). The same is true for D and E (they have the same attribute value, 1). F will not be returned. Even if it has an attribute value of 1, just as A, B, D, and E has, it has an attribute value of 3, w/c the others does not have. As I interpreted it, that is the predicate for the records that he wants displayed. And the query I'd given will, I hope, gives that. Regarding your answers, they are correct, no doubt. But they will need adjustment when attributes 23 and 45 are not anymore involved. What I've given was a more general approach, which will work (I hope), even if the user does not specify in a WHERE clause some value for the field named 'attribute'. (That is why, I've also stated, it is for the OP to determine if what fenway and mikosiko had given was sufficient.) Hope it helps.

Your welcome. Glad to know that this is now solved.

I forgot to mention, that the comments in the query (i.e., the "--") might need to be removed when actually used in PHP. In my experience, they are not properly handled by mysql_*, and results to syntax errors, where it should not.

$query = "SELECT * FROM entries WHERE LEFT(id, LENGTH(id) - 3) = '$u'"; Hope it helps.

Ok, try this first: SELECT last_games.team_id ,last_games.team_name ,COUNT(DISTINCT `all_games`.`all_games_id`) AS 'GP' ,SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN 1 ELSE 0 END) AS 'AWAY_GAMES' ,SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN 1 ELSE 0 END) AS 'HOME_GAMES' ,SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`, 0) ELSE 0 END) AS 'GOALS_WHEN_AWAY' ,SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN COALESCE(`all_games`.`home_goals`,0) ELSE 0 END) AS 'GOALS_WHEN_HOME' --,etc., etc.. --,last_games_ids --,all_games.* FROM all_games INNER JOIN ( SELECT DISTINCT b.`team_id` ,b.`team_name` ,SUBSTRING_INDEX( GROUP_CONCAT(a.`all_games_id` ORDER BY a.`all_games_id` DESC) ,',' ,6 --change the as applicable. this represents the latest N games ) AS 'last_games_ids' FROM all_games a INNER JOIN teams b ON a.`away_team` = b.`team_id` OR a.`home_team` = b.team_id GROUP BY b.team_id )last_games ON FIND_IN_SET(all_games.`all_games_id`, last_games.last_games_ids) >= 1 --WHERE last_games.`team_name` LIKE '%SUTTON%' GROUP BY last_games.team_id ORDER BY last_games.team_id, all_games_id DESC; I took the liberty of aggregating some attributes for you. You can do the rest, I suppose. -------------- Are you sure you are getting an empty table? Maybe you mean an empty column? To be sure, aggregate functions will NOT cause you getting an empty table, but some of the relational operators will cause you to have an empty relation/table (e.g, JOIN, WHERE, MINUS, etc..). In any case, check first your query, as it might have a syntax error. And PHP (or the library you are using) might be returning an empty array when it encounters exceptions during db calls. I suggest that you first run the query I gave in an external program (say, phpmyadmin), and investigate the matter there first. Further, DO NOT at first "process" your query with variables. I suggest you copy/paste the code i've given "as is" to your existing PHP script, and check if it is working. If it is, then perform you modifications, add your PHP variables. Just a suggestion. ---------------- Regarding this: count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) When you need to COUNT the number of games, that will not work. Why not COUNT(DISTINCT all_games_id) instead? ------------- If you are getting an empty column, that is because you have NULLs in the home_goals and away_goals column. If you use SUM on those fields, the result will always be NULL, even if the same attribute(s) of other rows are not NULLs. You might want to use COALESCE on such cases. (Please see query above) Hope it helps. P.S. If the answer to my question at post http://www.phpfreaks.com/forums/index.php?topic=325737.msg1591582#msg1591582 are all "yes", I strongly suspect that fenway's methods are not anymore necessary and that your problem can be solved by the simpler query given above. Of course, I might be wrong.

Would do the same thing. The advantage of doing the deletion per table on a loop than by manually doing it, is that the whole functionality is automated. If in the future, a new table with the field(s) of your interest is introduced, you need not edit your script.