Joining multiple tables with grouping functions

[Ville Mattila]

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 get a list of all Invoices with total sum of the invoice and
paid sum of each invoices, as well as a customer name. I try following
query:

SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price)
AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal,
MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents
ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON
(Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON
(Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY
Invoices.ID;

The query works fine, but multiples the total paid sum with the number
of the matched InvoiceContents. If I remove the JOIN with
InvoiceContents, the query works fine (except I can't get the total sum
of the invoice).

How should I do the join to get proper results?

Thanks,
Ville

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Occasional permission problems

[Ville Mattila]

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 run the script again manually, no 
problems appear. There is no connection count limitations in the privilege 
table.

If you have any idea about reasons why this happens, I would be happy to hear 
any comments. =)

Thanks,
Ville


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Complex joining - multiple tables to one

[Ville Mattila]

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
- orderid
- productid
- quantity
shippeditems

- shippingid
- orderid
- productid
- shippedqty
Now I try to find out a list of orders, giving also the quantity of 
shipped and unshipped products in the list.

SELECT o.*,c.customername, SUM(oi.quantity) orderedTotal, 
SUM(si.shippedqty) shippedTotal
FROM orders o
LEFT JOIN customers c ON (c.id = o.customerid)
LEFT JOIN orderitems oi ON (oi.orderid = o.orderid)
LEFT JOIN shippeditems si ON (si.orderid = o.orderid)
  GROUP BY o.orderid ORDER BY o.orderid;

This query returns all other information correct but the SUM functions 
return too large numbers.

Why I'm not able to user normal JOIN, is that I need also order 
information in that case that no items are shipped.

Thanks for your tips!
Ville
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Strange join results

[Ville Mattila]

Hi there!
I have three tables:
1. Products
 - id
 - name
2. OutOrders (orders from customers to us)
 - id
 - productid
 - quantity
3. InOrders (our ourders to the traders)
 - id
 - productid
 - quantity
When I try to find out the current amount of products in our stock, and 
ordered quantities I use this query:

SELECT p.id, SUM(out.quantity) ordered_out, SUM(in.quantity) ordered_in 
FROM products p LEFT JOIN outorders out ON (out.productid = p.id) LEFT 
JOIN inorders (in.productid = p.id);

Even I have only one OutOrder for certain product, I get ordered_out 
value as 2. ordered_in value is anyway correct. If I remove all things 
related to InOrders I will get correct result to ordered_out.

Have I missed something? Isn't LEFT JOIN the correct query to join 
tables to the main table (in this case products)?

Thanks!
Ville
--
.
Ville Mattilagsm 040 849 7506
Pilotmedia  fax (03) 458 9080
Tapiolankatu 11   [EMAIL PROTECTED]
39500 Ikaalinen www.pilotmedia.fi
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Table name aliases in FULLTEXT and table locking

[Ville Mattila]

Hi there,
I have noticed a few things that cause problems when using table aliases 
(SELECT ... FROM table1 t1, table2 t2):

1) Fulltext index queries don't work. I tried to complete a following query:
a)
SELECT p.*, c.name AS categoryname FROM products p, categories c WHERE 
MATCH(p.name,p.description) AGAINST('keywords');

It will cause an error that there is no FULLTEXT index matching the query.
b)
SELECT products.*, categories.name AS categoryname FROM products, 
categories WHERE MATCH(products.name,products.description) 
AGAINST('keywords');

works anyway well.
2) Table name locking doesn't work with table aliases - or works, but 
eatch alias must be locked separately and the final queries must contain 
same aliases. Examples a and b cause both an error that tables are not 
locked. Examples c and d work well.

a)
LOCK TABLES products READ, categories READ;
SELECT p.*, c.name catname FROM products p, categories c WHERE (...);
b)
LOCK TABLES products READ, categories READ;
SELECT p.*, c.name catname FROM products p, products p2, categories c 
WHERE (...)

c)
LOCK TABLES products p READ, categories c READ;
SELECT p.*, c.name catname FROM products p, categories c WHERE (...);
d)
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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Old and new clients with MySQL 4.1.3

[Ville Mattila]

Hi there,
We have a following situation. Our MySQL server is version 4.1.3 and 
therefore it doesn't accept old clients as a default. I would like to 
use (and I do) the new client authentication system in most of the 
clients connected to the server. Anyway, now there is a situation that I 
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
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Repairing InnoDB table

[Ville Mattila]

Hello,

