subject:"Query help,,,"

Re: Query Help...

2015-10-22 Thread shawn l.green




On 10/22/2015 11:48 AM, Don Wieland wrote:



On Oct 20, 2015, at 1:24 PM, shawn l.green  wrote:

Which release of MySQL are you using?


Version 5.5.45-cll


How many rows do you get if you remove the GROUP_CONCAT operator? We don't need 
to see the results. (sometimes it is a good idea to look at the raw, 
unprocessed results)

Is it possible that you are attempting to concat more values than allowed by 
--group-concat-max-len ?


When I did this I realized I was missing a GROUP BY clause

Her is the debugged working version. Thanks guys.

SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc
FROM hiv_transactions ht
LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id
WHERE ht.donor_id = 730 AND ht.tr_date BETWEEN "2015-01-01 00:00:00" AND "2015-12-31 
23:59:59"
GROUP BY ht.`transaction_id`
ORDER BY ht.tr_date DESC, ht.rec_code ASC;

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band




Thank you for sharing your solution.

Best wishes,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: Query Help...

2015-10-22 Thread Don Wieland


> On Oct 20, 2015, at 1:24 PM, shawn l.green  wrote:
> 
> Which release of MySQL are you using?

Version 5.5.45-cll

> How many rows do you get if you remove the GROUP_CONCAT operator? We don't 
> need to see the results. (sometimes it is a good idea to look at the raw, 
> unprocessed results)
> 
> Is it possible that you are attempting to concat more values than allowed by 
> --group-concat-max-len ?

When I did this I realized I was missing a GROUP BY clause

Her is the debugged working version. Thanks guys. 

SELECT 
ht.*, 
CONCAT(o.first_name, " ", o.last_name) AS orphan, 
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc 
FROM hiv_transactions ht 
LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id 
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id 
WHERE ht.donor_id = 730 AND ht.tr_date BETWEEN "2015-01-01 00:00:00" AND 
"2015-12-31 23:59:59" 
GROUP BY ht.`transaction_id`
ORDER BY ht.tr_date DESC, ht.rec_code ASC;

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band

Re: Query Help...

2015-10-20 Thread Peter Brawley


On 2015-10-20 12:54 PM, Don Wieland wrote:

Hi all,

Trying to get a query working:

SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc

FROM hiv_transactions ht

LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id

WHERE ht.donor_id = 730 AND ht.tr _date BETWEEN "2014-01-01 00:00:00" AND 
"2014-12-31 23:59:59"
ORDER BY ht.tr _date DESC, ht.rec_code ASC;



I am only showing one row of the “hiv_transactions” table when there are 
multiple rows.

On the GROUP_CONCAT I am trying to get a comma delineated list of the child 
rec_code with no duplicates

Appreciate any help. Hopefully a small mod ;-)


Group_Concat() is an aggregating function, so you need to Group By the 
column(s) on which you wish to aggregate, and for valid results you need 
to limit Selected columns to those on which you're aggregating plus 
those columns that have unique values for your aggregating columns..


PB





Don Wieland
D W   D a t a



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

Query Help...

2015-10-20 Thread Don Wieland

Hi all,

Trying to get a query working:

SELECT 
ht.*, 
CONCAT(o.first_name, " ", o.last_name) AS orphan, 
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc 

FROM hiv_transactions ht 

LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id 
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id 

WHERE ht.donor_id = 730 AND ht.tr _date BETWEEN "2014-01-01 
00:00:00" AND "2014-12-31 23:59:59" 
ORDER BY ht.tr _date DESC, ht.rec_code ASC;



I am only showing one row of the “hiv_transactions” table when there are 
multiple rows.

On the GROUP_CONCAT I am trying to get a comma delineated list of the child 
rec_code with no duplicates

Appreciate any help. Hopefully a small mod ;-)


Don Wieland
D W   D a t a

Re: Query Help...

2015-10-20 Thread shawn l.green




On 10/20/2015 1:54 PM, Don Wieland wrote:

Hi all,

Trying to get a query working:

SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc

FROM hiv_transactions ht

LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id

WHERE ht.donor_id = 730 AND ht.tr_date BETWEEN "2014-01-01 00:00:00" AND "2014-12-31 
23:59:59"
ORDER BY ht.tr_date DESC, ht.rec_code ASC;



I am only showing one row of the “hiv_transactions” table when there are 
multiple rows.

On the GROUP_CONCAT I am trying to get a comma delineated list of the child 
rec_code with no duplicates

Appreciate any help. Hopefully a small mod ;-)


Don Wieland



Which release of MySQL are you using?

How many rows do you get if you remove the GROUP_CONCAT operator? We 
don't need to see the results. (sometimes it is a good idea to look at 
the raw, unprocessed results)


Is it possible that you are attempting to concat more values than 
allowed by --group-concat-max-len ?


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Pivot Query Help

2013-11-04 Thread Jan Steinman

I'm using MySQL 5.0.92-log.

I'm trying to do a pivot-sort-of-thing. I've tried a few things from the 
O'Reilly SQL Cookbook, but I seem to be having a mental block.

I have a table of farm harvests. Each harvest has a date, quantity, and foreign 
keys into product and harvester tables:

CREATE TABLE s_product_harvest (
 id int(10) unsigned NOT NULL auto_increment,
 `date` datetime NOT NULL COMMENT 'Date and time of harvest.',
 product int(11) unsigned NOT NULL default '53',
 quantity decimal(10,3) NOT NULL default '1.000',
 units 
enum('kilograms','grams','pounds','ounces','liters','each','cords','bales') 
character set utf8 NOT NULL default 'kilograms',
 who1 int(5) unsigned NOT NULL default '2' COMMENT 'Who harvested this 
resource?',
 notes varchar(255) character set utf8 NOT NULL,
 PRIMARY KEY  (id),
 KEY product (product),
 KEY `date` (`date`),
 KEY who1 (who1),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='historical list 
of EcoReality farm products harvested';


What I want is a report with years as columns, and rows of:
first harvest (MIN(date)),
last harvest (MAX(date)),
days of harvest (DATEDIFF(MAX(date), MIN(date))) and
total (SUM(quantity)).

first/last  200720082009...
first   Aug 5   Sep 27  Aug 7
lastOct 1   Nov 24  Oct 16
days57  108 82
kg  10.17   16.746.53

This is my first attempt, and it appears to be giving me a row per year, with 
the first sequential harvest date for each year. I can get the data I want by 
making each one a separate column, but that's ugly and I want them in rows.

SELECT
 'first_last' AS `First/Last`,
 CASE WHEN YEAR(harvest.date)='2007' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2007',
 CASE WHEN YEAR(harvest.date)='2008' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2008',
 CASE WHEN YEAR(harvest.date)='2009' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2009',
 CASE WHEN YEAR(harvest.date)='2010' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2010',
 CASE WHEN YEAR(harvest.date)='2011' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2011',
 CASE WHEN YEAR(harvest.date)='2012' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2012',
 CASE WHEN YEAR(harvest.date)='2013' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2013',
 CASE WHEN YEAR(harvest.date)='2014' THEN DATE_FORMAT(harvest.date, '%b %e') 
ELSE 0 END AS '2014'
FROM
 s_product_harvest harvest
WHERE harvest.product = 4 /* product ID for tomatoes */
GROUP BY YEAR(harvest.date)

Using an example from SQL Cookbook on page 372, I tried to select from a 
subquery, grouped by a rank, but I kept getting one result row, and I can't 
figure out how to get the literal row headers.

Any ideas?

 Compared to those on pasteurized milk, children who received raw certified 
milk had better weight gain and greater protection against rachitis. -- Ron 
Schmid
 Jan Steinman, EcoReality Co-op 

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

RE: Query Help

2013-04-19 Thread Ilya Kazakevich

Hello Richard, 

the count(*) for each week of 2013 so that I end up with:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

Ilya.


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

Re: Query Help

2013-04-19 Thread Larry Martell

On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina gatorre...@gmail.com wrote:
 Hello All,

 Happy Friday! I know how to do the following query:

select count(*) from sales where WEEK(sale_date)=15 AND
 YEAR(sale_date)=2013;

 But can someone tell me I can do a query that will give me:

 the count(*) for each week of 2013 so that I end up with:

 WEEK | COUNT
 1   | 22
 2   | 31
 3   | 29
 etc...

select WEEK(sale_date), count(*) from sales where YEAR(sale_date)=2013
group by WEEK(sale_date);

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

Re: Query Help

2013-04-19 Thread Richard Reina

Perfect! Thank you Larry et all.

Have a great weekend.


2013/4/19 Larry Martell larry.mart...@gmail.com

 On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina gatorre...@gmail.com
 wrote:
  Hello All,
 
  Happy Friday! I know how to do the following query:
 
 select count(*) from sales where WEEK(sale_date)=15 AND
  YEAR(sale_date)=2013;
 
  But can someone tell me I can do a query that will give me:
 
  the count(*) for each week of 2013 so that I end up with:
 
  WEEK | COUNT
  1   | 22
  2   | 31
  3   | 29
  etc...

 select WEEK(sale_date), count(*) from sales where YEAR(sale_date)=2013
 group by WEEK(sale_date);

Query Help

2013-04-19 Thread Richard Reina

Hello All,

Happy Friday! I know how to do the following query:

select count(*) from sales where WEEK(sale_date)=15 AND
YEAR(sale_date)=2013;

But can someone tell me I can do a query that will give me:

the count(*) for each week of 2013 so that I end up with:

WEEK | COUNT
1   | 22
2   | 31
3   | 29
etc...

Thanks,

Richard

Query help -

2013-03-31 Thread william drescher


I have a table, schedule:
CREATE TABLE `schedule` (
  `schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `provider` varchar(15) NOT NULL,
  `apptTime` datetime NOT NULL,
  `location` varchar(10) NOT NULL,
  `duration` smallint(5) unsigned NOT NULL,
  `standing_script` mediumint(9) DEFAULT NULL,
  `appt_status` char(1) NOT NULL,
  `patient_number` mediumint(9) NOT NULL,
  `notify` smallint(6) DEFAULT NULL,
  `comment` varchar(80) DEFAULT NULL,
  `history` varchar(200) DEFAULT NULL,
  `posted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`schedule_id`),
  UNIQUE KEY `patient` (`patient_number`,`apptTime`,`schedule_id`),
  UNIQUE KEY `user` (`user`,`apptTime`,`schedule_id`),
  KEY `standing` (`standing_script`),
  KEY `posted` (`posted`,`user`,`apptTime`)
) ENGINE=InnoDB  DEFAULT CHARSET=ascii;

all of which can be ignored except for 'provider' and apptTime.

I want to query the database and have the result be only the next 
appointment for  'patient_number' with each user (the doctor).


eg:
2013-04-04 JSmith
2013-04-20 WJones

where the database contains:
2013-04-04 JSmith
2013-04-10 JSmith
2013-04-17 Jsmith
2013-04-20 WJones
2013-04-24 JSmith
etc

I can get a list of future appointments for 1 patient, but can't 
figure out how to just get the first for each provider (there 
might be 1..5 providers)


Any suggestions will be appreciated.
--bill


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

Re: Query help -

2013-03-31 Thread william drescher


On 3/31/2013 7:32 AM, william drescher wrote:

