Re: Append one table to another?
Hi Brian! How do I append one table's contents to another? Both have identical structure. Problem is I don't have shell access, only phpAdmin or a PHP file I write upload myself. You can do it this way: INSERT INTO table1 SELECT * FROM table2; If you need to have more control over what is copied, you can add a WHERE clause in the SELECT above. Also, you can restrict how it is done by adding some fields restrictions: INSERT INTO table1 (field1, field2) SELECT field1, field FROM table2 WHERE field1 = Some value Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Subquery bug or am I doing something wrong?
Hi Folks! I have a problem with a subquery using MySQL 4.1.12 (Fedora Core 4). I will include the SHOW CREATE TABLE for the two tables involved. Please, forgive me if I include this information in my original language (Spanish) but I wish to keep the field names as is to avoid any mistake. I don't know if this problem is a MySQL subquery bug or I'm doing something wrong. Anyway, what I'm doing here is pretty straighforward. First, the table information: -- SHOW CREATE TABLE FOR TABLE dominios_propios_completos CREATE TABLE `dominios_propios_completos` ( `criterio` char(100) NOT NULL default '', `idDominio` int(10) unsigned NOT NULL default '0', `idSite` int(10) unsigned default NULL, `fechaCreacion` datetime default NULL, `idIdioma` tinyint(3) unsigned default NULL, `estado` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`criterio`,`idDominio`), KEY `idSite` (`idSite`), KEY `fechaCreacion` (`fechaCreacion`), CONSTRAINT `FK_dominios_propios_completos_1` FOREIGN KEY (`idSite`) REFERENCES `sites` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED -- SHOW CREATE TABLE FOR TABLE sites_criterios CREATE TABLE `sites_criterios` ( `idSite` int(10) unsigned NOT NULL default '0', `criterio` varchar(100) NOT NULL default '', `criterio_pub` varchar(100) default NULL, `idIdioma` int(10) unsigned NOT NULL default '1', KEY `idSite` (`idSite`), KEY `idIdioma` (`idIdioma`), KEY `criterio` (`criterio`), KEY `criterio_pub` (`criterio_pub`), CONSTRAINT `sites_criterios_ibfk_1` FOREIGN KEY (`idSite`) REFERENCES `sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 OK, now for the problem. If you look at the above tables, they are somewhat related via dominios_propios_completos.criterio with sites_criterios.criterio_pub. In fact, what I am doing is trying to make dominios_propios_completos.criterio a constraint for criterio_pub on table sites_criterios. Now, look carefully: mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio = su-turno; +--++ | criterio | idSite | +--++ | su-turno | 4305 | +--++ 1 row in set (0.00 sec) mysql SELECT criterio_pub, idSite - FROM sites_criterios - WHERE criterio_pub = su-turno; Empty set (0.00 sec) In the above queries it's pretty clear the word su-turno exists in table dominios-propios-completos but NOT EXISTS in the table sites_criterios as a value of criterio_pub. The problem is that the following subquery return no results... Why??? mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.05 sec) Why is this? It's obvious (at least to me) that this query must return su-turno at least. It interesting to note that if I further restrict the WHERE clauses, I get the correct output!! Take a look at this, for instance: mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE idSite = 4305 AND - criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - WHERE idSite = 4305 - ); +++ | criterio | idSite | +++ ... cut ... | su-turno | 4305 | +++ 245 rows in set (0.00 sec) I don't know why this last query is working and the former, more general one, isn't. I have tried different combinations, like using DISTINCT at the subquery, etc. I begun thinking this is a bug because I get different results if I modify the WHERE clause in the inner subquery, like this: mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE idSite = 4305 AND - criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.01 sec) Note that this query is THE SAME as the previous one, but with a wider result set inside the subquery. This makes no difference in my queries as we confirmed at least the word su-turno does not exists. Thanks for your patience. If you think I'm doing something wrong please reply. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Subquery bug or am I doing something wrong?
Hi Pooly! mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.05 sec) Do you have NULL values in sites_criterios.criterio_pub ? Yes Pooly, certainly it had. Thanks for your comment!! This was the problem, again thank you very much. I am now very ashamed as this is a newbie mistake. I was fooled because I was pretty sure I had no NULL values, but you know... I'm not the only one inserting values into the table. :-) Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT on string field
Hi Arcangelo! Try this: SELECT COUNT(*), url, SUBSTRING_INDEX(url, ?, 1) AS page_name FROM sites GROUP BY page_name SUBSTRING_INDEX will return the leftmost (or rightmost) part of a string before the ocurrence of a delimiter (? in this case. See Documentation: Chapter String Functions). The trick here is to GROUP BY the result of this operation. Note that you could elaborate this further so that you could GROUP by the host name. You will need to anidate SUBSTRING_INDEX calls so that you end up with the desired data. For example: SELECT COUNT(*), url, SUBSTRING_INDEX(SUBSTRING_INDEX(url, ?, 1), /, 1) AS host_name FROM sites GROUP BY host_name This will group together the following URIs as one: - www.domain.com/pageone.php(There is no ?) - www.domain.com?id=2 (There is no /) Beware though, this usage will not use indexes on url column so be careful and do not use this on big tables. You will need to use more elaborated techniques to speed up query times on big tables. Cheers, Jose Miguel. - ORIGINAL MESSAGE --- I've a field with www.s.com/p.php?id=3 www.s.com/p.php?id=4 www.s.com/p.php?id=5 if a do a query with the atribute 'group by' i want that the system regroup all that is after the '?' is that possible? SELECT field WHERE all that is after the '?' GROUP BY all that is after the '?' the result should be: www.s.com/p.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: c program Bus Error (core dumped)
Hi Andy!
but I still get the Bus Error message with the following code...
int main(char **args) {
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL *connection, mysql;
int state;
int PORTNUM = 3306;
[...]
Andy, I have copied and pasted your code into a fresh new check.cpp file
and it worked for me (obviously changing the user and password data from the
connection string).
I am using Linux Red Hat 9.0 with 2.6.8 Kernel. This is what I used to
compile:
g++ -O3 -c -o check.o check.cpp
g++ -o check ppp.o -lmysqlclient_r -lz
It worked fine, maybe you should try to compile with debug options and
give gdb a try.
Cheers,
Jose Miguel.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: c program Bus Error (core dumped)
Hi Andy! After compiling with the options (as suggested by Jose Miguel Pérez) [...] I now get ... Everything Cool with the connection Bus Error (core dumped) Sound familiar!! Sorry Andy but I couldn't help any more. I am not familiar with Solaris, not even with 64 bit machines. The only thing I can think of is you are linking with a 32 bit compiled libmysql library, but... I don't know. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling 4.0.22 source including some 4.1.x functions
Hi all! I would like to know if it's possible to compile a custom 4.0.22 server including some functionality from 4.1.x source tree. The functionality I would like to include it's neither critical nor substantial. Basically, what I would like to include is the COMPRESS / UNCOMPRESS functions (which I suppose I can include them even as UDF functions). Please, anyone could give me advice on that? Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: COUNT Problem
Hi Shaun! A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; So if Project A has 3 tasks and Project B has 4 tasks I need the query to return 4. This question is very easy to answer, but I think it's very difficult to explain to you. For the first thing, you haven't added a GROUP BY clause, which is the first thing you should do to begin with. It's strange you didn't put an GROUP BY function, and if you don't know how it works, please read a book on SQL first. Given the tables you have, this query will do the work, without subqueries: SELECT P.Project_ID, COUNT(T.Project_ID) as HowMany FROM Projects P LEFT JOIN Tasks T USING(Project_ID) GROUP BY P.Project_ID ORDER BY HowMany DESC LIMIT 0,1 Column 1 (ProjecT_ID) is the Project ID with the most assigned tasks, and Column 2 (HowMany) lists how many taks are actually asigned. It is worth pointing out two notes about the above query: - The query will return 0 as HowMany and the Project_ID for the project which has no task assigned if this is the case. I mean, thanks to the LEFT JOIN, we take into account projects with no taks attached. (This is not possible with a straigh join like yours). - Thanks to the LIMIT clause, we get only the first result, which by the way is one of the projects with the most assigned tasks. If you want to further select which one of the projects with the most assigned tasks you want, you must order by another column, like date (or filter out the projects in the WHERE clause). Given said that, Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: COUNT Problem
Hi Shaun! I beg you pardon, my last message was incomplete! I will quote the last lines from my previous post: ---8- Cut here ---8--- - Thanks to the LIMIT clause, we get only the first result, which by the way is one of the projects with the most assigned tasks. If you want to further select which one of the projects with the most assigned tasks you want, you must order by another column, like date (or filter out the projects in the WHERE clause). Given said that, Cheers, Jose Miguel. ---8- Cut here ---8--- It was my intention to finish the message as: Given said that, if you want more information on how this select works, do not hesitate to ask. ;-) Again, I apologize for the confusion. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with group by
Hi Michael! Talking about the query with group by issue... I'll explain my reasoning below. [...] From the manual, section 7.2.8 How MySQL Optimizes LEFT JOIN and RIGHT JOIN http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimization.html: A LEFT JOIN B join_condition is implemented in MySQL as follows: ... * The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.) ... So, the WHERE c2.id IS NULL cannot be applied until after the rows which match the ON clause (and the NULL rows) have been fetched. Thanks for your explanations here, it's clear now I was confused. I missed this LEFT JOIN behaviour. I was in the idea the WHERE xx IS NULL was taken into account. [...] You are certainly right that temporary and filesort are to be avoided. And they will be, if the table is properly indexed. Single column indexing won't help much here, because the WHERE condition, the GROUP BY column, and the MAX column are all different. A multi-column index on (content, location, date), however, will allow mysql to use the index to find the matching rows, find the groups, and calculate the MAX date. I still refuse to use the temporary table solution, call me fussy here. Indeed, I think giving more indexes than necessary is a bit redundant, unless completely necessary. (You will end up having more space on indexes than data itself). It's a handle with care issue for me. Anyway, I don't know if one can program an agregate UDF called something like EXTERNAL_MAX(...) or something, so that we could do like: SELECT EXTERNAL_MAX(date, version) --- i.e: Returns the version value for the row with MAX(date). This, for sure, will be the best solution. ;-) That would have to do the same thing behind the scenes. I have to beg you pardon here. ;-) Think again this solution doesn't require a JOIN, nor a temporary table. I think the UDF solution doesn't have to be less efficient than a MAX or AVG aggregate function by itself. I'm in the process of creating such a monster :-) Let me know if you are curious about and have time to test it, I will test MAX() and EXTERNAL_MAX() against a very large table. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with group by
Hi Vincent! i'm trying to built a query that report me the latest tuple for a given field. I explain myself a bit. Let's say i have a table such this : There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause which is NOT on the GROUP BY clause. (Read chapter: 13.9.3 GROUP BY with Hidden Fields from the MySQL Documentation.). Anyway, MySQL is relaxed from ANSI SQL in such a way it's permitted to include a column which is not in the GROUP BY clause (hidden columns). The values returned from such a query are unpredictable. I started saying there is no _simple_ solution. However, you can think a little bit and your problem goes away. I can think of two solutions to your problem which neither involves a subquery. You are lucky in that you know for sure what your last value is (you do have a date field). You already know the value of using MAX(date) in the SELECT. You can concatenate the date column with the version, and return a SUBSTRING from that operation, something like this: SELECT SUBSTRING_INDEX(MAX(CONCAT(date, ' ', version)), ' ', -1) AS correct_version, location, version AS wrong_version FROM cities WHERE content='ALPHA' GROUP BY location You will get the following result set: (Notice the correct_version column). +-+--+---+ | correct_version | location | wrong_version | +-+--+---+ | 10 | NEW-YORK |11 | | 10 | PARIS|10 | | 11 | TOKYO|10 | +-+--+---+ There is another option, however, and this one is what I like most. In two words, you can join the table with itself, like this: SELECT c1.date, c1.location, c1.version FROM cities c1 LEFT JOIN cities c2 ON c1.location=c2.location AND c1.content=c2.content AND c2.datec1.date WHERE c2.id IS NULL AND c1.content = 'ALPHA' What we have done here is first LEFT JOIN the table (cities in my example) with itself so that we have in the left part those rows which have the maximum date and with NULL on the right as the value of c2. We then select those saying WHERE c2.id IS NULL. The last operation is selecting the rows for an 'ALPHA' content. Notice that with this version we also eliminate the GROUP BY, since all rows returned from the join are unique. The result set returned with the last example is this: ++--+-+ | date | location | version | ++--+-+ | 2004-09-16 | PARIS| 10 | | 2004-09-16 | NEW-YORK | 10 | | 2004-09-15 | TOKYO| 11 | ++--+-+ Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with group by
Hi Michael! Yes, you're right, thanks for extending and clarifying my message. However, I'm not confident about your comments on the inefficiency for the following query: SELECT c1.date, c1.location, c1.version FROM cities c1 LEFT JOIN cities c2 ON c1.location=c2.location AND c1.content=c2.content AND c2.datec1.date WHERE c2.id IS NULL AND c1.content = 'ALPHA' This will work, but it is also somewhat inefficient. The LEFT JOIN is creating numerous extra, unwanted rows, only to throw them away with the WHERE c2.id IS NULL. Assuming n rows for a particular location value, you [...] and then [...] The most efficient way is probably to use a temporary table. CREATE TEMPORARY TABLE max_dates SELECT location, MAX(date) AS max_date FROM temp WHERE content = 'ALPHA' GROUP BY location; SELECT t.* FROM temp t, max_dates m WHERE t.location = m.location AND t.date = m.max_date; DROP TABLE max_dates; I don't think the temporary table is such an efficient way of doing this. Pardon me, I'm provably wrong, but let me explain to see if I think correctly. First, I assume as true this table have an index on location, content and date, apart from the PK on ID. Given that, on my query we are using the keys at full, I mean, although you say the left join is creating numerous extra, unwanted rows, this is not true. We could apply the standard algebra here, but the real world query optimizers are smart enough to not retrieve unwanted data. (What about joining four or more tables! Multiply then). Your query is creating a temporary table, doing a full scan of it (thanks to the MAX(date) function), etc. If you do a EXPLAIN SELECT for your query, you'll notice there is an Extra of: Using where; Using temporary; Using FILESORT. Reading the MySQL documentation, one can see If you want to make your queries as fast as possible, you should look out for Extra values of Using filesort and Using temporary.. (Chapter 7.2.1 EXPLAIN Syntax). If I'm not wrong, maybe the first LEFT JOIN is worse from a mathematical point of view, but the temporary one may be is the worst from a practical perspective. And you'll see I'm very cautious because I'm not such a SQL guru, but I'd like to know other opinions. Anyway, I don't know if one can program an agregate UDF called something like EXTERNAL_MAX(...) or something, so that we could do like: SELECT EXTERNAL_MAX(date, version) --- i.e: Returns the version value for the row with MAX(date). This, for sure, will be the best solution. ;-) Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In search of a good MySQL GUI client
Karam Chand said: I use SQLyog. It does allow you to create/delete FKs with ON UPDATE criterias. Yeah, it does not allow you to create Fks in graphical manner like MS SQL Server but does my purpose. One feature I really miss is Editing of FKs. Yeah, I also tried SQLyog. It's a very good program indeed, a very nice feature is the structure syncronization tool. However, I said every program I tried has problems, and SQLyog is no exception. I downloaded SQLyog v3.71, I'm using MySQL 4.1.4-gamma-standard. When I manage relationships (F10 key), the FK's not even show up on the list... If I now press New.. to create a relationship, I can fill in the blanks and press Create, however SQLyog didn't show the new relationship either. I ended up creating 5 (five) equal relationships since SQLyog wasn't showing them in the list. A nasty GUI bug, sure, but this renders SQLyog unuseable to me. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In search of a good MySQL GUI client
On Thursday, September 09, 2004 - Karam Chand said: Probably you should put it as a bug in SQLyogs forums. I have put a bug request into the MySQL Query Browser bug track instead. I like this tool very much, it has some very nice features like the multithreaded results fetching. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
In search of a good MySQL GUI client
Hi All! Why are all MySQL GUI clients so bad with foreign keys? Sorry to seem annoyed, but I have used for quite some time a few GUI clients, notably MySQL-Front, which I were relatively confortable with. Anyway, all GUI clients I used (SQLyog, MySql-Front, MySQL-CC, you name it) seemed to be some sort of shortcut to the MySQL command line. No problem with that, that's what I am looking for and not some high-level CASE tool. It's when looking for foreign keys and referential integrity where these tools fail miserably. None of them fully supports editing / creating / deleting foreign keys. If you are to reply to this message, please read again the FULLY supports statement. For example, with version 1.0.7-beta of MySQL Query Browser (Own MySQL AB tool) I'm unable to add an ON UPDATE constraint... silly, huh? This is not a strange Fails when lib-foo v1.2.3.5.7 connects throu a CISCO 90238234 router in a full moon night error, it's simply that the programmer (nor the bug-testing labs) have not tried the On Update: drop-down menu from the edit table dialog. I know foreign keys are a relatively new area in the MySQL field and maybe the programmers are slowly adding support for it, but it's quite annoying to see monumental bugs editing and changing foreign keys in ALL programs I've tried. Please, note that this message is not intended to flame anyone. I fully aknowledge the effort involved creating such a tool, I'm a programmer after all, and some of them are in beta anyway. I would appreciate if anyone knows some tool which good foreign key support. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting using a table as reference.
Hi All! I have a problem deleting from a table and using another as reference. I use a query like: DELETE tableA FROM tableB WHERE tableA.idB = tableB.id AND tableA.data1=VALUE AND tableB.data2=VALUE (I'm using MySQL 4.0.12 on Red Hat 8 - Note: VALUE are integer values) What I want to do is delete some rows from tableA (which is a detail table for tableB) meeting some criteria (tableA.data1=VALUE) and with some value also in tableB. From the documentation, this is a correct DELETE statement, however, I get the following error: Not unique table/alias: 'tableA' As I understand, this error says that I cannot delete from tableA if I use it in the WHERE clause. But I need to list it in the where clause so that I can do the master/detail relationship. How can I do that? Please note that I use MySQL 4.0.12, so I cannot use the DELETE ... USING ... syntax which were added in 4.0.2 Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many records can a single MySql Table Hold.
Hi Rupak! We are using MySql version 3.27.53 on a Red Hat Linux platform version 7.2. For the past couple of months we are noticing that the performance of the server has gone down very badly. Every, single insertion is taking a hell lot of time.The particular table has only 150,000 records. Is there any remedy for this problem. I have been working with MySQL tables up to 16 million records, and the INSERTS was lightning fast (Making SELECTS was another history, but raw INSERTS worked fine). So I think your problem relates more to index structure, make sure you don't have more indexes than really needed. You can test with a temporal table without any keys (not even primary) and adding indexes until you get the behaviour described so that you can locate which is the problem. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nested ORDER BY statements
Hi David, Hello - I'm a relative newcomer to development with MySQL and am having a problem with ordering my query results... This is a general SQL question. Try this: ... ORDER BY time_created DESC, name LIMIT ... In general, you can't declare a clause twice, except for the JOIN clauses, of course. For example, you cant type ... WHERE blah, blah WHERE blah, blah too nor repeat a GROUP BY, SORT, etc. May I suggest you to read the manual more carefully? ;-) Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specifics on using join multiple tables
Hi Patrick! I need: acc.name, acc.phone, acc.acctno, sales1.amt as mo1sales, sales2.amt as mo2sales, (sales1.amt - sales2.amt) as diff SORT by diff I need all data in sales1 and sales2, but only for records from acc that are in either/both sales1, sales2. I tried: select from acc,sales1,sales2 where acc.acctno=sales1.acctno and acc.acctno=sales2.acctno I got only records which were in all three tables. I looked at using join, but can not determine which style of JOIN I need, nor how to write the JOIN statement. Can someone please help? The JOIN you're asking for is the LEFT JOIN. Let's compare straight JOIN and the LEFT JOIN SELECT * FROM a, b WHERE a.id=b.id -- * Will return records which are in both a and b tables. SELECT * FROM a LEFT JOIN b ON a.id=b.id -- * Will return ALL records from a, and those of b which are also in a. (Note the non-existence of a WHERE clause) Given that, we can start doing your query: 1) SELECT * FROM acc LEFT JOIN sales1 ON acc.acctno=sales1.acctno LEFT JOIN sales2 ON acc.acctno=sales2.acctno If you have this data: acc sales1 sales2 --- --- 11 1 22 3 3 4 The above query will return the following: acc.acctno sales1.acctno sales2.acctno --- -- - 11 1 22 NULL 3NULL3 4NULLNULL That is, ALL the rows from a, and either sales1.acctno or sales2.acctno. You don't clearly state if you want the row from acc.acctno ID 4 in the result, but in case you don't, let's modify our query. 2) SELECT * FROM acc LEFT JOIN sales1 ON acc.acctno=sales1.acctno LEFT JOIN sales2 ON acc.acctno=sales2.acctno WHERE sales1.acctno IS NOT NULL OR sales2.acctno IS NOT NULL You see, this WHERE clause is even human readable, we want the rows which sales1.acctno is not null OR sales2.acctno is not null. Anyway, you say you want to do a (sales1.amt - sales2.amt). As far as I can see, this is a calculation of the sales grow, that is, the difference between each month sales. BEWARE, every calculation in which NULL is involved, becomes NULL. For example: a) 1 - NULL = NULL b) NULL - 10 = NULL c) NULL * 2 = NULL You see, NULL is a killer. :-D You can get rid of this using some of the MySQL functions like IF_NULL. The effect of this behaviour is getting erroneous results where a customer don't have a sale in a particular month. Let's see: If sales1.amt=NULL and sales2.amt=1000 then (sales2.amt - sales1.amt) == NULL which is, obviously, erroneous for your pourposes of getting an increment of 1000 in the sales. I hope this helped! Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem connecting with sockets to MySQL
Hi all! I have asked this before, but no replys. I'll rewrite the question because I don't know if I got understood. ;-) Anyway, I have not found a MySQL list best suited for this question, so here I go... ¿Anyone knows why the server (mysqld) behaves differently to clients connecting from the same remote machine? (Server 3.23.41 on a RedHat 7.2. The client is Windows XP Professional.) In other words, say we are connecting from Host B to Server A. Well, I will list the possible responses I'm getting (remember, always speaking of low level socket connections). - telnet servera 3306-- Response OK. (First handshake packet received OK). - MySQLFront -- Response OK. (MySQLFront connects OK). - mysql -u user -h servera -- Response OK. (MySQL client connects OK). - nc servera 3306-- Response OK. (First handshake packet received OK). OK, at this point, you do think all is ok, don't you? not quite... - Connecting via a simple TCP socket coded in Borland C++ Builder 5.0: The first packet response is: 1130: Host 'hostb' isn't allowed to connect to this mysql server. I am not even allowed to reply with the packet that identifies my to the server. Pardon? What?? I'm not ALLOWED to connect to the server? As you can see, this is nonsense because I CAN connect to the server as seen above. I have not come to a conclusion on this topic, so all you can help is greatly appreciated. I go so far as to capture the packets with a packet sniffer (ethereal), and I got crazy: Everything seems fine, all connections generate the same packets, in the same order... But when connecting from my program, I get that 1130 error. Cheers, Jose Miguel. PS: Yes, I have done a mysqladmin flush-hosts everytime. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