I have a database using a few InnoDB tables. The database has not been 
in use for a while and now I noticed that all InnoDB tables in the 
database cannot be opened and used at all. Whatever I try to do, I got 
Can't open asiakkaat.InnoDB (errno: 1) error. I'd like to know, how 
could I 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 PROTECTED]

Inserting a row with a value from another table

[Ville Mattila]

Hello readers,

I was wondering if there is a feature in MySQL that allows us to insert 
a row with only some values from another table. INSERT...SELECT syntax 
helps to insert multiple rows and it seems not to allow any constant values.

Let's clarify the case with an example.

I have a table of concerts and different pricing classes. The table 
structure of this price class table is like following:
- concertid
- code
- name
- price

Then there is another table having information of bookings. Its 
structire is following:
- id
- concertid
- priceclasscode
- price

Normally I would just make a relation query that joins a price from the 
price class table. But as there is a need to customize the price in some 
cases, I decided to make another price field to the booking table. When 
a new booking is made, I should copy the default price to the booking 
table. Now I'm looking a quick way 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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Selecting the latest entries

[Ville Mattila]

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 
specified cities. I mean, if I had three different types of reports for 
Helsinki and Turku, how can I get them all in one query?

One way is to make two queries: first to get the latest report time for 
each type/city...
SELECT MAX(time) FROM reports WHERE city IN ('Helsinki', 'Turku') AND 
type IN ('Observation', 'Forecast 1', 'Forecast 2') GROUP BY type, city;
... and then another query getting the report text for each time and 
city and type.

Anyway I find this not very good one and now wondering if there is any 
other way to make it in one query...

Thanks for any help. :)

Ville

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Join only the latest entry...

[Ville Mattila]

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 status by one query? I can do it of course by two different 
queries, but I don't find it as very good solution.

Thanks,
Ville
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Querying for continuous bookings

[Ville Mattila]

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 12:15:00
APP | 2003-09-30 12:15:00 | 2003-09-30 13:00:00
DEP | 2003-09-30 10:30:00 | 2003-09-30 13:30:00
...
Now I should make a query that, in some way, gives me an information of 
the positions that are booked without any pause for specified time. For 
example, 2003-09-30 11:00 - 2003-09-30 13:00 should return APP and DEP. 
Anyway, if I queried for period of 2003-09-30 10:30 - 2003-09-30 12:20, 
I should receive only DEP.

Any ideas how to build such a query?

Thanks for information,
Ville M.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Copying a row

[Ville Mattila]

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, but it's not very efficient way to do it (in my opinion) as when I
modify the table (add or remove columns), I should modify also all
queries.

Any help is appreciated.

Thanks,
Ville

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Counting rows with same information

[Ville Mattila]

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 CD's as well as a 
number of albums of each artist. Yes, I'd like to have that info with 
each row. So, the result would be something like that:
id   artist   album   albumsofartist
1   A.R.T   The best of A.R.T   3
2   ATB   Big Hits Collection   1
3   A.R.T   My Artistic Life   3
4   A.R.T   You are the only one   3

I've already tried this
SELECT DISTINCT mh.*, COUNT(mh2.id) AS albumsofartist FROM cd.mh, cd.mh2 
WHERE mh.artist= mh2.artist  GROUP BY mh2.artist

But it dosen't give correct results.

Thanks for info,
Ville M.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Counting question

[Ville Mattila]

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 COUNT(delivery='post') AS post, COUNT(delivery='pickup') AS
pickup... Or should I just make two separated queries?

Thanks,
Ville

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Join with no matches on other table

[Ville Mattila]

Hello,

I have two tables, other having information of cottages and other including
information when each cottage is booked. The table structures are following:

Cottages:
- code
- name
- equipment

Reservations:
- cottagecode
- begindate
- enddate

I'm looking for a query structure that I can use 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

[Ville Mattila]

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 another server -
but maybe there are also better ways to do it?

I have two database servers, the one is running inside a local network and
the other is running in the web. Because there are no web access to the
local network database server, I need to do frequent copies (automatically)
to the web to have some information in the website. This is why I'd need a
mirroring feature.

Any ideas?

- Ville


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

[Ville Mattila]

 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 following?

SELECT URL, Name FROM websites WHERE
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE CONCAT(URL,
'%');

- Ville


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Wrong mysql.sock

[Ville Mattila]

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 script, but I get following errors:

ERROR 2002: Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

This is my old data directory location. The new is /raid2/var/lib/mysql/ and
there is mysql.sock active.

How I should tell scripts that the mysql.sock file has moved to a new
location?

Thanks,
Ville


