Re: Join syntax
I don't know what you are trying to accomplish but here is the way I would build a table using ALL the data field from two tables. first is a straight join which will only build the records that have an equal in both tables. insert into tbla select distinct tblb.*, tblc.* from tblb, tblc where tblb.flda = tblc.flda the second will build records for every record in tblb and will include data from tblc when there is a corresponding record in tblc. When there is no corresponding record in tblc the tblc data fields will be set to null. insert into tbla select distinct tblb.*, tblc.* from tblb left join tblc on tblb.flda = tblc.flda hope this helps. Jack :-)= - Original Message - From: "Willie Klein" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 06, 2001 2:20 PM Subject: Join syntax Hi all: I think what I want to do is join tables in a select statement but I'm not having any luck. I'm using MySQL 3.22 PHP and apache on RH6.2 I have an application that has multiple tables that customers upload order data to. People who look at this data can see their orders from each table individually. Now they want to download their data from each of these tables into their own database. When I do a select on my test table like: Select * into outfile /home/temp/test.txt from apple where RepID = NYS I get a file that is 150K. When I do a select on 2 of my test tables(which are identical)like: Select * into outfile /home/temp/test.txt from apple as t1, bass as t2 where t1.RepID = 'NYS' and t2.RepID='NYS' I get a file that is 116MB with each record being repeated 866 times. When I do a left join it does about the same thing. I'm going to want to do this with more than 2 tables so is my approach to this wrong or just my syntax. I've read the manual page on join and (I admit it) I don't understand it. Thanks for your help willie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do you format this query?
If all you need is a count of the items why not issue Select count(distinct threads) as ctr from messages Jack ;-)= - Original Message - From: "Ed Lazor" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 28, 2001 11:53 PM Subject: how do you format this query? Hi =) Could you help me with creating a proper query? I have a table that stores messages with a field for the message id, thread, and parent. The goal is to create a query that gives me the total threads. I read in a book and found the DISTINCT key allows me to run a query like this: select DISTINCT threads from messages; Then all I have to do is count the total number of rows returned. I'm concerned this query will create unnecessary overhead as the number of messages in the archive exceeds tens of thousands. Is there a query that will give me the information I'm looking for without creating the overhead? -Ed - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP!!
elaine; Put the escape character in front of it (/') jack :-)= - Original Message - From: "Elaine Silva" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 21, 2001 6:17 PM Subject: HELP!! Please, How can i insert the "'" character (like "sbie'2000") in a database using the INSERT command? tanks, Elaine. Don't E-Mail, ZipMail! http://www.zipmail.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can anyone do this ?
Hi: the way I handle this situation is: "create temporary table if not exists tblName (somefld I need, etc.) select iptable.* (or field names) from myTable where bluemoon = yes" Works every time for me. You need to know that temporary tables are only available to the creating user during the session in which it is created (you can't find it with freemascon or any other tool that I am aware of) after the session is completed the temporary table is poofware. hope this helps. Jack :-)= - Original Message - From: "Web Depressed" [EMAIL PROTECTED] To: "Bob Hall" [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 14, 2001 4:46 PM Subject: Re: Can anyone do this ? Hi Bob/ All, I cannot get the creation of the TEMPORARY Table to work: The select statement works fine: mysql SELECT table1_id, Count(date) as c FROM Table2, Table1 - WHERE Table2.date BETWEEN "2001-02-02" AND "2001-02-06" - AND Table2.table1_id=Table1.id - GROUP BY Table1.id HAVING c=3; +---+---+ | table1_id | c | +---+---+ | 1 | 3 | +---+---+ 1 row in set (0.02 sec) mysql CREATE TEMPORARY TABLE counts - SELECT table1_id, Count(date) as c FROM Table2, Table1 - WHERE Table2.date BETWEEN "2001-02-02" AND "2001-02-06" - AND Table2.table1_id=Table1.id - GROUP BY Table1.id HAVING c=3; If I manually create this counts table and work with that I do get the desired output: mysql CREATE TABLE count (table1_id VARCHAR(10), c VARCHAR(10)); Query OK, 0 rows affected (0.06 sec) mysql insert into count VALUES (1,3); Query OK, 1 row affected (0.01 sec) mysql select * from count; +---+--+ | table1_id | c| +---+--+ | 1 | 3| +---+--+ 1 row in set (0.00 sec) mysql SELECT Table1.*, Table2.* - FROM Table1, Table2, count - WHERE Table1.id=Table2.table1_id AND Table1.id = count.table1_id - AND Table2.date BETWEEN "2001-02-04" AND "2001-02-06"; ++---++-+---+ | id | Item | date | no_of_items | table1_id | ++---++-+---+ | 1 | Item1 | 2001-02-04 | 2 | 1 | | 1 | Item1 | 2001-02-05 | 2 | 1 | | 1 | Item1 | 2001-02-06 | 2 | 1 | ++---++-+---+ 3 rows in set (0.02 sec) Do you have any idea where I'm going wrong ? Kind Regards, -- Frank Sir, create a variable day_count with the count of days in the user-supplied range. Then set up the following temp table. CREATE TEMPORARY TABLE counts SELECT table1_id, Count(DISTINCT date_field) as cnt WHERE data_field BETWEEN min AND max GROUP BY id HAVING cnt = day_count; Now you have a table with the IDs of items that occur at least once each day in the user-supplied range. You don't need the DISTINCT if each item can only have one record per day. Now some inner joins should get the results you want. SELECT table1.*, table2.* FROM table1, table2, counts WHERE table1.id = table2.table1_id AND table1.id = counts.table1_id AND date_field BETWEEN min AND max; I haven't actually run this, which means that there's probably a mistake or three. Bob Hall __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