I have a table, schedule:
CREATE TABLE `schedule` (
   `schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT,
   `provider` varchar(15) NOT NULL,
   `apptTime` datetime NOT NULL,
   `location` varchar(10) NOT NULL,
   `duration` smallint(5) unsigned NOT NULL,
   `standing_script` mediumint(9) DEFAULT NULL,
   `appt_status` char(1) NOT NULL,
   `patient_number` mediumint(9) NOT NULL,
   `notify` smallint(6) DEFAULT NULL,
   `comment` varchar(80) DEFAULT NULL,
   `history` varchar(200) DEFAULT NULL,
   `posted` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`schedule_id`),
   UNIQUE KEY `patient` (`patient_number`,`apptTime`,`schedule_id`),
   UNIQUE KEY `user` (`user`,`apptTime`,`schedule_id`),
   KEY `standing` (`standing_script`),
   KEY `posted` (`posted`,`user`,`apptTime`)
) ENGINE=InnoDB  DEFAULT CHARSET=ascii;

all of which can be ignored except for 'provider' and apptTime.

I want to query the database and have the result be only the next
appointment for  'patient_number' with each user (the doctor).

eg:
2013-04-04 JSmith
2013-04-20 WJones

where the database contains:
2013-04-04 JSmith
2013-04-10 JSmith
2013-04-17 Jsmith
2013-04-20 WJones
2013-04-24 JSmith
etc

I can get a list of future appointments for 1 patient, but can't
figure out how to just get the first for each provider (there
might be 1..5 providers)

Any suggestions will be appreciated.
--bill


This will be a seldom used query and the schedule database is 
relatively small, so overhead is not a big deal.


--bill



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

Re: Query help - Solved

2013-03-31 Thread william drescher



of course, Group By

bill


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

NOT_REGEXP Query Help

2012-10-01 Thread John Nichel

Hi all,

  I'm having an issue using regular expressions, and I was hoping someone
can see the syntax error that I'm missing (Monday morning, not enough
coffee, etc).  Running the following query returns the error:

SELECT `a.custid`,`a.login` FROM ol.customers a WHERE `a.login` NOT_REGEXP
'^anonymous[[:digit:]]{3,}$';

Error:

MySQL said
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'NOT_REGEXP '^anonymous[[:digit:]]{3,}$'
LIMIT 0, 30' at line 1

MySQL server version 4.1.22 (yeah, I know it's old :) )

Any help would be greatly appreciated.

--
John C. Nichel IV
System Administrator
KegWorks
http://www.kegworks.com
716.362.9212 x160
j...@kegworks.com

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

Re: NOT_REGEXP Query Help

2012-10-01 Thread Rik Wasmus

   I'm having an issue using regular expressions, and I was hoping someone
 can see the syntax error that I'm missing (Monday morning, not enough
 coffee, etc).  Running the following query returns the error:
 
 SELECT `a.custid`,`a.login` FROM ol.customers a WHERE `a.login` NOT_REGEXP
 '^anonymous[[:digit:]]{3,}$';
 Error:
 
 MySQL said
 #1064 - You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use near
 'NOT_REGEXP '

It's 
NOT REGEXP
... without the underscore...
-- 
Rik Wasmus

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

RE: NOT_REGEXP Query Help

2012-10-01 Thread John Nichel

 -Original Message-
 From: Rik Wasmus [mailto:r...@grib.nl]
 Sent: Monday, October 01, 2012 10:44 AM
 To: mysql@lists.mysql.com
 Subject: Re: NOT_REGEXP Query Help

I'm having an issue using regular expressions, and I was hoping
  someone can see the syntax error that I'm missing (Monday morning,
 not
  enough coffee, etc).  Running the following query returns the error:

  SELECT `a.custid`,`a.login` FROM ol.customers a WHERE `a.login`
  NOT_REGEXP '^anonymous[[:digit:]]{3,}$';
  Error:

  MySQL said
  #1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'NOT_REGEXP '

 It's
 NOT REGEXP
 ... without the underscore...
 --
 Rik Wasmus

You know, I've been staring at this thing for the better part of an
hour...baffled as to why it isn't working.

New rule: THREE cups of coffee before I start working.

Thanks Rik!

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

query help

2012-09-13 Thread Richard Reina

I have a table like this:

|ORDERS|
|ID| DATE | QNT | LEAD |
|342  | 8-12-12 | 32   | F|
|345  | 8-15-12 | 12   | S|
|349  | 8-16-12 | 9 | R|

I am looking for a way to query it with counts by the LEAD column in
order to tell what the number of each type lead is, so that I get
something like this:

F_LEADS  S_LEADS  R_LEADS
 44 122 32

Is this possible?  If so can anyone help with syntax?

Thanks,

Richard

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

RE: query help

2012-09-13 Thread Stillman, Benjamin

I think this will get you there:

SELECT LEAD, COUNT(*) FROM ORDERS GROUP BY LEAD;

It'll give you something more like:

| LEAD | COUNT(*) |
| F | 44 |
| S | 122   |
| R | 32 |



-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com]
Sent: Thursday, September 13, 2012 9:51 AM
To: mysql@lists.mysql.com
Subject: query help

I have a table like this:

|ORDERS|
|ID| DATE | QNT | LEAD |
|342  | 8-12-12 | 32   | F|
|345  | 8-15-12 | 12   | S|
|349  | 8-16-12 | 9 | R|

I am looking for a way to query it with counts by the LEAD column in order to 
tell what the number of each type lead is, so that I get something like this:

F_LEADS  S_LEADS  R_LEADS
 44 122 32

Is this possible?  If so can anyone help with syntax?

Thanks,

Richard

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




Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

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

RE: query help

2012-09-13 Thread Rick James

Transposing is ugly in SQL.  It is better done in some other language (PHP, 
Java, ...)

If you must do it in SQL, search for
pivot Peter Brawley

 -Original Message-
 From: Stillman, Benjamin [mailto:bstill...@limitedbrands.com]
 Sent: Thursday, September 13, 2012 7:09 AM
 To: 'Richard Reina'; mysql@lists.mysql.com
 Subject: RE: query help
 
 I think this will get you there:
 
 SELECT LEAD, COUNT(*) FROM ORDERS GROUP BY LEAD;
 
 It'll give you something more like:
 
 | LEAD | COUNT(*) |
 | F | 44 |
 | S | 122   |
 | R | 32 |
 
 
 
 -Original Message-
 From: Richard Reina [mailto:gatorre...@gmail.com]
 Sent: Thursday, September 13, 2012 9:51 AM
 To: mysql@lists.mysql.com
 Subject: query help
 
 I have a table like this:
 
 |ORDERS|
 |ID| DATE | QNT | LEAD |
 |342  | 8-12-12 | 32   | F|
 |345  | 8-15-12 | 12   | S|
 |349  | 8-16-12 | 9 | R|
 
 I am looking for a way to query it with counts by the LEAD column in
 order to tell what the number of each type lead is, so that I get
 something like this:
 
 F_LEADS  S_LEADS  R_LEADS
  44 122 32
 
 Is this possible?  If so can anyone help with syntax?
 
 Thanks,
 
 Richard
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 Notice: This communication may contain privileged and/or confidential
 information. If you are not the intended recipient, please notify the
 sender by email, and immediately delete the message and any attachments
 without copying or disclosing them. LBI may, for any reason, intercept,
 access, use, and disclose any information that is communicated by or
 through, or which is stored on, its networks, applications, services,
 and devices.
 
 --
 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: query help

2012-08-07 Thread hsv

 2012/07/31 15:02 -0700, Haluk Karamete 
So, in a case like this

shop.orders.32442
shop.orders.82000
shop.orders.34442

It would be the record whose source_recid  is shop.orders.82000. Why? Cause
82000 happens to be the largest integer.

Now, if they are always 5-digit-long integers, try ending with
ORDER BY SUBSTRING_INDEX(source_recid, '.', -1)


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

RE: query help

2012-08-01 Thread Rick James

Might need some type coercion:

SELECTSUBSTRING(recid, 13, column size) AS numbers FROM table
 ORDER BY SUBSTRING(recid, 13, column size)+0  DESC

 -Original Message-
 From: Paul Halliday [mailto:paul.halli...@gmail.com]
 Sent: Tuesday, July 31, 2012 3:27 PM
 To: Haluk Karamete
 Cc: MySQL
 Subject: Re: query help

 On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete
 halukkaram...@gmail.com wrote:
  I've got a text field called source_recid. It stores half string half
  number like strings in it.

  Example

  shop.orders.32442

  the syntax is DATABASENAME.TABLENAME.RECID

  My goal is to scan this col and find out the biggest RECID ( the
  integer) in it.

  So, in a case like this

  shop.orders.32442
  shop.orders.82000
  shop.orders.34442

  It would be the record whose source_recid  is shop.orders.82000. Why?
  Cause
  82000 happens to be the largest integer.

  What SQL statement would get me that record?

  One option to this is to create a new column ( the_ids ) and move all
  the integers in it and then run something like this

  select source_recid from mytable where source_recid like
 'shop.orders.%'
  order by the_ids DESC LIMIT 1

  Is there a way to pull this off without going thru this step?

 Would substring work?

 SELECT SUBSTRING(recid, 13, column size) AS numbers FROM table ORDER
 BY numbers DESC

 --
 Paul Halliday
 http://www.pintumbler.org/

 --
 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: query help

2012-08-01 Thread Vikas Shukla

Hi,

Use LIMIT 1 to limit the number of output to single record.

Regards,

Vikas Shukla


On Wed, Aug 1, 2012 at 3:56 AM, Paul Halliday paul.halli...@gmail.comwrote:

  On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete halukkaram...@gmail.com
 wrote:
  I've got a text field called source_recid. It stores half string half
  number like strings in it.
 
  Example
 
  shop.orders.32442
 
  the syntax is DATABASENAME.TABLENAME.RECID
 
  My goal is to scan this col and find out the biggest RECID ( the
  integer) in it.
 
  So, in a case like this
 
  shop.orders.32442
  shop.orders.82000
  shop.orders.34442
 
  It would be the record whose source_recid  is shop.orders.82000. Why?
 Cause
  82000 happens to be the largest integer.
 
  What SQL statement would get me that record?
 
  One option to this is to create a new column ( the_ids ) and move all the
  integers in it and then run something like this
 
  select source_recid from mytable where source_recid like 'shop.orders.%'
  order by the_ids DESC LIMIT 1
 
  Is there a way to pull this off without going thru this step?

 Would substring work?

 SELECT SUBSTRING(recid, 13, column size) AS numbers FROM table ORDER
 BY numbers DESC


 --
 Paul Halliday
 http://www.pintumbler.org/

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

query help

2012-07-31 Thread Haluk Karamete

I've got a text field called source_recid. It stores half string half
number like strings in it.

Example

shop.orders.32442

the syntax is DATABASENAME.TABLENAME.RECID

My goal is to scan this col and find out the biggest RECID ( the
integer) in it.

So, in a case like this

shop.orders.32442
shop.orders.82000
shop.orders.34442

It would be the record whose source_recid  is shop.orders.82000. Why? Cause
82000 happens to be the largest integer.

What SQL statement would get me that record?

One option to this is to create a new column ( the_ids ) and move all the
integers in it and then run something like this

select source_recid from mytable where source_recid like 'shop.orders.%'
order by the_ids DESC LIMIT 1

Is there a way to pull this off without going thru this step?

Re: query help

2012-07-31 Thread Paul Halliday

On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete halukkaram...@gmail.com wrote:
 I've got a text field called source_recid. It stores half string half
 number like strings in it.

 Example

 shop.orders.32442

 the syntax is DATABASENAME.TABLENAME.RECID

 My goal is to scan this col and find out the biggest RECID ( the
 integer) in it.

 So, in a case like this

 shop.orders.32442
 shop.orders.82000
 shop.orders.34442

 It would be the record whose source_recid  is shop.orders.82000. Why? Cause
 82000 happens to be the largest integer.

 What SQL statement would get me that record?

 One option to this is to create a new column ( the_ids ) and move all the
 integers in it and then run something like this

 select source_recid from mytable where source_recid like 'shop.orders.%'
 order by the_ids DESC LIMIT 1

 Is there a way to pull this off without going thru this step?

Would substring work?

SELECT SUBSTRING(recid, 13, column size) AS numbers FROM table ORDER
BY numbers DESC


-- 
Paul Halliday
http://www.pintumbler.org/

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

Re: Need Query Help

2012-06-22 Thread Shawn Green


On 6/22/2012 12:18 AM, Anupam Karmarkar wrote:

Thanks Rick for your reply,

Here i am asking about logic to perpare query or whole query itself.



A set-based approach to doing the basic task is to convert your set of 
start/stop times into duration values. The timediff() function mentioned 
already is a good way to do this.


CREATE TEMPORARY TABLE tmpHours SELECT EmployeeID, timediff(logouttime, 
logintime) as duration FROM sourcetable;


At this point, you have a temporary table of (EmployeeID, duration). It 
becomes very simple to write a summary query:


SELECT employeeid, sum(duration) as totalhours from tmpHours group by 
employeeid;


If you want to breakdown your final report by other values (by date, by 
week, by shift, etc) then you need to compute those and add them to the 
tmpHours table when you create it.


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: Need Query Help

2012-06-21 Thread Anupam Karmarkar

Thanks Rick for your reply,

Here i am asking about logic to perpare query or whole query itself.

--Anupam




 From: Rick James rja...@yahoo-inc.com
To: Anupam Karmarkar sb_akarmar...@yahoo.com; mysql@lists.mysql.com 
mysql@lists.mysql.com 
Sent: Wednesday, 20 June 2012 10:52 PM
Subject: RE: Need Query Help
 
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff
and SEC_TO_TIME()/3600

 -Original Message-
 From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
 Sent: Wednesday, June 20, 2012 2:39 AM
 To: mysql@lists.mysql.com
 Subject: Need Query Help
 
 Hi All,
 
 I need query help for following table struture, where we need to
 calculate login duration of that employee for give period.
 
 Example table
 
 
 EmployeeID     LoginTime   LogoutTIme
 
 101             2012-05-01 10:00:00     2012-05-01 12:30:00
 102             2012-04-31 23:00:00      2012-05-02 05:00:00
 
 103             2012-05-01 14:00:00  NULL
 104             2012-05-02 00:10:00  2012-05-02 05:00:00
 
 
 I tried to fit all scenario in above table, Consider NULL as yet to
 logout
 
 
 How would i calcuate Employee and it Login duration for period say from
 2012-05-01 08:00:00 to 2012-05-01 22:00:00
 
 
 --Anupam

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

Need Query Help

2012-06-20 Thread Anupam Karmarkar

Hi All,

I need query help for following table struture, where we need to calculate 
login duration of that employee for give period.

Example table


EmployeeID     LoginTime   LogoutTIme

101             2012-05-01 10:00:00     2012-05-01 12:30:00
102             2012-04-31 23:00:00      2012-05-02 05:00:00  

103             2012-05-01 14:00:00  NULL    
104             2012-05-02 00:10:00  2012-05-02 05:00:00


I tried to fit all scenario in above table, Consider NULL as yet to logout


How would i calcuate Employee and it Login duration for period say from 
2012-05-01 08:00:00 to 2012-05-01 22:00:00


--Anupam

RE: Need Query Help

2012-06-20 Thread Rick James

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff
and SEC_TO_TIME()/3600

 -Original Message-
 From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
 Sent: Wednesday, June 20, 2012 2:39 AM
 To: mysql@lists.mysql.com
 Subject: Need Query Help
 
 Hi All,
 
 I need query help for following table struture, where we need to
 calculate login duration of that employee for give period.
 
 Example table
 
 
 EmployeeID     LoginTime   LogoutTIme
 
 101             2012-05-01 10:00:00     2012-05-01 12:30:00
 102             2012-04-31 23:00:00      2012-05-02 05:00:00
 
 103             2012-05-01 14:00:00  NULL
 104             2012-05-02 00:10:00  2012-05-02 05:00:00
 
 
 I tried to fit all scenario in above table, Consider NULL as yet to
 logout
 
 
 How would i calcuate Employee and it Login duration for period say from
 2012-05-01 08:00:00 to 2012-05-01 22:00:00
 
 
 --Anupam

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

Query help...

2012-05-28 Thread Don Wieland

I have been working with a query but need to add a few more  
conditions. I was a to do a query that contains a few more selects but  
want to know if there is a more efficient way to get results I need:


This query is to find NEW or FORMER CLIENT within a moving window  
(Date Range) of time.


I am starting off with finding a group of:

paid (status = 3) appointments (appts)
within a DR
in a Location (r.location_id = 1)
by on or many users (apt_user_id)

---

SELECT c.client_id, c.first_name, c.last_name, apt.*   FROM tl_appt apt

JOIN tl_rooms r on r.room_id = apt.room_id
JOIN tl_clients c on c.client_id = apt.client_id
JOIN tl_users u on u.user_id = apt.user_id

WHERE
apt.appt_id IS NOT NULL AND
apt.time_start between '1293858000' and '1325393999' AND
apt.appt_status_id = '3' and
r.location_id = '1' and
apt.appt_status_id IN (3) and
apt.user_id IN (506)

ORDER BY apt.user_id, apt.client_id, apt.time_start

---

From this above set of results, I want to find the earliest appt for  
each client.


Then from that set of rows, I want to check to see if that client has  
every had an appt earlier with ANY user, those appts can be earlier  
than the start of the date range.


If they have a count of ZERO appts prior to the earliest appt within  
the date range, the are a NEW CLIENT else a FORMER CLIENT.


Any assistance would be appreciated. Thanks!

Don Wieland

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

Re: Query help...

2012-05-28 Thread hsv

 2012/05/28 08:03 -0700, Don Wieland 
Any assistance would be appreciated. Thanks!


Maybe something like this:


SELECT usie.client_id, first_name, last_name, COUNT(anie.client_id)

FROM
(SELECT client_id, first_name, last_name, time_start
FROM tl_appt
JOIN tl_rooms USING(room_id)
JOIN tl_clients USING(client_id)
JOIN tl_users on USING(user_id)

WHERE
appt_id IS NOT NULL AND
time_start between '1293858000' and '1325393999' AND
location_id = '1' and
appt_status_id IN (3) and
user_id IN (506)

GROUP BY user_id, client_id) AS usie

LEFT JOIN

(SELECT client_id, time_start
FROM tl_appt
JOIN tl_rooms USING(room_id)
JOIN tl_clients USING(client_id)

WHERE
appt_id IS NOT NULL AND
location_id = '1' and
appt_status_id IN (3)) AS anie

ON usie.client_id = anie.client_id AND usie.time_start  anie.time_start
GROUP BY usie.client_id

**
Scanned by  MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**



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

Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

2012-05-19 Thread Mark Kelly

Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

 Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


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

RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

2012-05-19 Thread Daevid Vincent

There are a bunch of other columns in all these tables. A quick reason is
need the dvd.title too therefore the dvd table is needed. Another reason is
that the query is generated programmatically based upon parameters passed to
a method. But yes, I do she your point and maybe I can refactor some things
in this special case.

I haven't tried your query as I'm home and not at work right ATM, but I
think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows
all with the same dvd_id since multiple scene_ids will match.

d

-Original Message-
From: Mark Kelly [mailto:my...@wastedtimes.net] 
Sent: Saturday, May 19, 2012 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene
of a certain encoding format

Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

 Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene
table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


-- 
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: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

2012-05-19 Thread Baron Schwartz

I would work from the inside out. What you're doing is grouping scenes
by DVD and throwing away the ones that have no scenes. If you start
with DVDs and do a subquery for each row, you'll process DVDs without
scenes and then filter them out. If you start with a subquery that's
grouped by DVD ID, alias it with an AS clause, and then join from that
into the other tables, you can avoid that. It requires a little
backwards-thinking but it tends to work well in a lot of cases.  It
would look something like this. Here's the query against the scenes:

select dvd_id, count(*) as cnt from scenes_list group by dvd_id having
count(*)  0;

Now you can put that into a subquery and join to it:

select ...
from (
  copy/paste the above
) as s_sl
inner join dvds using (dvd_id)
rest of query;

I'm taking shortcuts because you said there is more to this query than
you've shown us, so I won't spend the time to make it a complete
query.

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

SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

2012-05-18 Thread Daevid Vincent

I have a table of DVDs, another of scenes and a last one of encoding
formats/files...

I want to find in one query all the dvd_id that have  0 scene_id that's
encoded in format_id = 13.
In other words all DVDs that are format_id = 13 despite not having a direct
link.

CREATE TABLE `dvds` (
  `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
  `dvd_title` varchar(64) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`dvd_id`),
)

