Joining multiple tables with grouping functions

Hello all, I'm sure that this situation is one of the most wondered questions with JOIN clauses. Anyway, I couldn't find any clear information how to carry out multiple joins in one query with proper results. I have four tables: 1. Invoices 2. InvoiceContents 3. Customers 4. Payments I try to

Occasional permission problems

Hi list readers, I'm experiencing some strange problems with MySQL 4.1.14. I have a script that is run as a cronjob in five minute intervals. The script initiates a database connection and executes certain SQL commands. Sometimes, but not regularly, the server gives access denied error. If I

Complex joining - multiple tables to one

Hi there! I have some problems with the complex MySQL join operations. In most cases, LEFT JOIN fulfills my needs but sometimes it doesn't work in the expected way. I'll give an example: orders -- - orderid - customerid customers - - id - customername orderitems -- - itemid

Strange join results

-- . Ville Mattilagsm 040 849 7506 Pilotmedia fax (03) 458 9080 Tapiolankatu 11 [EMAIL PROTECTED] 39500 Ikaalinen www.pilotmedia.fi . -- MySQL

Table name aliases in FULLTEXT and table locking

) LOCK TABLES products p1 READ, products p2 READ, categories c READ; SELECT p.*, c.name catname FROM products p, products p2, categories c WHERE (...) Thank you - just to write down my experiences to the history and mail archives. =) Ville Mattila -- MySQL General Mailing List For list archives

Old and new clients with MySQL 4.1.3

should also connect an old client to the same server. The old client cannot be updated at this moment. Is there any workaround that certain users could connect with the old authentication system, when the default would still be the new method? Thank you, Ville Mattila -- MySQL General Mailing List

Repairing InnoDB table

repair the tables and get even the structure copied. I took a look to the data files and there are .frm files for the tables available. Thank you for help, Ville Mattila -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL

Inserting a row with a value from another table

to it. Like this: INSERT INTO bookings SET concertid = 1, priceclasscode=A, price = (SELECT price FROM priceclasses WHERE concertid = 1 AND code=A); Any possibilities? Or should I just make two queries in my application? Thanks for tips and trics! =) Ville Mattila Ikaalinen, Finland -- MySQL

Selecting the latest entries

Hi there, This subject might be discussed before, but I couldn't find any mail from the archives. I have a table containing weather reports of different types and cities. The structure is following: - type - city - time - report Which kind of query should I use to select the latest reports of

Join only the latest entry...

Hello there, I have a table including information about my projects, the structure has each id and name. Then I have another table including status information of each project: entryid, projectid, status and timestamp. Is there any possibility to fetch a list of projects with the most recent

Querying for continuous bookings

Hi there, My problem at this time is following: I have a table of position bookings, having information of a position code, beginning time of the booking and end time: Pos | Begings | Ends APP | 2003-09-30 11:00:00 | 2003-09-30

Copying a row

Hi everyone, Is there any easy way to duplicate a row in a table with auto increment column, that shouldn't of course be copied. I tried a query NSERT INTO table SELECT * FROM table but it caused an error due to the auto increment column. I succeeded with listing all required fields in the query,

Counting rows with same information

Hi there, I have a table containing my CD collection information, the simplified structure is following: id artist album 1 A.R.T The best of A.R.T 2 ATB Big Hits Collection 3 A.R.T My Artistic Life 4 A.R.T You are the only one How can I build up a query that will contain all

Counting question

Hi there, We are currently having a large festival here in Ikaalinen, Finland (www.satahamesoi.fi) and we have our booking system based on MySQL. I should find out how many tickets are sold in each concert with different delivery methods. How could I make a count like this: SELECT

Join with no matches on other table

to find for example cottages that are free on 15. - 16. July. Any help? Thank you, Ville Mattila Ikaalinen, Finland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Mirroring a table

Hello everyone, I was wondering how could I make a mirror copy of a table to another server in the easiest way? I browsed through the MySQL documentation but didn't find any clue of a COPY TABLE command or a similar method. I know that I can make a dump from a table and then run that dump file in

RE: Using LIKE to search for occurence of a column value in a string

I tried to use LIKE: SELECT URL, Name FROM websites WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE (URL + '%'); But this doesn't return any results. I would like the following as output: 'http://www.microsoft.com/kb/'Microsoft Knowledgebase Hi! How about the

Wrong mysql.sock

Hi there, I'm upgrading my old MySQL 3.2 to a new MySQL 4.0 and at the same time I decided to change the location of my data directory. Everything went fine until I begin to do things mentioned at http://www.mysql.com/doc/en/Upgrading-from-3.23.html. I tried to run mysql_fix_privilege_tables

Re-installing MySQL

interesting situation... Thanks, Ville Mattila Ikaalinen, Finland - 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

Removing duplicate entries

Hello, I was wondering how would it be possible to delete those records from database which has just same data? There can be also up to ten copies of the record in database, and all but one should be deleted. Do you have any easy way to do this? Thank you, Ville Mattila

SELECT-problem

I would like to get also those waypoints which ones are next to these results (on the same path). In this case, I want also points 1 and 4 on P2. How? Emm... Hope that you could understand even something. ;) - Ville . Ville Mattila

Hierarchical trees in MySQL

that this cannot be made with MySQL without any accessories, PHP etc? Emm... Hope that you understood. :) Cheers, - Ville . Ville Mattila Ikaalinen, Finland Ei ne heinäsirkat ole minnekään kadonneet, ovat lentämässä. - Jaakko Kuusisto

JOIN-problems

products ordered (listed on ordercontents -table) and also that there can be many separated payments for same order. Now this query works, but it doesn't return correct amounts for TotalPaid and TotalSum -columns. Thanks for help, - Ville Mattila, Ikaalinen, Finland

Re: JOIN-problems

Sir, your query doesn't work on my machine. I either have to GROUP BY orderID, or drop orderID and Name from the SELECT clause. I'm not sure what you're trying to do, so I don't know which to suggest. Oh, sorry, I didn't check out what did I wrote... ;) Anyway, I got this work after GROUP

JOIN-problems

be many separated payments for same order. Now this query works, but it doesn't return correct amounts for TotalPaid and TotalSum -columns. Thanks for help, - Ville Mattila, Ikaalinen, Finland - Before posting, please check