Re: MySQL Administration Tools
At 02:19 PM 1/22/2004, David Blomstrom wrote: I'm using a preconfigured package that includes PHPMyAdmin, which seems to be a pretty good program. However, I just read that similar programs are available, including MySQL Control Center, EMS MySQL Manager, urSAL, PremiumSoft MySQL Studio and MySQLGUI. I just wondered if there might be some advantage to having two or more such programs installed. Would working with MySQL through two different interfaces give you a different perspective, making it a little easier to understand? Or are there particular strengths each program has? Also, would different MySQL administration tools conflict with each other? If you created a new database with phpMyAdmin, would it be recognized by MSQL Control Center? Could you work on MySQL with both programs on at the same time? Thanks. I have both PHPMyAdmin MySQL Control Center Installed, and use both. Each program has a slightly different interface and method of access the databases, tables, and configuration information. Two big differencesall interfaces must be able to directly access the SQL Server. The thing about the PHPMyAdmin is that you install it on a web server that has access to the MySQL server. I have my SQL server behind a firewall and no one outside of the local LAN can access the SQL server. So when I am out of the office and need access to the SQL databases/server, I always use PHPMyAdmin. I have one machine with secure access setup running PHPMyAdmin that I access from the Internet, then use PHPMyAdmin on the Web Server to access and work on the mySQL Server. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Control Center!!!
At 12:42 PM 1/3/2004, Ugo Bellavance wrote: -Message d'origine- De : Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Envoyé : Saturday, January 03, 2004 9:24 AM À : [EMAIL PROTECTED] Objet : MySQL Control Center!!! Hello: I have been reading MySQL Control Center. There are screen shots but I have not been able to find documentation. Is the documentation is hidden or not available? I think that it is so straightforward that you don't need doc. Or if you need help, use the help in the program. There is no help in the program. The program is very straight forward. My theory is since the program is still in beta, the actual help will not be available till some one writes it, which won't be till after ver 1.0 production is released. If there is documentation, kindly direct me to the URL. Thanks. Kirti Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Started: Dreamweaver vs Passwords
At 02:57 PM 1/22/2004, David Blomstrom wrote: I recently installed a preconfigured package with Apache, PHP and MySQL from Apache Friends (XAMPP). It seems to be a pretty slick package, and I got all three programs up and running without too much trouble. Now I'm beginning to learn about MySQL. I finally got MySQL connected to Dreamweaver, but it was hardly a star effort. I read a tutorial at http://www.macromedia.com/support/dreamweaver/ts/documents/mysql_config.htm, but it's really confusing. Another problem is that this tutorial requires you to create a password for the root user. I did that twice - once from the Windows Command Prompt, then through phpMyAdmin. Each time, it knocked out phpMyAdmin, and I had to reinstall everything from scratch. So I'm finished with the root password. Password are a very funny issue. When adding users allowing access to databases table, you have to be very careful, as you found out, you can break things quickly. Here are a few items to help you... 1) after adding a new user, on the privileges page, try issuing a reload (very last line on the page has a link). You need to reload, flush, the users table to make users visible to the world. 2) when changing the password for root, make sure you have access to the phpMyAdmin program configuration file. You will need to set the new root password there, or configure phpMyAdmin to ask for the username/password. 3) DreamWeaver will access the mySQL using root with no password, but that is frowned on by all. Anyone would have full access to mySQL and be able to create havoc for you. 4) If an ISP is hosting you MySQL data, then you will be issued usernames/passwords to access MySQL, most ISP's do not provide you a dedicated MySQL Server with root access. Hope this helps some... In fact, I've been unable to create a password for any existing user. Every time I try to connect as any existing user, I get the error message Access Denied - Using a Password (No); or Using a Password (Yes), depending on whether I type in a fictitious password. I couldn't create a password until I created a new user, which I was finally able to connect to Dreamweaver. But if the root user controls everything, then will I eventually have to connect it to Dreamweaver? If so, is there a way to connect it without a password? I'm not administering MySQL for other people. At the moment, I'm just using it on my computer. My websites are hosted by an ISP. I don't yet understand how MySQL works online, but it sounds like ISP's have a sort of master account, which any accounts I create will operate within, right? So if I create three new users named One, Two and Three, and publish them online, the Root user will be whatever the ISP set up? My MySQL program is installed in the following folder: C:/xampp/mysql/ I put some screenshots from phpMyAdmin online at http://geowebworks.geobop.org/mysql/ and http://geowebworks.geobop.org/mysql/2.php Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shared Physical Database Question
As to the direct question of two servers accessing the same file via SAN, I don't know. But here is an option we are using. I have two Linux Servers, a Web Server and SQL server. Our web server resides on both the internal external networks (two nics), with some firewall software installed (IPTables). The outside network accesses the webserver and the webserver accesses the SQL server. Therefore no one on the outside can directly access the internal network the SQL server. Anyone using a SQL GUI interface or and scripting language would not have direct access to the SQL server. They would need to access the webserver which has programs on that allows limited in-direct access to the SQL server. Hope this helps. Patrick Shoaf IT Manager At 02:41 PM 1/19/2004, [EMAIL PROTECTED] wrote: Could someone please tell me if tyhe following is possible or if a solution accomplishing the same thing is available? I would like to build a database using two MySQL servers accessing the same physical file on a common Drive attached to each computer via a SAN. Can this be done or is there data integrity issues and database file locking issues?. Our main goal is to provide the data gathered on a secure network and allow it to be seen on an insecure network. I do understand that this can be accomplished using firewalls and other networking tools, but our management has been very firm in their insistence that a user has no direct network access to our internal/secure LAN. Thanks for any help anyone can provide. Matthew Harris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction support
At 09:37 PM 1/5/2004, Bryan Koschmann - GKT wrote: Hi, I'm trying to get a software designer to write us some software using MySQL as the database server (he currently requires MS SQL). It is all windows based software (written in VB). So far his arguments against it are this (not my words): -No explicit transactional support -MySQL is still buggy -MyODBC is buggy and not used in production environments -Only way to connect using ODBC is third party drivers that cost over half as much as MS SQL This is just for our current software, the new software he is bidding on says he would use .NET so that supposedely causes other problems. Now, I know there are a few discrepancies there but I just don't know enough to argue it. I * need* to use MySQL as the server because of cost reasons. I *WANT* to use MySQL because I don't care for MS choose not to run their products. If you can give me any information to help me argue this I would really appreciate it. Thanks, Bryan I have previously used Borland's Delphi for various programs I needed to write in the M$ environment. This program was very solid and had very good ODBC support. While it has been awhile since I used it (version 2.0, they are now at or beyond 6.0) I have had many individuals claim that the Borland programming languages/compilers are the best and most stable compilers running under M$ Windows. Borland has various Visual programming languages available. you might want to suggest your programming try one of Borland's programs over the MS programs, if you really don't care to use M$ products. When I am forced to use M$ platform, I use every means at my disposal to use non-M$ products to accomplish the job. I use Apache for Web Servers, Perl for Web Scripting, ColdFusion for dynamic web sites accessing various DBs, Bind for DNS, MySQL for all new DBs, etc. For those of us that prefer to not use M$, but are forced to use the OS, I always try my best to make sure I can at least program in something other than MS. Whenever I am forced to program using a M$ compiler, I always add a line to all agreements, that code broken is not the fault of the programmer, but rather the fault of M$ and their constant change in procedures, compilers, and bugs. I have never heard and programmer who uses M$ C Compiler ever say they have had there program work right and stay working after each and every patch, or upgrade of the M$ compiler and operating system. Most programmers who use M$ C compiler constantly complain they must write code that works around a bug in the compiler or OS, only to have to rewrite the code after M$ tries to fix their bug. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choosing between VARCHAR and TEXT
It is always best to set fields sizes as small as possible, to both conserve disk space, as well as improve performance. The smaller the record the faster the queries. While you do not want to make any field too small, you also don't want to make it too big. With current databases, you can easily change field size after designing without causing major problems. Old database languages did not allow you to change field sizes on the fly. You had to redesign the database, then write a program to upgrade the database, then modify your programs to tell it the changes in your database. Consider your output, typically when printing an address onto a label, you can only write approx 35 characters, unless you are using very large shipping labels. I usually define for addresses the following: add1 varchar(35) add2 varchar(35) city varchar(25) state char(2) zip varchar(10) Total space 107 characters max At 11:05 AM 11/21/2003, you wrote: Will any names or addresses exceed 255 characters? The documentation recommends to use the smallest column size possible. -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: Paul Fine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 21, 2003 7:34 AM Subject: Choosing between VARCHAR and TEXT Can anyone tell me what is better to use for items such as names and addresses? I suspect VARCHAR(max anticipated length) but why? Is it because Text will pad? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
I have several queries that are taking at least a full minute to process... From a web page, user submits parameters to a perl program which performs multiple queries, then spits the info out formatted for the web. Does anyone have a quick dirty method of locating a bottle neck slowing these queries down? I am hitting multiple tables within the same database, all through the same connection. Ie perform Q1, store results, perform Q2, store results, etc. I am still fairly new to MySQL. I am currently running 4.1 for some advanced features (GROUP_CONCAT). Any/all suggestions are helpful. Thanks Patrick Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
Try the following: SELECT ip_address, url, count(distinct ip_address) FROM tablename GROUP BY ip_address, url At 09:48 PM 10/21/2003, John Kelly wrote: : -Original Message- : From: John Kelly [mailto:[EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 3:45 PM : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Subject: Re: Need help constructing query ... : : : - Original Message - : From: Daniel Clark [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Cc: [EMAIL PROTECTED] : Sent: Tuesday, October 21, 2003 2:33 PM : Subject: Re: Need help constructing query ... : : : : Hi, I have a table full of logged urls and ip addresses. : The following : : query returns all the urls and the number of requests. How would I : : modify it to return unique requests based on distinct ip : addresses? : : : : select url, count(*) as pageviews from table group by url order by : : pageviews desc : : : : How about: : : : : SELECT ip_address, url, count(*) : : FROM tablename : : GROUP BY ip_adress, url : : : Thanks but I could not get that to work. It does not appear : to count the number of page requests by distinct IPs anyway : does it? Don't you need something like a : count(distinct(ip_address)) somewhere in there? : : -- - Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'John Kelly' [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 3:57 PM Subject: RE: Need help constructing query ... : Then I think you want : SELECT url, COUNT(DISTINCT ip_address) : FROM tablename : GROUP BY url; Thanks, this must be a resource intensive query as it works in a few seconds on a small table but takes 6+ minutes when done on a table with just 100,000 records. Anyway, thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird Query..need help
I have a single table that I need to pull info from can anyone help? Table: Product Fields: Itm_Code, Itm_Color, Itm_Size, Itm_Price, Itm_Desc, otherjunk Primary Key(Itm_Code,Itm_Color,Itm_Size) I am using ColdFusion MX as my programming language, CF does not permit queries within queries. Sample Data itm1,blue,small,4.00,Item Number 1, itm1,blue,med,5.00,Item Number 1, itm1,blue,lrg,6.00,Item Number 1, itm1,red,small,4.00,Item Number 1, itm1,red,med,6.00,Item Number 1, Givens: For any given Item_Code, desc is same Where more than 1 color is used, sizes Output desired: ItemCode,Color/Size,Price,desc Item1,blue:small;med;lrg|red:small;med,4.00;5.00;6.00;4.00;6.00,Item Number 1 Any help is greatly appreciated. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Q: How to display Search Results in a secure way?
I am not very familiar with php, but why are you using an echo within an
echo statement?
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
why not try this
echo td width=200$fnamenbsp;$lname/td;
echo td width=200$title/td;
echo tdinput type=\hidden\ name=\fid\ value=\$fid\;
echo input type=\submit\ name=\submit\ value=\Choose this
faculty\/td;
Also, most languages require you to escape when used within quotes.
At 01:32 PM 10/17/2003, Jordan Morgan wrote:
Hi,
I'm still learning on this. Pls. excuse me if I simply overlooked
something.
Originally I had this:
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200a
href=FacDetails.php?fid=$fid$fnamenbsp;$lname/a/tdtd
width=200$title/td;
echo /tr/table;
}
?
to list the search results and display them as a link which will take
the user to a detailed page. However, the fid has to show up as part of
the URL. I want to hide the fid as I don't want people to view the other
detailed records just by randomly entering the number for the fid. So I
tried to modify it and came up with the following:
form action=FacDetails.php4 method=post
?
// list matches
while (list($fid, $title, $lname, $fname) = mysql_fetch_row($result))
{
echo table border=2 width=400 cellpadding=2 cellspacing=2tr;
echo td width=200$fnamenbsp;$lname/tdtd
width=200$title/tdtdinput type=hidden name=fid value=? echo
$fid; ?input type=submit name=submit value=Choose this
faculty/td;
echo /tr/table;
}
?
/form
Basically I was trying to see if I can simply add a submit button to
every row so that fid will be invisible to the users. But it didn't
work. It kept telling me: Parse error: parse error, unexpected
T_STRING, expecting ',' or ';' in
/home/virtual/site482/fst/var/www/html/PHP/latest/SearchFaculty_Rslt.php4
on line 145 where line 145 is the line where the 2nd echo statement is
located as above.
I tried to move the input type=hidden..?echo $fid;? section up,
just below the form tag. Still same error for the same line 145.
My questions are:
1) What's wrong with my script? How can I fix that?
or
2) Is there another way to achieve my goal without using the Submit
button at all?
Thanks for reading this and all your help.
Jordan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patrick J. Shoaf, Systems Engineer
mailto:[EMAIL PROTECTED][EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
or888-638-6963
Fax: 724-489-4386
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect to 4.1 using MyOBDC
I believe the problem is in the ODBC drivers on MS Winx platforms. I have no problems accessing MySQL from MySQL Control Center or from perl or other programs NOT using ODBC. I have a MS FoxPro application using ODBC to connect to MS SQL Server via TCP/IP and MS FoxPro has problems connecting sometime to MS SQL. Given that problem, and my success accessing MySQL from any place with a natural (not ODBC) driver, I would look to ODBC first the MySQL second. At 01:57 PM 9/25/2003, Randy Chrismon wrote: Seems to be a problem with the fact that the server is on a Linux box. I was having this problem connecting the mysql command environment to the linux server until I added -protocol=TCP to the login statement. Or maybe it's just that we use a TCP/IP network (doesn't everybody?). Anyway, my test user is using Win2K, accessing the MySQL server which resides on a Redhat 9 linux box. We are on the same network domain inside the firewall. Essentially, I believe I need some way to tell the ODBC driver (and also the mysql command center and the command line) that the protcol is TCP. Is their an environment variable that would do this? This is starting to make me look bad... Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help writing query
I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help writing query
I loaded MySQL 4.1, but now the text is gibberish in the Group_Concat field SELECT Cat_Item_Img,Cat_Price,Product.Item_Code,GROUP_CONCAT(Item_Size SEPARATOR ;) as sizes,Item_Img, Description,Category,Retail_Price,Short_Desc,Product.Item_Color FROM Cat_Items,Product WHERE cat_code =BoltTech and Cat_Items.Item_Code = Product.Item_Code and Category =Casual Wear GROUP BY Product.Item_Code RESULTS: | Cat_Item_Img | Cat_Price | Item_Code | sizes | Item_Img | Description | Category| | | 19.00 | 288 | ?L.MYI | 288 | bPromenade/b Blended Knit Shirt | Casual Wear | | | 28.00 | 71080 | ?L.MYI | 71080| bLee Denim Shirt/bbr | Casual Wear | | | 24.00 | 77123 | ?L.MYI | 77123| bLee Golf Shirt/bbr | Casual Wear | The data should read S;M;L;XL;2XL;3XL not ?L.MYI (?L.MYI is translation when using copy/paste, there were other characters in sizes field.) Is this a 4.1 Bug? I loaded from the 4.1.0-0 Linux x86 RPM files. So far nothing else appears broken. Any suggestions? Patrick At 09:11 AM 9/18/2003, you wrote: Patrick Shoaf [EMAIL PROTECTED] wrote: I have a table name product defined as follows: Item_Code Item_Size Item_Color Item_img Description Cost Retail_Price Category and other non-essential items, such as qty based on code,size,color The data is as follows: j2400 S BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 M BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 L BLK j2400blk.jpgBlack Jacket12.00 24.00 Jacket j2400 4XL BLK j2400blk.jpgBlack Jacket18.00 36.00 Jacket p2400 S BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 M BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 L BLK p2400blk.jpgBlack Pants 16.00 32.00 Pants p2400 4XL BLK p2400blk.jpgBlack Pants 24.00 48.00 Pants I need the result to show: j2400 S,M,L,4XL BLK j2400blk.jpgBlack Jacket12,12,12,18 p2400 S,M,L,4XL BLK p2400blk.jpgBlack Pants 16,16,16,24 How would you write this query? I used initially SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product WHERE Category=Jacket or Category=Pants GROUP BY Item_code This resulted in only retrieving the first item in the list for size price info. There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOINs
At 03:18 PM 8/19/2003, you wrote: I may be trying to do too much in a single query here, but it would be nice if I could get it working! Apparently, the version of MySQL used by my work does not support the WITH ROLLUP feature, otherwise I think that would work. What I have are 3 tables: gc_info, amends, payments. 'gc_info' contains a number that represents the original award given to a contractor. If there are any increases, these are kept in 'amends', and all payments are kept in 'payments'. What I need to do is get three numbers: current total award (award+amendments), current total paid (SUM(payments)), and current balance ((awards + SUM(amendments)) - SUM(payments)). This almost works with the below query, except that the where there should only be 1 amendment, 11 more show up, or one for each of the 12 payments made to the contractor: SELECT org_name,FORMAT(award+amend,2) AS current_total, FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, FORMAT(SUM(paid),2) AS total_paid FROM contact_info LEFT JOIN gc_info ON contact_info.id=gc_info.contact_id LEFT JOIN amends ON gc_info.gc_number=amends.gc_number LEFT JOIN payments ON gc_info.gc_number = payments.gc_number WHERE gc_info.gc_number = 06-8479 GROUP BY gc_info.gc_number For example, on a contract with an original award of 168,000, one amendment of 168,000, and 12 payments of 14,000, this gives me: |org_name|current_total |balance |total_paid | |Org Name|336,000.00 (correct)|2,016,000.00 (incorrect)|168,000.00 (correct)| So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is adding up 12 instances of amend instead of 1. Any advice would be helpful. I've read through the JOIN section in the MySQL manual, tried different JOINs, GROUP BYs, etc. but cannot figure this one out. Thank you very much, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Try the following (not sure why, but this is how I set my queries up) SELECT org_name,FORMAT(award+amend,2) AS current_total, FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, FORMAT(SUM(paid),2) AS total_paid FROM contact_info LEFT JOIN gc_info ON contact_info.id=gc_info.contact_id LEFT JOIN amends ON contact_info.gc_number=amends.gc_number LEFT JOIN payments ON contact_info.gc_number = payments.gc_number WHERE contact_info.gc_number = 06-8479 GROUP BY contact_info.gc_number I always use the same field (from 1st table) when I do JOIN / GROUP BY / WHERE / etc. This way I know I should always receive the same results. I can't guarantee this, but try it Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Try SELECT * FROM contact INNER JOIN contact_account ON contact.contact_id=contact_account.contact_id WHERE contact_account.account_id = 13 At 04:17 PM 8/19/2003, Jack Lauman wrote: I have two tables, contact_account and contact. contact_account has two fields: contact_id (pk) and account_id contact has a PK of contact_id I to select all the columns in contact where account_id=13 I tried: SELECT * FROM (contacts INNER JOIN account on contact.contact_id = account.account_id) WHERE account.account_id = 13; One I get the query to work right I neet to write it to a file in CSV format. Any help would be appreciated. Thanks, Jack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CF MySQL
I am running the current version which is labeled ColdFusion MX. According to Macromedia, you need a Pentium Processor, 128M RAM Minimum, 256M RAM recommended, and 512M RAM preferred, and 350M id HD space. I was running MySQL, Apache standard secure, Sendmail for 25 people, ColdFusion MX, and a Firewall on a Celeron 400 with 384M Ram and slower IDE HDs. This configuration did noticeably slow down the server. After removing all large MySQL databases, leaving the server running did improve performance. Loading ColdFusion onto a Xeon 800 box w/Raid 5 SCSI, 512M RAM, running multiple HighHit Web Sites, with MySQL running some small databases, has not slowed performance at all. Personally, I am not sure which component caused the biggest slow down, CPU, Ram, or HD. My guess is RAM, then CPU, then HD. Personally, I have found, it best to have any SQL server running on the fastest box, with the most RAM, and your WebServer and CF on a 2nd box. This will give you the best performance without having to buy 2,4 or 8way processor boxes. Patrick At 03:58 PM 7/15/2003, William R. Mussatto wrote: I am currently running ColdFusion MX on my Linux Servers access both MySQL from different Linux Servers and MS SQL from a MS2000 WS. ColdFusion and MySQL are both available and run on both Win Linux machines. I also have PHP installed on Linux, but have never attempted to learn fully utilize PHP. ColdFusion is capable of access many DB programs. You simply need to tell CF how and where to access the Data. At 12:01 PM 7/15/2003, Andrew wrote: Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 If you don't mind me asking, which version of the server are you using on Linux. We have been asked to set such a system up and are looking for background. Also, does CF put much of a load on the server. We are running Debian Stable. (woody). Thanks for any info. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CF MySQL
I am currently running ColdFusion MX on my Linux Servers access both MySQL from different Linux Servers and MS SQL from a MS2000 WS. ColdFusion and MySQL are both available and run on both Win Linux machines. I also have PHP installed on Linux, but have never attempted to learn fully utilize PHP. ColdFusion is capable of access many DB programs. You simply need to tell CF how and where to access the Data. At 12:01 PM 7/15/2003, Andrew wrote: Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query across multiple tables
I have four tables I need to query for information; acc,accmemo,aard,sdtik where acc contains basic information about a customer (1 rec per acctno) accmemo contains multiple Account Memo messages (0 or more recs per acctno) aard contains credit card(s) information (0 or more recs per acctno) sdtik contains customer sales information (0 or more recs per acctno) all tables have acctno as a key I need to select the following information: acc.strref, acc.acctno, acc.namelast, acc.namefirst, acc.adddate, accmemo.memo, aard.credcardtype, aard.credcardnum, count(distinct(sdtik.datein)) as visits, sum(if(sdtik.voidreason0,0,sdtik.amt)) as sales I need the following conditions met: 1) all accts where strref=1 2) all accmemo.memo fields, if any, for each acct 3) all credcardtype credcardnum from aard, if any, for each acct 4) count sum from sdtik to be only records where datein=20020701 Is there anyway to right this query? I am still fairly new in writing queries. At present, I am using perl to do this in multiple steps and queries. 1) select info from acc 2) for each acctno, query accmemo, 3) for each acctno, query aard, 4) for each acctno, query sdtik, 5) display information. I know that 1 single query is more efficient than looping and processing 3 additional queries per acctno. Any/All help will be greatly appreciated. Thanks in advance. Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select not producing desired results
I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddatefsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select not producing desired results
Thanks, worked perfectly! At 04:17 PM 7/9/2003, gerald_clark wrote: fsttik has dashes in it and your having does not. Either add dashes to your having or change the alias to min(datein+0) as fsttik. Patrick Shoaf wrote: I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddate fsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replacing data in 1 table from another
I tired both statements you suggested. The update is what I would like to use, but I got an error, ERROR 1064: You have an error in your SQL syntax near ' sdtik set sales.sales ' I used the REPLACE command, but it had the effect of adding records for customers not in the statistics file. Any suggestions on how to correct the UPDATE statement? Patrick At 05:14 PM 6/16/2003, Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2003-06-16 15:15:31 -0400: I have two tables... Table 1 newdata contains a lot of statistical data on our customers, including last 12months sales amt. table 2 sdtik contains all ticket information for all customers. Both tables have acctno as primary key. I can use: SELECT sum(if(voidreason0,0,amt)) as sales FROM sdtik where acctno=1234 and datein=20020615 to gather customers' sales totals. How can I setup a single query where the newdata.sales would be replaced with the summarized data from the select. you need UPDATE or REPLACE, together with GROUP BY http://www.mysql.com/doc/en/REPLACE.html http://www.mysql.com/doc/en/UPDATE.html http://www.mysql.com/doc/en/SELECT.html REPLACE sales (acctno, sales) SELECT acctno, SUM(IF(voidreason 0, 0, amt)) FROM sdtik GROUP BY acctno or UPDATE sales, sdtik SET sales.sales = SUM(IF(voidreason 0, 0, sdtik.amt)) WHERE sales.acctno = sdtik.acctno -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID hardware suggestions/experience
I am using 4 120G IDE Drives with an Adaptec IDE RAID Controller on RedHat Linux providing 240G of RAID 5 storage. While not quite as fast as SCSI, I have found this to work very well. You should be able to pickup a nice dual processor XENON 2.4Ghz system w/1G Ram and IDE RAID loaded with RedHat Linux ES for around $4,000. At 12:25 PM 6/17/2003, you wrote: Hi there, Our databank with all tables and idices is about 130GB big. The biggest limitations we encounter are on the I/O side. Therefore we are willing to update our data storage system to a RAID system (RAID 0+1, RAID 5, or RAID 10). Has anyone experience with such RAID systems? What should we buy? From whom should we buy (We are located in New York City)? Do you have any experience you want to share? Thank you very much for your help and support! Bernd Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replacing data in 1 table from another
I have two tables... Table 1 newdata contains a lot of statistical data on our customers, including last 12months sales amt. table 2 sdtik contains all ticket information for all customers. Both tables have acctno as primary key. I can use: SELECT sum(if(voidreason0,0,amt)) as sales FROM sdtik where acctno=1234 and datein=20020615 to gather customers' sales totals. How can I setup a single query where the newdata.sales would be replaced with the summarized data from the select. All help is greatly appreciated Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Loading Date DATA
I have a question, when loading dates into mysql I have found I can always use MMDD or -MM-DD, but frequently I need to load data from ASCII csv files where the date is M/D/. Currently, I am using perl to read the datafile and insert each record after converting the date data. I would prefer to be able to use 'LOAD FROM INFILE', but the dates do not convert correctly. Is there any way to use the data in M/D/ format? Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Specifics on using join multiple tables
I am fairly new to MySQL and SQL in general. I have three tables, acc,sales1,sales2. All have acctno in common. acc is general customer table with lots of information. sales1 is a generated table of sales for specific customer from a given month/year. sales2 is the sale as sales1 except for a different month/year. 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? Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- 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
At 09:48 AM 6/4/2003, you wrote: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2003-06-04 09:17:01 -0400: I need all data in sales1 and sales2, but only for records from acc that are in either/both sales1, sales2. SELECT acc.name, acc.phone, acc.acctno, sales1.amt AS mo1sales, sales2.amt AS mo2sales, (sales1.amt - sales2.amt) AS diff FROM acc LEFT JOIN sales1 USING acctno LEFT JOIN sales2 USING acctno ORDER BY diff To make sure that the rows exist in either sales1 or sales2, include a WHERE (sales1.acctno IS NOT NULL OR sales2.acctno IS NOT NULL). Also, I suspect that sales1 and sales2 should really be a single 'sales' table with an extra column indicating which month the sales are for (and maybe one for year as well), but I don't have enough information to be sure. Bruce Feist Table sales1 sales2 are tables generated from a larger table containing way too much information. Tables Sales1 contains only sales tickets summarized by day for month 1, and Sales2 contains same info for month 2. I needed to generate a sales comparison report for 1 store with two different months. Since I did not know how to retrieve in 1 select, I generated three, first pull sales info for month1 to sales1, then pull sales info for month 2 to sales2, then try and combine these to show customers sales and difference between the two months. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