CREATE TABLE `scenes_list` (
  `scene_id` int(11) NOT NULL auto_increment,
  `dvd_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  PRIMARY KEY  (`scene_id`),
)

CREATE TABLE `moviefiles` (
  `scene_id` int(11) NOT NULL default '0',
  `format_id` int(3) NOT NULL default '0',
  `filename` varchar(255),
  `volume` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`scene_id`,`format_id`),
)



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

RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

2012-05-18 Thread Daevid Vincent

 -Original Message-
 Sent: Friday, May 18, 2012 5:34 PM
 
 I have a table of DVDs, another of scenes and a last one of encoding
 formats/files...
 
 I want to find in one query all the dvd_id that have  0 scene_id that's
 encoded in format_id = 13.
 In other words all DVDs that are format_id = 13 despite not having a
direct
 link.
 
 CREATE TABLE `dvds` (
   `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
   `dvd_title` varchar(64) NOT NULL default '',
   `description` text NOT NULL,
   PRIMARY KEY  (`dvd_id`),
 )
 
 CREATE TABLE `scenes_list` (
   `scene_id` int(11) NOT NULL auto_increment,
   `dvd_id` int(11) NOT NULL default '0',
   `description` text NOT NULL,
   PRIMARY KEY  (`scene_id`),
 )
 
 CREATE TABLE `moviefiles` (
   `scene_id` int(11) NOT NULL default '0',
   `format_id` int(3) NOT NULL default '0',
   `filename` varchar(255),
   `volume` smallint(6) NOT NULL default '0',
   PRIMARY KEY  (`scene_id`,`format_id`),
 )

Actually, I may have figured it out. Is there a better way to do this?

SELECT DISTINCT 
d.`dvd_id` AS `id`,
(SELECT 
COUNT(s_sl.scene_id) AS s_tally 
FROM
scenes_list AS s_sl 
JOIN moviefiles AS s_mf USING (scene_id) 
WHERE s_sl.dvd_id = d.`dvd_id` 
AND s_mf.format_id = 13) AS s_tally 
FROM
`dvds` AS d 
WHEREd.`date_release` = '2012-05-18' 
HAVING s_tally  0 
ORDER BY d.`date_release` DESC;


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

Query help,,,

2012-05-17 Thread Don Wieland


Hi folks,

I am trying to compile a query that does statistics on appointments  
based on specific criteria. Here is my starting query:


SELECT
u.user_id,
c.client_id,
c.first_name,
c.last_name,
a.time_start AS stime,
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted

 FROM tl_appt a
  LEFT JOIN tl_users u ON a.user_id = u.user_id
  LEFT JOIN tl_clients c ON a.client_id = c.client_id
  LEFT JOIN tl_rooms r on a.room_id = r.room_id

  WHERE a.appt_id IS NOT NULL AND FROM_UNIXTIME(a.time_start,'%Y-%m- 
%d') between '2011-05-01' and '2011-12-31' and r.location_id = '2' and  
a.user_id IN (14) ORDER BY u.last_name, u.first_name, c.last_name,  
c.first_name


This will return a set of rows where a client may have MORE THEN ONE  
appointment. From this set I need to narrow more:


1) Only display the first appointment PER Client. (there will be no  
duplicate client_id)


Then once I have that set of rows established, I need to query for two  
more result:


1) Show New Customers = those where the COUNT of appointments (in the  
entire tl_appt table) LESS THAN the stime = 0


2) Show FORMER Customers = those where the COUNT of appointments (in  
the entire tl_appt table) LESS THAN the stime  0


I am sure I need a multiple select query, but I am having a hard time  
wrapping my head around it.


Thanks for any feedback.

Don

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

RE: Query help,,,

2012-05-17 Thread Rick James

For (1), ponder:

Group-by trick example: Find the most populous city in each state:

SELECT  state, city, population, COUNT(*) AS num_cities
FROM
  ( SELECT  state, city, population
FROM  us
ORDER BY  state, population DESC ) p
GROUP BY  state
ORDER BY  state;
+---+-+++
| state | city| population | num_cities |
+---+-+++
| AK| Anchorage   | 276263 | 16 |
| AL| Birmingham  | 231621 | 58 |
| AR| Little Rock | 184217 | 40 |
| AZ| Phoenix |1428509 | 51 |
| CA| Los Angeles |3877129 |447 |
...

 -Original Message-
 From: Don Wieland [mailto:d...@pointmade.net]
 Sent: Thursday, May 17, 2012 7:37 AM
 To: mysql@lists.mysql.com
 Subject: Query help,,,
 
 Hi folks,
 
 I am trying to compile a query that does statistics on appointments
 based on specific criteria. Here is my starting query:
 
 SELECT
  u.user_id,
  c.client_id,
  c.first_name,
  c.last_name,
  a.time_start AS stime,
  FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted
 
   FROM tl_appt a
LEFT JOIN tl_users u ON a.user_id = u.user_id
LEFT JOIN tl_clients c ON a.client_id = c.client_id
LEFT JOIN tl_rooms r on a.room_id = r.room_id
 
WHERE a.appt_id IS NOT NULL AND FROM_UNIXTIME(a.time_start,'%Y-%m-
 %d') between '2011-05-01' and '2011-12-31' and r.location_id = '2' and
 a.user_id IN (14) ORDER BY u.last_name, u.first_name, c.last_name,
 c.first_name
 
 This will return a set of rows where a client may have MORE THEN ONE
 appointment. From this set I need to narrow more:
 
 1) Only display the first appointment PER Client. (there will be no
 duplicate client_id)
 
 Then once I have that set of rows established, I need to query for two
 more result:
 
 1) Show New Customers = those where the COUNT of appointments (in the
 entire tl_appt table) LESS THAN the stime = 0
 
 2) Show FORMER Customers = those where the COUNT of appointments (in
 the entire tl_appt table) LESS THAN the stime  0
 
 I am sure I need a multiple select query, but I am having a hard time
 wrapping my head around it.
 
 Thanks for any feedback.
 
 Don
 
 --
 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: Query help,,,

2012-05-17 Thread Peter Brawley


On 2012-05-17 9:37 AM, Don Wieland wrote:

Hi folks,

I am trying to compile a query that does statistics on appointments 
based on specific criteria. Here is my starting query:


SELECT
u.user_id,
c.client_id,
c.first_name,
c.last_name,
a.time_start AS stime,
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') AS formatted

 FROM tl_appt a
  LEFT JOIN tl_users u ON a.user_id = u.user_id
  LEFT JOIN tl_clients c ON a.client_id = c.client_id
  LEFT JOIN tl_rooms r on a.room_id = r.room_id

  WHERE a.appt_id IS NOT NULL AND 
FROM_UNIXTIME(a.time_start,'%Y-%m-%d') between '2011-05-01' and 
'2011-12-31' and r.location_id = '2' and a.user_id IN (14) ORDER BY 
u.last_name, u.first_name, c.last_name, c.first_name


This will return a set of rows where a client may have MORE THEN ONE 
appointment. From this set I need to narrow more:


1) Only display the first appointment PER Client. (there will be no 
duplicate client_id)


Then once I have that set of rows established, I need to query for two 
more result:


1) Show New Customers = those where the COUNT of appointments (in the 
entire tl_appt table) LESS THAN the stime = 0


2) Show FORMER Customers = those where the COUNT of appointments (in 
the entire tl_appt table) LESS THAN the stime  0


I am sure I need a multiple select query, but I am having a hard time 
wrapping my head around it.


Thanks for any feedback.


Conceptually the hard bit might be narrowing to the first appt per 
client. For various approaches to this task see Within-group 
aggregates at http://www.artfulsoftware.com/infotree/queries.php.


If new  former clients are to be retrieved from first appts only, you 
might want to save the result of the within-groups agggregate query to a 
result table and query that. If the whole process has to happen often, 
consider developing a wee cube, or just a denormalised reslt table that 
can be invoked whenever needed.


PB

-



Don



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

A Simple Query Help

2012-04-22 Thread Rafael Ribeiro

Dear Friends,

 

I m new on this list, and I m trying to learn more about mysql.

 

After perform a lot of searchs in the Internet, I have no answer to my
question and would like to ask your help.

 

I wanna a perform a query that depends of the result from another (query)
table inside the same database.

 

On this scenario:

 

I have 02 tables:

 

Table 1 = users

Table 2 = sent_emails

 

 

I wanna select ONLY the users that are NOT inside the table SENT_emails

 

Example:

 

$query1 = SELECT * FROM users WHERE accept_email = 1 

 

The results from query above SHOULD depends of the query Bellow:

 

$query2 = SELECT * FROM sent_emails WHERE email = $email_from_query_above
AND messageID NOT LIKE = XX

 

The results of the first query, should display only the users that are NOT
inside the condition of query 2.

 

I read about INNER JOIN LEFT ... but I can´t understand ...

 

Can help me?

 

With Regards,

Rafael Ribeiro

Re: A Simple Query Help

2012-04-22 Thread Igor Shevtsov

Hi Rafael,
You can try using correlated subquery instead of outer join. This can be slow 
with big tables though:

SELECT * FROM users WHERE accept_email = 1 and email not in (SELECT email FROM 
sent_emails WHERE sent_emails
.email = users.email AND messageID NOT LIKE = ‘XX’) 

OR OUTER JOIN as a better option:

SELECT u.* FROM users AS u OUTER LEFT JOIN sent_emails AS se USING (email) 
where u.accept_email = 1 AND se.messageID NOT LIKE = ‘XX’ AND se.email IS 
NULL



Thanks,
Egor




SELECT * FROM sent_emails WHERE email in (SELECT email FROM users WHERE 
accept_email = 1)
AND messageID NOT LIKE = ‘XX’