-
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-installing MySQL

[Ville Mattila]

Hello folks,

I'd need to re-install MySQL server on my RH 7.3 box. As I installed the
server from RPMs, I tried to update MySQL with rpm and -Uvh options. Results
were like this:

[root@pilotmedia root]# rpm -Uvh MySQL-3.23.51-1.i386.rpm
Preparing...###
[100%]
package MySQL-3.23.51-1 is already installed

OK Well, I decided to uninstall the installed package... but...

[root@pilotmedia root]# rpm -e  MySQL-3.23.51-1.i386.rpm
error: package MySQL-3.23.51-1.i386.rpm is not installed

So, any ideas what could I do? It's rather 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 [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Removing duplicate entries

[Ville Mattila]

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


-
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

SELECT-problem

[Ville Mattila]

Hi there,

This is my problem now... I have a table containing different paths, like this:

+--++---++
| Path | X  | Y | WaypointNr |
+--++---++
| P1   |  1 | 5 |  1 |
| P1   |  2 | 6 |  2 |
| P1   |  3 | 7 |  3 |
| P1   |  8 | 3 |  4 |
| P2   | 11 | 4 |  1 |
| P2   |  7 | 3 |  2 |
| P2   |  5 | 2 |  3 |
| P2   |  2 | 1 |  4 |
+--++---++


I need to draw a map from these paths, so I make a following query to get waypoints 
and paths located in defined area (where the corners are (3,2) and (7,6).

mysql SELECT * FROM waypoints WHERE X  2 AND X  8 AND Y  1 AND Y  7;
+--+---+---++
| Path | X | Y | WaypointNr |
+--+---+---++
| P2   | 7 | 3 |  2 |
| P2   | 5 | 2 |  3 |
+--+---+---++

That's OK... but now 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
Ikaalinen, Finland
[EMAIL PROTECTED]



-
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

Hierarchical trees in MySQL

[Ville Mattila]

Hi there,

I have heard that MySQL has some special functions or it's some other way capable to 
handle hierarchical forms in table. Let me give an example:

Table contains following rows:
IDNameParentID
1Main10
2Main20
3Main30
4Child1-11
5Child2-12
6Child2-1-15

Now I want to get the full path for some of the nodes, for example the node ID 6. The 
result should be following
IDNameParentID
2Main20
5Child2-12
6Child2-2-15

Or just a string Main2/Child2-1/Child2-2-1...

Any ideas? Are they just rumours 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


-
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

JOIN-problems

[Ville Mattila]

Hi again,

(Posting this once again because I didn't ever get this back from list server)

I have five tables in the database: customers, orders, ordercontents, products and 
payments. The problem is that I should get a list which shows some kind of a ledger 
list (shows who has paid and so on).

My first try looks like this:
SELECT orders.OrderID, customers.Name, SUM(ordercontents.Amount * products.Price) AS 
TotalSum, SUM(payments.PaidFIM) AS TotalPaid FROM orders
LEFT JOIN customers ON (customers.CustID = orders.CustID)
LEFT JOIN payments ON (payments.OrderID = orders.OrderID)
LEFT JOIN ordercontents ON (ordercontents.OrderID = orders.OrderID)
LEFT JOIN products ON (products.ProdID = ordercontents.ProdID)
WHERE orders.Cancelled=0 AND ordercontents.Removed=0;

That's it... I hope that you understood the structure. Also there should be noticed 
that the order can have many different 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




-
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: JOIN-problems

[Ville Mattila]

 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 BY OrderID was added to the end of query line. And so it had to be, just forgot 
it.

- Ville


-
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

JOIN-problems

[Ville Mattila]

Hi there,

I have five tables in the database: customers, orders, ordercontents, products and 
payments. The problem is that I should get a list which shows some kind of a ledger 
list (shows who has paid and so on).

My first try looks like this:
SELECT orders.OrderID, customers.Name, SUM(ordercontents.Amount * products.Price) AS 
TotalSum, SUM(payments.PaidFIM) AS TotalPaid FROM orders
LEFT JOIN customers ON (customers.CustID = orders.CustID)
LEFT JOIN payments ON (payments.OrderID = orders.OrderID)
LEFT JOIN ordercontents ON (ordercontents.OrderID = orders.OrderID)
LEFT JOIN products ON (products.ProdID = ordercontents.ProdID)
WHERE orders.Cancelled=0 AND ordercontents.Removed=0;

That's it... I hope that you understood the structure. Also there should be noticed 
that the order can have many different 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



-
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