Re: Getting data from 2 tables if records have same date!
On 2/29/2012 5:54 PM, LUCi5R wrote: JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Try this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12 This will give you a list of all customers for a given date and a list of every call they made on that date. If a customer made no calls on a date, then all of the columns for that table will be NULL. If you only want a list of customers and details about the calls on a date then an INNER JOIN is appropriate. If you want to see the full list of customers and any calls on that date use this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 If you only want a list of customers that made any calls on a given date, you can use the EXISTS comparator like this: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12) http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html It's possible to get you any combination of data you want, we just need you to clarify the relationship you are trying to find and how much data you really want to get back. NOTE: the name of the column date is using a reserved word. You may want to enclose it in backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date literals uses ISO notation. So instead of using 02/28/12 (using double quotes) I expected to see '2012-02-28' (using single quotes) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green shawn.l.gr...@oracle.comwrote: On 2/29/2012 5:54 PM, LUCi5R wrote: JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Try this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12 This will give you a list of all customers for a given date and a list of every call they made on that date. If a customer made no calls on a date, then all of the columns for that table will be NULL. If you only want a list of customers and details about the calls on a date then an INNER JOIN is appropriate. If you want to see the full list of customers and any calls on that date use this: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 If you only want a list of customers that made any calls on a given date, you can use the EXISTS comparator like this: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12) http://dev.mysql.com/doc/**refman/5.5/en/exists-and-not-** exists-subqueries.htmlhttp://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html It's possible to get you any combination of data you want, we just need you to clarify the relationship you are trying to find and how much data you really want to get back. NOTE: the name of the column date is using a reserved word. You may want to enclose it in backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date literals uses ISO notation. So instead of using 02/28/12 (using double quotes) I expected to see '2012-02-28' (using single quotes) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN Another gem from Shawn. ;-)
RE: Getting data from 2 tables if records have same date!
JW, Youre correct .. that is what I was getting with the LEFT JOIN and therefore it wasnt the correct answer. I was able to get the correct answer using UNION ALL, however, like you said, I needed 2 queries in that case. One to get the PHONE numbers from both tables and a 2nd query to get all the CUSTOMERS matching the PHONE numbers from the CUSTOMERS table. Although I still have a few issues with that (since I cant use a VIEW cause VIEWS dont allow subqueries in SELECT statements; and I dont really want to create a new table every time this query is run) but anyhow, this is the UNION ALL query that got the correct 86 records result: SELECT PHONE FROM ( (SELECT PHONE,DATE FROM CUSTOMERS) UNION ALL (SELECT PHONE,DATE FROM CALLS) ) results WHERE DATE = 02/28/12 GROUP BY PHONE; I would still like to explore the possibility of doing this using 1 single query which gives me the results I need rather than first getting the correct PHONE numbers; inserting them in a temporary table; and then pulling records out of CUSTOMERS matching those PHONE numbers. THANKS! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 3:15 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! After looking at this again, the query you are using; SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Would return all customers in the customer's table created on 2/28/12 WITH no calls or a call on 2/28. Maybe you should try.. SELECT * FROM CUSTOMERS WHERE CUSTOMERS.DATE = 02/28/12 UNION SELECT * FROM CUSTOMERS INNER JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CALLS.DATE = 02/28/12 Basically, you have to do two queries to get the data you want. The WHERE customers.date=2/28 is only getting those customers created on 2/28 regardless if they had a call or not on 2/28. So if you had a customer created on 2/27 and a call on 2/28, the query we were using is not going to pick that customer up. If you want duplicate customers from the second query in the UNION above, you can use UNION ALL instead of just UNION between the queries. -JW On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R luc...@luci5r.com wrote: JW, Im trying to understand LEFT JOIN as we go but its not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which Im not quite sure what they are but its not the right results. The way Im testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. Im trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) JW On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote: Guys, I've been working with MySQL for a while (been on off this list over the last 10 years or so); I'm definitely not a n00b and have worked with SQL extensively. Used JOIN and all quite a bit ... but I haven't done subqueries, union or nested joins. I'm completely stumped on this problem; and Google hasn't been helpful at all. I'll try to be as descriptive as possible. I have 2 tables ... CUSTOMERS and CALLS. Think of Customers table as your Directory. It has the customer's contact information some other information. In total about 20 fields in there. The Calls table has only about 7 fields. Each time a customer calls in, the conversation details gets recorded in this Calls table. The PHONE field is the key field that joins the CUSTOMERS CALLS
RE: Getting data from 2 tables if records have same date!
SELECT * FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12 ^^^ This is going into an endless loop; I'm not getting any result at all. I'm not sure why. I haven't used USING before so I need to read up a bit on that to understand what you're doing here. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com -Original Message- From: Halász Sándor [mailto:h...@tbbs.net] Sent: Wednesday, February 29, 2012 2:57 PM To: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! ; 2012/02/29 15:29 -0600, Johnny Withers Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Exactly; but I believe that this is the right thing: SELECT * FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12 If you have a hit --PHONE found in both tables--, you will get a record if either date matches, and I believe that you wanted that. If it is a miss --there is no CALLS-record for the PHONE-- CALLS.DATE will be NULL and not equal and only CUSTOMERS.DATE will match a date. And if this works, surely it is clear where to put BETWEEN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Getting data from 2 tables if records have same date!
-Original Message-
From: Shawn L Green [mailto:shawn.l.gr...@oracle.com]
Sent: Thursday, March 01, 2012 6:57 AM
To: luc...@luci5r.com
Cc: 'Johnny Withers'; mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!
Try this:
SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12
This will give you a list of all customers for a given date and a list of
every call they made on that date. If a customer made no calls on a date,
then all of the columns for that table will be NULL.
If you only want a list of customers and details about the calls on a date
then an INNER JOIN is appropriate. If you want to see the full list of
customers and any calls on that date use this:
SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = 02/28/12
If you only want a list of customers that made any calls on a given date,
you can use the EXISTS comparator like this:
SELECT customers.*
FROM customers
WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = 02/28/12)
http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html
It's possible to get you any combination of data you want, we just need you
to clarify the relationship you are trying to find and how much data you
really want to get back.
NOTE: the name of the column date is using a reserved word. You may want to
enclose it in backticks to avoid confusion as in `date`. Also, the standard
MySQL syntax for date literals uses ISO notation. So instead of using
02/28/12 (using double quotes) I expected to see '2012-02-28' (using
single quotes)
--
Shawn Green
MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and
Software, Engineered to Work Together.
Office: Blountville, TN
~~
Shawn,
I still need to try your queries but I wanted to quickly get back to you on
a couple of things.
You're correct! The DATE column is a reserved word and in my queries I do
indeed use `backticks`. Interestingly, I never knew that's what they are
called!!! I used to call that symbol `Grave Accent` ... never heard it being
called `backticks`. Good to know :)
Secondly, unfortunately this DATE field came from an original FoxPro
Database (DBF) which was an Char (8) field and got translated into mysql as
such. So unfortunately it's not a DATE field ... it's a Char (8) field. For
simplicity, I've been using Date = 02/28/12 to explain my queries here,
but in reality, in my program, I've actually been using the STR_TO_DATE()
function as such ...
STR_TO_DATE(`DATE`, '%m/%d/%Y') = STR_TO_DATE('02/2/12', '%m/%d/%Y')
Lastly, you're correct again, I don't actually use the Double Quotes around
the dates ... I do use Single Quotes.
In regards to the relationship/data that I'm looking for ... this is what
I'm looking for ...
I need to pull the records from the CUSTOMERS table, where
a) The customer was created on given date (Eg:- '02/28/12')
b) The customer called on given date and the call was recorded in the Calls
table (Eg:- '02/28/12')
Let me give your queries a shot understand them!
THANKS!!
~~
LUCi5R
e: luc...@luci5r.com
w: http://www.luci5r.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
Stupid wrapping helped me to make a simple mistake. I wrote On 3/1/2012 10:40 AM, LUCi5R wrote: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 02/28/12 But I meant it to be SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 But based on your description: a) The customer was created on given date (Eg:- '02/28/12') b) The customer called on given date and the call was recorded in the Calls table There are possibly two different dates at play, a creation date (customers.date) and an activity date (calls.date). Therefore, we need to list them separately. Also, you said you wanted just the CUSTOMERS records (without any call details) so I assume you only want to see a single copy of each customer. This would work best using the EXISTS pattern I provided last with a simple modification: SELECT customers.* FROM customers WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = activity date) AND customers.date=creation date There are many other ways to find this same set of data. Here is a two-step process using an indexed temporary table (technically, it's a 3-step process as you need to drop the explicit temp table, too). CREATE TEMPORARY TABLE tmp_custs(key(phone) USINB BTREE) ENGINE=MEMORY SELECT DISTINCT phone FROM CALLS WHERE date=activity date; SELECT customers.* FROM customers INNER JOIN tmp_custs ON tmp_custs.phone = customers.phone WHERE customers.date = create date; DROP TEMPORARY TABLE tmp_custs; By default the MEMORY engine creates all indexes as HASH indexes. So in order to replace the =activity date comparison with any sort of ranged comparison, you need a BTREE index. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting data from 2 tables if records have same date!
Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) JW On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote: Guys, I've been working with MySQL for a while (been on off this list over the last 10 years or so); I'm definitely not a n00b and have worked with SQL extensively. Used JOIN and all quite a bit ... but I haven't done subqueries, union or nested joins. I'm completely stumped on this problem; and Google hasn't been helpful at all. I'll try to be as descriptive as possible. I have 2 tables ... CUSTOMERS and CALLS. Think of Customers table as your Directory. It has the customer's contact information some other information. In total about 20 fields in there. The Calls table has only about 7 fields. Each time a customer calls in, the conversation details gets recorded in this Calls table. The PHONE field is the key field that joins the CUSTOMERS CALLS tables. That is the only identifying key that gets written on the Calls record when that customer calls. One thing to note -- It is possible for a customer to exist in the CUSTOMERS table, but not exist in the CALLS table; however, it is not possible for a PHONE # to be in the CALLS table but not in CUSTOMERS table. Essentially, a customer's record has to be created first in the CUSTOMERS table before a call can be recorded from him in the CALLS table. Also, CALLS table can have multiple entries with same PHONE # (Customer called many times - maybe even same day), but CUSTOMERS will only have a single entry for a PHONE #. Here comes my problem ... I have a PHONE SEARCH box with the ability to define a date range; for simplicity sake - we'll use just One Date instead of DATE ... BETWEEN for now. When someone searches for a PHONE number, I want to show ALL the CUSTOMERS that: a. Were CREATED on that day (Date defined in Search Criteria) b. Had CALLED in that day (Date defined in Search Criteria) The DATA that I need to pull up and show is in the CUSTOMERS table; not the CALLS table -- so the DATA I need needs to come out of the CUSTOMERS table matching on phone from both tables for the given DATE. In other words - any CUSTOMER that has the PHONE NUMBER which appears in BOTH CUSTOMERS CALLS table with the DATE defined should pull up. For the life of me - I can't get this to work!! Let's take the date 02/28/12 for example sake. My biggest issue is ... using JOIN, I can pull up ... a. ALL the phone/customers that appeared in the CALLS table with date 02/28/12 b. ALL the phone/customers that appeared in CALLS CUSTOMERS with date 02/28/12 BUT -- If there's a customer with date 02/28/12 who DOES NOT appear in CALLS table at all - does NOT show up!! And that is because I'm using CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't pick up a record where the phone didn't exist in both tables. My initial query was: SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND CALLS.DATE = 02/28/12 I've tried 100's of combinations of this query; many different OR, AND, GROUP BY combinations --- but I can't pull up ALL records from CUSTOMERS with DATE 02/28/12 and ALL records from CALLS with DATE 02/28/12 in a single query. I've hit a wall here. Any ideas/suggestions/advice? THANKS ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: Getting data from 2 tables if records have same date!
JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) JW On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote: Guys, I've been working with MySQL for a while (been on off this list over the last 10 years or so); I'm definitely not a n00b and have worked with SQL extensively. Used JOIN and all quite a bit ... but I haven't done subqueries, union or nested joins. I'm completely stumped on this problem; and Google hasn't been helpful at all. I'll try to be as descriptive as possible. I have 2 tables ... CUSTOMERS and CALLS. Think of Customers table as your Directory. It has the customer's contact information some other information. In total about 20 fields in there. The Calls table has only about 7 fields. Each time a customer calls in, the conversation details gets recorded in this Calls table. The PHONE field is the key field that joins the CUSTOMERS CALLS tables. That is the only identifying key that gets written on the Calls record when that customer calls. One thing to note -- It is possible for a customer to exist in the CUSTOMERS table, but not exist in the CALLS table; however, it is not possible for a PHONE # to be in the CALLS table but not in CUSTOMERS table. Essentially, a customer's record has to be created first in the CUSTOMERS table before a call can be recorded from him in the CALLS table. Also, CALLS table can have multiple entries with same PHONE # (Customer called many times - maybe even same day), but CUSTOMERS will only have a single entry for a PHONE #. Here comes my problem ... I have a PHONE SEARCH box with the ability to define a date range; for simplicity sake - we'll use just One Date instead of DATE ... BETWEEN for now. When someone searches for a PHONE number, I want to show ALL the CUSTOMERS that: a. Were CREATED on that day (Date defined in Search Criteria) b. Had CALLED in that day (Date defined in Search Criteria) The DATA that I need to pull up and show is in the CUSTOMERS table; not the CALLS table -- so the DATA I need needs to come out of the CUSTOMERS table matching on phone from both tables for the given DATE. In other words - any CUSTOMER that has the PHONE NUMBER which appears in BOTH CUSTOMERS CALLS table with the DATE defined should pull up. For the life of me - I can't get this to work!! Let's take the date 02/28/12 for example sake. My biggest issue is ... using JOIN, I can pull up ... a. ALL the phone/customers that appeared in the CALLS table with date 02/28/12 b. ALL the phone/customers that appeared in CALLS CUSTOMERS with date 02/28/12 BUT -- If there's a customer with date 02/28/12 who DOES NOT appear in CALLS table at all - does NOT show up!! And that is because I'm using CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't pick up a record where the phone didn't exist in both tables. My initial query was: SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND CALLS.DATE = 02/28/12 I've tried 100's of combinations of this query; many different OR, AND, GROUP BY combinations --- but I can't pull up ALL records from CUSTOMERS with DATE 02/28/12 and ALL records from CALLS with DATE 02/28/12 in a single query. I've hit a wall here. Any ideas
Re: Getting data from 2 tables if records have same date!
what about select customers.* from customers left join calls on (customers.date=calls.date) where customers.date=02/28/12; of course date should be an index in both tables. I think it migth work On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R luc...@luci5r.com wrote: JW, I'm trying to understand LEFT JOIN as we go - but it's not working. This query SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Is giving me some results which I'm not quite sure what they are - but it's not the right results. The way I'm testing is, on 02/28/12 I had 57 Customers created in the CUSTOMERS table. I also had a total of 105 Calls recorded in the CALLS table. Some calls were from the same customers more then once. Essentially, I need the result to be 86 which I got from some manual calculations. Out of those 86 records, 1 record is in the CUSTOMERS table but not in the CALLS table. The other 85 were in both tables. The above LEFT JOIN query gave me 69 records and quite a few duplicate entries. I'm trying to dissect it to understand what exactly it selected. Thanks! ~~ LUCi5R e: luc...@luci5r.com w: http://www.luci5r.com From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, February 29, 2012 1:30 PM To: luc...@luci5r.com Cc: mysql@lists.mysql.com Subject: Re: Getting data from 2 tables if records have same date! Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) JW On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote: Guys, I've been working with MySQL for a while (been on off this list over the last 10 years or so); I'm definitely not a n00b and have worked with SQL extensively. Used JOIN and all quite a bit ... but I haven't done subqueries, union or nested joins. I'm completely stumped on this problem; and Google hasn't been helpful at all. I'll try to be as descriptive as possible. I have 2 tables ... CUSTOMERS and CALLS. Think of Customers table as your Directory. It has the customer's contact information some other information. In total about 20 fields in there. The Calls table has only about 7 fields. Each time a customer calls in, the conversation details gets recorded in this Calls table. The PHONE field is the key field that joins the CUSTOMERS CALLS tables. That is the only identifying key that gets written on the Calls record when that customer calls. One thing to note -- It is possible for a customer to exist in the CUSTOMERS table, but not exist in the CALLS table; however, it is not possible for a PHONE # to be in the CALLS table but not in CUSTOMERS table. Essentially, a customer's record has to be created first in the CUSTOMERS table before a call can be recorded from him in the CALLS table. Also, CALLS table can have multiple entries with same PHONE # (Customer called many times - maybe even same day), but CUSTOMERS will only have a single entry for a PHONE #. Here comes my problem ... I have a PHONE SEARCH box with the ability to define a date range; for simplicity sake - we'll use just One Date instead of DATE ... BETWEEN for now. When someone searches for a PHONE number, I want to show ALL the CUSTOMERS that: a. Were CREATED on that day (Date defined in Search Criteria) b. Had CALLED in that day (Date defined in Search Criteria) The DATA that I need to pull up and show is in the CUSTOMERS table; not the CALLS table -- so the DATA I need needs to come out of the CUSTOMERS table matching on phone from both tables for the given DATE. In other words - any CUSTOMER that has the PHONE NUMBER which appears in BOTH CUSTOMERS CALLS table with the DATE defined should pull up. For the life of me - I can't get this to work!! Let's take the date 02/28/12 for example sake. My biggest issue is ... using JOIN, I can pull up ... a. ALL the phone/customers that appeared in the CALLS table with date 02/28/12 b. ALL the phone/customers that appeared in CALLS CUSTOMERS with date 02/28/12 BUT -- If there's a customer with date 02/28/12 who DOES NOT appear in CALLS table at all - does NOT show up!! And that is because I'm using CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't pick up a record where the phone didn't exist in both tables. My initial query was: SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE
Re: Getting data from 2 tables if records have same date!
; 2012/02/29 15:29 -0600, Johnny Withers Sounds like you need to LEFT JOIN: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12 WHERE CUSTOMERS.DATE = 02/28/12 But that would only get customers created on 2/28 AND having a call on 2/28 OR not call at all on 2/28. This would give you customers created on 2/28 with no calls AND customers created on 2/28 with a call on 2/28: SELECT * FROM CUSTOMERS LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE = 02/28/12) Exactly; but I believe that this is the right thing: SELECT * FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12 If you have a hit --PHONE found in both tables--, you will get a record if either date matches, and I believe that you wanted that. If it is a miss --there is no CALLS-record for the PHONE-- CALLS.DATE will be NULL and not equal and only CUSTOMERS.DATE will match a date. And if this works, surely it is clear where to put BETWEEN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Join 2 tables and compare then calculate something
Try out query with UNION also select A,B,C from (select A,B,C from X UNION select A,B,C from Y) group by A,B,C having count(*)1 From: Gian Karlo C webdev...@gmail.com To: mysql@lists.mysql.com Sent: Sunday, 2 October 2011 12:49 PM Subject: Join 2 tables and compare then calculate something Hi All, I decided to join and write to the list hoping someone could help and shed a light on me. Here's the scenario. I have a database running in mysql 5.x in Centos 5. The database has 2 tables that is almost identical with some additional fields. Table 1 Name, IPAddress, Description, Issue, Severity, Timestamp, DateReceived Table 2 Name, IPAddress, Description, Issue, Severity, Timestamp, DataReceived, Owner Here's my SQL statement to compare both tables if fields data are the same then consider it as a valid record. select Table1.Name, Table1.IPAddress, Table1.Description, Table1.Issue, Table1.Severity, Table1.Timestamp FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name WHERE Table1.Name = Table2.Name AND Table1.IPAddress = Table2.IPAddress AND Table1.Description = Table2.Description AND Table1.Issue = Table2.Issue AND Table1.Severity = Table2.Severity AND Table1.Timestamp = Table2.Timestamp group by 1; I need to compare Name, IPAddress, Description, Issue, Severity and Timestamp to consider as I valid data then I group it so that only one record will show although there is no duplicate entry on the results. Just want to make sure. Using that SQL statement I was able to get and compare data (which I don't know if this is a good approach), now when I get a valid results, I want to compute the DateReceived. Table1 DateReceived = 10:05 Table2 DateReceived = 10:15 So the computation is to get the difference between DateReceived which the result should be 10 minutes. How would I add that computation to my existing SQL statement and maybe someone suggest a good approach with regards to my current statement. Thanks in advance.
Join 2 tables and compare then calculate something
Hi All, I decided to join and write to the list hoping someone could help and shed a light on me. Here's the scenario. I have a database running in mysql 5.x in Centos 5. The database has 2 tables that is almost identical with some additional fields. Table 1 Name, IPAddress, Description, Issue, Severity, Timestamp, DateReceived Table 2 Name, IPAddress, Description, Issue, Severity, Timestamp, DataReceived, Owner Here's my SQL statement to compare both tables if fields data are the same then consider it as a valid record. select Table1.Name, Table1.IPAddress, Table1.Description, Table1.Issue, Table1.Severity, Table1.Timestamp FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name WHERE Table1.Name = Table2.Name AND Table1.IPAddress = Table2.IPAddress AND Table1.Description = Table2.Description AND Table1.Issue = Table2.Issue AND Table1.Severity = Table2.Severity AND Table1.Timestamp = Table2.Timestamp group by 1; I need to compare Name, IPAddress, Description, Issue, Severity and Timestamp to consider as I valid data then I group it so that only one record will show although there is no duplicate entry on the results. Just want to make sure. Using that SQL statement I was able to get and compare data (which I don't know if this is a good approach), now when I get a valid results, I want to compute the DateReceived. Table1 DateReceived = 10:05 Table2 DateReceived = 10:15 So the computation is to get the difference between DateReceived which the result should be 10 minutes. How would I add that computation to my existing SQL statement and maybe someone suggest a good approach with regards to my current statement. Thanks in advance.
COUNT from 2 tables
I'm trying to get a count for columns in 2 tables at once. I have sessions and downloads tables and would like to get some basic stats. mysql describe sessions; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| +-+--+--+-+-++ 3 rows in set (0.01 sec) mysql describe downloads; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| | item_file_id | int(10) unsigned | NO | MUL | NULL|| | session_id | int(10) unsigned | NO | | NULL| | | path | text | NO | | NULL|| +--+--+--+-+-++ 6 rows in set (0.01 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM sessions GROUP BY month; +---++ | month | num_logins | +---++ | July | 6 | | June |214 | | May |150 | +---++ 3 rows in set (0.00 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads FROM downloads GROUP BY month; +---+---+ | month | num_downloads | +---+---+ | June | 389 | | May | 220 | +---+---+ 2 rows in set (0.01 sec) In trying to get the count from both tables at once, the logins are no longer being summed as expected: mysql SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins, COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month; +---++---+ | month | num_logins | num_downloads | +---++---+ | July | 6 | 0 | | June |539 | 389 | | May |350 | 220 | +---++---+ 3 rows in set (0.31 sec) Is this possible to do without using a sub-query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: COUNT from 2 tables
Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0) 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month From: b [my...@logi.ca] Sent: Wednesday, July 08, 2009 12:21 PM To: mysql@lists.mysql.com Subject: COUNT from 2 tables I'm trying to get a count for columns in 2 tables at once. I have sessions and downloads tables and would like to get some basic stats. mysql describe sessions; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| +-+--+--+-+-++ 3 rows in set (0.01 sec) mysql describe downloads; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| | item_file_id | int(10) unsigned | NO | MUL | NULL|| | session_id | int(10) unsigned | NO | | NULL| | | path | text | NO | | NULL|| +--+--+--+-+-++ 6 rows in set (0.01 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM sessions GROUP BY month; +---++ | month | num_logins | +---++ | July | 6 | | June |214 | | May |150 | +---++ 3 rows in set (0.00 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads FROM downloads GROUP BY month; +---+---+ | month | num_downloads | +---+---+ | June | 389 | | May | 220 | +---+---+ 2 rows in set (0.01 sec) In trying to get the count from both tables at once, the logins are no longer being summed as expected: mysql SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins, COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month; +---++---+ | month | num_logins | num_downloads | +---++---+ | July | 6 | 0 | | June |539 | 389 | | May |350 | 220 | +---++---+ 3 rows in set (0.31 sec) Is this possible to do without using a sub-query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: COUNT from 2 tables
On 07/08/2009 03:33 PM, Gary Smith wrote: Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0) 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month Nope, I'm still getting those same incorrect sums. Thanks, though. It seems to me that the problem is that I'm grouping by the month for one table but counting from both. I'd paste the output here but I just upgraded Fedora and the BETA (wtf?) version of Thunderbird crashes when I paste into an email (how the earlier paste worked I don't know). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: COUNT from 2 tables
Create a view or sub select, denormalizing the data and then group it. select month, sum(login) as num_logins, sum(download) as num_downloads from ( select monthname(s.created) as month_name , if(ifnull(s.id, 0) 0, 1, 0) as login , if(ifnull(d.id, 0) 0, 1, 0) as download from sessions s left join downloads d on s.id = d.session_id ) group by month name; which is the left table? downloads or logins? If logins shouldn't it be on the left side of the ON statement? (I'm not sure) My understanding is that is the ON statement that runs the left join, not which table is listed first (I could be wrong though -- chime in if you know the definitive answer please). Anyway, try this and see if it gets you closer. From: b [my...@logi.ca] Sent: Wednesday, July 08, 2009 12:55 PM To: mysql@lists.mysql.com Subject: Re: COUNT from 2 tables On 07/08/2009 03:33 PM, Gary Smith wrote: Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0) 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month Nope, I'm still getting those same incorrect sums. Thanks, though. It seems to me that the problem is that I'm grouping by the month for one table but counting from both. I'd paste the output here but I just upgraded Fedora and the BETA (wtf?) version of Thunderbird crashes when I paste into an email (how the earlier paste worked I don't know). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: COUNT from 2 tables
On 07/08/2009 06:11 PM, Gary Smith wrote: Create a view or sub select, denormalizing the data and then group it. select month, sum(login) as num_logins, sum(download) as num_downloads from ( select monthname(s.created) as month_name , if(ifnull(s.id, 0) 0, 1, 0) as login , if(ifnull(d.id, 0) 0, 1, 0) as download from sessions s left join downloads d on s.id = d.session_id ) group by month name; which is the left table? downloads or logins? If logins shouldn't it be on the left side of the ON statement? (I'm not sure) My understanding is that is the ON statement that runs the left join, not which table is listed first (I could be wrong though -- chime in if you know the definitive answer please). Anyway, try this and see if it gets you closer. I had to change month_name to month and add AS foo just before the GROUP BY (Every derived table must have its own alias) but still no joy. I'm still getting those same incorrect numbers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT single row from 2 tables with WHERE clause
Many thanks Peter. That's the definition I was after. Kerry -Original Message- From: Peter K AGANYO [mailto:[EMAIL PROTECTED] Behalf Of Peter K AGANYO Sent: 19 February 2007 00:35 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: SELECT single row from 2 tables with WHERE clause Hi Kerry, Try this: SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.linkfield=t2.linkfield WHERE t1.lookup='Z' Without the WHERE condition this would return all 1000 rows of table 1 since A Left join returns all rows of the left of the conditional even if there is no right column to match. but t1.lookup='Z' constrains this to only the one row of table one with lookup equal to 'Z'. Enjoy Peter On 2/17/07, Kerry Frater [EMAIL PROTECTED] wrote: I am trying to select a particular row from a table and include a column for aq second table but I cannot get the result I am after. I have table1 with 1000 rows and table2 with 12 rows. The relationship between the tables is a column linkedfield. Table1 has a unique key called lookup If I use the code SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') I get a result of 12 rows (as expected) SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.linkfield=t2.linkfield) I get 1000 rows as expected SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') and (t1.linkfield=t2.linkfield) returns 1 row if there is an active link between the two tables and returns 0 rows if there isn't. This is where I am stuck. In the last example I would like the 1 row whether there is an active link or not. The difference will be simply that the t2desc rsulting column will be blank or contain a value. Can anyone help me with the logic? Kerry
Re: SELECT single row from 2 tables with WHERE clause
It sounds to me like you're needing to use a left outer join on t2. Give that a shot instead of the inner join you're currently using. Cheers, -bemansell On 2/17/07, Kerry Frater [EMAIL PROTECTED] wrote: I am trying to select a particular row from a table and include a column for aq second table but I cannot get the result I am after. I have table1 with 1000 rows and table2 with 12 rows. The relationship between the tables is a column linkedfield. Table1 has a unique key called lookup If I use the code SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') I get a result of 12 rows (as expected) SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.linkfield=t2.linkfield) I get 1000 rows as expected SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') and (t1.linkfield=t2.linkfield) returns 1 row if there is an active link between the two tables and returns 0 rows if there isn't. This is where I am stuck. In the last example I would like the 1 row whether there is an active link or not. The difference will be simply that the t2desc rsulting column will be blank or contain a value. Can anyone help me with the logic? Kerry -- 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]
SELECT single row from 2 tables with WHERE clause
I am trying to select a particular row from a table and include a column for aq second table but I cannot get the result I am after. I have table1 with 1000 rows and table2 with 12 rows. The relationship between the tables is a column linkedfield. Table1 has a unique key called lookup If I use the code SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') I get a result of 12 rows (as expected) SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.linkfield=t2.linkfield) I get 1000 rows as expected SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='Z') and (t1.linkfield=t2.linkfield) returns 1 row if there is an active link between the two tables and returns 0 rows if there isn't. This is where I am stuck. In the last example I would like the 1 row whether there is an active link or not. The difference will be simply that the t2desc rsulting column will be blank or contain a value. Can anyone help me with the logic? Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question: updating between 2 tables
Hi all, trying to figure out if there is a query I can use for this, or if I have to write a php script to loop tru each row... table1: entryid int(11) itemid int(11) table2: object_id int(11) The situation is: table2.objectid is populated with the values of table1.itemid, but they have to be replaced with the corresponding table1.entryid. I could do in PHP: - select * from table2 - for each row, update table2 with select from table1 but I am wondering if there is 1 query that could take care of this? Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: updating between 2 tables
2006/9/19, Peter Van Dijck [EMAIL PROTECTED]: Hi all, trying to figure out if there is a query I can use for this, or if I have to write a php script to loop tru each row... table1: entryid int(11) itemid int(11) table2: object_id int(11) The situation is: table2.objectid is populated with the values of table1.itemid, but they have to be replaced with the corresponding table1.entryid. I could do in PHP: - select * from table2 - for each row, update table2 with select from table1 but I am wondering if there is 1 query that could take care of this? Peter something like : update table2,table1 set table2.objectid=table1.itemid where table1.entryid=table2.objectid you need 4.0 at least IIRC. -- http://www.myspace.com/sakuradrop : credit runs faster http://www.w-fenec.org/ Rock Webzine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
returning username/pass from 2 tables
How do I check two tables is it? Username and userpass are submitted through a from and are unique $sql = SELECT username, userpass FROM mytable, mytable2 WHERE username = '$username' AND userpass = '$userpass'; Ta, Ross
Re: returning username/pass from 2 tables
[EMAIL PROTECTED] wrote: How do I check two tables is it? Username and userpass are submitted through a from and are unique $sql = SELECT username, userpass FROM mytable, mytable2 WHERE username = '$username' AND userpass = '$userpass'; This way you'll have an ambiguous error as username and userpass belongs to both table (as far as I could understand). You can do both selects or do something like this: select a.user, a.pass, b.user, b.pass from table1 a, table2 b where (...) Or create a MERGE storage engine if both your tables are identical: http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: returning username/pass from 2 tables
Renato Golin schreef: [EMAIL PROTECTED] wrote: How do I check two tables is it? Username and userpass are submitted through a from and are unique $sql = SELECT username, userpass FROM mytable, mytable2 WHERE username = '$username' AND userpass = '$userpass'; This way you'll have an ambiguous error as username and userpass belongs to both table (as far as I could understand). You can do both selects or do something like this: select a.user, a.pass, b.user, b.pass from table1 a, table2 b where (...) Or create a MERGE storage engine if both your tables are identical: http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html cheers, --renato Or if your tables are not identical you can use a LEFT JOIN: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php Mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Search across 2 tables.
I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search across 2 tables.
Maybe something like this: select ft.topic, fm.message from forums_topics ft, forums_messages fm match (ft.topic, fm.message) against (...) if topic is null then the hit is from fm and vice versa... haven't tried it, so might not work :) Steffan A. Cline wrote: I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join 2 tables using specific fields
Ow Mun Heng wrote: I have 2 table. Zones(Z1..Z20) and Radius(R0..R4) where R0 is equivalent to Z1 R1 Z5 R2 Z9 R3 Z13 R4 Z17 How can I make the query to join them in such ways? eg: select A,B,C,D from Zone inner join radius on R1 = Z5 on R2 = Z9 on R3 = Z13 on R4 = Z17 or do I have to use a subquery?? How about: select A,B,C,D from Zone left join radius on R1 = Z5 and R2 = Z9 and R3 = Z13 and R4 = Z17 select A,B,C,D from Zone left join radius on R1 = Z5 or R2 = Z9 or R3 = Z13 or R4 = Z17 It's not clear what the relationship is between the tables so I don't know which of the two is what you want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join 2 tables using specific fields
I have 2 table. Zones(Z1..Z20) and Radius(R0..R4) where R0 is equivalent to Z1 R1 Z5 R2 Z9 R3 Z13 R4 Z17 How can I make the query to join them in such ways? eg: select A,B,C,D from Zone inner join radius on R1 = Z5 on R2 = Z9 on R3 = Z13 on R4 = Z17 or do I have to use a subquery?? -- Ow Mun Heng [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update 2 tables
table master has alast_dat, a date that is not updated. pk is reg_id. table detail has date_update, inserting each transaction. fk is reg_id. is there an update command that will take max date from detail table and update alast_dat? tia, .V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing 2 Tables
I need to update a table without disturing the recordset however there are or could be some overlaps with data is there a way I can compare id fro the same 2 tables, one new one old? andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing 2 Tables
AM COMS [EMAIL PROTECTED] wrote on 01/23/2006 10:01:17 AM: I need to update a table without disturing the recordset however there are or could be some overlaps with data is there a way I can compare id fro the same 2 tables, one new one old? andrew Your question is quite vague. What kind of comparison do you want to do. What kind of results do you want to have? What do you want to do with the comparisons once you get them? Basically you said I have two boxes of things. Some of the things in one box may be the same as in another. How can I compare what's in my boxes? Without knowing a bit more about your situation, nobody could do anything but guess towards giving you a reasonable solution for your real problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Comparing 2 Tables
Andrew, I need to update a table without disturing the recordset however there are or could be some overlaps with data is there a way I can compare id fro the same 2 tables, one new one old? Here's one way... SELECT MIN(TableName) as TableName, id FROM ( SELECT 'Table a' as TableName, a.id FROM a UNION ALL SELECT 'Table b' as TableName, b.id FROM b ) AS tmp GROUP BY id HAVING COUNT(*) = 1 ORDER BY id; PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting from 2 tables
Nurullah Akkaya [EMAIL PROTECTED] wrote on 10/08/2005 08:43:27 PM: i have two tables one of them is a que of urls and the other is list of urls that have been processed now i want to select a url from Que that is not in the processed urls table how can i do this in one select i do not want my application to do two seperate select statements? thx.. Nurullah Akkaya What lies behind us and what [EMAIL PROTECTED] lies before us are tiny matters Registered Linux User #301438 compared to what lies within us. WARNING all messages If at first an idea is not containing attachments absurd, there is no hope for it or html will be silently Albert Einstein deleted. Send only plain text. Because the people who are crazy enough to think they can change the world, are the ones who do. It's a simple LEFT JOIN query (works on any version): SELECT q.url from que q LEFT JOIN processedlist l on q.url = l.url WHERE l.url IS NULL; Since you didn't actually post your table definitions, you will need to convert the above query to fit your column and table names. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: deleting rows in 2 tables
Merlin [EMAIL PROTECTED] wrote on 10/09/2005 01:31:17 PM: Hi there, I am wondering if there is a possiblity to delete rows in more than one table with one query. At the moment I am doing this: I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Thanx for any hint, Merlin Have you considered RTFM? http://dev.mysql.com/doc/mysql/en/delete.html There are two forms that accept multiple tables as the targets of the deletion and as the determinates for the deletion. I would still run two separate queries, just to make sure there was no parent-child-deletion-sequence race condition: DELETE FROM child USING parent p INNER JOIN child c ON c.parent_id = p.id WHERE p.id = ...; DELETE FROM parent WHERE id = ...; The other suggestion about cascading deletes would only work if both tables are InnoDB and you have defined the appropriate foreign key (as described). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: selecting from 2 tables
thx worked great.. Nurullah Akkaya What lies behind us and what [EMAIL PROTECTED] lies before us are tiny matters Registered Linux User #301438 compared to what lies within us. WARNING all messages If at first an idea is not containing attachments absurd, there is no hope for it or html will be silently Albert Einstein deleted. Send only plain text. Because the people who are crazy enough to think they can change the world, are the ones who do. On Oct 8, 2005, at 11:28 PM, Rhino wrote: If you are using a version of MySQL that supports subqueries, you can do something like this: select * from unprocessed_url_table where url not in (select url from processed_url_table) In other words, the inner query (the part in brackets) is returning a list of all the URLs that have been processed, according to the table which lists the processed URLs; the rest of the query is getting rows of tables whose URLs have *not* been processed. I *think* MySQL V4.1.x supports subqueries but I might be wrong; perhaps only V5.x supports it. I'm afraid I haven't been doing much with MySQL lately and forget exactly when each feature was made available. If you don't have a version that supports subqueries, it should be possible to do what you want a different way, probably with temporary tables. Ask again in you need to know how to do it that way. I think I've got an example lying around of how to do it with temporary tables. Rhino - Original Message - From: Nurullah Akkaya [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 08, 2005 8:43 PM Subject: selecting from 2 tables i have two tables one of them is a que of urls and the other is list of urls that have been processed now i want to select a url from Que that is not in the processed urls table how can i do this in one select i do not want my application to do two seperate select statements? thx.. Nurullah Akkaya What lies behind us and what [EMAIL PROTECTED] lies before us are tiny matters Registered Linux User #301438 compared to what lies within us. WARNING all messages If at first an idea is not containing attachments absurd, there is no hope for it or html will be silently Albert Einstein deleted. Send only plain text. Because the people who are crazy enough to think they can change the world, are the ones who do. -- -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005
deleting rows in 2 tables
Hi there, I am wondering if there is a possiblity to delete rows in more than one table with one query. At the moment I am doing this: I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Thanx for any hint, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting rows in 2 tables
- Original Message - From: Merlin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 09, 2005 1:31 PM Subject: deleting rows in 2 tables Hi there, I am wondering if there is a possiblity to delete rows in more than one table with one query. At the moment I am doing this: I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Have you considered using foreign keys with DELETE CASCADE rules? Basically, if you define the dependent tables as children of the first (parent) table via the appropriate foreign keys and primary keys, and if you establish DELETE CASCADE on the foreign keys, you could be sure that whenever you deleted a row in the parent table, all dependent rows in all dependent tables are deleted automatically. In other words, you set up the rules in your table definitions and then simply delete what you like from the parent table; the dependent rows will be deleted for you without you having to write any explicit DELETE statements for the dependent tables. For example, if your parent table was EMPLOYEE and your dependent tables were EMPLOYEE_CARS and EMPLOYEE_CLAIMS, you'd have the definitions set up roughly like this: create table EMPLOYEE empno integer not null, ... primary key(empno)); create table EMPLOYEE_CARS empno integer not null, licence_plate_state char(2) not null, licence_plate_number char(8) not null, ... primary key(empno, licence_plate_state, licence_plate_number), foreign key(empno) references EMPLOYEE on delete cascade)); create table EMPLOYEE_CLAIMS empno integer not null, claim_number integer not null,, ... primary key(empno, claim_number), foreign key(empno) references EMPLOYEE on delete cascade)); Then, once the tables are defined, all your program needs to do is delete a given employee and the employee's cars and claims will be deleted automatically, via the DELETE CASCADE rules in the definitions of the dependent tables: delete from EMPLOYEE where empno = 10; --- If you want to use this approach, I believe you have to be using InnoDB tables; I don't think the other engines support foreign keys. By the way, this whole concept is called Referential Integrity and is very useful for ensuring that your tables are consistent with one another. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date: 09/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting rows in 2 tables
Rhino wrote: - Original Message - From: Merlin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 09, 2005 1:31 PM Subject: deleting rows in 2 tables Hi there, I am wondering if there is a possiblity to delete rows in more than one table with one query. At the moment I am doing this: I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Have you considered using foreign keys with DELETE CASCADE rules? Basically, if you define the dependent tables as children of the first (parent) table via the appropriate foreign keys and primary keys, and if you establish DELETE CASCADE on the foreign keys, you could be sure that whenever you deleted a row in the parent table, all dependent rows in all dependent tables are deleted automatically. In other words, you set up the rules in your table definitions and then simply delete what you like from the parent table; the dependent rows will be deleted for you without you having to write any explicit DELETE statements for the dependent tables. For example, if your parent table was EMPLOYEE and your dependent tables were EMPLOYEE_CARS and EMPLOYEE_CLAIMS, you'd have the definitions set up roughly like this: create table EMPLOYEE empno integer not null, ... primary key(empno)); create table EMPLOYEE_CARS empno integer not null, licence_plate_state char(2) not null, licence_plate_number char(8) not null, ... primary key(empno, licence_plate_state, licence_plate_number), foreign key(empno) references EMPLOYEE on delete cascade)); create table EMPLOYEE_CLAIMS empno integer not null, claim_number integer not null,, ... primary key(empno, claim_number), foreign key(empno) references EMPLOYEE on delete cascade)); Then, once the tables are defined, all your program needs to do is delete a given employee and the employee's cars and claims will be deleted automatically, via the DELETE CASCADE rules in the definitions of the dependent tables: delete from EMPLOYEE where empno = 10; --- If you want to use this approach, I believe you have to be using InnoDB tables; I don't think the other engines support foreign keys. By the way, this whole concept is called Referential Integrity and is very useful for ensuring that your tables are consistent with one another. Rhino Excellent info Rhino. This will help me greatly with my application! * Jason * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting from 2 tables
i have two tables one of them is a que of urls and the other is list of urls that have been processed now i want to select a url from Que that is not in the processed urls table how can i do this in one select i do not want my application to do two seperate select statements? thx.. Nurullah Akkaya What lies behind us and what [EMAIL PROTECTED] lies before us are tiny matters Registered Linux User #301438 compared to what lies within us. WARNING all messages If at first an idea is not containing attachments absurd, there is no hope for it or html will be silently Albert Einstein deleted. Send only plain text. Because the people who are crazy enough to think they can change the world, are the ones who do.
Re: selecting from 2 tables
If you are using a version of MySQL that supports subqueries, you can do something like this: select * from unprocessed_url_table where url not in (select url from processed_url_table) In other words, the inner query (the part in brackets) is returning a list of all the URLs that have been processed, according to the table which lists the processed URLs; the rest of the query is getting rows of tables whose URLs have *not* been processed. I *think* MySQL V4.1.x supports subqueries but I might be wrong; perhaps only V5.x supports it. I'm afraid I haven't been doing much with MySQL lately and forget exactly when each feature was made available. If you don't have a version that supports subqueries, it should be possible to do what you want a different way, probably with temporary tables. Ask again in you need to know how to do it that way. I think I've got an example lying around of how to do it with temporary tables. Rhino - Original Message - From: Nurullah Akkaya [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, October 08, 2005 8:43 PM Subject: selecting from 2 tables i have two tables one of them is a que of urls and the other is list of urls that have been processed now i want to select a url from Que that is not in the processed urls table how can i do this in one select i do not want my application to do two seperate select statements? thx.. Nurullah Akkaya What lies behind us and what [EMAIL PROTECTED] lies before us are tiny matters Registered Linux User #301438 compared to what lies within us. WARNING all messages If at first an idea is not containing attachments absurd, there is no hope for it or html will be silently Albert Einstein deleted. Send only plain text. Because the people who are crazy enough to think they can change the world, are the ones who do. No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting records in 2 tables using 1 query
[EMAIL PROTECTED] wrote: There may be other ways to get at the information you want. What is the purpose of your query? Ok here are the details. I have a wish list/gift registry site (thewishzone.com). I have a table listing all the data on my users. I also have a table listing all the gifts my users want. Finally I have a table with gift giving events for the users. What I need to know is how many events and how many gifts each user has in the database so I can make certain changes to the content of the main user page on my site. Right now I just use 2 queries but I would like to do it in one just to reduce the code some what. I have other uses for similar queries but this is the main reason. Chris W 2wsxdr5 [EMAIL PROTECTED] wrote on 01/13/2005 01:57:31 PM: I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query. SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want this holiday season http://thewishzone.com They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin, 1759 Historical Review of Pennsylvania -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting records in 2 tables using 1 query
OK, then you were on the right track with your JOINS because you wanted to know how many of each type of item (gift or event) each user had. Off the top of my head, I think you need to perform an OUTER JOIN (not the implicit INNER JOIN you create by listing table names separated by commas) and use a COUNT(DISTINCT...) http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html I am going to make up a table structure (adjust to fit your actual tables). One table, users, has an ID and other information on it. Two child tables, gifts and events, have an ID column, a user_ID column, and other information. SELECT u.ID, COUNT(DISTINCT g.ID) as gifts, COUNT(DISTINCT e.ID) as events FROM users u LEFT JOIN gifts g ON g.user_ID = u.ID LEFT JOIN events e ON e.user_ID = u.ID GROUP BY u.ID; That should tell you how many of each item (gift or event) belongs to each user. Both COUNT() and COUNT(DISTINCT ) ignore NULLS but the DISTINCT modifier helps by eliminating duplicates. If you don't think this query originally contained duplicates try this non-GROUPED version of the query and look at the data. SELECT u.ID, g.ID, e.ID FROM users u LEFT JOIN gifts g ON g.user_ID = u.ID LEFT JOIN events e ON e.user_ID = u.ID ORDER BY u.ID, g.id, e.id LIMIT 200; What you should see is that every possible combination of GIFT and EVENT for each user is present in this data** . The DISTINCT modifier eliminated those duplicates from our totals. Shawn Green Database Administrator Unimin Corporation - Spruce Pine NOTE: **By joining any two tables you form a Cartesian product of the data on those tables. You restrict the results of a Cartesian product by putting conditions in the ON clause of an explicit JOIN or in the WHERE clause of an implicit JOIN. We were able to restrict the rows returned from the USERS-GIFTS join and the USERS-EVENTS join. However, we had no way to isolate the GIFTS-EVENTS combinations to reduce gift duplication so their full product became part of the source data for our SELECT...GROUP BY operations. If, for instance you had a field on the GIFTS table that determined what EVENT the gift was for, then we could make that restriction by adding it as a term to either one of our ON clauses or as a WHERE restriction. Something like: SELECT u.ID, g.ID, e.ID FROM users u LEFT JOIN events e ON e.user_ID = u.ID LEFT JOIN gifts g ON g.user_ID = u.ID AND g.event_ID = e.ID ORDER BY u.ID, g.id, e.id see the difference? -S 2wsxdr5 [EMAIL PROTECTED] wrote on 01/14/2005 01:18:18 PM: [EMAIL PROTECTED] wrote: There may be other ways to get at the information you want. What is the purpose of your query? Ok here are the details. I have a wish list/gift registry site (thewishzone.com). I have a table listing all the data on my users. I also have a table listing all the gifts my users want. Finally I have a table with gift giving events for the users. What I need to know is how many events and how many gifts each user has in the database so I can make certain changes to the content of the main user page on my site. Right now I just use 2 queries but I would like to do it in one just to reduce the code some what. I have other uses for similar queries but this is the main reason. Chris W 2wsxdr5 [EMAIL PROTECTED] wrote on 01/13/2005 01:57:31 PM: I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query. SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want this holiday season http://thewishzone.com They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin, 1759 Historical Review of Pennsylvania -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want http://thewishzone.com
counting records in 2 tables using 1 query
I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query. SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want this holiday season http://thewishzone.com They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin, 1759 Historical Review of Pennsylvania -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting records in 2 tables using 1 query
How about ... select @a:=count(*) from ... where ... union select @b:=count(*) from ... where ... union select @[EMAIL PROTECTED]; PB --- 2wsxdr5 wrote: I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query. SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting records in 2 tables using 1 query
There may be other ways to get at the information you want. What is the purpose of your query? Shawn Green Database Administrator Unimin Corporation - Spruce Pine 2wsxdr5 [EMAIL PROTECTED] wrote on 01/13/2005 01:57:31 PM: I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query. SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want this holiday season http://thewishzone.com They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin, 1759 Historical Review of Pennsylvania -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting data from 2 tables.
God, I feel real stupid this morning and know I should know this. I have 2 tables in the same database and I'm trying to select distinct data from a row with the same name in each table. SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC I'm missing something I'm sure because it doesn't work. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
Hi, God, I feel real stupid this morning and know I should know this. I have 2 tables in the same database and I'm trying to select distinct data from a row with the same name in each table. SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC I'm missing something I'm sure because it doesn't work. Feel stupid again ;-) Where's your JOIN? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
Feel stupid again ;-) Where's your JOIN? With regards, Martijn Tonies Thanks, that makes me feel better :) Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
He does have a join. He has an *implied* INNER JOIN (http://dev.mysql.com/doc/mysql/en/JOIN.html): FROM pages, pdflog What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine Martijn Tonies [EMAIL PROTECTED] wrote on 08/09/2004 11:38:31 AM: Hi, God, I feel real stupid this morning and know I should know this. I have 2 tables in the same database and I'm trying to select distinct data from a row with the same name in each table. SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC I'm missing something I'm sure because it doesn't work. Feel stupid again ;-) Where's your JOIN? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
On Mon, 9 Aug 2004 [EMAIL PROTECTED] wrote: He does have a join. He has an *implied* INNER JOIN (http://dev.mysql.com/doc/mysql/en/JOIN.html): FROM pages, pdflog What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Like I said I knew I was missing something. I just couldn't for the life of me remember what it was. All I needed was a nudge. Thanks all, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine OK now I really really feel stupid. Now that I've been given the correct way this particular person wants this done. What I need to produce is a distinct list from pages.magazine and pdflog.magazine without a condition. Just a list of all data in these table columns without duplicates. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
I think a quick way to write this query would be (http://dev.mysql.com/doc/mysql/en/UNION.html): ( SELECT magazine FROM pages ) UNION DISTINCT ( SELECT magazine FROM pdflog ) ORDER BY magazine; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Curtis [EMAIL PROTECTED] wrote on 08/09/2004 12:10:16 PM: What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine OK now I really really feel stupid. Now that I've been given the correct way this particular person wants this done. What I need to produce is a distinct list from pages.magazine and pdflog.magazine without a condition. Just a list of all data in these table columns without duplicates. Ed
Re: Selecting data from 2 tables.
That depends on your definition of a join... I don't call it a join without a join condition. It gives you tableA * tableB results - that's a carthesian product. Hardly a normal join. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com He does have a join. He has an *implied* INNER JOIN (http://dev.mysql.com/doc/mysql/en/JOIN.html): FROM pages, pdflog What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine Martijn Tonies [EMAIL PROTECTED] wrote on 08/09/2004 11:38:31 AM: Hi, God, I feel real stupid this morning and know I should know this. I have 2 tables in the same database and I'm trying to select distinct data from a row with the same name in each table. SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC I'm missing something I'm sure because it doesn't work. Feel stupid again ;-) Where's your JOIN?
Re: Selecting data from 2 tables.
I think a quick way to write this query would be (http://dev.mysql.com/doc/mysql/en/UNION.html): ( SELECT magazine FROM pages ) UNION DISTINCT ( SELECT magazine FROM pdflog ) ORDER BY magazine; Thanks for all the help on this one. I just also realized that the server I'm working with has version 3.23.x of MySQL. My only other option now is to create a temp table with distinct data from both tables, is it not? Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
I think so: CREATE TEMPORARY TABLE tmpList SELECT DISTINCT magazine FROM pages; INSERT tmpList SELECT DISTINCT magazine FROM pdflog; SELECT DISTINCT magazine FROM tmpList; DROP TABLE tmpList; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Curtis [EMAIL PROTECTED] wrote on 08/09/2004 12:33:28 PM: I think a quick way to write this query would be (http://dev.mysql.com/doc/mysql/en/UNION.html): ( SELECT magazine FROM pages ) UNION DISTINCT ( SELECT magazine FROM pdflog ) ORDER BY magazine; Thanks for all the help on this one. I just also realized that the server I'm working with has version 3.23.x of MySQL. My only other option now is to create a temp table with distinct data from both tables, is it not? Ed
Re: load data into 2 tables and set id
Hi Shawn, I wondered if you might be able to help me with an SQL query. I want to list all the internet sites I've surfed in my database. Here's a query that matches the url with a urlid: SELECT concat(usc.scheme,://,us.server,up.path) FROM url_visit uv INNER JOIN url_servers us ON us.id=uv.url_server_ID INNER JOIN url_paths up ON up.id=uv.url_path_ID INNER JOIN url_queries uq ON uq.id=uv.url_query_ID INNER JOIN url_schemes usc ON usc.id=uv.url_scheme_ID AND uv.urlid=10023; And that works really quickly. However I want to run this for each urlid matching my uid in table internet_usage. mysql desc internet_usage; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | uid | varchar(10) | YES | MUL | NULL| | | time | datetime | YES | | NULL| | | ip| bigint(20) | YES | | 0 | | | urlid | mediumint(9) | | | 0 | | | size | int(11) | YES | | 0 | | +---+--+--+-+-+---+ So maybe it's something like: SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,://,us.server,up.path) FROM internet_usage us, url_visit uv WHERE iu.uid=u752352 INNER JOIN url_servers us ON us.id=uv.url_server_ID INNER JOIN url_paths up ON up.id=uv.url_path_ID INNER JOIN url_queries uq ON uq.id=uv.url_query_ID INNER JOIN url_schemes usc ON usc.id=uv.url_scheme_ID But I haven't tried this because my tables are very big and I didn't want to do the wrong join! Thanks for any help you can offer. js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Figured it out! Took a gamble and run the below command! SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,://,us.server,up.path) FROM url_visit uv INNER JOIN internet_usage iu ON iu.urlid=uv.urlid INNER JOIN url_servers us ON us.id=uv.url_server_ID INNER JOIN url_paths up ON up.id=uv.url_path_ID INNER JOIN url_queries uq ON uq.id=uv.url_query_ID INNER JOIN url_schemes usc ON usc.id=uv.url_scheme_ID WHERE iu.uid=u752359; Hi Shawn, I wondered if you might be able to help me with an SQL query. I want to list all the internet sites I've surfed in my database. Here's a query that matches the url with a urlid: SELECT concat(usc.scheme,://,us.server,up.path) FROM url_visit uv INNER JOIN url_servers us ON us.id=uv.url_server_ID INNER JOIN url_paths up ON up.id=uv.url_path_ID INNER JOIN url_queries uq ON uq.id=uv.url_query_ID INNER JOIN url_schemes usc ON usc.id=uv.url_scheme_ID AND uv.urlid=10023; And that works really quickly. However I want to run this for each urlid matching my uid in table internet_usage. mysql desc internet_usage; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | uid | varchar(10) | YES | MUL | NULL| | | time | datetime | YES | | NULL| | | ip| bigint(20) | YES | | 0 | | | urlid | mediumint(9) | | | 0 | | | size | int(11) | YES | | 0 | | +---+--+--+-+-+---+ So maybe it's something like: SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,://,us.server,up.path) FROM internet_usage us, url_visit uv WHERE iu.uid=u752352 INNER JOIN url_servers us ON us.id=uv.url_server_ID INNER JOIN url_paths up ON up.id=uv.url_path_ID INNER JOIN url_queries uq ON uq.id=uv.url_query_ID INNER JOIN url_schemes usc ON usc.id=uv.url_scheme_ID But I haven't tried this because my tables are very big and I didn't want to do the wrong join! Thanks for any help you can offer. js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec) mysql select * from url_visit where urlid=1753; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1753 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.00 sec) Shawn, Thanks for your reply below. I found it extremely useful. I have followed your instructions and got good results up to the url_visits table. I have a perl script to parse the values out of the log. The log has 3,770,246 lines and is gzipped. I then applied your SQL statements with the following times: 1. Parse script: 10m31 2. Load infile: 2m39 3. Insert url_servers and url_paths: 5m38 4. Insert url_visits 9m50 However when I ran the sql to insert the internet usage table, it just ran until all the log space was filled up: -rw-rw 1 mysqlmysql379694680 Jun 22 10:05 bulk_table.MYD
Re: load data into 2 tables and set id
J S, Check to see if the url_scheme_ID part of a unique constraint/key or the Primary Key? If it isn't, you will get dupes even if you use INSERT IGNORE unless you pre-screen your INSERTs some other way to avoid duplication. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 04:13 Subject: Re: load data into 2 tables and set id AM Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec
Re: load data into 2 tables and set id
Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) J S, Check to see if the url_scheme_ID part of a unique constraint/key or the Primary Key? If it isn't, you will get dupes even if you use INSERT IGNORE unless you pre-screen your INSERTs some other way to avoid duplication. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 04:13 Subject: Re: load data into 2 tables and set id AM Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from
Re: load data into 2 tables and set id
No, url_scheme_ID has key type MUL, which means that that multiple occurences of a given value are allowed within the field. To prevent duplicate entries in url_visit, decide which combination of columns should have no duplicates, then add a unique index on that combination. Michael J S wrote: Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) J S, Check to see if the url_scheme_ID part of a unique constraint/key or the Primary Key? If it isn't, you will get dupes even if you use INSERT IGNORE unless you pre-screen your INSERTs some other way to avoid duplication. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 04:13 Subject: Re: load data into 2 tables and set id AM Shawn, I uncovered a problem this morning. I wonder if you (or anyone else) can help me out again? mysql select * from url_visit where url_scheme_ID=3 limit 10; +-+---+---+-+---+--+ | urlid | url_scheme_ID | url_server_ID | url_path_ID | query | category | +-+---+---+-+---+--+ | 23392 | 3 | 1070 | 1 | NULL | none | | 1346269 | 3 | 1070 | 1 | NULL | none | +-+---+---+-+---+--+ 2 rows in set (0.00 sec) This is the insert statement I'm using: INSERT IGNORE url_visit (url_scheme_ID, url_server_ID, url_path_ID, query, category) SELECT DISTINCT uc.ID, us.ID, up.ID,bt.query,bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_schemes uc ON uc.scheme=bt.scheme Do I need brackets after the distinct? e.g SELECT DISTINCT (uc.ID, us.ID, up.ID,bt.query,bt.category) Thanks, js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT
Re: load data into 2 tables and set id
I can't tell from a DESCRIBE output. Please post the results of SHOW CREATE TABLE url_visit; Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 09:57 Subject: Re: load data into 2 tables and set id AM Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) -- previous responses clipped for space - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Sorry! I'm not that experienced with databases as you probably realised! url_visit | CREATE TABLE `url_visit` ( `urlid` mediumint(9) NOT NULL auto_increment, `url_scheme_ID` int(11) NOT NULL default '0', `url_server_ID` int(11) NOT NULL default '0', `url_path_ID` int(11) NOT NULL default '0', `query` text, `category` varchar(50) default NULL, PRIMARY KEY (`urlid`), KEY `url_scheme_ID` (`url_scheme_ID`,`url_server_ID`,`url_path_ID`) ) TYPE=MyISAM I can't tell from a DESCRIBE output. Please post the results of SHOW CREATE TABLE url_visit; Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/23/2004 09:57 Subject: Re: load data into 2 tables and set id AM Hi Shawn, Here's the url_Schemes table (it's the same as the url_paths and url_servers). This means url_scheme_ID is part of a unique constraint/key ? mysql desc url_schemes; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | ID | int(11) | | MUL | NULL| auto_increment | | scheme | varchar(20) | | PRI | || ++-+--+-+-++ 2 rows in set (0.00 sec) mysql desc url_visit; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | urlid | mediumint(9) | | PRI | NULL| auto_increment | | url_scheme_ID | int(11) | | MUL | 0 || | url_server_ID | int(11) | | | 0 || | url_path_ID | int(11) | | | 0 || | query | text | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +---+--+--+-+-++ 6 rows in set (0.00 sec) mysql select * from url_schemes; +++ | ID | scheme | +++ | 1 | http | | 2 | tcp| | 3 | - | | 4 | ftp| | 5 | https | +++ 5 rows in set (0.00 sec) -- previous responses clipped for space - _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Do this to prevent duplication on those three columns in the future:
ALTER TABLE url_visit DROP KEY `url_scheme_ID`, add UNIQUE KEY
(url_scheme_ID, url_server_ID, url_path_id);
The way I have composed that key (table-column order), it will force you to
include the url_scheme_ID if you want to use the index to find
url_server_id and url_path_id. Maybe a better Idea is to organize that new
key so that the columns are listed in their order of prevalence in your
queries, if you search by url_sever_ID most often, list it first. If
url_scheme_ID is not something you need as often put it last. That
changes the statement to look like:
ALTER TABLE url_visit DROP KEY `url_scheme_ID`, add UNIQUE KEY
(url_server_ID, url_path_ID, url_scheme_ID);
This way you can search on {url_server_ID}, {url_server_ID, url_path_ID},
or (url_server_ID, url_path_ID, url_scheme_ID} and MySQL will still use the
index. Because it's designated as UNIQUE key, there will always be at most
1 record with any combination of those three values.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
J S
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
com cc: [EMAIL PROTECTED]
Fax to:
06/23/2004 10:38 Subject: Re: load data into 2 tables
and set id
AM
Sorry! I'm not that experienced with databases as you probably realised!
url_visit | CREATE TABLE `url_visit` (
`urlid` mediumint(9) NOT NULL auto_increment,
`url_scheme_ID` int(11) NOT NULL default '0',
`url_server_ID` int(11) NOT NULL default '0',
`url_path_ID` int(11) NOT NULL default '0',
`query` text,
`category` varchar(50) default NULL,
PRIMARY KEY (`urlid`),
KEY `url_scheme_ID` (`url_scheme_ID`,`url_server_ID`,`url_path_ID`)
) TYPE=MyISAM
I can't tell from a DESCRIBE output. Please post the results of SHOW
CREATE TABLE url_visit;
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
J S
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
com cc:
[EMAIL PROTECTED]
Fax to:
06/23/2004 09:57 Subject: Re: load data
into 2 tables and set id
AM
Hi Shawn,
Here's the url_Schemes table (it's the same as the url_paths and
url_servers). This means url_scheme_ID is part of a unique constraint/key
?
mysql desc url_schemes;
++-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-++
| ID | int(11) | | MUL | NULL| auto_increment |
| scheme | varchar(20) | | PRI | ||
++-+--+-+-++
2 rows in set (0.00 sec)
mysql desc url_visit;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| urlid | mediumint(9) | | PRI | NULL| auto_increment |
| url_scheme_ID | int(11) | | MUL | 0 ||
| url_server_ID | int(11) | | | 0 ||
| url_path_ID | int(11) | | | 0 ||
| query | text | YES | | NULL||
| category | varchar(50) | YES | | NULL||
+---+--+--+-+-++
6 rows in set (0.00 sec)
mysql select * from url_schemes;
+++
| ID | scheme |
+++
| 1 | http |
| 2 | tcp|
| 3 | - |
| 4 | ftp|
| 5 | https |
+++
5 rows in set (0.00 sec)
-- previous responses clipped for space -
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL
Re: load data into 2 tables and set id
Shawn,
Thanks for your reply below. I found it extremely useful. I have followed
your instructions and got good results up to the url_visits table.
I have a perl script to parse the values out of the log. The log has
3,770,246 lines and is gzipped. I then applied your SQL statements with the
following times:
1. Parse script: 10m31
2. Load infile: 2m39
3. Insert url_servers and url_paths: 5m38
4. Insert url_visits 9m50
However when I ran the sql to insert the internet usage table, it just ran
until all the log space was filled up:
-rw-rw 1 mysqlmysql379694680 Jun 22 10:05 bulk_table.MYD
-rw-rw 1 mysqlmysql 1024 Jun 22 10:05 bulk_table.MYI
-rw-rw 1 mysqlmysql 8760 Jun 22 09:59 bulk_table.frm
-rw-rw 1 mysqlmysql2114977792 Jun 22 11:11 internet_usage.MYD
-rw-rw 1 mysqlmysql 1024 Jun 22 10:34 internet_usage.MYI
-rw-rw 1 mysqlmysql 8646 Jun 22 09:59 internet_usage.frm
-rw-rw 1 mysqlmysql33437600 Jun 22 10:23 url_paths.MYD
-rw-rw 1 mysqlmysql27696128 Jun 22 10:23 url_paths.MYI
-rw-rw 1 mysqlmysql 8574 Jun 22 09:59 url_paths.frm
-rw-rw 1 mysqlmysql 646160 Jun 22 10:19 url_servers.MYD
-rw-rw 1 mysqlmysql 796672 Jun 22 10:19 url_servers.MYI
-rw-rw 1 mysqlmysql 8578 Jun 22 09:59 url_servers.frm
-rw-rw 1 mysqlmysql119076844 Jun 22 10:32 url_visit.MYD
-rw-rw 1 mysqlmysql73026560 Jun 22 10:33 url_visit.MYI
-rw-rw 1 mysqlmysql 8694 Jun 22 09:59 url_visit.frm
I'm sure something's not right because the internet Usage table is bigger
than the bulk table. I changed your sql a bit (on the last line with the
query). Could this be the problem?
INSERT internet_usage (uid,time,ip,urlid,size)
SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.size
FROM bulk_table bt
INNER JOIN url_servers us
ON us.server=bt.server
INNER JOIN url_paths up
ON up.path=bt.path
INNER JOIN url_visit uv
ON uv.url_server_ID=us.ID
AND uv.url_path_ID=up.ID
AND uv.query=bt.query;
Thanks again,
js.
welcome to a basic overview of bulk importing and normalizing as
you go
[ author's note: if you are seeing this thread for the first time and
certain items seem to be
introduced out of context, please review all previous posts in this thread.
There has been
a lot of information already exchanged on this topic that I clipped
out of this response. Thanks! -- SG]
In an earlier post you said
The data values I have for each record are:
user_id date_time size url category
for example:
u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html
business
To me that implies that you have a standard text log where each field is
separated by a space and rows are delimited by a CRLF pair. To get that log
into MySQL you will need to do something _like_ (I say like as you will
most likely need to tweak it to accommodate your actual data)
CREATE TABLE bulk_table (
user varchar(10)
, eventdate date
, eventtime time
, size int
, url text
, category varchar(50)
);
LOAD DATA INFILE a proxy log file INTO bulk_table FIELDS TERMINATED BY '
' LINES TERMINATED BY '\r\n';
Now we can start merging the logs into your data tables.
To quote RFC 2396:
The URI syntax does not require that the scheme-specific-part have
any general structure or set of semantics which is common among all
URI. However, a subset of URI do share a common syntax for
representing hierarchical relationships within the namespace. This
generic URI syntax consists of a sequence of four main components:
scheme://authoritypath?query
each of which, except scheme, may be absent from a particular URI.
That translates into 4 logical pieces we can split a URL into:
1) the scheme -- HTTP, FTP, GOPHER, etc...
2) the authority -- that is the server (www.yahoo.com) and any port numbers
or login information
3) the path -- /somefolder/somefile.whatever
4) the query -- everything after the ?
Not part of the generic URI is that bit known as a fragment (as identified
by the #) it is ALWAYS at the end of the _entire URL_ (including the query)
when it's used. I have always lumped those and queries into the same field.
So you could create tables for each of those parts and get VERY normalized
or you can partly normalize like this:
ALTER TABLE bulk_table add server_split int not null default 0
, add path_split int not null default 0
, add server varchar(255)
, add path varchar(255)
UPDATE bulk_table
set server_split = LOCATE('/', URL , 8)-1;
UPDATE bulk_table
SET path_split = if(LOCATE('?', URL, server_split) 0, LOCATE('?', URL,
server_split), LOCATE('#', URL, server_split)-1);
UPDATE bulk_table
set server=LEFT(URL, server_split )
#those 4 new columns helped us to parse out the 3 major parts of the url
#I added them to the table so
Re: load data into 2 tables and set id
Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec) mysql select * from url_visit where urlid=1753; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1753 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.00 sec) Shawn, Thanks for your reply below. I found it extremely useful. I have followed your instructions and got good results up to the url_visits table. I have a perl script to parse the values out of the log. The log has 3,770,246 lines and is gzipped. I then applied your SQL statements with the following times: 1. Parse script: 10m31 2. Load infile: 2m39 3. Insert url_servers and url_paths: 5m38 4. Insert url_visits 9m50 However when I ran the sql to insert the internet usage table, it just ran until all the log space was filled up: -rw-rw 1 mysqlmysql379694680 Jun 22 10:05 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 10:05 bulk_table.MYI -rw-rw 1 mysqlmysql 8760 Jun 22 09:59 bulk_table.frm -rw-rw 1 mysqlmysql2114977792 Jun 22 11:11 internet_usage.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 10:34 internet_usage.MYI -rw-rw 1 mysqlmysql 8646 Jun 22 09:59 internet_usage.frm -rw-rw 1 mysqlmysql33437600 Jun 22 10:23 url_paths.MYD -rw-rw 1 mysqlmysql27696128 Jun 22 10:23 url_paths.MYI -rw-rw 1 mysqlmysql 8574 Jun 22 09:59 url_paths.frm -rw-rw 1 mysqlmysql 646160 Jun 22 10:19 url_servers.MYD -rw-rw 1 mysqlmysql 796672 Jun 22 10:19 url_servers.MYI -rw-rw 1 mysqlmysql 8578 Jun 22 09:59 url_servers.frm -rw-rw 1 mysqlmysql119076844 Jun 22 10:32 url_visit.MYD -rw-rw 1 mysqlmysql73026560 Jun 22 10:33 url_visit.MYI -rw-rw 1 mysqlmysql 8694 Jun 22 09:59 url_visit.frm I'm sure something's not right because the internet Usage table is bigger than the bulk table. I changed your sql a bit (on the last line with the query). Could this be the problem? INSERT internet_usage (uid,time,ip,urlid,size) SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.size FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_visit uv ON uv.url_server_ID=us.ID AND uv.url_path_ID=up.ID AND uv.query=bt.query; Thanks again, js. welcome to a basic overview of bulk importing and normalizing as you go [ author's note: if you are seeing this thread for the first time and certain items seem to be introduced out of context, please review all previous posts in this thread. There has been a lot of information already exchanged on this topic that I clipped out of this response. Thanks! -- SG] In an earlier post you said The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403
Re: load data into 2 tables and set id
I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec) mysql select * from url_visit where urlid=1753; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1753 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.00 sec) Shawn, Thanks for your reply below. I found it extremely useful. I have followed your instructions and got good results up to the url_visits table. I have a perl script to parse the values out of the log. The log has 3,770,246 lines and is gzipped. I then applied your SQL statements with the following times: 1. Parse script: 10m31 2. Load infile: 2m39 3. Insert url_servers and url_paths: 5m38 4. Insert url_visits 9m50 However when I ran the sql to insert the internet usage table, it just ran until all the log space was filled up: -rw-rw 1 mysqlmysql379694680 Jun 22 10:05 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 10:05 bulk_table.MYI -rw-rw 1 mysqlmysql 8760 Jun 22 09:59 bulk_table.frm -rw-rw 1 mysqlmysql2114977792 Jun 22 11:11 internet_usage.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 10:34 internet_usage.MYI -rw-rw 1 mysqlmysql 8646 Jun 22 09:59 internet_usage.frm -rw-rw 1 mysqlmysql33437600 Jun 22 10:23 url_paths.MYD -rw-rw 1 mysqlmysql27696128 Jun 22 10:23 url_paths.MYI -rw-rw 1 mysqlmysql 8574 Jun 22 09:59 url_paths.frm -rw-rw 1 mysqlmysql 646160 Jun 22 10:19 url_servers.MYD -rw-rw 1 mysqlmysql 796672 Jun 22 10:19 url_servers.MYI -rw-rw 1 mysqlmysql 8578 Jun 22 09:59 url_servers.frm -rw-rw 1 mysqlmysql119076844 Jun 22 10:32 url_visit.MYD -rw-rw 1 mysqlmysql73026560 Jun 22 10:33 url_visit.MYI -rw-rw 1 mysqlmysql 8694 Jun 22 09:59 url_visit.frm I'm sure something's not right because the internet Usage table is bigger than the bulk table. I changed your sql a bit (on the last line with the query). Could this be the problem? INSERT internet_usage (uid,time,ip,urlid,size) SELECT bt.user, bt.time, bt.ip, uv.urlid, bt.size FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN url_paths up ON up.path=bt.path INNER JOIN url_visit uv ON uv.url_server_ID=us.ID AND uv.url_path_ID=up.ID AND uv.query=bt.query; Thanks again, js. welcome to a basic overview of bulk importing and normalizing as you go
Re: load data into 2 tables and set id
Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec) mysql select * from url_visit where urlid=1753; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1753 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.00 sec) Shawn, Thanks for your reply below. I found it extremely useful. I have followed your instructions and got good results up to the url_visits table. I have a perl script to parse the values out of the log. The log has 3,770,246 lines and is gzipped. I then applied your SQL statements with the following times: 1. Parse script: 10m31 2. Load infile: 2m39 3. Insert url_servers and url_paths: 5m38 4. Insert url_visits 9m50 However when I ran the sql to insert the internet usage table, it just ran until all the log space was filled up: -rw-rw 1 mysqlmysql379694680 Jun 22 10:05 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 10:05 bulk_table.MYI -rw-rw 1 mysqlmysql 8760 Jun 22 09:59 bulk_table.frm -rw-rw 1 mysqlmysql2114977792 Jun 22 11:11 internet_usage.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 10:34 internet_usage.MYI -rw-rw 1 mysqlmysql 8646 Jun 22 09:59 internet_usage.frm -rw-rw 1 mysqlmysql33437600 Jun 22 10:23 url_paths.MYD -rw-rw 1 mysqlmysql
Re: load data into 2 tables and set id
Shawn, Here are the results: $ ls -l /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz -rw-r- 1 bluecoat staff138510199 Jun 14 10:04 /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz $ time ./logfile.pl /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz Tue Jun 22 16:53:40 2004: PARSING /sawmill/rawlog/SG_CSGL02_main_47060821.log.gz... Tue Jun 22 17:03:37 2004: BULK_TABLE_INSERT... Tue Jun 22 17:05:41 2004: INSERT_URL_SCHEMES... Tue Jun 22 17:06:08 2004: INSERT_URL_SERVERS... Tue Jun 22 17:08:06 2004: INSERT_URL_PATHS... Tue Jun 22 17:11:20 2004: INSERT_URL_VISITS... Tue Jun 22 17:28:16 2004: INSERT_INTERNET_USAGE... Tue Jun 22 17:42:01 2004: Finished real47m16.68s user10m44.47s sys 0m17.95s $ # ls -l total 1206168 -rw-rw 1 mysqlmysql379692348 Jun 22 17:05 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jun 22 17:05 bulk_table.MYI -rw-rw 1 mysqlmysql 8760 Jun 22 16:51 bulk_table.frm -rw-rw 1 mysqlmysql115443608 Jun 22 17:40 internet_usage.MYD -rw-rw 1 mysqlmysql19328000 Jun 22 17:42 internet_usage.MYI -rw-rw 1 mysqlmysql 8646 Jun 22 16:51 internet_usage.frm -rw-rw 1 mysqlmysql33437600 Jun 22 17:11 url_paths.MYD -rw-rw 1 mysqlmysql27696128 Jun 22 17:11 url_paths.MYI -rw-rw 1 mysqlmysql 8574 Jun 22 16:51 url_paths.frm -rw-rw 1 mysqlmysql 80 Jun 22 17:06 url_schemes.MYD -rw-rw 1 mysqlmysql 3072 Jun 22 17:06 url_schemes.MYI -rw-rw 1 mysqlmysql 8578 Jun 22 16:51 url_schemes.frm -rw-rw 1 mysqlmysql 646160 Jun 22 17:08 url_servers.MYD -rw-rw 1 mysqlmysql 796672 Jun 22 17:08 url_servers.MYI -rw-rw 1 mysqlmysql 8578 Jun 22 16:51 url_servers.frm -rw-rw 1 mysqlmysql24082472 Jun 22 17:28 url_visit.MYD -rw-rw 1 mysqlmysql16331776 Jun 22 17:28 url_visit.MYI -rw-rw 1 mysqlmysql 8736 Jun 22 16:51 url_visit.frm # du -sk . 603088 . I had a problem trying to get the hits results. It works through the mysql command line but if I try to run mysql hits.sql hits.txt it can't find the temp table tmpStats. js. Great catch! I believe you found the problem. Sorry about that! ;-D So... just curious... from the original 60GB of text data, how much space did the final data require (including indices)? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/22/2004 07:55 Subject: Re: load data into 2 tables and set id AM I think I fixed it! INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring, category) SELECT DISTINCT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path After this I ran the sql to insert into internet_usage and this time it finished after 10m32. I'll do some more testing and let you know how it goes. Thanks, js. Did you mean there to be duplicates in the url_visits? Do I need to use IGNORE in the following SQL? INSERT url_visit (url_server_ID, url_path_ID, querystring, category) SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL), bt.category FROM bulk_table bt INNER JOIN url_servers us ON us.server = bt.server INNER JOIN url_paths up on up.path=bt.path js. mysql select * from url_visit where urlid=1631; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category | +---+---+-++--+ | 1631 | 21720 | 630695 | cid=mrkbakisrc=catcmrkdswi|p1|rshop/catcmrkseai|p1|rshop/catcmrkhori|p1|rshop/catcmrki|p1|rshop/catcripa|p1|rgift | Online Sales | +---+---+-++--+ 1 row in set (0.01 sec) mysql select * from url_visit where urlid=1753; +---+---+-++--+ | urlid | url_server_ID | url_path_ID | query | category
Re: load data into 2 tables and set id
Mos forgot to populate the url_id column in your user table. I would use his same process but re-arrange it like this: 1) create table BIG_TABLE 2) load data infile 3) create table URL_TABLE ( url_id bigint not null auto_increment, url varchar(25) not null primary key, category ) 4) INSERT IGNORE URL_TABLE (url, category) SELECT url,category FROM BIG_TABLE 4) create table USER_TABLE ( user_id varchar?(...) primary key, date_time datetime, url_id bigint, size int ) 5) insert USER_TABLE SELECT bt.userID, bt.datetime, u.url_id, bt.size FROM BIG_TABLE bt INNER JOIN URL_TABLE u ON u.url = bt.url doing it this way lets the SQL engine handle the conversion of URLs to their IDs in the USER_TABLE...see? No scripting required at all! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, Many thanks for your reply. It sounds like this might be quicker than my perl script which parses a 1GB log file and fills the database in 40 mins at the mo. (The actual parsing only takes 1.5 mins). There's one snag which I forgot about and that's in the url_table I have another column called hits which is the number of hits for each url. I'm terrible at SQL and wondered if you might be able to suggest a way of doing this with the method above? Thanks, JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
No problem!! Please post the structures of your big_table and your url_table (whatever you called them) and I will help you to rewrite step 4 to count how many times a URL appears in the big_table. Is this bulk import a process you need to do repeatedly? if so we need to worry about updating the count column on the next batch import and not just re-creating it from the new data. That will change the query significantly. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/13/2004 12:29 Subject: Re: load data into 2 tables and set id PM Mos forgot to populate the url_id column in your user table. I would use his same process but re-arrange it like this: 1) create table BIG_TABLE 2) load data infile 3) create table URL_TABLE ( url_id bigint not null auto_increment, url varchar(25) not null primary key, category ) 4) INSERT IGNORE URL_TABLE (url, category) SELECT url,category FROM BIG_TABLE 4) create table USER_TABLE ( user_id varchar?(...) primary key, date_time datetime, url_id bigint, size int ) 5) insert USER_TABLE SELECT bt.userID, bt.datetime, u.url_id, bt.size FROM BIG_TABLE bt INNER JOIN URL_TABLE u ON u.url = bt.url doing it this way lets the SQL engine handle the conversion of URLs to their IDs in the USER_TABLE...see? No scripting required at all! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, Many thanks for your reply. It sounds like this might be quicker than my perl script which parses a 1GB log file and fills the database in 40 mins at the mo. (The actual parsing only takes 1.5 mins). There's one snag which I forgot about and that's in the url_table I have another column called hits which is the number of hits for each url. I'm terrible at SQL and wondered if you might be able to suggest a way of doing this with the method above? Thanks, JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Shawn, Thanks for helping on this. I really appreciate it. No problem!! Please post the structures of your big_table and your url_table (whatever you called them) and I will help you to rewrite step 4 to count how many times a URL appears in the big_table. mysql desc internet_usage; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | uid| varchar(10) | YES | MUL | NULL| | | time | datetime| YES | | NULL| | | ip | varchar(20) | YES | | NULL| | | action | varchar(20) | YES | | NULL| | | urlid | int(11) | YES | | NULL| | | size | int(11) | YES | | NULL| | ++-+--+-+-+---+ 6 rows in set (0.03 sec) mysql desc url_table; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra| +--+--+--+-+-++ | urlid| mediumint(9) | | PRI | NULL| auto_increment | | url | text | YES | MUL | NULL|| | hits | mediumint(9) | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +--+--+--+-+-++ 4 rows in set (0.01 sec) mysql Is this bulk import a process you need to do repeatedly? if so we need to worry about updating the count column on the next batch import and not just re-creating it from the new data. That will change the query significantly. Yes it will be a repeated process. Actually I have a backlog of 6 months data to load! Here's an example of what the data looks like: mysql select * from internet_usage limit 5; +-+-++--+---+--+ | uid | time| ip | action | urlid | size | +-+-++--+---+--+ | n58396 | 2004-06-07 21:12:16 | 21.38.25.204 | TCP_TUNNELED | 5999 | 5297 | | u344584 | 2004-06-07 21:07:12 | 21.33.136.74 | TCP_HIT | 4494 | 438 | | - | 2004-06-07 21:07:02 | 21.38.92.76| TCP_NC_MISS | 2134 | 771 | | u524797 | 2004-06-07 21:03:27 | 21.32.25.41| TCP_NC_MISS | 260 | 582 | | - | 2004-06-07 21:09:13 | 21.201.130.240 | TCP_HIT | 3112 | 542 | +-+-++--+---+--+ mysql select * from url_table limit 5; +---+---+--+---+ | urlid | url | hits | category | +---+---+--+---+ | 1 | http://www.bbc.co.uk/horoscopes/chinesehoroscopes/images/hp-snake.gif | NULL | Entertainment | | 2 | http://www.call18866.co.uk/images/logo.jpg | NULL | none | | 3 | http://m2.doubleclick.net/866421/0409_santoku_250.gif | NULL | none | | 4 | http://lysto1-dc02.ww.ad.ba.com/ | NULL | Travel| | 5 | http://www.aboutscotland.com/edin/newstreetph/sitview.jpg | NULL | Travel| +---+---+--+---+ 5 rows in set (0.00 sec) One other problem I'm having here is making the rows in internet_usage unique. At the moment I have lots of duplicates, and I was trying to create a temporary table but unfortunately got an error 27 (I think this refers to a 2GB limit). mysql CREATE TEMPORARY TABLE bad_temp AS SELECT DISTINCT * FROM internet_usage; ERROR 1030: Got error 27 from table handler Is there another way of doing this? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mos forgot to populate the url_id column in your user table. I would use his same process but re-arrange it like this: 1) create table BIG_TABLE 2) load data infile 3) create table URL_TABLE ( url_id bigint not null auto_increment, url varchar(25) not null primary key, category ) 4) INSERT IGNORE URL_TABLE (url, category) SELECT url,category FROM BIG_TABLE 4) create table USER_TABLE ( user_id varchar?(...) primary key, date_time datetime, url_id bigint, size int ) 5) insert USER_TABLE SELECT bt.userID, bt.datetime, u.url_id, bt.size FROM BIG_TABLE bt INNER JOIN URL_TABLE u ON u.url = bt.url doing it this way lets the SQL engine handle the conversion of URLs to their IDs in
Re: load data into 2 tables and set id
JS, I need one more piece of information to help make that query work for you, I need the structure of the table that you use (or would use) to bulk import those logs into. If you are running out of room you may consider further normalizing you data (which saves space, but creates more data maintenance steps). I, too, have had to deal with millions of rows of internet usage logs so I understand your pain. You can store your IP addresses in an INT and get them back in dotted notation with the MySQL functions INET_ATON() and INET_NTOA(). That will save you an average of 10 bytes PER ROW (it adds up when you are into the millions of rows). Create a Proxy_Action table (id tinyint, action varchar(20) ) and populate it with all of the actions your proxy/firewall can log. Then replace the column action varchar(20) with ProxyAction_id tinyint. (I assume there are less than 256 action messages available from your proxy?) That's 1 byte vs. 8 at the low end of your sample data (counting the null at the end of the string). First add the new column to the table, populate it with the ID values from your new Action table, then drop the old column. URLs consist (in a basic sense) of the server portion (to the left of the first single / ) and the path portion (right of the first / and left of a ? or #) and either a fragment (after the #) or a query string (after the ?) I would at least split out the server portion into it's own table. For each page request (assume 1 page and 9 pictures, all from the same server) that would be 10 rows of log data that all contain the same chunk of similar information. Reducing that heavily repeated portion of your data to an ID number will greatly help reduce the size of your database. About the non-uniqueness of your internet_usage table. Even if the same user visits the same URL multiple times (is that what you mean by repeated records?) the times should all be slightly different. If they are not different, it is still possible that the same person requested the same page twice or more during the same second (the auto-login feature of MSIE comes to mind as one culprit). OR you could have multiple users all on the same userID hitting the same page from different machines I guess I would have to see more data to understand your problem better. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/18/2004 09:40 Subject: Re: load data into 2 tables and set id AM Shawn, Thanks for helping on this. I really appreciate it. No problem!! Please post the structures of your big_table and your url_table (whatever you called them) and I will help you to rewrite step 4 to count how many times a URL appears in the big_table. mysql desc internet_usage; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | uid| varchar(10) | YES | MUL | NULL| | | time | datetime| YES | | NULL| | | ip | varchar(20) | YES | | NULL| | | action | varchar(20) | YES | | NULL| | | urlid | int(11) | YES | | NULL| | | size | int(11) | YES | | NULL| | ++-+--+-+-+---+ 6 rows in set (0.03 sec) mysql desc url_table; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra| +--+--+--+-+-++ | urlid| mediumint(9) | | PRI | NULL| auto_increment | | url | text | YES | MUL | NULL|| | hits | mediumint(9) | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +--+--+--+-+-++ 4 rows in set (0.01 sec) mysql Is this bulk import a process you
Re: load data into 2 tables and set id
Shawn, Thanks for the email below - I will go through it over later tonight in more detail (going home time now!) although I can already see good points there. JS, I need one more piece of information to help make that query work for you, I need the structure of the table that you use (or would use) to bulk import those logs into. I'm completely flexible over this as I'm still at the testing stage. You might be able to suggest the most efficient structure? Isn't the problem getting the urlid before I insert into internet_usage so that I can find the associated url. If you are running out of room you may consider further normalizing you data (which saves space, but creates more data maintenance steps). I, too, have had to deal with millions of rows of internet usage logs so I understand your pain. You can store your IP addresses in an INT and get them back in dotted notation with the MySQL functions INET_ATON() and INET_NTOA(). That will save you an average of 10 bytes PER ROW (it adds up when you are into the millions of rows). Create a Proxy_Action table (id tinyint, action varchar(20) ) and populate it with all of the actions your proxy/firewall can log. Then replace the column action varchar(20) with ProxyAction_id tinyint. (I assume there are less than 256 action messages available from your proxy?) That's 1 byte vs. 8 at the low end of your sample data (counting the null at the end of the string). First add the new column to the table, populate it with the ID values from your new Action table, then drop the old column. URLs consist (in a basic sense) of the server portion (to the left of the first single / ) and the path portion (right of the first / and left of a ? or #) and either a fragment (after the #) or a query string (after the ?) I would at least split out the server portion into it's own table. For each page request (assume 1 page and 9 pictures, all from the same server) that would be 10 rows of log data that all contain the same chunk of similar information. Reducing that heavily repeated portion of your data to an ID number will greatly help reduce the size of your database. About the non-uniqueness of your internet_usage table. Even if the same user visits the same URL multiple times (is that what you mean by repeated records?) the times should all be slightly different. If they are not different, it is still possible that the same person requested the same page twice or more during the same second (the auto-login feature of MSIE comes to mind as one culprit). OR you could have multiple users all on the same userID hitting the same page from different machines I guess I would have to see more data to understand your problem better. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine J S [EMAIL PROTECTED]To: [EMAIL PROTECTED] com cc: [EMAIL PROTECTED] Fax to: 06/18/2004 09:40 Subject: Re: load data into 2 tables and set id AM Shawn, Thanks for helping on this. I really appreciate it. No problem!! Please post the structures of your big_table and your url_table (whatever you called them) and I will help you to rewrite step 4 to count how many times a URL appears in the big_table. mysql desc internet_usage; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | uid| varchar(10) | YES | MUL | NULL| | | time | datetime| YES | | NULL| | | ip | varchar(20) | YES | | NULL| | | action | varchar(20) | YES | | NULL| | | urlid | int(11) | YES | | NULL| | | size | int(11) | YES | | NULL| | ++-+--+-+-+---+ 6 rows in set (0.03 sec) mysql desc url_table; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra| +--+--+--+-+-++ | urlid| mediumint(9) | | PRI | NULL| auto_increment | | url | text | YES | MUL | NULL|| | hits | mediumint(9) | YES | | NULL|| | category | varchar(50) | YES | | NULL|| +--+--+--+-+-++ 4 rows in set (0.01 sec) mysql Is this bulk import a process you need to do repeatedly? if so we need to worry about updating the count column on the next batch import and not just re-creating it from the new data. That will change the query significantly. Yes it will be a repeated process. Actually I have a backlog of 6 months data to load! Here's an example of what the data looks like: mysql select * from
Re: load data into 2 tables and set id
welcome to a basic overview of bulk importing and normalizing as
you go
[ author's note: if you are seeing this thread for the first time and
certain items seem to be
introduced out of context, please review all previous posts in this thread.
There has been
a lot of information already exchanged on this topic that I clipped
out of this response. Thanks! -- SG]
In an earlier post you said
The data values I have for each record are:
user_id date_time size url category
for example:
u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html
business
To me that implies that you have a standard text log where each field is
separated by a space and rows are delimited by a CRLF pair. To get that log
into MySQL you will need to do something _like_ (I say like as you will
most likely need to tweak it to accommodate your actual data)
CREATE TABLE bulk_table (
user varchar(10)
, eventdate date
, eventtime time
, size int
, url text
, category varchar(50)
);
LOAD DATA INFILE a proxy log file INTO bulk_table FIELDS TERMINATED BY '
' LINES TERMINATED BY '\r\n';
Now we can start merging the logs into your data tables.
To quote RFC 2396:
The URI syntax does not require that the scheme-specific-part have
any general structure or set of semantics which is common among all
URI. However, a subset of URI do share a common syntax for
representing hierarchical relationships within the namespace. This
generic URI syntax consists of a sequence of four main components:
scheme://authoritypath?query
each of which, except scheme, may be absent from a particular URI.
That translates into 4 logical pieces we can split a URL into:
1) the scheme -- HTTP, FTP, GOPHER, etc...
2) the authority -- that is the server (www.yahoo.com) and any port numbers
or login information
3) the path -- /somefolder/somefile.whatever
4) the query -- everything after the ?
Not part of the generic URI is that bit known as a fragment (as identified
by the #) it is ALWAYS at the end of the _entire URL_ (including the query)
when it's used. I have always lumped those and queries into the same field.
So you could create tables for each of those parts and get VERY normalized
or you can partly normalize like this:
ALTER TABLE bulk_table add server_split int not null default 0
, add path_split int not null default 0
, add server varchar(255)
, add path varchar(255)
UPDATE bulk_table
set server_split = LOCATE('/', URL , 8)-1;
UPDATE bulk_table
SET path_split = if(LOCATE('?', URL, server_split) 0, LOCATE('?', URL,
server_split), LOCATE('#', URL, server_split)-1);
UPDATE bulk_table
set server=LEFT(URL, server_split )
#those 4 new columns helped us to parse out the 3 major parts of the url
#I added them to the table so that we would not have to keep recalculating
those values later on
# if it turns out that adding the columns takes a LONG time, we can create
this table with those columns
# already created and just not import to them (change the LOAD DATA INFILE
statement slightly)
CREATE TABLE IF NOT EXISTS url_servers (
ID int not null auto_increment,
server varchar(255) primary key,
Key (ID)
)
CREATE TABLE IF NOT EXISTS url_paths (
ID int not null auto_increment,
path varchar(255) primary key,
Key (ID)
)
INSERT IGNORE INTO url_servers(server)
SELECT DISTINCT server
FROM bulk_table
INSERT IGNORE INTO url_paths (path)
SELECT DISTINCT path
FROM bulk_table
# at this point we have all of our new Servers and our Paths uniquely
numbered
# but we are going to need a slightly different URL table to track visits.
CREATE TABLE url_visit (
urlid mediumint not null auto_increment primary key,
url_server_ID int not null default 0,
url_path_ID int not null default 0,
querystring text default null,
category varchar(50)default null,
KEY(url_server_ID, url_path_ID)
)
## that last key is to speed up our joins to our _servers and _paths
tables...
# we finally have enough information to insert to the visit table
INSERT url_visit (url_server_ID, url_path_ID, querystring, category)
SELECT us.ID, up.ID, if(bt.path_split 0, SUBSTRING(bt.url,path), NULL),
bt.category
FROM bulk_table bt
INNER JOIN url_servers us
ON us.server = bt.server
INNER JOIN url_paths up
on up.path=bt.path
## (see where the new pre-computed columns come in handy?) :-D
# and now we have enough information to load the internet_usage table.
Though there isn't enough data in your sample
# source data to fill in all of the columns
INSERT internet_usage ( uid,`time`,urlid, size)
SELECT bt.user, ADDTIME(bt.date, bt.time), uv.urlid, bt.size
FROM bulk_table bt
INNER JOIN url_servers us
ON us.server = bt.server
INNER JOIN url_paths up
ON up.path=bt.path
INNER JOIN url_visit uv
ON uv.url_server_ID = us.ID
AND uv.url_path_ID = up.id
AND uv.querystring = if(bt.path_split 0, SUBSTRING(bt.url,path),
NULL)
It may not be perfect but it's how
Re: load data into 2 tables and set id
Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. js, Is this a one time operation? If so, you could create a temporary table with all the fields: BIG_TABLE user_id date_time url_id size url category and use Load Data InFile to load the data into this one table. Then create the two smaller tables using: create table USER_TABLE select user_id, date_time, url_id, size from BIG_TABLE; create table URL_TABLE select url_id, url, category from BIG_TABLE; drop table BIG_TABLE; So there you have it in 3 lines of code. :-) Mike P.S. If you could break the text file into two files to begin with, then two Load Data InFile statements would handle things nicely. Thanks Mos, that sounds ideal but the url_id value has to come from the database. js. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Mos forgot to populate the url_id column in your user table. I would use his same process but re-arrange it like this: 1) create table BIG_TABLE 2) load data infile 3) create table URL_TABLE ( url_id bigint not null auto_increment, url varchar(25) not null primary key, category ) 4) INSERT IGNORE URL_TABLE (url, category) SELECT url,category FROM BIG_TABLE 4) create table USER_TABLE ( user_id varchar?(...) primary key, date_time datetime, url_id bigint, size int ) 5) insert USER_TABLE SELECT bt.userID, bt.datetime, u.url_id, bt.size FROM BIG_TABLE bt INNER JOIN URL_TABLE u ON u.url = bt.url doing it this way lets the SQL engine handle the conversion of URLs to their IDs in the USER_TABLE...see? No scripting required at all! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mos [EMAIL PROTECTED]To: [EMAIL PROTECTED] m cc: Fax to: 06/09/2004 05:00 Subject: Re: load data into 2 tables and set id PM At 02:34 PM 6/9/2004, you wrote: Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. js, Is this a one time operation? If so, you could create a temporary table with all the fields: BIG_TABLE user_id date_time url_id size url category and use Load Data InFile to load the data into this one table. Then create the two smaller tables using: create table USER_TABLE select user_id, date_time, url_id, size from BIG_TABLE; create table URL_TABLE select url_id, url, category from BIG_TABLE; drop table BIG_TABLE; So there you have it in 3 lines of code. :-) Mike P.S. If you could break the text file into two files to begin with, then two Load Data InFile statements would handle things nicely. -- 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]
load data into 2 tables and set id
Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
J S said: Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger What language did you use to do the parsing. If it was perl I'd recommend looking at using perl's DBI interface and doing it progromaticaly. Do you have any idea of how many different url's you have. Basic steps: Get record, check to see if url is in database, if it is get the url_id. if not insert it and get the generated url_id. insert the user record using the url_id you now have. repeat until you run out of records. Not elegent but it will get the job done. Note look into documentation on how to get the new url_id after you do an insert. Its in the DBD::mysql for perl. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
At 02:34 PM 6/9/2004, you wrote: Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. js, Is this a one time operation? If so, you could create a temporary table with all the fields: BIG_TABLE user_id date_time url_id size url category and use Load Data InFile to load the data into this one table. Then create the two smaller tables using: create table USER_TABLE select user_id, date_time, url_id, size from BIG_TABLE; create table URL_TABLE select url_id, url, category from BIG_TABLE; drop table BIG_TABLE; So there you have it in 3 lines of code. :-) Mike P.S. If you could break the text file into two files to begin with, then two Load Data InFile statements would handle things nicely. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger What language did you use to do the parsing. If it was perl I'd recommend looking at using perl's DBI interface and doing it progromaticaly. Do you have any idea of how many different url's you have. Basic steps: Get record, check to see if url is in database, if it is get the url_id. if not insert it and get the generated url_id. insert the user record using the url_id you now have. repeat until you run out of records. Not elegent but it will get the job done. Note look into documentation on how to get the new url_id after you do an insert. Its in the DBD::mysql for perl. Thanks for your reply William. I am using a perl script. If I have to insert these records one by one it's going to be really slow isn't it? Maybe the quickest way is to parse the logs twice i.e. fill the URL_TABLE first using your procedure above, then on the second run, create a LOAD file for USER_TABLE? js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
J S said:
Hi,
I need some help please! I have 60GB of proxy logs to parse and load
into a mysql database. I've written a parsing script but I'm stuck
now on how to load the data in.
I have a database called PROXY_LOG with 2 tables:
USER_TABLE
user_id date_time url_id size
and
URL_TABLE
url_id url category
The data values I have for each record are:
user_id date_time size url category
for example:
u752359 2004-04-02 12:33:04 3403
http://www.mysql.com/index.html
business
The problem is I'm not sure how to load the data into the 2 tables
and set the url_id. Could anyone point me in the right direction
please?
Many thanks,
js.
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
What language did you use to do the parsing. If it was perl I'd
recommend looking at using perl's DBI interface and doing it
progromaticaly. Do you have any idea of how many different url's you
have.
Basic steps:
Get record,
check to see if url is in database, if it is get the url_id.
if not insert it and get the generated url_id.
insert the user record using the url_id you now have.
repeat until you run out of records.
Not elegent but it will get the job done. Note look into documentation
on how to get the new url_id after you do an insert. Its in the
DBD::mysql for perl.
Thanks for your reply William. I am using a perl script. If I have to
insert these records one by one it's going to be really slow isn't it?
Maybe the quickest way is to parse the logs twice i.e. fill the
URL_TABLE first using your procedure above, then on the second run,
create a LOAD file for USER_TABLE?
js.
How will you get the information for the url-id's? I can see splitting
the logs and using a load file for the url_table (if you can eliminate
duplicates). You can save some time, if you can build a perl hash with
the
$url{urlvalue} = url_id. Test that and only do inserts if you need to.
Hash look up is faster than db query, but you will have to have the hash
in memory. You can use the hash to prepare the USER_TABLE and then load
infile that.
Just thought, url is going to have to be a unique key? You can speed up
the initial inserts by inserting without that key (using the perl hash to
avoid collisions) and then altering table to add the key in. However,
question comes back to do you have enough memory for the hash in perl?
Notice also, that you don't have a rowID equivalent in the USER_TABLE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using 1 statement to delete from 2 tables
I'm using an older version of Mysql, and I know it doesn't support trigger. What I'm trying to do, is to delete records from 2 tables (Sessions Cart). These experimental statements didn't help: DELETE FROM Sessions INNER JOIN Cart on Sessions.id where Sessions.id =$id; DELETE FROM Sessions where Sessions.id =$id INNER JOIN Cart on Sessions.id ; I'll appreciate any suggestion on how to go about it. Best regard Babs
Re: Using 1 statement to delete from 2 tables
Hello B., Wednesday, April 14, 2004, 7:02:06 PM, you wrote: BF I'm using an older version of Mysql, and I know it doesn't support BF trigger. What I'm trying to do, is to delete records from 2 tables BF (Sessions Cart). In a nutshell - you cannot do it in a single SQL Query on MySQL 3.x MySQL 4 supports a cascaded delete, but 3 does not. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie question on Comparing fields in 2 tables?
I'd just like to say thanks to both Michael and Rocar for both solutions. I will be giving them a go. I now need to buy a more comprehensive manual for MySQL, as neither of these commands are in the 2 books I currently have. Many thanks Ian -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 11:29 PM To: Ian Izzard Cc: Rocar Peças; [EMAIL PROTECTED] Subject: Re: Newbie question on Comparing fields in 2 tables? An equivalent, but slightly simpler, query would be SELECT sw.pcname, sw.product FROM software sw, keywords kw WHERE sw.product RLIKE kw.Searchname RLIKE is for regular expression pattern matching. Regular expressions, unlike LIKE patterns, don't have to match the whole string, so there is no need to paste '%' onto each end. For more, see http://www.mysql.com/doc/en/String_comparison_functions.html http://www.mysql.com/doc/en/Pattern_matching.html Michael Rocar Peças wrote: Mr. Izzard, We have these tables: Table software - pcname char(..) - product char(..) Table keywords - id int(..) - searchname char(...) and you want to pick out the pcname from the software table, where the product field contains the searchname from the keywords table Try this and you´ll succeed: == SELECT software.pcname, software.product FROM software, keywords WHERE software.product LIKE CONCAT(%, keywords.searchname, %) == Best wishes, Leandro M Neves, ROCAR PEÇAS LTD. Sete Lagoas/MG - Brazil - Original Message - From: Ian Izzard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 2:05 PM Subject: Newbie question on Comparing fields in 2 tables? Hi, I am new to using MySQL and SQL queries. I have in the past only carried out simple queries. I am trying to write a query for use in our Helpdesk/Audit software. I want to compare the values in one table (Keywords) to the values found in another table (Software) so that I can get records of games that are installed on PCs. The Software table is created from an audit run on each PC. The keywords table is created by myself. The keywords table contains 2 fields, ID and Searchname. A sample of the data in this table would be: ID Searchname 1worm 2kazaa 3delta 4game The software table has 2 fields, pcname and product. A sample of the data in this table would be: pcname product SW0638CADS Support SW0638Citrix ICA Client SW0638Winzip SW0653Winzip SW0653Delta Force 2 SW0462Winzip SW0462Delta Force SW0462Worms 2000 SW0785Winzip SW0785Worms2 The software table has some 50,000 records in it. What I am looking to do is to pick out the pcname from the software table, where the product field contains the searchname from the keywords table. Something like: select pcname, product from software, keywords where product like searchname I would then expect the results to come out as: pcname product SW0653Delta Force 2 SW0462Delta Force SW0462Worms 2000 SW0785Worm2 I have tried using the LIKE command, but the manuals only show examples when comparing a field to a string, ie product LIKE 'worm%' As the keyword table is likely to get quite long (currently 163 records) I don't want to do a query using the LIKE command immediately above (, as the query will get very long and unmanageable. Can someone help with a solution to this? Can it be done in a single query? Is there a command that I just haven't yet found? If it is of any help, the version of MySQL being used 4.0.16. This version is installed by the Helpdesk/Audit software, and so cannot be upgraded. Your help is greatly appreciated. Ian Izzard Visit our web site at www.scottwilson.com Privilege and Confidentiality Notice. This e-mail and any attachments to it are intended only for the party to whom they are addressed. They may contain privileged and/or confidential information. If you have received this transmission in error, please notify the sender immediately and delete any digital copies and destroy any paper copies. Thank you. Scott Wilson Kirkpatrick Co Ltd Registered in London: No. 880328 Registered office: Scott House, Basing View, Basingstoke, Hampshire, RG21 4JG. UK. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk Visit our web site
Newbie question on Comparing fields in 2 tables?
Hi, I am new to using MySQL and SQL queries. I have in the past only carried out simple queries. I am trying to write a query for use in our Helpdesk/Audit software. I want to compare the values in one table (Keywords) to the values found in another table (Software) so that I can get records of games that are installed on PCs. The Software table is created from an audit run on each PC. The keywords table is created by myself. The keywords table contains 2 fields, ID and Searchname. A sample of the data in this table would be: ID Searchname 1worm 2kazaa 3delta 4game The software table has 2 fields, pcname and product. A sample of the data in this table would be: pcname product SW0638CADS Support SW0638Citrix ICA Client SW0638Winzip SW0653Winzip SW0653Delta Force 2 SW0462Winzip SW0462Delta Force SW0462Worms 2000 SW0785Winzip SW0785Worms2 The software table has some 50,000 records in it. What I am looking to do is to pick out the pcname from the software table, where the product field contains the searchname from the keywords table. Something like: select pcname, product from software, keywords where product like searchname I would then expect the results to come out as: pcname product SW0653Delta Force 2 SW0462Delta Force SW0462Worms 2000 SW0785Worm2 I have tried using the LIKE command, but the manuals only show examples when comparing a field to a string, ie product LIKE 'worm%' As the keyword table is likely to get quite long (currently 163 records) I don't want to do a query using the LIKE command immediately above (, as the query will get very long and unmanageable. Can someone help with a solution to this? Can it be done in a single query? Is there a command that I just haven't yet found? If it is of any help, the version of MySQL being used 4.0.16. This version is installed by the Helpdesk/Audit software, and so cannot be upgraded. Your help is greatly appreciated. Ian Izzard Visit our web site at www.scottwilson.com Privilege and Confidentiality Notice. This e-mail and any attachments to it are intended only for the party to whom they are addressed. They may contain privileged and/or confidential information. If you have received this transmission in error, please notify the sender immediately and delete any digital copies and destroy any paper copies. Thank you. Scott Wilson Kirkpatrick Co Ltd Registered in London: No. 880328 Registered office: Scott House, Basing View, Basingstoke, Hampshire, RG21 4JG. UK. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk
Re: Newbie question on Comparing fields in 2 tables?
Mr. Izzard, We have these tables: Table software - pcname char(..) - product char(..) Table keywords - id int(..) - seachname char(...) and you want to pick out the pcname from the software table, where the product field contains the searchname from the keywords table Try this and you´ll succeed: == SELECT software.pcname, software.product FROM software, keywords WHERE software.product LIKE CONCAT(%, keywords.searchname, %) == Best wishes, Leandro M Neves, ROCAR PEÇAS LTD. Sete Lagoas/MG - Brazil - Original Message - From: Ian Izzard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 2:05 PM Subject: Newbie question on Comparing fields in 2 tables? Hi, I am new to using MySQL and SQL queries. I have in the past only carried out simple queries. I am trying to write a query for use in our Helpdesk/Audit software. I want to compare the values in one table (Keywords) to the values found in another table (Software) so that I can get records of games that are installed on PCs. The Software table is created from an audit run on each PC. The keywords table is created by myself. The keywords table contains 2 fields, ID and Searchname. A sample of the data in this table would be: ID Searchname 1worm 2kazaa 3delta 4game The software table has 2 fields, pcname and product. A sample of the data in this table would be: pcname product SW0638CADS Support SW0638Citrix ICA Client SW0638Winzip SW0653Winzip SW0653Delta Force 2 SW0462Winzip SW0462Delta Force SW0462Worms 2000 SW0785Winzip SW0785Worms2 The software table has some 50,000 records in it. What I am looking to do is to pick out the pcname from the software table, where the product field contains the searchname from the keywords table. Something like: select pcname, product from software, keywords where product like searchname I would then expect the results to come out as: pcname product SW0653Delta Force 2 SW0462Delta Force SW0462Worms 2000 SW0785Worm2 I have tried using the LIKE command, but the manuals only show examples when comparing a field to a string, ie product LIKE 'worm%' As the keyword table is likely to get quite long (currently 163 records) I don't want to do a query using the LIKE command immediately above (, as the query will get very long and unmanageable. Can someone help with a solution to this? Can it be done in a single query? Is there a command that I just haven't yet found? If it is of any help, the version of MySQL being used 4.0.16. This version is installed by the Helpdesk/Audit software, and so cannot be upgraded. Your help is greatly appreciated. Ian Izzard Visit our web site at www.scottwilson.com Privilege and Confidentiality Notice. This e-mail and any attachments to it are intended only for the party to whom they are addressed. They may contain privileged and/or confidential information. If you have received this transmission in error, please notify the sender immediately and delete any digital copies and destroy any paper copies. Thank you. Scott Wilson Kirkpatrick Co Ltd Registered in London: No. 880328 Registered office: Scott House, Basing View, Basingstoke, Hampshire, RG21 4JG. UK. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question on Comparing fields in 2 tables?
An equivalent, but slightly simpler, query would be SELECT sw.pcname, sw.product FROM software sw, keywords kw WHERE sw.product RLIKE kw.Searchname RLIKE is for regular expression pattern matching. Regular expressions, unlike LIKE patterns, don't have to match the whole string, so there is no need to paste '%' onto each end. For more, see http://www.mysql.com/doc/en/String_comparison_functions.html http://www.mysql.com/doc/en/Pattern_matching.html Michael Rocar Peças wrote: Mr. Izzard, We have these tables: Table software - pcname char(..) - product char(..) Table keywords - id int(..) - searchname char(...) and you want to pick out the pcname from the software table, where the product field contains the searchname from the keywords table Try this and you´ll succeed: == SELECT software.pcname, software.product FROM software, keywords WHERE software.product LIKE CONCAT(%, keywords.searchname, %) == Best wishes, Leandro M Neves, ROCAR PEÇAS LTD. Sete Lagoas/MG - Brazil - Original Message - From: Ian Izzard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 2:05 PM Subject: Newbie question on Comparing fields in 2 tables? Hi, I am new to using MySQL and SQL queries. I have in the past only carried out simple queries. I am trying to write a query for use in our Helpdesk/Audit software. I want to compare the values in one table (Keywords) to the values found in another table (Software) so that I can get records of games that are installed on PCs. The Software table is created from an audit run on each PC. The keywords table is created by myself. The keywords table contains 2 fields, ID and Searchname. A sample of the data in this table would be: ID Searchname 1worm 2kazaa 3delta 4game The software table has 2 fields, pcname and product. A sample of the data in this table would be: pcname product SW0638CADS Support SW0638Citrix ICA Client SW0638Winzip SW0653Winzip SW0653Delta Force 2 SW0462Winzip SW0462Delta Force SW0462Worms 2000 SW0785Winzip SW0785Worms2 The software table has some 50,000 records in it. What I am looking to do is to pick out the pcname from the software table, where the product field contains the searchname from the keywords table. Something like: select pcname, product from software, keywords where product like searchname I would then expect the results to come out as: pcname product SW0653Delta Force 2 SW0462Delta Force SW0462Worms 2000 SW0785Worm2 I have tried using the LIKE command, but the manuals only show examples when comparing a field to a string, ie product LIKE 'worm%' As the keyword table is likely to get quite long (currently 163 records) I don't want to do a query using the LIKE command immediately above (, as the query will get very long and unmanageable. Can someone help with a solution to this? Can it be done in a single query? Is there a command that I just haven't yet found? If it is of any help, the version of MySQL being used 4.0.16. This version is installed by the Helpdesk/Audit software, and so cannot be upgraded. Your help is greatly appreciated. Ian Izzard Visit our web site at www.scottwilson.com Privilege and Confidentiality Notice. This e-mail and any attachments to it are intended only for the party to whom they are addressed. They may contain privileged and/or confidential information. If you have received this transmission in error, please notify the sender immediately and delete any digital copies and destroy any paper copies. Thank you. Scott Wilson Kirkpatrick Co Ltd Registered in London: No. 880328 Registered office: Scott House, Basing View, Basingstoke, Hampshire, RG21 4JG. UK. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.12 / UPDATE using 2 tables / ERROR 1114: The table is full
It seems this issue will be fixed in 4.0.13, I filed a bug and it was
closed with this report:
Sinisa Milivojevic:
Yes, this is a known problem.
It is already fixed in 4.0.13, which will come out this month.
This is a patch:
= sql/sql_update.cc 1.79 vs 1.80 =
*** /tmp/sql_update.cc-1.79-11704 Wed Mar 19 00:45:43 2003
--- 1.80/sql/sql_update.cc Wed Apr 2 17:05:30 2003
***
*** 741,747
(error != HA_ERR_FOUND_DUPP_KEY
error != HA_ERR_FOUND_DUPP_UNIQUE))
{
! if (create_myisam_from_heap(table, tmp_table_param + offset,
error, 1))
{
do_update=0;
DBUG_RETURN(1); // Not a table_is_full
error
--- 741,747
(error != HA_ERR_FOUND_DUPP_KEY
error != HA_ERR_FOUND_DUPP_UNIQUE))
{
! if (create_myisam_from_heap(tmp_table, tmp_table_param +
offset, error, 1))
{
do_update=0;
DBUG_RETURN(1); // Not a table_is_full
error
Cheers,
--
=={Gerrit Hannaert}==
IT Department, CropDesign N.V.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.12 / UPDATE using 2 tables / ERROR 1114: The table is full
I've seen this on the mailing lists just a day ago ('*have a problem'*),
and wow! I have a similar problem.
None of the suggestions in the online documentation helped. I tried both
MyISAM and InnoDB table formats. This obviously only works on MySQL
=4.0 (I'm running the 4.0.12 RPM on Linux).
UPDATE a, b
SET a.date_out='2003-04-03 15:48:06',
a.is_dirty_date_out='Y'
WHERE a.file_id=b.file_id
AND date_out='2099-12-31'
AND file_mdate = '2002-04-03 15:48:06'
AND a.storage_id='6';
ERROR 1114: The table 'a' is full
- a currently contains 1,419,724 rows
- b currently contains 825,770 rows
- the disk is in no way full, and both tables apart take up at most a
few 100 MB. The machine has 1GB of RAM.
- the SHOW TABLE STATUS FROM db LIKE 'a' output:
| Name | Type | Row_format | Rows| Avg_row_length
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment
| Create_time | Update_time | Check_time | Create_options |
Comment|
| a | InnoDB | Fixed | 1411497 | 97
| 137003008 |NULL |153534464 | 0 |1419728
| NULL| NULL| NULL | max_rows=1 | InnoDB
free: 294912 kB |
(as a solution I'm going to denormalize a bit so I don't have to update
using two tables, but this looks like a bug or a hole in the
documentation/known issues)
If anyone has any pointers I'd be happy to hear them, please CC: me
since I'm not on the mailing list (yet). The similarities between this
TABLE STATUS and the previous poster are large, but there are probably
important differences - namely table type, max_rows, Auto_increment,
Row_format are all quite different.
Cheers,
--
=={Gerrit Hannaert}==
IT Department, CropDesign N.V.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting identical rows from 2 tables (basically Row AND Row)
I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! Hal - 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: Selecting identical rows from 2 tables (basically Row AND Row)
Do you have two identical tables? If not, then doing a select the way you propose won't yield any rows. If they are identical, then you've already got the data, and so wouldn't need to select anything. I'm sure I'm misunderstanding exactly what you're trying to do, so maybe you could explain it a bit further. In any case, if you've got a unique key that tied the two tables together (or if you could set it up so that you did), that would be your best bet. Using all those values in your where clause is going to slow your selects down considerably if there aren't keys on them (and if the number of columns is variable, it would probably be difficult to have the keys to handle all combinations of columns). Brian McCain - Original Message - From: Hal Vaughan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:29 PM Subject: Selecting identical rows from 2 tables (basically Row AND Row) I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! Hal - 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: Selecting identical rows from 2 tables (basically Row AND Row)
On Friday 21 March 2003 05:42 pm, Brian McCain wrote: Do you have two identical tables? If not, then doing a select the way you propose won't yield any rows. If they are identical, then you've already got the data, and so wouldn't need to select anything. I'm sure I'm misunderstanding exactly what you're trying to do, so maybe you could explain it a bit further. I have a table where different people will select items. The items Person1 selects will go into Table1 (described as TestCases below), the items Person2 selects go in Table2 (described as TestTemp below). After Person1 and Person2 have each selected items, I want to be able to look at which items they have BOTH selected and list only those items -- only the rows selected by both. Hal remainder of correspondence follows... In any case, if you've got a unique key that tied the two tables together (or if you could set it up so that you did), that would be your best bet. Using all those values in your where clause is going to slow your selects down considerably if there aren't keys on them (and if the number of columns is variable, it would probably be difficult to have the keys to handle all combinations of columns). Brian McCain - Original Message - From: Hal Vaughan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:29 PM Subject: Selecting identical rows from 2 tables (basically Row AND Row) I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! Hal - 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 - 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
How to get the sum of rows in 2 tables ?
Hello and hope that everybody on this list will pass a wonderful 2003 year, Here's my worry: I have 3 tables, one for genre, one for html and one for shockwave html and shockwave have a foreign key genreID I want to count for each genre how many html and shockwave entries have the same genreID, so I can have as a result : genreID ! nbgenre 1 ! 6 2 ! 2 3 ! 8 where for exemple genreID 1 has 4 html and 2 shockwave entries. Here's one query I've tested and that doesn't return the addition of html and shockwave entries, but their multiplication !!!?? (in the exemple I obtain for genbreID 1 = 4 * 2 = 8 instead of 6) $query = SELECT COUNT(*) as nbgenre, g.intitule, g.genreID FROM genre g, html h, shockwave s WHERE g.genreID = s.genreID AND g.genreID = h.genreID GROUP BY g.intitule, g.genreID ORDER BY nbgenre DESC, g.intitule I've tried using LEFT JOIN from exemples on the web, but I don't have enough experience in SQL to use them correctly in my own queries, and I am not sure it would solve the problem or if it's just another way of obtaining the same (wrong) result. Cheers, Damien - 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: Counting results using 2 tables.
SELECT name,COUNT(listings.*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent GROUP BY name; IMO, joining by name is a real bad idea. Thanks for the help. The above query provided gives the follwing error. ERROR 1064: You have an error in your SQL syntax near '*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent ' at line 1 If I leave off the (listings.*) and make it just (*) it works just fine. 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
Re: Counting results using 2 tables.
Oops! I guess it doesn't work fine. The query returns 1 as the count for agents that do not have any listings. How can I remedy that? Thanks, Ed SELECT name,COUNT(listings.*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent GROUP BY name; IMO, joining by name is a real bad idea. Thanks for the help. The above query provided gives the follwing error. ERROR 1064: You have an error in your SQL syntax near '*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent ' at line 1 If I leave off the (listings.*) and make it just (*) it works just fine. 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
Counting results using 2 tables.
I wish to get a count of records contained in 1 table as compared to the contents of a different table. What I have is a list of realtors in 1 table (agents.name) The listings are contained in a different table (listings) and each can be identified by their agent by listings.agent. Here's the query I've tried using alias and COUNT but it doesn't seem to work. In fact it's telling me that the table (listings) doesn't even exist when I know it does. SELECT name, COUNT(*) as cnt from agents where agents.name = listings.agent; What I need to accomplish is to generate a report where it will list out each agent in agents followed by the number of listings posted by them in listings even if it is zero. TIA, 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
Re: Counting results using 2 tables.
On Tue, 2002-12-24 at 12:11, [EMAIL PROTECTED] wrote: I wish to get a count of records contained in 1 table as compared to the contents of a different table. What I have is a list of realtors in 1 table (agents.name) The listings are contained in a different table (listings) and each can be identified by their agent by listings.agent. Here's the query I've tried using alias and COUNT but it doesn't seem to work. In fact it's telling me that the table (listings) doesn't even exist when I know it does. SELECT name, COUNT(*) as cnt from agents where agents.name = listings.agent; SELECT name,COUNT(listings.*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent GROUP BY name; IMO, joining by name is a real bad idea. What I need to accomplish is to generate a report where it will list out each agent in agents followed by the number of listings posted by them in listings even if it is zero. TIA, 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: pulling information from 2 tables based on information from firstselect
Tim Luoma wrote: newbie warning I'm trying to write my first shell script using MySQL. I've checked Welling Thomson and Google but I think I'm searching the wrong keywords or something. The shell script will be non-interactive, but interactively this is what I would do: mysql select * from specialdays where month=6 and day=5; +---+-+-+-++ | month | day | who | uid | type | +---+-+-+-++ | 6 | 5 | Homer Burns | 8 | Birthday | +---+-+-+-++ Then I take the UID and search the 'names' table to get the corresponding email address mysql select email from names where uid=8; +--+ | email| +--+ | [EMAIL PROTECTED] | +--+ Now what I would really like to get is something that looks like this: 6/5 Homer Burns [EMAIL PROTECTED] Birthday Try: SELECT concat(s.month,'/',s.day) AS date, s.who, n.email, s.type FROM names n LEFT JOIN specialdays s USING (uid) WHERE month = 6 AND day = 5 ; I'd appreciate any tips on how to proceed I'm stumped. TjL - 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 -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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