On 04/22/2012 09:30 PM, Rafael Ribeiro wrote:
 Dear Friends,

  

 I m new on this list, and I m trying to learn more about mysql.

  

 After perform a lot of searchs in the Internet, I have no answer to my
 question and would like to ask your help.

  

 I wanna a perform a query that depends of the result from another (query)
 table inside the same database.

  

 On this scenario:

  

 I have 02 tables:

  

 Table 1 = users

 Table 2 = sent_emails

  

  

 I wanna select ONLY the users that are NOT inside the table SENT_emails

  

 Example:

  

 $query1 = SELECT * FROM users WHERE accept_email = ‘1’ 

  

 The results from query above SHOULD depends of the query Bellow:

  

 $query2 = SELECT * FROM sent_emails WHERE email = $email_from_query_above
 AND messageID NOT LIKE = ‘XX’

  

 The results of the first query, should display only the users that are NOT
 inside the condition of query 2.

  

 I read about INNER JOIN LEFT ... but I can´t understand ...

  

 Can help me?

  

 With Regards,

 Rafael Ribeiro

  

  

  

  

  

  



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

Re: Multi select Query help...

2012-03-03 Thread Hal�sz S�ndor

 2012/03/01 19:56 -0800, Don Wieland 
I do not get the same results. Am I missing something? Hopefully  
something simple ;-)

O, you are. You do not want GROUP_CONCAT in the subquery. It gives you the 
comma-separated string whereto you referred, which, as far as the IN goes, is 
only one string for comparing for equality. You want the IDs separate, not 
joined into one string.


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

Multi select Query help...

2012-03-01 Thread Don Wieland


Appreciate a little guidance here:

Background: I have an invoicing system. Invoices are generated and  
(invoice and Invoice Items) and Payments are generated (Payments and  
Payment Items). Payment items are amount of the Payment Total  
allocated to payoff open invoices. So I may have 3 open invoice for  
$100 each and I may generate one payment for $300 with 3 payment items  
for $100 each to pay off those 3 open invoices.


In most cases, clients will pay their own invoices off, but in rare  
cases another client will pay an invoice for that client (ie...  
spouse, parent, etc...) My client want me to some how display when the  
payee (or one of the payees - there can be multiple) of the invoice IS  
NOT the same client as the invoice being paid.


So I need to display a result that show a comma delineated string of  
payees OMITTING the invoice's client_id. I only want to show a result  
if one or more of the payees are different than the invoice's client_id.


So now with the mySQL queries that are working:

First of all, the client_id of the invoice I am querying on is 251719.

query 1 = select group_concat(payment_id) from tl_trans_pmt_items  
where inv_id = 1033911


This produces a string 1033882,1021630,1021632. These are parent  
Payment records which have the payee client_ids.


So if I run a query:

query 2 = select group_concat(client_id) FROM tl_transactions WHERE  
transaction_id IN (1033882,1021630,1021632)  AND client_id != 251719


This produces a string 251711,251713. These are the client_ids of  
the Payment records OMITTING the Invoice's client_id


So far this works fine. Now where I run into issues is where I try to  
combine these queries together:


query 3 = select group_concat(client_id) FROM tl_transactions WHERE  
transaction_id IN ((select group_concat(payment_id) from  
tl_trans_pmt_items where inv_id = 1033911))  AND client_id != 251719


I do not get the same results. Am I missing something? Hopefully  
something simple ;-)


Don





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

Query help...

2012-02-29 Thread Don Wieland


Little help...

In my mySQL query editor, I am trying to return a value of 0 when  
there is no related rows from this query:


(select if(count(ip.payment_amount) IS NOT NULL,  
count(ip.payment_amount) , 0)  FROM tl_trans_pmt_items ip WHERE  
t.transaction_id = ip.inv_id GROUP BY ip.inv_id) as d,


regardless of the combination I use, invalid relationships come back  
as NULL - need to return 0  so I can use it in a math formula.


Probably simple - maybe ;-)

Don

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

Re: Query help...

2012-02-29 Thread Singer X.J. Wang

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

On Wed, Feb 29, 2012 at 13:15, Don Wieland d...@pointmade.net wrote:

 Little help...

 In my mySQL query editor, I am trying to return a value of 0 when there is
 no related rows from this query:

 (select if(count(ip.payment_amount) IS NOT NULL, count(ip.payment_amount)
 , 0)  FROM tl_trans_pmt_items ip WHERE t.transaction_id = ip.inv_id GROUP
 BY ip.inv_id) as d,

 regardless of the combination I use, invalid relationships come back as
 NULL - need to return 0  so I can use it in a math formula.

 Probably simple - maybe ;-)

 Don

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



-- 
--
Pythian proud winner of Oracle North America Titan Award for Exadata 
Solution...watch the video on pythian.com

Re: Query help...

2012-02-29 Thread Michael Heaney


On 2/29/2012 1:15 PM, Don Wieland wrote:

Little help...

In my mySQL query editor, I am trying to return a value of 0 when
there is no related rows from this query:

(select if(count(ip.payment_amount) IS NOT NULL,
count(ip.payment_amount) , 0)  FROM tl_trans_pmt_items ip WHERE
t.transaction_id = ip.inv_id GROUP BY ip.inv_id) as d,

regardless of the combination I use, invalid relationships come back
as NULL - need to return 0  so I can use it in a math formula.

Probably simple - maybe ;-)

Don




I think you want the 'coalesce' function:

Syntax:
COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no
non-NULL values.

URL: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

Examples:
mysql SELECT COALESCE(NULL,1);
- 1


Michael Heaney
JCVI


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

RE: Within-group aggregate query help please - customers and latest subscription row

2011-10-25 Thread Hal�sz S�ndor

 2011/10/24 16:31 -0700, Daevid Vincent 
  WHERE cs.customer_id = 7
GROUP BY customer_id

Well, the latter line is now redundant.

How will you make the '7' into a parameter?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Within-group aggregate query help please - customers and latest subscription row

2011-10-24 Thread Daevid Vincent

I know this is a common problem, and I've been struggling with it for a full
day now but I can't get it. 

I also tried a few sites for examples:
http://www.artfulsoftware.com/infotree/queries.php#101
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
-problem-10210.html

Anyways, pretty standard situation:

CREATE TABLE `customers` (
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(64) NOT NULL default '',
  `name` varchar(128) NOT NULL default '',
  `username` varchar(32) NOT NULL,
...
);

CREATE TABLE `customers_subscriptions` (
  `subscription_id` bigint(12) unsigned NOT NULL default '0',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `date` date NOT NULL default '-00-00',
  ...
);

I want to show a table where I list out the ID, email, username, and LAST
SUBSCRIPTION.

I need this data in TWO ways:

The FIRST way, is with a query JOINing the two tables so that I can easily
display that HTML table mentioned. That is ALL customers and the latest
subscription they have.

The SECOND way is when I drill into the customer, I already know the
customer_id and so don't need to JOIN with that table, I just want to get
the proper row from the customers_subscriptions table itself.

SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
`date` DESC;

subscription_id  processor  customer_id  date 
---  -  ---  --  
  134126370  chargem  7  2005-08-04  
 1035167192  billme   7  2004-02-08  

SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
GROUP BY customer_id;

gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a
crap on your face when you try what would seem to be the right query:

SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
customer_id = 7 GROUP BY customer_id;

subscription_id  MAX(`date`)
---  ---
 1035167192  2005-08-04 
 
Notice how I have the correct DATE, but the wrong subscription_id.

In the example web sites above, they seem to deal more with finding the
MAX(subscription_id), which in my case will not work.

I need the max DATE and the corresponding row (with matching
subscription_id).

Thanks,

d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Within-group aggregate query help please - customers and latest subscription row

2011-10-24 Thread Daevid Vincent

A kind (and shy) soul replied to me off list and suggested this solution,
however,
this takes 28 seconds (that's for a single customer_id, so this is not going
to scale). 
Got any other suggestions? :-)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
GROUP BY customer_id) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate 
WHERE c.customer_id = 7;

There are 781,270 customers (nearly 1 million) and  1,018,092
customer_subscriptions.

Our tables have many indexes on pretty much every column and for sure the
ones we use here.

EXPLAIN says:

id  select_type  table   typepossible_keys key
key_len  refrows  Extra  
--  ---  --  --    ---
---  --  ---  ---
 1  PRIMARY  c   const   PRIMARY   PRIMARY  4
const 1 
 1  PRIMARY  s   ref date,customer_id  customer_id  4
const 2 
 1  PRIMARY  derived2  ALL (NULL)(NULL)
(NULL)   (NULL)   781265  Using where
 2  DERIVED  cs  ALL (NULL)(NULL)
(NULL)   (NULL)  1018092  Using temporary; Using filesort

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, October 24, 2011 1:46 PM
 To: mysql@lists.mysql.com
 Subject: Within-group aggregate query help please - customers and latest
 subscription row
 
 I know this is a common problem, and I've been struggling with it for a
full
 day now but I can't get it.
 
 I also tried a few sites for examples:
 http://www.artfulsoftware.com/infotree/queries.php#101

http://forums.devarticles.com/general-sql-development-47/select-max-datetime
 -problem-10210.html
 
 Anyways, pretty standard situation:
 
 CREATE TABLE `customers` (
   `customer_id` int(10) unsigned NOT NULL auto_increment,
   `email` varchar(64) NOT NULL default '',
   `name` varchar(128) NOT NULL default '',
   `username` varchar(32) NOT NULL,
   ...
 );
 
 CREATE TABLE `customers_subscriptions` (
   `subscription_id` bigint(12) unsigned NOT NULL default '0',
   `customer_id` int(10) unsigned NOT NULL default '0',
   `date` date NOT NULL default '-00-00',
   ...
 );
 
 I want to show a table where I list out the ID, email, username, and LAST
 SUBSCRIPTION.
 
 I need this data in TWO ways:
 
 The FIRST way, is with a query JOINing the two tables so that I can easily
 display that HTML table mentioned. That is ALL customers and the latest
 subscription they have.
 
 The SECOND way is when I drill into the customer, I already know the
 customer_id and so don't need to JOIN with that table, I just want to get
 the proper row from the customers_subscriptions table itself.
 
 SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
 `date` DESC;
 
 subscription_id  processor  customer_id  date
 ---  -  ---  --
   134126370  chargem  7  2005-08-04
  1035167192  billme   7  2004-02-08
 
 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
 GROUP BY customer_id;
 
 gives me 2005-08-04 obviously, but as you all know, mySQL completely takes
a
 crap on your face when you try what would seem to be the right query:
 
 SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
 customer_id = 7 GROUP BY customer_id;
 
 subscription_id  MAX(`date`)
 ---  ---
  1035167192  2005-08-04
 
 Notice how I have the correct DATE, but the wrong subscription_id.
 
 In the example web sites above, they seem to deal more with finding the
 MAX(subscription_id), which in my case will not work.
 
 I need the max DATE and the corresponding row (with matching
 subscription_id).
 
 Thanks,
 
 d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Within-group aggregate query help please - customers and latest subscription row

2011-10-24 Thread Daevid Vincent

Okay, it seems I am learning... slowly...

So there needs to be a second WHERE in the sub-select...

To get ONE customer's last subscription (0.038s):

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  WHERE cs.customer_id = 7
GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
WHERE c.customer_id = 7;

To get ALL customers and their last subscription row (1m:28s)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
ORDER BY customer_id LIMIT 10;

Thanks to you know who you are for pointing me in the right direction. 

Hopefully this helps someone else.

d.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, October 24, 2011 4:06 PM
 To: mysql@lists.mysql.com
 Subject: RE: Within-group aggregate query help please - customers and
latest
 subscription row
 
 A kind (and shy) soul replied to me off list and suggested this solution,
 however,
 this takes 28 seconds (that's for a single customer_id, so this is not
going
 to scale).
 Got any other suggestions? :-)
 
 SELECT
 c.customer_id,
 c.email,
 c.name,
 c.username,
 s.subscription_id,
 s.`date`
 FROM
 customers AS c
 INNER JOIN customers_subscriptions AS s
 ON c.customer_id = s.customer_id
 INNER JOIN
 (SELECT
 MAX(`date`) AS LastDate,
 customer_id
 FROM
 customers_subscriptions AS cs
 GROUP BY customer_id) AS `x`
 ON s.customer_id = x.customer_id
 AND s.date = x.LastDate
 WHERE c.customer_id = 7;
 
 There are 781,270 customers (nearly 1 million) and  1,018,092
 customer_subscriptions.
 
 Our tables have many indexes on pretty much every column and for sure the
 ones we use here.
 
 EXPLAIN says:
 
 id  select_type  table   typepossible_keys key
 key_len  refrows  Extra
 --  ---  --  --    ---
 ---  --  ---  ---
  1  PRIMARY  c   const   PRIMARY   PRIMARY  4
 const 1
  1  PRIMARY  s   ref date,customer_id  customer_id  4
 const 2
  1  PRIMARY  derived2  ALL (NULL)(NULL)
 (NULL)   (NULL)   781265  Using where
  2  DERIVED  cs  ALL (NULL)(NULL)
 (NULL)   (NULL)  1018092  Using temporary; Using filesort
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Monday, October 24, 2011 1:46 PM
  To: mysql@lists.mysql.com
  Subject: Within-group aggregate query help please - customers and latest
  subscription row
 
  I know this is a common problem, and I've been struggling with it for a
 full
  day now but I can't get it.
 
  I also tried a few sites for examples:
  http://www.artfulsoftware.com/infotree/queries.php#101
 

http://forums.devarticles.com/general-sql-development-47/select-max-datetime
  -problem-10210.html
 
  Anyways, pretty standard situation:
 
  CREATE TABLE `customers` (
`customer_id` int(10) unsigned NOT NULL auto_increment,
`email` varchar(64) NOT NULL default '',
`name` varchar(128) NOT NULL default '',
`username` varchar(32) NOT NULL,
  ...
  );
 
  CREATE TABLE `customers_subscriptions` (
`subscription_id` bigint(12) unsigned NOT NULL default '0',
`customer_id` int(10) unsigned NOT NULL default '0',
`date` date NOT NULL default '-00-00',
...
  );
 
  I want to show a table where I list out the ID, email, username, and
LAST
  SUBSCRIPTION.
 
  I need this data in TWO ways:
 
  The FIRST way, is with a query JOINing the two tables so that I can
easily
  display that HTML table mentioned. That is ALL customers and the latest
  subscription they have.
 
  The SECOND way

Optimize query help.

2011-03-15 Thread Paul Nowosielski

Dear all,


I have a query that takes a rather long time and was wondering if there is 
anyway to optimize it.
Normally we removing duplicate records by phone number. This query takes about 
a 
second and 

it really slows down the process when we are importing several 1000 records a 
day.

Here is the query:

SELECT count(id) c  FROM leads
WHERE (phone_home = '(770) 512-8990' 
OR phone_work = '(770) 512-8990' 
OR phone_other = '(770) 512-8990'  
OR phone_mobile = '(770) 512-8990' 
OR phone_fax = '(770) 512-8990')
AND date_entered DATE_SUB(NOW(),INTERVAL 45 DAY) 
AND deleted != '1';

This is the describe:

DESCRIBE SELECT count( id ) c
FROM leads
WHERE (
phone_home = '(770) 512-8990'
OR phone_work = '(770) 512-8990'
OR phone_other = '(770) 512-8990'
OR phone_mobile = '(770) 512-8990'
OR phone_fax = '(770) 512-8990')
AND date_entered  DATE_SUB( NOW( ) , INTERVAL 45 
DAY ) 
AND deleted != '1'


id 
select_type 
table 
type 
possible_keys 
key 
key_len 
ref 
rows 
Extra 

1 SIMPLE leads ALL 
idx_del_user,phone_home,phone_mobile,phone_work,phone_other,phone_fax,date_entered
 NULL NULL NULL 636433 Using where 

Any thoughts?

Thank you,

Paul




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Optimize query help.

2011-03-15 Thread Michael Dykman

The OR conditions require a full table scan everytime this is called.
You didn't say how many rows you had, nor if there were indexes on
your various phone_xxx fields.  If you do, you should get some value
by approaching it as a UNION

select count(id)from (
   select id from leads where phone_work = 'xx'
UNION
   select id from leads where phone_home = 'xx'
UNION
 -- etc..
) tmp

 - michael dykman

On Tue, Mar 15, 2011 at 1:15 PM, Paul Nowosielski
paulnowosiel...@yahoo.com wrote:
 Dear all,


 I have a query that takes a rather long time and was wondering if there is
 anyway to optimize it.
 Normally we removing duplicate records by phone number. This query takes 
 about a
 second and

 it really slows down the process when we are importing several 1000 records a
 day.

 Here is the query:

 SELECT count(id) c  FROM leads
 WHERE (phone_home = '(770) 512-8990'
 OR phone_work = '(770) 512-8990'
 OR phone_other = '(770) 512-8990'
 OR phone_mobile = '(770) 512-8990'
 OR phone_fax = '(770) 512-8990')
 AND date_entered DATE_SUB(NOW(),INTERVAL 45 DAY)
 AND deleted != '1';

 This is the describe:

 DESCRIBE SELECT count( id ) c
 FROM leads
 WHERE (
 phone_home = '(770) 512-8990'
 OR phone_work = '(770) 512-8990'
 OR phone_other = '(770) 512-8990'
 OR phone_mobile = '(770) 512-8990'
 OR phone_fax = '(770) 512-8990')
 AND date_entered  DATE_SUB( NOW( ) , INTERVAL 45
 DAY )
 AND deleted != '1'


 id
 select_type
 table
 type
 possible_keys
 key
 key_len
 ref
 rows
 Extra

 1 SIMPLE leads ALL
 idx_del_user,phone_home,phone_mobile,phone_work,phone_other,phone_fax,date_entered
  NULL NULL NULL 636433 Using where

 Any thoughts?

 Thank you,

 Paul




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query help

2011-03-02 Thread Tompkins Neil

Hi

I've the following basic table

login_id
email_address
ip_address

I want to extract all records from this table in which a user has used the
same IP address but different email address to login ?

Thanks,
Neil

Re: Query help

2011-03-02 Thread Claudio Nanni

Hi Neil,

select
   login_id,
   ip_address
from
   basic_table
group by
   login_id,ip_address
having
   count(login_id,ip_address)1

this should work

in case you want to see also the list of emails add:

   group_concat(email_address,',') as list_of_used_emails

to the select fields.


Claudio




2011/3/2 Tompkins Neil neil.tompk...@googlemail.com

 Hi

 I've the following basic table

 login_id
 email_address
 ip_address

 I want to extract all records from this table in which a user has used the
 same IP address but different email address to login ?

 Thanks,
 Neil




-- 
Claudio

RE: Query help

2011-03-02 Thread Jerry Schwartz

-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Wednesday, March 02, 2011 6:00 AM
To: [MySQL]
Subject: Query help

Hi

I've the following basic table

login_id
email_address
ip_address

I want to extract all records from this table in which a user has used the
same IP address but different email address to login ?

Thanks,
Neil
[JS] I haven't looked at my code lately, but I'm pretty sure that

SELECT
ip_address
FROM
basic_table
GROUP BY
ip_address
HAVING
COUNT(*)  1;

is what you want. You don't need to group on login_id. And, as Claudio said,

SELECT
ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids

will give you the IP addresses as well.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.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: Query help

2011-03-02 Thread Tompkins Neil

Thanks for the response.  This is what I was after.  Although, I am looking
to find out the email addresses used to login from the same IP ?

On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz je...@gii.co.jp wrote:

 -Original Message-
 From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
 Sent: Wednesday, March 02, 2011 6:00 AM
 To: [MySQL]
 Subject: Query help

 Hi

 I've the following basic table

 login_id
 email_address
 ip_address

 I want to extract all records from this table in which a user has used the
 same IP address but different email address to login ?

 Thanks,
 Neil
 [JS] I haven't looked at my code lately, but I'm pretty sure that

 SELECT
ip_address
 FROM
basic_table
 GROUP BY
ip_address
 HAVING
COUNT(*)  1;

 is what you want. You don't need to group on login_id. And, as Claudio
 said,

 SELECT
ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids

 will give you the IP addresses as well.

 Regards,

 Jerry Schwartz
 Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032

 860.674.8796 / FAX: 860.674.8341
 E-mail: je...@gii.co.jp
 Web site: www.the-infoshop.com

RE: Query help

2011-03-02 Thread Jerry Schwartz

If you want one row for each combination, you'll need either a temporary table 
or a sub-query. Try this:

SELECT ip_address, login_id
FROM basic_table
JOIN
 (SELECT
  ip_address
  FROM
  basic_table
 GROUP BY
  ip_address
 HAVING
 COUNT(*)  1) AS x
ON basic_table.ip_address = x.ip_address;

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com


-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Wednesday, March 02, 2011 10:12 AM
To: Jerry Schwartz
Cc: [MySQL]
Subject: Re: Query help

Thanks for the response.  This is what I was after.  Although, I am looking
to find out the email addresses used to login from the same IP ?

On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz je...@gii.co.jp wrote:


 -Original Message-
 From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
 Sent: Wednesday, March 02, 2011 6:00 AM
 To: [MySQL]
 Subject: Query help
 
 Hi
 
 I've the following basic table
 
 login_id
 email_address
 ip_address
 
 I want to extract all records from this table in which a user has used the
 same IP address but different email address to login ?
 
 Thanks,
 Neil
 [JS] I haven't looked at my code lately, but I'm pretty sure that

 SELECT
ip_address
 FROM
basic_table
 GROUP BY
ip_address
 HAVING
COUNT(*)  1;

 is what you want. You don't need to group on login_id. And, as Claudio
 said,

 SELECT
ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids

 will give you the IP addresses as well.

 Regards,

 Jerry Schwartz
 Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032

 860.674.8796 / FAX: 860.674.8341
 E-mail: je...@gii.co.jp
 Web site: www.the-infoshop.com











-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query Help

2010-10-27 Thread Nuno Mendes


I have 3 tables: (1) Companies, (2) locations and (3) employees:

CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(75) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `locations ` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(75) NOT NULL,
  `company_id` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) NOT NULL,
  `location_id` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

How do I retrieve list of all companies with total number of locations 
and total number of employees? The query bellow is the closest I could 
get to what I want but it's not quite there.


SELECT
  companies.name,
  Count(locations.id) AS locations_count,
  Count(employees.id) AS employees_count
FROM
  companies
  LEFT JOIN locations ON (companies.id = locations.company_id)
  LEFT JOIN employees ON (locations.id = employees .locations_id)
GROUP BY
  companies.id

Thank you!
Nuno Mendes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Query Help

2010-10-27 Thread Shawn Green (MySQL)


On 10/27/2010 6:55 AM, Nuno Mendes wrote:

I have 3 tables: (1) Companies, (2) locations and (3) employees:

CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `locations ` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
`company_id` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(5) NOT NULL,
`location_id` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

How do I retrieve list of all companies with total number of locations
and total number of employees? The query bellow is the closest I could
get to what I want but it's not quite there.

SELECT
companies.name,
Count(locations.id) AS locations_count,
Count(employees.id) AS employees_count
FROM
companies
LEFT JOIN locations ON (companies.id = locations.company_id)
LEFT JOIN employees ON (locations.id = employees .locations_id)
GROUP BY
companies.id

Thank you!
Nuno Mendes




Have you looked at the WITH ROLLUP query modifier?

http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Advanced query help

2010-09-27 Thread Johan De Meersman

At a guess, because you use @team in an if statement before you actually
define it.


On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil 
neil.tompk...@googlemail.com wrote:

 Hi,

 I've the following query

 SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
 teams_id ,players_id ,rating ,IF(@team  teams_id, @row := 1, @row := @row
 + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
 ,players.players_id ,players_master.rating FROM players JOIN players_master
 ON players.players_id = players_master.players_id WHERE players.worlds_id =
 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND
 players.teams_id  0 ORDER BY players.teams_id, players_master.rating DESC)
 s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC

 I'm running MySQL 5.0.77.  However, if I run this query nothing is
 returned,
 but if I run the query again I get the desired results.  Why is this ?

 Cheers
 Neil




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Advanced query help

2010-09-27 Thread Tompkins Neil

Hi,

I did try defining it before the IF statement, but still the same ?

Cheers
Neil

On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote:

 At a guess, because you use @team in an if statement before you actually
 define it.


 On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've the following query

 SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
 teams_id ,players_id ,rating ,IF(@team  teams_id, @row := 1, @row :=
 @row
 + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
 ,players.players_id ,players_master.rating FROM players JOIN
 players_master
 ON players.players_id = players_master.players_id WHERE players.worlds_id
 =
 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND
 players.teams_id  0 ORDER BY players.teams_id, players_master.rating
 DESC)
 s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC

 I'm running MySQL 5.0.77.  However, if I run this query nothing is
 returned,
 but if I run the query again I get the desired results.  Why is this ?

 Cheers
 Neil




 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel

Re: Advanced query help

2010-09-27 Thread Johan De Meersman

Then you'll probably need to define it with a separate select before using
it. I'm half-guessing here, really, but that sounds like it makes sense :-)

On Mon, Sep 27, 2010 at 11:49 AM, Tompkins Neil 
neil.tompk...@googlemail.com wrote:

 Hi,

 I did try defining it before the IF statement, but still the same ?

 Cheers
 Neil

 On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman vegiv...@tuxera.be
 wrote:

  At a guess, because you use @team in an if statement before you actually
  define it.
 
 
  On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil 
  neil.tompk...@googlemail.com wrote:
 
  Hi,
 
  I've the following query
 
  SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM
 (SELECT
  teams_id ,players_id ,rating ,IF(@team  teams_id, @row := 1, @row :=
  @row
  + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
  ,players.players_id ,players_master.rating FROM players JOIN
  players_master
  ON players.players_id = players_master.players_id WHERE
 players.worlds_id
  =
  1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0
 AND
  players.teams_id  0 ORDER BY players.teams_id, players_master.rating
  DESC)
  s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating
 DESC
 
  I'm running MySQL 5.0.77.  However, if I run this query nothing is
  returned,
  but if I run the query again I get the desired results.  Why is this ?
 
  Cheers
  Neil
 
 
 
 
  --
  Bier met grenadyn
  Is als mosterd by den wyn
  Sy die't drinkt, is eene kwezel
  Hy die't drinkt, is ras een ezel
 




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Advanced query help

2010-09-25 Thread Tompkins Neil

Hi,

I've the following query

SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
teams_id ,players_id ,rating ,IF(@team  teams_id, @row := 1, @row := @row
+ 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
,players.players_id ,players_master.rating FROM players JOIN players_master
ON players.players_id = players_master.players_id WHERE players.worlds_id =
1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND
players.teams_id  0 ORDER BY players.teams_id, players_master.rating DESC)
s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC

I'm running MySQL 5.0.77.  However, if I run this query nothing is returned,
but if I run the query again I get the desired results.  Why is this ?

Cheers
Neil

Query help please

2010-09-23 Thread Tompkins Neil

Hi all,

I've the following query :

SELECT fixtures_results.seasons_id ,
home_teams_id AS teams_id ,
1 AS home ,0 AS away ,
(SELECT SUM(goals) FROM players_appearances WHERE
fixtures_results.fixtures_results_id =
players_appearances.fixtures_results_id AND players_appearances.teams_id =
home_teams_id) AS home_goals_aa,
IF(home_goals  away_goals, 1, 0) AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals  away_goals, 1, 0) AS lost_home ,
home_goals AS scored_home ,
away_goals AS conceded_home ,
0 AS won_away ,
0 AS drawn_away ,
0 AS lost_away ,
0 AS scored_away ,
0 AS conceded_away
FROM fixtures_results
WHERE fixtures_results.competitions_id = 1
AND fixtures_results.seasons_id = 1
AND fixtures_results.status = 'approved'

Basically I have a table called player_appearances which contains a SUM of
goals for each fixture for the home and away team.  How can I use this SUM
called home_goals_aa, in my logic like IF(home_goals  away_goals, 1, 0)
AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals  away_goals, 1, 0) AS lost_home ,

Cheers
Neil

Fwd: Query help please

2010-09-23 Thread Tompkins Neil

I wondered if anyone can help me ?  Do you need any further information ?

Cheers
Neil

-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Thu, Sep 23, 2010 at 9:49 AM
Subject: Query help please
To: [MySQL] mysql@lists.mysql.com

Hi all,

I've the following query :

SELECT fixtures_results.seasons_id ,
home_teams_id AS teams_id ,
1 AS home ,0 AS away ,
(SELECT SUM(goals) FROM players_appearances WHERE
fixtures_results.fixtures_results_id =
players_appearances.fixtures_results_id AND players_appearances.teams_id =
home_teams_id) AS home_goals_aa,
IF(home_goals  away_goals, 1, 0) AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals  away_goals, 1, 0) AS lost_home ,
home_goals AS scored_home ,
away_goals AS conceded_home ,
0 AS won_away ,
0 AS drawn_away ,
0 AS lost_away ,
0 AS scored_away ,
0 AS conceded_away
FROM fixtures_results
WHERE fixtures_results.competitions_id = 1
AND fixtures_results.seasons_id = 1
AND fixtures_results.status = 'approved'

Basically I have a table called player_appearances which contains a SUM of
goals for each fixture for the home and away team.  How can I use this SUM
called home_goals_aa, in my logic like IF(home_goals  away_goals, 1, 0)
AS won_home ,
IF(home_goals = away_goals, 1, 0) AS drawn_home ,
IF(home_goals  away_goals, 1, 0) AS lost_home ,

Cheers
Neil

Query help

2010-09-06 Thread Tompkins Neil

Hi,

I've the following fields within a table :

fixtures_results_id
home_teams_id
away_teams_id
home_goals
away_goals
home_users_id
away_users_id

From this I want to extract the following type of information if
the home_users_id or away_users_id = 1 :

total number of games games
number of games won
number of games drawn
number of games lost
number of goals scored
number of goals conceded
biggest win
biggest loss
most goals in a game

I'd appreciate any help with regards the query and whether or not it is
possible to do ?

Cheers
Neil

Re: Query help

2010-09-06 Thread Ananda Kumar

Tompkins,
Which field stores the result of matches.

regards
anandkl

On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil
neil.tompk...@googlemail.comwrote:

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

 From this I want to extract the following type of information if
 the home_users_id or away_users_id = 1 :

 total number of games games
 number of games won
 number of games drawn
 number of games lost
 number of goals scored
 number of goals conceded
 biggest win
 biggest loss
 most goals in a game

 I'd appreciate any help with regards the query and whether or not it is
 possible to do ?

 Cheers
 Neil

Re: Query help

2010-09-06 Thread Tompkins Neil

These two fields

home_goals and away_goals

Cheers
Neil


On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar anan...@gmail.com wrote:

 Tompkins,
 Which field stores the result of matches.

 regards
 anandkl

 On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

 From this I want to extract the following type of information if
 the home_users_id or away_users_id = 1 :

 total number of games games
 number of games won
 number of games drawn
 number of games lost
 number of goals scored
 number of goals conceded
 biggest win
 biggest loss
 most goals in a game

 I'd appreciate any help with regards the query and whether or not it is
 possible to do ?

 Cheers
 Neil

Re: Query help

2010-09-06 Thread Ananda Kumar

Also, can u please lets u know the value's in this table.
Just one row, an example would do.

regards
anandkl



On Mon, Sep 6, 2010 at 5:35 PM, Tompkins Neil
neil.tompk...@googlemail.comwrote:

 These two fields

  home_goals and away_goals

 Cheers
 Neil


 On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar anan...@gmail.com wrote:

 Tompkins,
 Which field stores the result of matches.

 regards
 anandkl

   On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

 From this I want to extract the following type of information if
 the home_users_id or away_users_id = 1 :

 total number of games games
 number of games won
 number of games drawn
 number of games lost
 number of goals scored
 number of goals conceded
 biggest win
 biggest loss
 most goals in a game

 I'd appreciate any help with regards the query and whether or not it is
 possible to do ?

 Cheers
 Neil

Re: Query help

2010-09-06 Thread Tompkins Neil

For sure here is some sample data

home_teams_id,away_teams_id,home_goals,away_goals,home_users_id,away_users_id
100,200,2,1,5,6
200,100,1,1,6,5

Here is two rows of data for the same fixture both home and away

Let me know if you need any more info.

Cheers
Neil



On Mon, Sep 6, 2010 at 1:08 PM, Ananda Kumar anan...@gmail.com wrote:

 Also, can u please lets u know the value's in this table.
 Just one row, an example would do.

 regards
 anandkl



 On Mon, Sep 6, 2010 at 5:35 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 These two fields

  home_goals and away_goals

 Cheers
 Neil


 On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar anan...@gmail.com wrote:

 Tompkins,
 Which field stores the result of matches.

 regards
 anandkl

   On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

 From this I want to extract the following type of information if
 the home_users_id or away_users_id = 1 :

 total number of games games
 number of games won
 number of games drawn
 number of games lost
 number of goals scored
 number of goals conceded
 biggest win
 biggest loss
 most goals in a game

 I'd appreciate any help with regards the query and whether or not it is
 possible to do ?

 Cheers
 Neil

Re: query help

2010-08-21 Thread Travis Ard


Something like this might work:

insert into domains
select a.accountid, reverse(a.domainid), a.mailname
from domains a
left outer join domains b on b.domainid = reverse(a.domainid) and 
b.accountid = a.accountid and b.mailname = a.mailname

where b.domainid is null;

-Travis

--
From: Steven Buehler st...@ibushost.com
Sent: Friday, August 20, 2010 3:30 PM
To: mysql@lists.mysql.com
Subject: query help


I am hoping that I can do this with one query, I have a table, Domains
with 3 columns
accountID, domainID, mailname



what I am trying to do is find all accountID's for domainID of 12345 and
see if a second row with domainID of 54321 exists for that
accountID,mailname.  If it doesn't exist, I want it to insert another 
row
with the same accountID and mailname, but with the second (54321) 
domainid.




Any help would be appreciated.



Thanks

Steve




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

query help

2010-08-20 Thread Steven Buehler

I am hoping that I can do this with one query, I have a table, Domains
with 3 columns
accountID, domainID, mailname

 

what I am trying to do is find all accountID's for domainID of 12345 and
see if a second row with domainID of 54321 exists for that
accountID,mailname.  If it doesn't exist, I want it to insert another row
with the same accountID and mailname, but with the second (54321) domainid.

 

Any help would be appreciated.

 

Thanks

Steve

RE: Query Help

2010-07-12 Thread Jay Blanchard

[snip]
For the life of me I cannot remember how to make a query like this and
what
it is called.
I know it is fairly basic though.


Table 1
Product_id Product_Name

Table 2
Category_id, Category_name

Table 3
Product_id, Category_id

Each product can have one or more categories.
So I want a result that has

Product A one category other category
Product B other category
[/snip]

Can you give us an example of how you would like the output to be?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Query Help

2010-07-12 Thread Phillip Baker

Table 1
Product_id  |  Product_Name
1|  Product A
2|  Product B
3|  Product C

Table 2
Category_id  |  Category_Name
1 |   Admin
2 |   Marketing
3 |   Support
4 |   IT

Table 3
Product_id  |  Category_id
1|  1
1|  3
2|  2
3|  3
3|  4

Result would look like
Product A, Admin, Support
Product B, Marketing
Product C, Support, IT

I believe this is a one to many using an index table?

Blessed Be

Phillip

If you try to protect idiots from themselves, even if you succeed, you just
wind up filling the world with idiots.
   - - Doug Casey


On Mon, Jul 12, 2010 at 5:27 AM, Jay Blanchard jblanch...@pocket.comwrote:

 [snip]
 For the life of me I cannot remember how to make a query like this and
 what
 it is called.
 I know it is fairly basic though.


 Table 1
 Product_id Product_Name

 Table 2
 Category_id, Category_name

 Table 3
 Product_id, Category_id

 Each product can have one or more categories.
 So I want a result that has

 Product A one category other category
 Product B other category
 [/snip]

 Can you give us an example of how you would like the output to be?

Query Help

2010-07-09 Thread Phillip Baker

Hello All,


For the life of me I cannot remember how to make a query like this and what
it is called.
I know it is fairly basic though.


Table 1
Product_id Product_Name

Table 2
Category_id, Category_name

Table 3
Product_id, Category_id

Each product can have one or more categories.
So I want a result that has

Product A one category other category
Product B other category

Thanks in advance.

Blessed Be

Phillip

If you try to protect idiots from themselves, even if you succeed, you just
wind up filling the world with idiots.
   - - Doug Casey

Re: Query Help

2010-07-09 Thread Michael Satterwhite

I believe you're describing a crosstab query. This should help you put it 
together:

http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

---Michael

On Friday, July 09, 2010 07:37:41 pm Phillip Baker wrote:
 Hello All,
 
 
 For the life of me I cannot remember how to make a query like this and what
 it is called.
 I know it is fairly basic though.
 
 
 Table 1
 Product_id Product_Name
 
 Table 2
 Category_id, Category_name
 
 Table 3
 Product_id, Category_id
 
 Each product can have one or more categories.
 So I want a result that has
 
 Product A one category other category
 Product B other category
 
 Thanks in advance.
 
 Blessed Be
 
 Phillip
 
 If you try to protect idiots from themselves, even if you succeed, you just
 wind up filling the world with idiots.
- - Doug Casey

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: query help

2010-06-16 Thread Joerg Bruehe

Hi!


Jay Blanchard wrote:
 [snip]
 I have a table similar to this:
 
  -
 |transactions |
 |ID   |DATE  |EMPLOYEE|
 |234  |2010-01-05| 345|
 |328  |2010-04-05| 344|
 |239  |2010-01-10| 344|
 
 Is there a way to query such a table to give the days of the year that
 employee 344 did not have a transaction?
 [/snip]
 
 SELECT DATE
 FROM transactions
 WHERE EMPLOYEE != '344'
 GROUP BY DATE;

I strongly doubt this will work - what if several employees have
transactions on the same day?

No, what the poster effectively needs is a set difference:
Take the set of all candidate dates, and subtract the set of days on
which the employee in question did have a transaction.

The first difficulty will be to construct the set of candidate dates, as
this needs a decision what to do about non-working dates (weekends,
public holidays, ...) and how to determine them - depending on the
business logic, that set may be specific to the employee (personal
vacation!).

Only when this has been decided, there is the question how to implement
the set difference:
- SQL minus is a candidate, but MySQL doesn't support that AFAIK.
- Outer Join is the other possibility, as proposed by Gavin.
- Having all candidate dates in some temporary table and then deleting
  those with a transaction is another way, but probably very slow.
  (The advantage of this might be that it is the most flexible way.)


Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: query help

2010-06-16 Thread Richard Reina

Thank you very much for all the insightful replies. I think I can get it to 
work with a join.  


 Joerg Bruehe joerg.bru...@sun.com wrote:

 Hi!
 
 
 Jay Blanchard wrote:
  [snip]
  I have a table similar to this:
  
   -
  |transactions |
  |ID   |DATE  |EMPLOYEE|
  |234  |2010-01-05| 345|
  |328  |2010-04-05| 344|
  |239  |2010-01-10| 344|
  
  Is there a way to query such a table to give the days of the year that
  employee 344 did not have a transaction?
  [/snip]
  
  SELECT DATE
  FROM transactions
  WHERE EMPLOYEE != '344'
  GROUP BY DATE;
 
 I strongly doubt this will work - what if several employees have
 transactions on the same day?
 
 No, what the poster effectively needs is a set difference:
 Take the set of all candidate dates, and subtract the set of days on
 which the employee in question did have a transaction.
 
 The first difficulty will be to construct the set of candidate dates, as
 this needs a decision what to do about non-working dates (weekends,
 public holidays, ...) and how to determine them - depending on the
 business logic, that set may be specific to the employee (personal
 vacation!).
 
 Only when this has been decided, there is the question how to implement
 the set difference:
 - SQL minus is a candidate, but MySQL doesn't support that AFAIK.
 - Outer Join is the other possibility, as proposed by Gavin.
 - Having all candidate dates in some temporary table and then deleting
   those with a transaction is another way, but probably very slow.
   (The advantage of this might be that it is the most flexible way.)
 
 
 Jörg
 
 -- 
 Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
 Sun Microsystems GmbH,   Komturstrasse 18a,   D-12099 Berlin
 Geschaeftsfuehrer: Juergen Kunz
 Amtsgericht Muenchen: HRB161028
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: query help

2010-06-16 Thread Martin Gainty


i would monitor the performance on outer-join to determine if your server 
pegging cpu,disk i/o or memory when executing te outer-join

then perhaps populating a temp table (and deleting the non-matching 
records..those records which will be considered in transaction) as joerg 
suggested

 

i was hoping to use a trigger
perhaps a trigger may work is it possible to initiate the trigger on 
login/connect or some other initiating event when entering the database.. thus 
far trigger events are DML only?

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

 

if not then you can create a test script which will create and populate the 
temp table thru cron

http://www.databasejournal.com/features/mysql/article.php/3833146/Running-MySQL-in-Batch-Mode.htm

 

would be interested to know which solution works best 

 

Vielen Danke,
Martin 
__ 
Verzicht und Vertraulichkeitanmerkung

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.

 

 From: rich...@rushlogistics.com
 To: joerg.bru...@sun.com; mysql@lists.mysql.com
 Subject: Re: query help
 CC: jblanch...@pocket.com; rich...@rushlogistics.com
 Date: Wed, 16 Jun 2010 08:23:21 -0400
 
 Thank you very much for all the insightful replies. I think I can get it to 
 work with a join. 
 
 
  Joerg Bruehe joerg.bru...@sun.com wrote:
 
  Hi!
  
  
  Jay Blanchard wrote:
   [snip]
   I have a table similar to this:
   
   -
   |transactions |
   |ID |DATE |EMPLOYEE|
   |234 |2010-01-05| 345 |
   |328 |2010-04-05| 344 |
   |239 |2010-01-10| 344 |
   
   Is there a way to query such a table to give the days of the year that
   employee 344 did not have a transaction?
   [/snip]
   
   SELECT DATE
   FROM transactions
   WHERE EMPLOYEE != '344'
   GROUP BY DATE;
  
  I strongly doubt this will work - what if several employees have
  transactions on the same day?
  
  No, what the poster effectively needs is a set difference:
  Take the set of all candidate dates, and subtract the set of days on
  which the employee in question did have a transaction.
  
  The first difficulty will be to construct the set of candidate dates, as
  this needs a decision what to do about non-working dates (weekends,
  public holidays, ...) and how to determine them - depending on the
  business logic, that set may be specific to the employee (personal
  vacation!).
  
  Only when this has been decided, there is the question how to implement
  the set difference:
  - SQL minus is a candidate, but MySQL doesn't support that AFAIK.
  - Outer Join is the other possibility, as proposed by Gavin.
  - Having all candidate dates in some temporary table and then deleting
  those with a transaction is another way, but probably very slow.
  (The advantage of this might be that it is the most flexible way.)
  
  
  Jörg
  
  -- 
  Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
  Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
  Geschaeftsfuehrer: Juergen Kunz
  Amtsgericht Muenchen: HRB161028
  
  
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 
  
_
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with 
Hotmail. 
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendarocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5

query help

2010-06-15 Thread Richard Reina


I have a table similar to this:

 -
|transactions |
|ID   |DATE  |EMPLOYEE|
|234  |2010-01-05| 345|
|328  |2010-04-05| 344|
|239  |2010-01-10| 344|

Is there a way to query such a table to give the days of the year that employee 
344 did not have a transaction?

Thanks for the help.

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: query help

2010-06-15 Thread Gavin Towey

Hi Richard,

This is a LEFT JOIN, but with only one table you may be wondering what to join. 
 Typically you'll have to create a new table that contains all the dates in the 
range you want to check.  Then you left join your transaction table, and all 
the rows from your dates table that don't have a match is your answer.

Regards,
Gavin Towey

-Original Message-
From: Richard Reina [mailto:rich...@rushlogistics.com]
Sent: Tuesday, June 15, 2010 11:30 AM
To: mysql@lists.mysql.com
Subject: query help


I have a table similar to this:

 -
|transactions |
|ID   |DATE  |EMPLOYEE|
|234  |2010-01-05| 345|
|328  |2010-04-05| 344|
|239  |2010-01-10| 344|

Is there a way to query such a table to give the days of the year that employee 
344 did not have a transaction?

Thanks for the help.

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

RE: query help

2010-06-15 Thread Jay Blanchard

[snip]
I have a table similar to this:

 -
|transactions |
|ID   |DATE  |EMPLOYEE|
|234  |2010-01-05| 345|
|328  |2010-04-05| 344|
|239  |2010-01-10| 344|

Is there a way to query such a table to give the days of the year that
employee 344 did not have a transaction?
[/snip]

SELECT DATE
FROM transactions
WHERE EMPLOYEE != '344'
GROUP BY DATE;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query Help!

2010-04-27 Thread John Daisley

Hi All,

I have a query I need to run but can't think how to get this working so I am
hoping someone can advise.

I have a table which logs start and end times of Scheduled jobs. It includes
for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both
`StartDateTime` and `EndDateTime` are 'datetime' datatypes.

What I need to do is find all times in a day when there was nothing running
on the system - so all times which do not occur between any of the
`StartDateTime` and `EndDateTime` values for a particular day.

A simple example, if the table had values

*DayId StartDateTimeEndDateTime
12010-02-26 16:40:27   2010-02-26 16:41:27
12010-02-26 16:41:21   2010-02-26 16:45:57
12010-02-26 16:47:01   2010-02-26 16:49:21
12010-02-26 16:49:27   2010-02-26 16:49:55

*I can see the system was free between 16:45:57 and 16:47:01 on 26th
February 2010 and this is what I would need the query to return only working
with a lot more data. Any ideas?

Thank you in advance for any help, suggestions. This is currently on a MySQL
5.1 system.

Regards



-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk

Re: Query Help!

2010-04-27 Thread Johan De Meersman

Hmm. You seem to have overlap, too. I suspect this would be easiest to do in
code - the data you're looking for doesn't exist in the data you have, only
the opposite of that data does.

You could try populating a table with a full day, using the resolution you
need (1 minute resolution means 1440 records) and then (somehow) join with
your data table and use not between; but they you'd get a list of free
$resolution blocks, which you may still want to aggregate into from-to
blocks using code.



On Tue, Apr 27, 2010 at 3:47 PM, John Daisley mg_s...@hotmail.com wrote:

 Hi All,

 I have a query I need to run but can't think how to get this working so I
 am
 hoping someone can advise.

 I have a table which logs start and end times of Scheduled jobs. It
 includes
 for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both
 `StartDateTime` and `EndDateTime` are 'datetime' datatypes.

 What I need to do is find all times in a day when there was nothing running
 on the system - so all times which do not occur between any of the
 `StartDateTime` and `EndDateTime` values for a particular day.

 A simple example, if the table had values

 *DayId StartDateTimeEndDateTime
 12010-02-26 16:40:27   2010-02-26 16:41:27
 12010-02-26 16:41:21   2010-02-26 16:45:57
 12010-02-26 16:47:01   2010-02-26 16:49:21
 12010-02-26 16:49:27   2010-02-26 16:49:55

 *I can see the system was free between 16:45:57 and 16:47:01 on 26th
 February 2010 and this is what I would need the query to return only
 working
 with a lot more data. Any ideas?

 Thank you in advance for any help, suggestions. This is currently on a
 MySQL
 5.1 system.

 Regards



 --
 John Daisley

 Certified MySQL 5 Database Administrator
 Certified MySQL 5 Developer
 Cognos BI Developer

 Telephone: +44 (0)7918 621621
 Email: john.dais...@butterflysystems.co.uk




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Query Help!

2010-04-27 Thread Jo�o C�ndido de Souza Neto

Not tested, but I think it can help you or at least give you an ideia on how 
to do it.

select
  EndDateTime + INTERVAL 1 SECOND as startLazy,
  (select StartDateTime - INTERVAL 1 SECOND from table t2 where 
t2.StartDateTime  t1.EndDateTime limit 1) as endLazy
from
  table t1
where
  (select StartDateTime - INTERVAL 1 SECOND from table t2 where 
t2.StartDateTime = t1.EndDateTime limit 1)  (EndDateTime + INTERVAL 1 
SECOND)


John Daisley mg_s...@hotmail.com escreveu na mensagem 
news:m2x571a6edf1004270647j3d1ef220n4eb9394c339f1...@mail.gmail.com...
 Hi All,

 I have a query I need to run but can't think how to get this working so I 
 am
 hoping someone can advise.

 I have a table which logs start and end times of Scheduled jobs. It 
 includes
 for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both
 `StartDateTime` and `EndDateTime` are 'datetime' datatypes.

 What I need to do is find all times in a day when there was nothing 
 running
 on the system - so all times which do not occur between any of the
 `StartDateTime` and `EndDateTime` values for a particular day.

 A simple example, if the table had values

 *DayId StartDateTimeEndDateTime
 12010-02-26 16:40:27   2010-02-26 16:41:27
 12010-02-26 16:41:21   2010-02-26 16:45:57
 12010-02-26 16:47:01   2010-02-26 16:49:21
 12010-02-26 16:49:27   2010-02-26 16:49:55

 *I can see the system was free between 16:45:57 and 16:47:01 on 26th
 February 2010 and this is what I would need the query to return only 
 working
 with a lot more data. Any ideas?

 Thank you in advance for any help, suggestions. This is currently on a 
 MySQL
 5.1 system.

 Regards



 -- 
 John Daisley

 Certified MySQL 5 Database Administrator
 Certified MySQL 5 Developer
 Cognos BI Developer

 Telephone: +44 (0)7918 621621
 Email: john.dais...@butterflysystems.co.uk
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

query help

2010-02-09 Thread Richard Reina

I am trying to write a query that merges 2 columns from different tables and 
show them as one column of data.  Something like the following.

payables
ID  |check_no| amount|
3   |3478| 67.00 |
4   |3489| 98.00 |
8   |3476| 56.00 |

paychecks
ID  |check_no| amount
23  |3469|498.00 |
34  |3502|767.00 |
36  |3504}754.00 |

I am struggling to write a select query that gives me amounts and check numbers 
from both of the tables in the same column.  Like the following:

ID  |check_no| amount|
3   |3478| 67.00 |
4   |3489| 98.00 |
8   |3476| 56.00 |
23  |3469|498.00 |
34  |3502|767.00 |
36  |3504}754.00 |

Any help would be greatly appreciated.

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: query help

2010-02-09 Thread Gavin Towey

SELECT ID, check_no, amount FROM payables UNION SELECT ID, check_no, amount 
FROM paychecks;

Regards,
Gavin Towey


-Original Message-
From: Richard Reina [mailto:rich...@rushlogistics.com]
Sent: Tuesday, February 09, 2010 9:23 AM
To: mysql@lists.mysql.com
Subject: query help

I am trying to write a query that merges 2 columns from different tables and 
show them as one column of data.  Something like the following.

payables
ID  |check_no| amount|
3   |3478| 67.00 |
4   |3489| 98.00 |
8   |3476| 56.00 |

paychecks
ID  |check_no| amount
23  |3469|498.00 |
34  |3502|767.00 |
36  |3504}754.00 |

I am struggling to write a select query that gives me amounts and check numbers 
from both of the tables in the same column.  Like the following:

ID  |check_no| amount|
3   |3478| 67.00 |
4   |3489| 98.00 |
8   |3476| 56.00 |
23  |3469|498.00 |
34  |3502|767.00 |
36  |3504}754.00 |

Any help would be greatly appreciated.

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

Query help

2009-12-13 Thread Richard Reina

I was wondering if someone could lend a hand with the following query.  I have 
table.

SEARCHES
|ID |trans_no|comp_id|result
13  | 455|  675| o
15  | 302|  675| o
16  | 455|  675| o
12  | 225|  629| y

SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY 
trans_no;

gives me a count of 3.

However, what I need is a count for how many different (unique) transactions 
company number 675 got a result 'o' which would be 2 (455  302).  I have tried 
different group by columns but to no avail. Can someone help?

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Query help

2009-12-13 Thread Jason Trebilcock

Depending on whether you just need to count or the transaction numbers, one of 
the following three should get you where you want/need to be:

To identify the count for comp_id = 675:
select count(distinct trans_no) from trans where comp_id = 675 and result = 'o';

To identify the transactions:
select distinct trans_no from trans where comp_id = 675 and result = 'o';

To identify the transactions and the individual counts:
select trans_no, count(*) from trans where comp_id = 675 and result = 'o' group 
by trans_no;

 -Original Message-
 From: Richard Reina [mailto:rich...@rushlogistics.com]
 Sent: Sunday, December 13, 2009 12:37 PM
 To: mysql@lists.mysql.com
 Cc: rich...@rushlogistics.com
 Subject: Query help
 
 I was wondering if someone could lend a hand with the following query.
 I have table.
 
 SEARCHES
 |ID |trans_no|comp_id|result
 13  | 455|  675| o
 15  | 302|  675| o
 16  | 455|  675| o
 12  | 225|  629| y
 
 SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY
 trans_no;
 
 gives me a count of 3.
 
 However, what I need is a count for how many different (unique)
 transactions company number 675 got a result 'o' which would be 2 (455
  302).  I have tried different group by columns but to no avail. Can
 someone help?
 
 Thanks,
 
 Richard
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=jason.trebilc...@gmail.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: Query help

2009-12-13 Thread Colin Streicher

On December 13, 2009 01:36:41 pm Richard Reina wrote:
 I was wondering if someone could lend a hand with the following query.  I
  have table.
 
 SEARCHES
 
 |ID |trans_no|comp_id|result
 
 13  | 455|  675| o
 15  | 302|  675| o
 16  | 455|  675| o
 12  | 225|  629| y
 
 SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY
  trans_no;
 
 gives me a count of 3.
 
 However, what I need is a count for how many different (unique)
  transactions company number 675 got a result 'o' which would be 2 (455 
  302).  I have tried different group by columns but to no avail. Can
  someone help?
 
 Thanks,
 
 Richard
 
select count(trans_no), comp_id from SEARCHES group by comp_id 

I think that should do it...haven't tested it though, 

Colin
-- 
Living your life is a task so difficult, it has never been attempted before.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Query help

2009-12-13 Thread DaWiz

SELECT count(distinct trans_no) from SEARCHES WHERE comp_id=675 and 
result='o';


- Original Message - 
From: Richard Reina rich...@rushlogistics.com

To: mysql@lists.mysql.com
Cc: rich...@rushlogistics.com
Sent: Sunday, December 13, 2009 11:36 AM
Subject: Query help


I was wondering if someone could lend a hand with the following query.  I 
have table.


SEARCHES
|ID |trans_no|comp_id|result
13  | 455|  675| o
15  | 302|  675| o
16  | 455|  675| o
12  | 225|  629| y

SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY 
trans_no;


gives me a count of 3.

However, what I need is a count for how many different (unique) 
transactions company number 675 got a result 'o' which would be 2 (455  
302).  I have tried different group by columns but to no avail. Can 
someone help?


Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Database design and query help

2009-11-16 Thread Eskil Kvalnes

Hello,

Currently, I have four tables (Items, UpdatePrice, UpdateStatus and
UpdateRelease). All the Update tables are linked to Items.ItemID via
Update(Price|Status|Release)ItemKey. Personally, I don't feel that
this is the best database design I could have, but I can't seem to
come up with one that'll work for me. I need to be able to add updates
to all three cases and still have access to a history of updates.

The problem(s) with this design is that I - in my opinion - get a very
slow result. Items consists of 500+ rows at the moment and the same
goes for the three Update tables. Those will obviously grow much
quicker. Another problem is that I can't seem to sort on the Items
table, while sorting on either of the Update tables seems for work.

This is my query for gathering all the data I need from all four tables:

SELECT * FROM Items t1
JOIN(SELECT * FROM UpdatePrice  ORDER BY
UpdatePrice.UpdatePriceID   DESC) AS t2 ON t1.ItemID =
t2.UpdatePriceItemKey
JOIN(SELECT * FROM UpdateStatusORDER BY
UpdateStatus.UpdateStatusID   DESC) AS t3 ON t1.ItemID =
t3.UpdateStatusItemKey
JOIN(SELECT * FROM UpdateRelease ORDER BY
UpdateRelease.UpdateReleaseID DESC) AS t4 ON t1.ItemID =
t4.UpdateReleaseItemKey
WHERE t1.ItemIsGame = 1
GROUP BY t1.ItemID

and then SORT BY t1.ItemTitle (doesn't work) or SORT BY
t2.UpdatePriceNew (does work).

http://grab.by/BWW - Screenshot of the query in case formatting is
lost in translation!

Basically, my questions are:
1) Is this a poor database design? If yes, how would you do it?
2) Is this a bloated query which can be perfected to work as intended
(mine doesn't) and perhaps faster?

Sincerely,
Eskil Kvalnes
eskil.kval...@gmail.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Basic SQL Query Help Needed

2009-08-25 Thread c...@hosting4days.com


I have a basic invoice table with related line items table

Goal :I'd like to get ALL the related line items - for ALL the  
'open' invoices...


-- this should get a list of open (unpaid) invoices

$query_invoice = SELECT DISTINCT ID from invoices where status =  
'open'


-

-- then I'd like to get ALL the line items - in ALL these 'open'  
invoices - so how do I write the next SQL statement :


$query_items = ??? SELECT ID, NAME from lineitems where --xx??? 
xx-- ???




Thanks,
c...@hosting4days.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: Basic SQL Query Help Needed

2009-08-25 Thread Martin Gainty


SELECT * FROM ORDER o INNER JOIN ORDER_LINE_ITEMS o_l
 ON (o.id=o_l.id)
Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




 To: mysql@lists.mysql.com
 From: c...@hosting4days.com
 Subject: Basic SQL Query Help Needed
 Date: Tue, 25 Aug 2009 16:21:45 -0700
 
 I have a basic invoice table with related line items table
 
 Goal :I'd like to get ALL the related line items - for ALL the  
 'open' invoices...
 
 -- this should get a list of open (unpaid) invoices
 
 $query_invoice = SELECT DISTINCT ID from invoices where status =  
 'open'
 
 -
 
 -- then I'd like to get ALL the line items - in ALL these 'open'  
 invoices - so how do I write the next SQL statement :
 
 $query_items = ??? SELECT ID, NAME from lineitems where --xx??? 
 xx-- ???
 
 
 
 Thanks,
 c...@hosting4days.com
 
 
 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

_
With Windows Live, you can organize, edit, and share your photos.
http://www.windowslive.com/Desktop/PhotoGallery

Query Help

2009-02-10 Thread Ben Wiechman

I keep hacking at this but haven't been able to get it right yet. 

 

I have two tables

Userinfo contains a login, User's Name, Group Name

Log contains login, host, datetime of last login

 

 

What I need to do is return user information (userinfo.name/groupname) of
users that have logged into a particular host last. i.e. A user can log into
any of the hosts but I want to know which one they were on last. Each time
they log the username, host and datetime of login are recorded in the log
table. I've been able to return the last login to the host but I'm at a loss
to get the list of all users that used the host last or all users that have
ever logged into that host.

RE: Query Help

2009-02-10 Thread Ben Wiechman

Thanks for the input! That is close to what I need, however not exactly. It
will give me the last time a user logged into the host in question but I
want to prune users who have since logged into a different host. Basically
find out how many users are logged into a given host or who are not
currently logged in but have not logged into a different host since they
logged out of the target. 

For perspective the host is a wireless access point with fixed clients. A
host that hasn't logged in somewhere else we can (somewhat) safely assume is
still pointed at the AP in question but is power off, or has its connection
interrupted for some other reason. If they have logged in somewhere else we
assume that the hardware was moved to a new location and installed there. 

The query I came up with for some reason doesn't seem to correctly order the
dates so if they have logged into the host in question the information is
returned. This produces too many results as some of those users have since
migrated to a different access point.

-Original Message-
From: Andrew Wallo [mailto:theme...@microneil.com] 
Sent: Tuesday, February 10, 2009 12:05 PM
To: Ben Wiechman
Subject: Re: Query Help


Select login, SQL MAX(DateTime) from Log groupby Log.Login ( Gives you the 
most recent login for a user - on any host. )

Select login, SQLMax(DateTime) from Log groupby Log.Login, Host ORDER BY 
HOST ASC, DATETIME DESC

(Should give you the largest, i.e. most recent, date for each user on each 
host, oganized by host, in descending login order. I think... )



- Original Message - 
From: Ben Wiechman b...@meltel.com
To: mysql@lists.mysql.com
Sent: Tuesday, February 10, 2009 12:31 PM
Subject: Query Help


I keep hacking at this but haven't been able to get it right yet.



 I have two tables

 Userinfo contains a login, User's Name, Group Name

 Log contains login, host, datetime of last login





 What I need to do is return user information (userinfo.name/groupname) of
 users that have logged into a particular host last. i.e. A user can log 
 into
 any of the hosts but I want to know which one they were on last. Each time
 they log the username, host and datetime of login are recorded in the log
 table. I've been able to return the last login to the host but I'm at a 
 loss
 to get the list of all users that used the host last or all users that 
 have
 ever logged into that host.





 





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Query Help

2009-02-10 Thread Ben Wiechman

Alright to reply to myself I can return the information but have been unable
to return the last row... instead it always returns the first row. Tried
max, tried min, tried converting the datetime to a timestamp with the same
results... 

mysql SELECT da_userinfo.UserName, da_userinfo.Name, radacct.AcctStartTime
as LoginTime, radacct.AcctStopTime as LogoutTime
- FROM radacct, da_userinfo
- WHERE da_userinfo.Username = radacct.Username
- AND `NASIPAddress` = '172.17.6.100'
- GROUP BY radacct.`UserName`
- HAVING MAX( radacct.`AcctStartTime` );
+--+-+-+
-+
| UserName | Name| LoginTime   | LogoutTime
|
+--+-+-+
-+
| 0010E70A8004 | User1   | 2009-02-09 09:16:24 | 2009-02-10
04:42:08 |
| 0010E70A80A8 | User2  | 2009-02-09 14:31:20 |
2009-02-10 09:57:42 |
| 0010E70A812D | User3 | 2009-02-09 13:19:51 |
2009-02-10 07:43:08 |
| 0010E70A8336 | User4 | 2009-02-05 14:10:41 |
2009-02-10 02:36:41 |
| 0010E70A833B | User5  | 2009-02-06 17:45:15 |
2009-02-09 20:27:21 |
| 0010E72A2258 | User6| 2009-01-29 13:40:42 | 2009-01-29
14:03:29 |
| 0010E72A2957 | User7  | 2009-02-09 15:16:06 | 2009-02-09
16:02:56 |
| 0010E72AA91C | User8 | 2009-02-03 09:45:36 | 2009-02-10
07:41:22 |
| 0010E7C2F6AF | 07B | 2009-01-28 16:13:44 | 2009-01-28
16:15:43 |
+--+-+-+
-+
16 rows in set (0.00 sec)

mysql select UserName,NASIPAddress as Host,AcctStartTime as
LoginTime,AcctStopTime as LogoutTime from radacct where UserName =
'0010E70A8004';
+--+--+-+-+
| UserName | Host | LoginTime   | LogoutTime  |
+--+--+-+-+
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:02 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:12 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:18:30 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:39:21 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:44:42 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:12:34 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:14:05 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:18:51 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-10 04:42:08 | -00-00 00:00:00 |
+--+--+-+-+
10 rows in set (0.00 sec)

mysql select UserName,NASIPAddress as Host,AcctStartTime as
LoginTime,AcctStopTime as LogoutTime from radacct where UserName =
'0010E70A8004' HAVING MAX( AcctStartTime );
+--+--+-+-+
| UserName | Host | LoginTime   | LogoutTime  |
+--+--+-+-+
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 |
+--+--+-+-+
1 row in set (0.00 sec)

-Original Message-
From: Ben Wiechman [mailto:b...@meltel.com] 
Sent: Tuesday, February 10, 2009 11:32 AM
To: mysql@lists.mysql.com
Subject: Query Help

I keep hacking at this but haven't been able to get it right yet. 

 

I have two tables

Userinfo contains a login, User's Name, Group Name

Log contains login, host, datetime of last login

 

 

What I need to do is return user information (userinfo.name/groupname) of
users that have logged into a particular host last. i.e. A user can log into
any of the hosts but I want to know which one they were on last. Each time
they log the username, host and datetime of login are recorded in the log
table. I've been able to return the last login to the host but I'm at a loss
to get the list of all users that used the host last or all users that have
ever logged into that host.  

 

 




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Query Help

2009-02-10 Thread ddevaudreuil


Ben Wiechman b...@meltel.com wrote on 02/10/2009 01:30:14 PM:

 Thanks for the input! That is close to what I need, however not exactly.
It
 will give me the last time a user logged into the host in question but I
 want to prune users who have since logged into a different host.
Basically
 find out how many users are logged into a given host or who are not
 currently logged in but have not logged into a different host since they
 logged out of the target.

Figure out the last time each user logged in to any host:

SELECT login, MAX(datetime)as lastlogindate
FROM Log
GROUP BY login

So use that query as a derived table to get the rest of the info (untested
SQL):

SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime
FROM Userinfo
INNER JOIN
  (SELECT login, MAX(datetime)as lastlogindate
  FROM Log
  GROUP BY login) AS lastlogin
  ON Userinfo.login=lastlogin.login
INNER JOIN Log  ON lastlogin.login=Log.login AND
lastlogin.lastlogindate=Log.datetime

Hope that helps.

Donna


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Query Help

2009-02-10 Thread Ben Wiechman

Awesome... that works. Had to add a where clause to limit it to a specific
host.

The explain for that looks... interesting. 

Thanks

-Original Message-
From: ddevaudre...@intellicare.com [mailto:ddevaudre...@intellicare.com] 
Sent: Tuesday, February 10, 2009 12:47 PM
To: Ben Wiechman
Cc: mysql@lists.mysql.com
Subject: RE: Query Help


Ben Wiechman b...@meltel.com wrote on 02/10/2009 01:30:14 PM:

 Thanks for the input! That is close to what I need, however not exactly.
It
 will give me the last time a user logged into the host in question but I
 want to prune users who have since logged into a different host.
Basically
 find out how many users are logged into a given host or who are not
 currently logged in but have not logged into a different host since they
 logged out of the target.

Figure out the last time each user logged in to any host:

SELECT login, MAX(datetime)as lastlogindate
FROM Log
GROUP BY login

So use that query as a derived table to get the rest of the info (untested
SQL):

SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime
FROM Userinfo
INNER JOIN
  (SELECT login, MAX(datetime)as lastlogindate
  FROM Log
  GROUP BY login) AS lastlogin
  ON Userinfo.login=lastlogin.login
INNER JOIN Log  ON lastlogin.login=Log.login AND
lastlogin.lastlogindate=Log.datetime

Hope that helps.

Donna





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

sub query help

2008-08-18 Thread Paul Nowosielski

Dear All,

I am interested in performing a sub query that  removes duplicate records from
a temporary table prior to pushing the data to the main table.

I am not sure if it is possible and thought I would ask prior to the endeavor.

I currently use php to perform this operation but is really bogs down the db.

Here is the code example i am using now to perform this function:

// select the name from the dedup row

$sql = select name,id from dedup;

$ret = run_query($sql);

// loop through and check if the name exists in the lead table

while($ddRow=mysql_fetch_assoc($ret)){

$sql2 = select name,id from leads where name LIKE '$ddupRow[name]';
$ret2 = run_query($sql2);

 // if rows returned is greater than 0 delete from the dedup table

if(myqsl_num_rows( $ret2 )  0){

$del_sql = DELETE FROM dedup WHERE id = '$ddRow[id]';
run_query($del_sql);

   // else insert the new record

   }else{
$iQ=INSERT into leads (name) VALUES ( '$ddupRow[name]');
;   run_query($iQ);
}
}


I am wondering if it is possible to do this with one query without the double 
hit to the DB?
Or ,if possible, perform one large query and remove the duplicates in one blow?

Any thought or suggestions would be very much appreciated.

Kind Regards,

Paul



  

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

Query help, please..

2007-12-11 Thread Anders Norrbring

I'm looking at a situation I haven't run into before, and I'm a bit 
puzzled by it.


I have this table structure:

Table USERS: userid, class
Table OBJECT: userid, class, result

Now I want to query the database for a certain user's result in a 
specified class, which is very, very easy. No problems.


But, I also want to find out the user's position relative to others 
depending on the result.


So, if the specified user's result is the 9:th best of all of the users, 
I want to have a reply from the DB query that say he has position number 9.


I really can't figure out how to do that... Somehow I have to make MySQL 
calculate the position based on the value in the result column.



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

1 2 3 4 5 6 >

1 - 100 of 544 matches

Mail list logo