Re: Query Help...

[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...

[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...

[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

Re: Query Help...

[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

Re: Query Help

[Richard Reina]

Perfect! Thank you Larry et all.

Have a great weekend.


2013/4/19 Larry Martell 

> On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina 
> 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);
>

Re: Query Help

[Larry Martell]

On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina  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

[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 - Solved

[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

Re: Query help -

[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

[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

[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

[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

[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 wrote:

>  On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete 
> 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, ) 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: query help

[Rick James]

Might need some type coercion:

SELECTSUBSTRING(recid, 13, ) AS numbers FROM table
 ORDER BY SUBSTRING(recid, 13, )+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
>  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, ) 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

[Paul Halliday]

On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete  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, ) 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: Query help...

[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: Query help,,,

[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

RE: Query help,,,

[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...

[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: Query help...

[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  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

[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  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

Re: Query help

[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  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

[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

[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 

> 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

[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: Query help

[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  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  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

[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
wrote:

> These two fields
>
>  home_goals and away_goals
>
> Cheers
> Neil
>
>
> On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar  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

[Tompkins Neil]

These two fields

home_goals and away_goals

Cheers
Neil


On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar  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

[Ananda Kumar]

Tompkins,
Which field stores the result of matches.

regards
anandkl

On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil
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

[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" 
Sent: Friday, August 20, 2010 3:30 PM
To: 
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

Re: Query Help

[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 wrote:

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

RE: Query Help

[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

[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

[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  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=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5

Re: query help

[Richard Reina]

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


 Joerg Bruehe  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

[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

[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

RE: query help

[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!

[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"  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

Re: Query Help!

[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  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

[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

Re: Query help

[DaWiz]

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


- Original Message - 
From: "Richard Reina" 

To: 
Cc: 
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

Re: Query help

[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

[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

[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"  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

[ddevaudreuil]

"Ben Wiechman"  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

[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

[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" 
To: 
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, please..

[Peter Brawley]

Anders,

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


For a given pUserID, something like this?

SELECT userid,result,rank
FROM (
 SELECT o1.userid,o1.result,COUNT(o2.result) AS rank
 FROM object o1
 JOIN object o2 ON o1.result < o2.result OR (o1.result=o2.result AND 
o1.userid=o2.userid)

 GROUP BY o1.userid,o1.result
)
WHERE userid = pUserID;

PB

-

Anders Norrbring wrote:
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]

Re: Query help, please..

[Jason Pruim]

On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote:


On Dec 11, 2007 8:38 AM, Anders Norrbring <[EMAIL PROTECTED]> wrote:

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.


Take a look at http://arjen-lentz.livejournal.com/55083.html . Very
similar ideas in play, though you also have a join.

The basic idea is that you do a count on the number of users that have
a lower score.


Is there any reason you wouldn't want to count the people in front of  
you and add 1 to get your place in line? It seems like depending on  
where you are, that may be a shorter number to count :)


But I don't know anything about how to do stuff off of separate tables  
yet still trying to grasp that :)






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




--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]



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

Re: Query help, please..

[Rob Wultsch]

On Dec 11, 2007 8:38 AM, Anders Norrbring <[EMAIL PROTECTED]> wrote:
> 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.

Take a look at http://arjen-lentz.livejournal.com/55083.html . Very
similar ideas in play, though you also have a join.

The basic idea is that you do a count on the number of users that have
a lower score.

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

Re: query help

[Peter Brawley]

I only want to view the product per product name
which has the lowest price.


SELECT product_name,MIN(price)
FROM tbl
GROUP BY product_name;

PB

uYe wrote:

Hi,
I have a table with product_name, price field. The product name may be exist
in more than one record. I only want to view the product per product name
which has the lowest price. Please help me to  build such query, many thanks
for your helps.

Regards


Willy


  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.16/582 - Release Date: 12/11/2006


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

Re: Query Help plss

[Renish]

Thank u so much. It woked now!
- Original Message - 
From: "Visolve DB Team" <[EMAIL PROTECTED]>
To: "Visolve DB Team" <[EMAIL PROTECTED]>; "Renish" 
<[EMAIL PROTECTED]>; 

Sent: Tuesday, October 10, 2006 1:42 PM
Subject: Re: Query Help plss



Hi,

Hope this link will you.

http://mysql.binarycompass.org/doc/refman/4.1/en/can-not-connect-to-server.html

Thanks,
ViSolve DB Team.

- Original Message - 
From: "Visolve DB Team" <[EMAIL PROTECTED]>

To: "Renish" <[EMAIL PROTECTED]>; 
Sent: Tuesday, October 10, 2006 11:03 AM
Subject: Re: Query Help plss


Hi

The .err file shows that the service is stopped Normally and no error was 
found.  sure the service is properly shutdown.


Try,
a)

net stop mysql
mysqld-nt remove
mysqld-nt install
net start mysql


b)
Also run 'services.msc' and double click on the mysql service, make sure 
its comming from C:\\www\mysql\bin


Thanks
ViSolve DB Team.
 - Original Message - 
 From: Renish

 To: Visolve DB Team ; mysql@lists.mysql.com
 Sent: Tuesday, October 10, 2006 10:07 AM
 Subject: Fw: Query Help plss



 - Original Message - 
 From: Renish

 To: Visolve DB Team ; mysql@lists.mysql.com
 Sent: Tuesday, October 10, 2006 12:35 PM
 Subject: Fw: Query Help plss



 - Original Message - 
 From: Renish

 To: mysql@lists.mysql.com ; Visolve DB Team
 Sent: Tuesday, October 10, 2006 12:34 PM
 Subject: Re: Query Help plss


 b)  And what happend when you open cmd.exe and run.
 mysql  -u root- It shows cant connect to local host error 10061

 *.error file shows this

 InnoDB: The first specified data file .\ibdata1 did not exist:
 InnoDB: a new database to be created!
 060717 10:32:22  InnoDB: Setting file .\ibdata1 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 060717 10:32:23  InnoDB: Log file .\ib_logfile0 did not exist: new to be 
created

 InnoDB: Setting log file .\ib_logfile0 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 060717 10:32:23  InnoDB: Log file .\ib_logfile1 did not exist: new to be 
created

 InnoDB: Setting log file .\ib_logfile1 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Doublewrite buffer not found: creating new
 InnoDB: Doublewrite buffer created
 InnoDB: Creating foreign key constraint system tables
 InnoDB: Foreign key constraint system tables created
 060717 10:32:24  InnoDB: Started; log sequence number 0 0
 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for 
connections.
 Version: '4.1.20-community-nt'  socket: ''  port: 3306  MySQL Community 
Edition (GPL)
 060717 13:47:31 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Normal shutdown


 061009 15:02:37  InnoDB: Starting shutdown...
 061009 15:02:42  InnoDB: Shutdown completed; log sequence number 0 
543205221
 061009 15:02:42 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Shutdown complete


 061009 15:03:46  InnoDB: Started; log sequence number 0 543205221
 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for 
connections.
 Version: '4.1.21-community-nt'  socket: ''  port: 3306  MySQL Community 
Edition (GPL)
 061010 11:58:13 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Normal shutdown


 061010 11:58:13  InnoDB: Starting shutdown...
 061010 11:58:15  InnoDB: Shutdown completed; log sequence number 0 
543205231
 061010 11:58:15 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Shutdown complete



   - Original Message - 
   From: Visolve DB Team

   To: Renish
   Sent: Tuesday, October 10, 2006 12:16 PM
   Subject: Re: Query Help plss


   Hi,

   a)  check the file /mysql/data/*.err file and what it shows.

   b)  And what happend when you open cmd.exe and run.
   mysql  -u root

   Thanks,
   ViSolve DB Team.

   - Original Message - 
   From: "Renish" <[EMAIL PROTECTED]>

   To: "Visolve DB Team" <[EMAIL PROTECTED]>
   Sent: Tuesday, October 10, 2006 9:17 AM
   Subject: Re: Query Help plss


   >
   > - Original Message ----- 
   > From: "Visolve DB Team" <[EMAIL PROTECTED]>

   > To: "Renish koshy" <[EMAIL PROTECTED]>
   > Sent: Tuesday, October 10, 2006 11:42 AM
   > Subject: Re: Query Help plss
   >
   >
   >> Hi,
   >>
   >> On which platform?
   >>
   >> Thanks
   >> ViSolve DB Team.
   >> - Original Message - 
   >> From: "Renish koshy" <[EMAIL PROTECTED]>

   >> To: ; <[EMAIL PROTECTED]>
   >> Sent: Tuesday, October 10, 2006 8:03 AM
   >> Subject: Query Help plss
   >>
   >>
   >>> Hi all,
   >>>>>
   >>>>> I have installed MySql 4.1 in my system. when I tried to run 
Mysql , I

   >>&

Re: Query Help plss

[Visolve DB Team]

Hi,

Hope this link will you.

http://mysql.binarycompass.org/doc/refman/4.1/en/can-not-connect-to-server.html

Thanks,
ViSolve DB Team.

- Original Message - 
From: "Visolve DB Team" <[EMAIL PROTECTED]>

To: "Renish" <[EMAIL PROTECTED]>; 
Sent: Tuesday, October 10, 2006 11:03 AM
Subject: Re: Query Help plss


Hi

The .err file shows that the service is stopped Normally and no error was 
found.  sure the service is properly shutdown.


Try,
a)

net stop mysql
mysqld-nt remove
mysqld-nt install
net start mysql


b)
Also run 'services.msc' and double click on the mysql service, make sure its 
comming from C:\\www\mysql\bin


Thanks
ViSolve DB Team.
 - Original Message - 
 From: Renish

 To: Visolve DB Team ; mysql@lists.mysql.com
 Sent: Tuesday, October 10, 2006 10:07 AM
 Subject: Fw: Query Help plss



 - Original Message - 
 From: Renish

 To: Visolve DB Team ; mysql@lists.mysql.com
 Sent: Tuesday, October 10, 2006 12:35 PM
 Subject: Fw: Query Help plss



 - Original Message - 
 From: Renish

 To: mysql@lists.mysql.com ; Visolve DB Team
 Sent: Tuesday, October 10, 2006 12:34 PM
 Subject: Re: Query Help plss


 b)  And what happend when you open cmd.exe and run.
 mysql  -u root- It shows cant connect to local host error 10061

 *.error file shows this

 InnoDB: The first specified data file .\ibdata1 did not exist:
 InnoDB: a new database to be created!
 060717 10:32:22  InnoDB: Setting file .\ibdata1 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 060717 10:32:23  InnoDB: Log file .\ib_logfile0 did not exist: new to be 
created

 InnoDB: Setting log file .\ib_logfile0 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 060717 10:32:23  InnoDB: Log file .\ib_logfile1 did not exist: new to be 
created

 InnoDB: Setting log file .\ib_logfile1 size to 10 MB
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Doublewrite buffer not found: creating new
 InnoDB: Doublewrite buffer created
 InnoDB: Creating foreign key constraint system tables
 InnoDB: Foreign key constraint system tables created
 060717 10:32:24  InnoDB: Started; log sequence number 0 0
 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for 
connections.
 Version: '4.1.20-community-nt'  socket: ''  port: 3306  MySQL Community 
Edition (GPL)
 060717 13:47:31 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Normal shutdown


 061009 15:02:37  InnoDB: Starting shutdown...
 061009 15:02:42  InnoDB: Shutdown completed; log sequence number 0 
543205221
 061009 15:02:42 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Shutdown complete


 061009 15:03:46  InnoDB: Started; log sequence number 0 543205221
 C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for 
connections.
 Version: '4.1.21-community-nt'  socket: ''  port: 3306  MySQL Community 
Edition (GPL)
 061010 11:58:13 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Normal shutdown


 061010 11:58:13  InnoDB: Starting shutdown...
 061010 11:58:15  InnoDB: Shutdown completed; log sequence number 0 
543205231
 061010 11:58:15 [Note] C:\Program Files\MySQL\MySQL Server 
4.1\bin\mysqld-nt: Shutdown complete



   - Original Message - 
   From: Visolve DB Team

   To: Renish
   Sent: Tuesday, October 10, 2006 12:16 PM
   Subject: Re: Query Help plss


   Hi,

   a)  check the file /mysql/data/*.err file and what it shows.

   b)  And what happend when you open cmd.exe and run.
   mysql  -u root

   Thanks,
   ViSolve DB Team.

   - Original Message - 
   From: "Renish" <[EMAIL PROTECTED]>

   To: "Visolve DB Team" <[EMAIL PROTECTED]>
   Sent: Tuesday, October 10, 2006 9:17 AM
   Subject: Re: Query Help plss


   >
   > - Original Message - 
   > From: "Visolve DB Team" <[EMAIL PROTECTED]>

   > To: "Renish koshy" <[EMAIL PROTECTED]>
   > Sent: Tuesday, October 10, 2006 11:42 AM
   > Subject: Re: Query Help plss
   >
   >
   >> Hi,
   >>
   >> On which platform?
   >>
   >> Thanks
   >> ViSolve DB Team.
   >> - Original Message - 
   >> From: "Renish koshy" <[EMAIL PROTECTED]>

   >> To: ; <[EMAIL PROTECTED]>
   >> Sent: Tuesday, October 10, 2006 8:03 AM
   >> Subject: Query Help plss
   >>
   >>
   >>> Hi all,
   >>>>>
   >>>>> I have installed MySql 4.1 in my system. when I tried to run Mysql 
, I

   >>>>> always gets this error.
   >>>>>
   >>>>> Could not start MySql service on a local computer
   >>>>> Error 1067: the process terminated unexpectedly.
   >>>>>
   >>>>> Any help is highly appreciated.
   >>>>>
   >>>>> Regards,
   >>>>> Renish
   >>>
   > 



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

Re: Query Help plss

[Visolve DB Team]

Hi

The .err file shows that the service is stopped Normally and no error was 
found.  sure the service is properly shutdown.

Try,
a)
>net stop mysql 
>mysqld-nt remove 
>mysqld-nt install 
>net start mysql

b)
Also run 'services.msc' and double click on the mysql service, make sure its 
comming from C:\\www\mysql\bin

Thanks
ViSolve DB Team.
  - Original Message - 
  From: Renish 
  To: Visolve DB Team ; mysql@lists.mysql.com 
  Sent: Tuesday, October 10, 2006 10:07 AM
  Subject: Fw: Query Help plss



  - Original Message - 
  From: Renish 
  To: Visolve DB Team ; mysql@lists.mysql.com 
  Sent: Tuesday, October 10, 2006 12:35 PM
  Subject: Fw: Query Help plss



  - Original Message - 
  From: Renish 
  To: mysql@lists.mysql.com ; Visolve DB Team 
  Sent: Tuesday, October 10, 2006 12:34 PM
  Subject: Re: Query Help plss


  b)  And what happend when you open cmd.exe and run. 
  mysql  -u root- It shows cant connect to local host error 10061

  *.error file shows this

  InnoDB: The first specified data file .\ibdata1 did not exist:
  InnoDB: a new database to be created!
  060717 10:32:22  InnoDB: Setting file .\ibdata1 size to 10 MB
  InnoDB: Database physically writes the file full: wait...
  060717 10:32:23  InnoDB: Log file .\ib_logfile0 did not exist: new to be 
created
  InnoDB: Setting log file .\ib_logfile0 size to 10 MB
  InnoDB: Database physically writes the file full: wait...
  060717 10:32:23  InnoDB: Log file .\ib_logfile1 did not exist: new to be 
created
  InnoDB: Setting log file .\ib_logfile1 size to 10 MB
  InnoDB: Database physically writes the file full: wait...
  InnoDB: Doublewrite buffer not found: creating new
  InnoDB: Doublewrite buffer created
  InnoDB: Creating foreign key constraint system tables
  InnoDB: Foreign key constraint system tables created
  060717 10:32:24  InnoDB: Started; log sequence number 0 0
  C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections.
  Version: '4.1.20-community-nt'  socket: ''  port: 3306  MySQL Community 
Edition (GPL)
  060717 13:47:31 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: 
Normal shutdown

  061009 15:02:37  InnoDB: Starting shutdown...
  061009 15:02:42  InnoDB: Shutdown completed; log sequence number 0 543205221
  061009 15:02:42 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: 
Shutdown complete

  061009 15:03:46  InnoDB: Started; log sequence number 0 543205221
  C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections.
  Version: '4.1.21-community-nt'  socket: ''  port: 3306  MySQL Community 
Edition (GPL)
  061010 11:58:13 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: 
Normal shutdown

  061010 11:58:13  InnoDB: Starting shutdown...
  061010 11:58:15  InnoDB: Shutdown completed; log sequence number 0 543205231
  061010 11:58:15 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: 
Shutdown complete


- Original Message - 
From: Visolve DB Team 
To: Renish 
Sent: Tuesday, October 10, 2006 12:16 PM
Subject: Re: Query Help plss


Hi,

a)  check the file /mysql/data/*.err file and what it shows.

b)  And what happend when you open cmd.exe and run. 
mysql  -u root

Thanks,
ViSolve DB Team.

- Original Message - 
From: "Renish" <[EMAIL PROTECTED]>
To: "Visolve DB Team" <[EMAIL PROTECTED]>
Sent: Tuesday, October 10, 2006 9:17 AM
Subject: Re: Query Help plss


> 
> - Original Message - 
> From: "Visolve DB Team" <[EMAIL PROTECTED]>
    > To: "Renish koshy" <[EMAIL PROTECTED]>
> Sent: Tuesday, October 10, 2006 11:42 AM
> Subject: Re: Query Help plss
> 
> 
>> Hi,
>> 
>> On which platform?
>> 
>> Thanks
>> ViSolve DB Team.
>> - Original Message - 
>> From: "Renish koshy" <[EMAIL PROTECTED]>
>> To: ; <[EMAIL PROTECTED]>
>> Sent: Tuesday, October 10, 2006 8:03 AM
>> Subject: Query Help plss
>> 
>> 
>>> Hi all,
>>>>>
>>>>> I have installed MySql 4.1 in my system. when I tried to run Mysql , I
>>>>> always gets this error.
>>>>>
>>>>> Could not start MySql service on a local computer
>>>>> Error 1067: the process terminated unexpectedly.
>>>>>
>>>>> Any help is highly appreciated.
>>>>>
>>>>> Regards,
>>>>> Renish
>>>
> 

Re: Query Help plss

[Renish]

Xp
- Original Message - 
From: "Visolve DB Team" <[EMAIL PROTECTED]>

To: "Renish koshy" <[EMAIL PROTECTED]>
Sent: Tuesday, October 10, 2006 11:42 AM
Subject: Re: Query Help plss



Hi,

On which platform?

Thanks
ViSolve DB Team.
- Original Message - 
From: "Renish koshy" <[EMAIL PROTECTED]>

To: ; <[EMAIL PROTECTED]>
Sent: Tuesday, October 10, 2006 8:03 AM
Subject: Query Help plss



Hi all,


I have installed MySql 4.1 in my system. when I tried to run Mysql , I
always gets this error.

Could not start MySql service on a local computer
Error 1067: the process terminated unexpectedly.

Any help is highly appreciated.

Regards,
Renish




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

Re: Query Help plss

[Chris]

Renish koshy wrote:

Hi all,


I have installed MySql 4.1 in my system. when I tried to run Mysql , I
always gets this error.

Could not start MySql service on a local computer
Error 1067: the process terminated unexpectedly.


Stop sending this message!

We get the idea!

The more you send it, the less likely anyone is to help you.

Check your event log and see what that tells you.

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

RE: Query Help for Loosely Couple Properties

[Robert DiFalco]

So the question is what if I have multiple conditions say "COLOR LIKE
'Blue' [AND|OR] NAME LIKE 'Fred'". Will I have to create a JOIN for
each?

SELECT *
  FROM T
LEFT JOIN StringVal CSV ON T.ID = CSV.REF_ID
INNER JOIN StringType CST
  ON CSV.TYPE_ID = CST.ID AND CST.ID = COLOR
LEFT JOIN StringVal NSV ON T.ID = NSV.REF_ID
INNER JOIN StringType NST
  ON NSV.TYPE_ID = NST.ID AND NST.ID = NAME
WHERE 
( CSV.VAL = 'Blue' OR (CST.VAL = 'Blue' AND CSV.REF_ID IS NULL)
)
AND
( NSV.VAL = 'Fred' OR (NST.VAL = 'Fred' AND NSV.REF_ID IS NULL)
)

How do I generally simplify this?

R.

-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 04, 2006 4:12 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: Query Help for Loosely Couple Properties

I think this only works if I "know" that "Orange" is the default value.
For example if "Orange" is the default and I am searching for "Blue" I
will have to use a different query. Since I don't know the default (its
in the type table) I would have to do two queries. How about something
like this?

SELECT *
  FROM T
LEFT JOIN StringVal SV ON T.ID = SV.REF_ID
INNER JOIN StringType ST
  ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR
WHERE SV.VAL = "Blue" OR (ST.VAL = "Blue" AND SV.REF_ID IS NULL);

 

-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 02, 2006 9:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> The question is, how do I query this? Say I want all records from 
> table T whose COLOR property value is ORANGE.
> 
> The only thing I can come up with (and I'm no SQL expert and this 
> looks wrong to me) is the following:
> 
> SELECT *
> FROM T
> WHERE
> (
> T.ID NOT IN 
> ( 
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringValue.TYPE_ID = COLOR 
> )
> AND
> EXISTS
> ( 
> SELECT * 
> FROM StringType 
> WHERE StringType.DEF_VAL LIKE "Orange" AND StringType.ID = 
> COLOR
> 
> )
> )
> OR
> (
> T.ID IN 
> (
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringVal.VAL LIKE "Orange" AND StringVal.TYPE_ID =
COLOR
> )
> )

SELECT * FROM T
LEFT JOIN StringVal V
ON T.ID = V.REF_ID
INNER JOIN StringType ST
ON V.TYPE_ID = ST.ID
AND ST.ID = COLOR
WHERE V.REF_ID IS NULL
OR V.VAL = "Orange";




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




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

RE: Query Help for Loosely Couple Properties

[Robert DiFalco]

I think this only works if I "know" that "Orange" is the default value.
For example if "Orange" is the default and I am searching for "Blue" I
will have to use a different query. Since I don't know the default (its
in the type table) I would have to do two queries. How about something
like this?

SELECT *
  FROM T
LEFT JOIN StringVal SV ON T.ID = SV.REF_ID
INNER JOIN StringType ST
  ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR
WHERE SV.VAL = "Blue" OR (ST.VAL = "Blue" AND SV.REF_ID IS NULL);

 

-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 02, 2006 9:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> The question is, how do I query this? Say I want all records from 
> table T whose COLOR property value is ORANGE.
> 
> The only thing I can come up with (and I'm no SQL expert and this 
> looks wrong to me) is the following:
> 
> SELECT *
> FROM T
> WHERE
> (
> T.ID NOT IN 
> ( 
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringValue.TYPE_ID = COLOR 
> )
> AND
> EXISTS
> ( 
> SELECT * 
> FROM StringType 
> WHERE StringType.DEF_VAL LIKE "Orange" AND StringType.ID = 
> COLOR
> 
> )
> )
> OR
> (
> T.ID IN 
> (
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringVal.VAL LIKE "Orange" AND StringVal.TYPE_ID =
COLOR
> )
> )

SELECT * FROM T
LEFT JOIN StringVal V
ON T.ID = V.REF_ID
INNER JOIN StringType ST
ON V.TYPE_ID = ST.ID
AND ST.ID = COLOR
WHERE V.REF_ID IS NULL
OR V.VAL = "Orange";




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

Re: Query Help for Loosely Couple Properties

[Jay Pipes]

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> The question is, how do I query this? Say I want all records from table
> T whose COLOR property value is ORANGE.
> 
> The only thing I can come up with (and I'm no SQL expert and this looks
> wrong to me) is the following:
> 
> SELECT *
> FROM T
> WHERE
> (
> T.ID NOT IN 
> ( 
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringValue.TYPE_ID = COLOR 
> )
> AND
> EXISTS
> ( 
> SELECT * 
> FROM StringType 
> WHERE StringType.DEF_VAL LIKE "Orange" AND StringType.ID = COLOR
> 
> )
> )
> OR
> (
> T.ID IN 
> (
> SELECT StringVal.REF_ID 
> FROM StringVal 
> WHERE StringVal.VAL LIKE "Orange" AND StringVal.TYPE_ID = COLOR
> )
> )

SELECT * FROM T
LEFT JOIN StringVal V
ON T.ID = V.REF_ID
INNER JOIN StringType ST
ON V.TYPE_ID = ST.ID
AND ST.ID = COLOR
WHERE V.REF_ID IS NULL
OR V.VAL = "Orange";


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

RE: Query Help for Loosely Couple Properties

[Robert DiFalco]

They are user defined properties. 

-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 01, 2006 8:11 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> I have a table that contains properties that can be associated with 
> any table whose primary key is a LONG. Lets say that there is just one

> kind of property. The table looks something like this:
> 
> TABLE StringVal
>   REF_ID  BIGINT// row to associate property with
>   TYPE_ID BIGINT// type of string property
>   VAL VARCHAR   // property value
> 
>   P_KEY( REF_ID, TYPE_ID )
> 
> There is another table to represent a specific StringVal type along 
> with its default value:
> 
>TABLE StringType
>   ID  BIGINT   // The TYPE ID
>   NAMEVARCHAR  // The unique name of this property
>   DEF_VAL VARCHAR  // The default value of this property

Actually, the rub is that you are not using specific columns for
specific entity attributes, and are pretty much storing everything in
one gigantic table.  Any particular reason for this?  For instance, why
not have a column called "color", instead of overcomplicating things?

-jay




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

Re: Query Help for Loosely Couple Properties

[Jay Pipes]

On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> I have a table that contains properties that can be associated with any
> table whose primary key is a LONG. Lets say that there is just one kind
> of property. The table looks something like this:
> 
> TABLE StringVal
>   REF_ID  BIGINT// row to associate property with
>   TYPE_ID BIGINT// type of string property
>   VAL VARCHAR   // property value
> 
>   P_KEY( REF_ID, TYPE_ID )
> 
> There is another table to represent a specific StringVal type along with
> its default value:
> 
>TABLE StringType
>   ID  BIGINT   // The TYPE ID
>   NAMEVARCHAR  // The unique name of this property
>   DEF_VAL VARCHAR  // The default value of this property

Actually, the rub is that you are not using specific columns for
specific entity attributes, and are pretty much storing everything in
one gigantic table.  Any particular reason for this?  For instance, why
not have a column called "color", instead of overcomplicating things?

-jay


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

Re: query help-multiple joins

[mel list_php]

Perfect!

I tried aliasing the field names but didn't think about the table, and was 
just stuck looking at that query without any idea...


Thanks a lot for your help.
melanie


From: Johan Höök <[EMAIL PROTECTED]>
To: mel list_php <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: query help-multiple joins
Date: Tue, 23 May 2006 13:16:33 +0200

Hi,
I guess you should be able to do something like:
SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name
FROM term t1
LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id
LEFT JOIN term t2 ON r.term_id2 = t2.term_id

/Johan

mel list_php skrev:

Hi!

I'm stuck with a join query

2 tables, term and relation, the first one with definition of terms the 
second one with the relations between them.


CREATE TABLE `term` (
 `term_id` int(11) unsigned NOT NULL auto_increment,
 `name` varchar(250) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
and
CREATE TABLE `relationTerm` (
 `relation_id` int(11) unsigned NOT NULL auto_increment,
 `term_id1` int(11) unsigned NOT NULL default '0',
 `term_id2` int(11) unsigned NOT NULL default '0',
 `type_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`relation_id`),
 UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


in the second table term_id1 and term_id2 are referencing term_id of the 
first table.


Example of data:

INSERT INTO `term` VALUES (1, 'A');
INSERT INTO `term` VALUES (2, 'B');

INSERT INTO `relationTerm` VALUES (1, 1, 2, 1);

I want to obtain:
A 1 B

I managed to obtain A 1 2, but I don't know how to replace the 2 with the 
actual name of the entry, which would be B.


My query is:
SELECT term_id, name, type_id, term_id2
FROM term
LEFT JOIN relationTerm ON term_id1 = term_id

I should be able to somehow join again on term_id2 but just don't see how 
to do it...


Thanks for any help,
melanie

_
Be the first to hear what's new at MSN - sign up to our free newsletters! 
http://www.msn.co.uk/newsletters










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


_
The new MSN Search Toolbar now includes Desktop search! 
http://join.msn.com/toolbar/overview



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

Re: query help-multiple joins

[Johan Höök]

Hi,
I guess you should be able to do something like:
SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name
FROM term t1
LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id
LEFT JOIN term t2 ON r.term_id2 = t2.term_id

/Johan

mel list_php skrev:

Hi!

I'm stuck with a join query

2 tables, term and relation, the first one with definition of terms the 
second one with the relations between them.


CREATE TABLE `term` (
 `term_id` int(11) unsigned NOT NULL auto_increment,
 `name` varchar(250) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
and
CREATE TABLE `relationTerm` (
 `relation_id` int(11) unsigned NOT NULL auto_increment,
 `term_id1` int(11) unsigned NOT NULL default '0',
 `term_id2` int(11) unsigned NOT NULL default '0',
 `type_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`relation_id`),
 UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


in the second table term_id1 and term_id2 are referencing term_id of the 
first table.


Example of data:

INSERT INTO `term` VALUES (1, 'A');
INSERT INTO `term` VALUES (2, 'B');

INSERT INTO `relationTerm` VALUES (1, 1, 2, 1);

I want to obtain:
A 1 B

I managed to obtain A 1 2, but I don't know how to replace the 2 with 
the actual name of the entry, which would be B.


My query is:
SELECT term_id, name, type_id, term_id2
FROM term
LEFT JOIN relationTerm ON term_id1 = term_id

I should be able to somehow join again on term_id2 but just don't see 
how to do it...


Thanks for any help,
melanie

_
Be the first to hear what's new at MSN - sign up to our free 
newsletters! http://www.msn.co.uk/newsletters






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

Re: Query Help

[Robert Gehrig]

Thanks that got it.


Robert Gehrig
Webmaster at www.gdbarri.com

e-mail: [EMAIL PROTECTED]




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

Re: Query Help

[Gabriel PREDA]

SELECT id, count(*) AS cnt
  FROM `table_name`
  GROUP BY id
  ORDER BY cnt DESC
  [ LIMIT 1 ]

--
Gabriel PREDA
Senior Web Developer

Re: Query help with count and join on same table I think

[Frank]

2wsxdr5 wrote:

> I have a table of people.  Some of the people in this table are
> related.  You can find out who is related by comparing a familyID
> number.  I have a query to select a certain group of people from the
> table and I want to also select anyone who is related to them, even
> though those who are related will not match the other criteria.  So my
> table is something like this
> 
> people{
> PID,
> Name
> FamilyID,
> BirthDate,
> Sex
> Address
> . . .
> )
> 
> I have a query like this
> Select * from people where BirthDate < 1987 and Birthday > 1950 and
> address = "xyz"
> 
> I need to change it so it includes everyone that has the same Family ID
> as each person that query will return and group it by Family ID
> 

Hi Chris,

if you are using a new enough version (4.1 or newer) you can use a subquery.
If I understand your intention correctly, the following statement should do
what you need:

select familyid, count(PID) from people where FamilyID = (select FamilyID
from people where BirthDate < 1987 and Birthday > 1950 and address = "xyz")
group by FamilyId

If your version does not support sub-queries, have at look at
http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

Cheers
Frank



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

Re: Query help

[Peter Brawley]

OKAN ARI wrote:

I have 3 tables
Table 1: user(id, name, surname)
Table 2: crime(id, detail)
Table 3: user_crime(id, user_id, crime_id)

Table 1
1, OKAN, ARI

Table 2
1, "detail 1"
2, "Detail 2"

Table 3
1, 1, 1
1, 1, 2

So user 1 takes 2 crime from crime table...

I want to receive info with 1 query: I need name surname and his 
crime_ids of user_id 1

So result might be:
user_id, name, suername, crime_ids
1, OKAN, ARI, {1-2}

SELECT u.name, u.surname, uc.crime_id, c.detail
FROM user AS u
INNER JOIN user_crime AS uc ON u.id=uc.user_id
INNER JOIN crime AS c ON uc.crime_id=c.id
WHERE u.id=1

PB



Is it possible?

gibi

Mumkun mu?


-
Iletisim teknolojilerinin gucunu ispatlayan iletisim teknolojileri 
dergisi: Tele.com.tr

http://www.tele.com.tr




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006


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

Re: query help?

[Richard Reina]

Actually I am looking for duplicates (vedor_no, date), but I think I can 
hopefully adapt the solution you have given me.

[EMAIL PROTECTED] wrote:  
If you are looking just for duplicate (ID,vendort_no) combinations, this will 
find them: 
 
SELECT ID, vendor_no, count(1) as dupes 
FROM table_name_here 
GROUP BY ID, vendor_no 
HAVING dupes >1; 
 
Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine 
 
 
Richard Reina <[EMAIL PROTECTED]> wrote on 02/23/2006 12:49:28 PM:
 
 > I's so sorry. You are very correct. The sample data is bad.  ID 
 > should be unique. Here it is corrected.
 > 
 >  |ID| vendor_no| date|
 >  |2354  | 578  | "2005-12-23"|
 >  |2355  | 334  | "2005-12-24"|
 >  |2356  | 339  | "2005-12-26"|
 >  |2357  | 339  | "2005-12-26"|
 >  |2358  | 339  | "2005-12-26"|
 >  |2359  | 445  | "2005-12-26"|
 >  |2360  | 522  | "2005-12-27"|
 >  |2361  | 522  | "2005-12-27"|
 >  
 >  
 > [EMAIL PROTECTED] wrote: > I am a novice when it come to queries 
 > such as this and  was hoping 
 > > someone could help me write a query that tells me how many records 
 > > have the same ID and vendor number.
 > > 
 > > |ID| vendor_no| date|
 > > |2354  | 578  | "2005-12-23"|
 > > |2355  | 334  | "2005-12-24"|
 > > |2356  | 339  | "2005-12-26"|
 > > |2357  | 339  | "2005-12-26"|
 > > |2358  | 339  | "2005-12-26"|
 > > |2359  | 445  | "2005-12-26"|
 > > |2354  | 522  | "2005-12-27"|
 > > |2355  | 522  | "2005-12-27"|
 > > 
 > > Would I use select count? Any help would be greatly appreciated.
 > 
 > I'd need a clearer spec to offer advise.  What results would you want
 > from the example data?  2354 is there twice but with different vendor
 > numbers.  And 522 is there twice with different IDs.  One interpretation
 > of "[records with] the same ID and vendor number" is 0 because no record
 > has both the same as any other.
 > 
 > 
 > 
 > 
 > 
 > A people that values its privileges above its principles soon loses both.
 >  -Dwight D. Eisenhower.



A people that values its privileges above its principles soon loses both.
 -Dwight D. Eisenhower.

Re: query help?

[SGreen]

If you are looking just for duplicate (ID,vendort_no) combinations, this 
will find them:

SELECT ID, vendor_no, count(1) as dupes
FROM table_name_here
GROUP BY ID, vendor_no
HAVING dupes >1;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Richard Reina <[EMAIL PROTECTED]> wrote on 02/23/2006 12:49:28 PM:

> I's so sorry. You are very correct. The sample data is bad.  ID 
> should be unique. Here it is corrected.
> 
>  |ID| vendor_no| date|
>  |2354  | 578  | "2005-12-23"|
>  |2355  | 334  | "2005-12-24"|
>  |2356  | 339  | "2005-12-26"|
>  |2357  | 339  | "2005-12-26"|
>  |2358  | 339  | "2005-12-26"|
>  |2359  | 445  | "2005-12-26"|
>  |2360  | 522  | "2005-12-27"|
>  |2361  | 522  | "2005-12-27"|
> 
> 
> [EMAIL PROTECTED] wrote: > I am a novice when it come to queries 
> such as this and  was hoping 
> > someone could help me write a query that tells me how many records 
> > have the same ID and vendor number.
> > 
> > |ID| vendor_no| date|
> > |2354  | 578  | "2005-12-23"|
> > |2355  | 334  | "2005-12-24"|
> > |2356  | 339  | "2005-12-26"|
> > |2357  | 339  | "2005-12-26"|
> > |2358  | 339  | "2005-12-26"|
> > |2359  | 445  | "2005-12-26"|
> > |2354  | 522  | "2005-12-27"|
> > |2355  | 522  | "2005-12-27"|
> > 
> > Would I use select count? Any help would be greatly appreciated.
> 
> I'd need a clearer spec to offer advise.  What results would you want
> from the example data?  2354 is there twice but with different vendor
> numbers.  And 522 is there twice with different IDs.  One interpretation
> of "[records with] the same ID and vendor number" is 0 because no record
> has both the same as any other.
> 
> 
> 
> 
> 
> A people that values its privileges above its principles soon loses 
both.
>  -Dwight D. Eisenhower.

Re: query help?

[Richard Reina]

I's so sorry. You are very correct. The sample data is bad.  ID should be 
unique. Here it is corrected.

 |ID| vendor_no| date|
 |2354  | 578  | "2005-12-23"|
 |2355  | 334  | "2005-12-24"|
 |2356  | 339  | "2005-12-26"|
 |2357  | 339  | "2005-12-26"|
 |2358  | 339  | "2005-12-26"|
 |2359  | 445  | "2005-12-26"|
 |2360  | 522  | "2005-12-27"|
 |2361  | 522  | "2005-12-27"|
 
 
[EMAIL PROTECTED] wrote: > I am a novice when it come to queries such as this 
and  was hoping 
> someone could help me write a query that tells me how many records 
> have the same ID and vendor number.
> 
> |ID| vendor_no| date|
> |2354  | 578  | "2005-12-23"|
> |2355  | 334  | "2005-12-24"|
> |2356  | 339  | "2005-12-26"|
> |2357  | 339  | "2005-12-26"|
> |2358  | 339  | "2005-12-26"|
> |2359  | 445  | "2005-12-26"|
> |2354  | 522  | "2005-12-27"|
> |2355  | 522  | "2005-12-27"|
> 
> Would I use select count? Any help would be greatly appreciated.

I'd need a clearer spec to offer advise.  What results would you want
from the example data?  2354 is there twice but with different vendor
numbers.  And 522 is there twice with different IDs.  One interpretation
of "[records with] the same ID and vendor number" is 0 because no record
has both the same as any other.





A people that values its privileges above its principles soon loses both.
 -Dwight D. Eisenhower.

RE: query help?

[Andy Eastham]

Richard,

If you mean with _both_ the same id _and_ vendor id, try this:

Select id, vendor_id, count(*) from tablename group by id, vendor_id;

If you just want separate counts for id and vendor_id, use:
Select id, count(*) from tablename group by id; 
Select vendor_id, count(*) from tablename group by vendor_id;

Andy
> -Original Message-
> From: Richard Reina [mailto:[EMAIL PROTECTED]
> Sent: 23 February 2006 16:48
> To: mysql@lists.mysql.com
> Subject: query help?
> 
> I am a novice when it come to queries such as this and  was hoping someone
> could help me write a query that tells me how many records have the same
> ID and vendor number.
> 
>  |ID | vendor_no  | date|
>  |2354  | 578  | "2005-12-23"|
>  |2355  | 334  | "2005-12-24"|
>  |2356  | 339  | "2005-12-26"|
>  |2357  | 339  | "2005-12-26"|
>  |2358  | 339  | "2005-12-26"|
>  |2359  | 445  | "2005-12-26"|
>  |2354  | 522  | "2005-12-27"|
>  |2355  | 522  | "2005-12-27"|
> 
>  Would I use select count? Any help would be greatly appreciated.
> 
> 
> 
> A people that values its privileges above its principles soon loses both.
>  -Dwight D. Eisenhower.



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

Re: query help?

[cnelson]

> I am a novice when it come to queries such as this and  was hoping 
> someone could help me write a query that tells me how many records 
> have the same ID and vendor number.
> 
> |ID| vendor_no| date|
> |2354  | 578  | "2005-12-23"|
> |2355  | 334  | "2005-12-24"|
> |2356  | 339  | "2005-12-26"|
> |2357  | 339  | "2005-12-26"|
> |2358  | 339  | "2005-12-26"|
> |2359  | 445  | "2005-12-26"|
> |2354  | 522  | "2005-12-27"|
> |2355  | 522  | "2005-12-27"|
> 
> Would I use select count? Any help would be greatly appreciated.

I'd need a clearer spec to offer advise.  What results would you want
from the example data?  2354 is there twice but with different vendor
numbers.  And 522 is there twice with different IDs.  One interpretation
of "[records with] the same ID and vendor number" is 0 because no record
has both the same as any other.


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

Re: Query Help

[Peter Brawley]

Ian,
>It now looks like this and still doesn't work, complains about exactly the
>same thing.

>SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
>frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
>mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
>c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN
>mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON
>(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
>cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
>cc.ordering, cc.title, c.ordering LIMIT 0,10;
Your query still has a comma join.

PB

-

Ian Barnes wrote:

  Hi,

It now looks like this and still doesn't work, complains about exactly the
same thing.

SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN
mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON
(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10;

Thanks,
Ian

-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]] 
Sent: 24 January 2006 09:50 PM
To: Ian Barnes
Cc: mysql@lists.mysql.com
Subject: Re: Query Help

Ian Barnes wrote:

  
  
Hi,

This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially support
mysql5 yet.

The original query:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c,
mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id =
c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN
mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f
ON f.content_id = c.id WHERE c.state >= 0 AND c.catid=cc.id AND
cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10

My modified version:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN
mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON
(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10;

The error I get for both ones is: Unknown column 'c.access' in 'on clause'

Thanks and sorry for the stupid question.

Cheers
Ian



 


  
  Replace all your comma joins to INNER JOIN syntax

  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date: 1/23/2006


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

RE: Query Help

[SGreen]

That's because you are still using comma joins!

> ... FROM mos_content c,  < one comma
> mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
   ^--- another comma
> c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN
> mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f 
ON
> (f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
> cc.section=s.id AND s.scope='content' AND c.sectionid='1' ...

If you feel uncomfortable making an INNER JOIN with no restrictions (no 
"ON" clause) you can call it a CROSS JOIN instead.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Ian Barnes" <[EMAIL PROTECTED]> wrote on 01/24/2006 03:15:24 PM:

> Hi,
> 
> It now looks like this and still doesn't work, complains about exactly 
the
> same thing.
> 
> SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id 
AS
> frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
> mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
> c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN
> mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f 
ON
> (f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
> cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
> cc.ordering, cc.title, c.ordering LIMIT 0,10;
> 
> Thanks,
> Ian
> 
> -Original Message-
> From: gerald_clark [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 09:50 PM
> To: Ian Barnes
> Cc: mysql@lists.mysql.com
> Subject: Re: Query Help
> 
> Ian Barnes wrote:
> 
> >Hi,
> >
> >This is my current query which works in mysql 4, but not in 5. Its from
> >mambo, but im trying to modify it because they don't officially support
> >mysql5 yet.
> >
> >The original query:
> >SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, 
f.content_id AS
> >frontpage, s.title AS section_name, v.name AS author FROM mos_content 
AS c,
> >mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON 
g.id =
> >c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN
> >mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage 
AS f
> >ON f.content_id = c.id WHERE c.state >= 0 AND c.catid=cc.id AND
> >cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
> >cc.ordering, cc.title, c.ordering LIMIT 0,10
> >
> >My modified version:
> >SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, 
f.content_id AS
> >frontpage, s.title AS section_name, v.name AS author FROM mos_content 
c,
> >mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
> >c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN
> >mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f 
ON
> >(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
> >cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
> >cc.ordering, cc.title, c.ordering LIMIT 0,10;
> >
> >The error I get for both ones is: Unknown column 'c.access' in 'on 
clause'
> >
> >Thanks and sorry for the stupid question.
> >
> >Cheers
> >Ian
> >
> >
> >
> > 
> >
> Replace all your comma joins to INNER JOIN syntax
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

RE: Query Help

[Ian Barnes]

Hi,

It now looks like this and still doesn't work, complains about exactly the
same thing.

SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN
mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage f ON
(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10;

Thanks,
Ian

-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED] 
Sent: 24 January 2006 09:50 PM
To: Ian Barnes
Cc: mysql@lists.mysql.com
Subject: Re: Query Help

Ian Barnes wrote:

>Hi,
>
>This is my current query which works in mysql 4, but not in 5. Its from
>mambo, but im trying to modify it because they don't officially support
>mysql5 yet.
>
>The original query:
>SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
>frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c,
>mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id =
>c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN
>mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f
>ON f.content_id = c.id WHERE c.state >= 0 AND c.catid=cc.id AND
>cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
>cc.ordering, cc.title, c.ordering LIMIT 0,10
>
>My modified version:
>SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
>frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
>mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
>c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN
>mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON
>(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
>cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
>cc.ordering, cc.title, c.ordering LIMIT 0,10;
>
>The error I get for both ones is: Unknown column 'c.access' in 'on clause'
>
>Thanks and sorry for the stupid question.
>
>Cheers
>Ian
>
>
>
>  
>
Replace all your comma joins to INNER JOIN syntax

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


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

Re: Query Help

[gerald_clark]

Ian Barnes wrote:


Hi,

This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially support
mysql5 yet.

The original query:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c,
mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id =
c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN
mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f
ON f.content_id = c.id WHERE c.state >= 0 AND c.catid=cc.id AND
cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10

My modified version:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title AS section_name, v.name AS author FROM mos_content c,
mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id =
c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN
mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON
(f.content_id = c.id) WHERE c.state >= 0 AND c.catid=cc.id AND
cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10;

The error I get for both ones is: Unknown column 'c.access' in 'on clause'

Thanks and sorry for the stupid question.

Cheers
Ian



 


Replace all your comma joins to INNER JOIN syntax

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

Re: query help

[Michael Stassen]

grKumaran wrote:
> Hello,
>
> CREATE TABLE t (
> dt datetime
> )
>
> Please assume this is the table structure and contains thousands of
> records. And I want to list them only last 12 months (that mean last 1
> year) records exactly.
>
> For that I tried using the following query, but it list sometimes 13 months
> when the current date is in the middle of the month.
>
> SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();
>
> I request you to help me.  And thanking you for the consideration.

First, don't do that.  Your WHERE clause calculates a value based on the 
contents of a row, then compares the calculated value to a constant.  An 
index on your dt column cannot be used to find matching rows in this case, 
so a full table scan is required.  Instead, you should rewrite your query to 
make a direct comparison of the dt column to a calculated constant.  The 
following query is equivalent to yours, in that it matches the same rows, 
but it can use the index on dt:


  SELECT * FROM t
  WHERE dt >= NOW - INTERVAL 1 YEAR;

Peter Brawley wrote:
> R,
>
> Try...
>
> DATE_ADD( dt, INTERVAL
> IF(YEAR(NOW())%4>0 AND YEAR(NOW())%100=0,366,365)
> DAY ) >= NOW()

(This won't use an index either.)  You think it's a leapyear problem?  I 
suspect Shawn is right that the real problem is that the requirements are 
not as stated.  The poster says he wants "only last 12 months (that mean 
last 1 year)", but then states that running the query in the middle of a 
month gives the wrong results.  My guess is that he wants something other 
than precisely the last year's results.  Perhaps he wants a year ending on 
the last day of the current or previous month.  Perhaps the start date 
shouldn't be precisely 1 year ago today, but at the start of a month.


Consider:

SET @last_month_end = LAST_DAY(CURDATE() - INTERVAL 1 MONTH)
 + INTERVAL 1 DAY
 - INTERVAL 1 SECOND;

SET @cur_month_yr = LAST_DAY(CURDATE()) - INTERVAL 1 YEAR + INTERVAL 1 DAY;

SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH)
 - INTERVAL 1 YEAR + INTERVAL 1 DAY;

SELECT @last_month_yr, @cur_month_yr, @last_month_end;
++---+-+
| @last_month_yr | @cur_month_yr | @last_month_end |
++---+-+
| 2004-10-01 | 2004-11-01| 2005-09-30 23:59:59 |
++---+-+
1 row in set (0.00 sec)

Now something like

  SELECT * FROM t
  WHERE dt BETWEEN @cur_month_yr AND NOW();

or

  SELECT * FROM t
  WHERE dt BETWEEN @last_month_yr AND @last_month_end;

or some similar combination may do what you want.

Michael


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

Re: query help

[Peter Brawley]

R,

>I tried using the following query, but it list sometimes 13 months
>when the current date is in the middle of the month.

SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();

Try...

DATE_ADD( dt, INTERVAL
IF(YEAR(NOW())%4>0 AND YEAR(NOW())%100=0,366,365)
DAY ) >= NOW()

PB

-

grKumaran wrote:


Hello,

CREATE TABLE t (
dt datetime
)

Please assume this is the table structure and contains thousands of records.
And I want to list them only last 12 months (that mean last 1 year) records
exactly.

For that I tried using the following query, but it list sometimes 13 months
when the current date is in the middle of the month.

SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();

I request you to help me.  And thanking you for the consideration.

Sincerely,
R.Kumaran


 




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005


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

Re: query help

[SGreen]

[EMAIL PROTECTED] wrote on 10/18/2005 06:28:03 AM:

> Hello,
> 
> 
> CREATE TABLE t (
> dt datetime
> )
> 
> Please assume this is the table structure and contains thousands of 
records.
> And I want to list them only last 12 months (that mean last 1 year) 
records
> exactly.
> 
> For that I tried using the following query, but it list sometimes 13 
months
> when the current date is in the middle of the month.
> 
> SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();
> 
> I request you to help me.  And thanking you for the consideration.
> 
> Sincerely,
> R.Kumaran
> 
> 

Assume for a moment that 1 CALENDAR YEAR = 12 months = 365 DAYS = 8760 
HOURS = 525600 MINUTES = 31536000 seconds (ignoring leap years)

The following calculation is correct:

mysql>SELECT NOW(), NOW() - INTERVAL 1 YEAR;
+-+-+
| NOW()   | NOW() - INTERVAL 1 YEAR |
+-+-+
| 2005-10-18 09:38:07 | 2004-10-18 09:38:07 |
+-+-+
1 row in set (0.09 sec)


That is exactly 12 months ago (to the second). I don't understand the 
problem. What kind of date-range calculation would you like to have? If 
you do not want to go back one whole year, how far do you want to go back? 


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: query help

[SGreen]

[EMAIL PROTECTED] wrote on 10/11/2005 01:27:12 PM:

> 
> 
> lets say i have two tables:
> 
> module
> -
> VARCHAR module_name
> INTEGER module_id
> 
> module_config
> 
> INTEGER module_id
> VARCHAR config_name
> VARCHAR config_value
> 
> 
> config item names and values are rather arbitrary and depend on the 
module.
> 
> each module can have zero to many config items..
> 
> How do i find out the id of a module with the following two 
> module_config entries:
> config_name='mapping' and config_value='true'
> and 
> config_name='loc_enabled' and config_value='true'
> 
> it seems like i would have to use a subquery to check two rows at 
> the same time.  I wasn't really sure how to express it in SQL though.
> 

No need for a subquery. There are at least two ways. The most direct uses 
a self join but it does not scale well to more than two or three 
conditions to match

SELECT a.module_id
FROM module_config a
INNER JOIN module_config b
on b.module_id = a.module_id
and b.config_name = 'loc_enabled'
and b.config_value = 'true'
WHERE a.config_name = 'mapping 
and a.config_value='true';

The more flexible method is to use a COUNT() and a HAVING() to test for 
how many conditions each module_id matches. It scales much better to more 
than 2 or 3 conditions to match


SELECT module_id, count(1) as matches
FROM module_config
WHERE config_name IN ('loc_enabled', 'mapping')
AND config_value='true'
GROUP BY module_id
HAVING matches = 2;

Make sense? If you need to reuse the list of module_id's in another query, 
just dump the results of this SELECT into a TEMPORARY TABLE and use them 
from there.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: query help

[Michael Stassen]

[EMAIL PROTECTED] wrote:


lets say i have two tables:

module
-
VARCHAR module_name
INTEGER module_id

module_config

INTEGER module_id
VARCHAR config_name
VARCHAR config_value


config item names and values are rather arbitrary and depend on the module.

each module can have zero to many config items..

How do i find out the id of a module with the following two module_config 
entries:
config_name='mapping' and config_value='true'
and 
config_name='loc_enabled' and config_value='true'


it seems like i would have to use a subquery to check two rows at the
same time. I wasn't really sure how to express it in SQL though.


You have two options:

1) Join to table module_config twice, looking for one row in the first copy 
and the second row in the second copy:


  SELECT m.module_id, m.module_name
  FROM module m
  JOIN module_config mc1 ON m.module_id = mc1.module_id
  JOIN module_config mc2 ON m.module_id = mc2.module_id
  WHERE mc1.config_name = 'mapping' AND mc1.config_value = 'true'
AND mc2.config_name = 'loc_enabled' AND mc2.config_value = 'true'

2) Join to module_config once and look for either row.  Each module_id will 
have 0, 1, or 2 matching rows in module_config.  You want the ones with 2 
matching rows (met both requirements):


  SELECT m.module_id, m.module_name
  FROM module m
  JOIN module_config mc ON m.module_id = mc.module_id
  WHERE mc.config_value = 'true'
AND mc.config_name IN ('mapping', 'loc_enabled')
  GROUP BY m.module_id
  HAVING COUNT(*) = 2;

Try both ways to see which is faster for your data.  If you ever need to 
match more than 2 config_names, you'll definitely want option 2, as it will 
be easier to write and faster.


Michael

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

Re: Query help

[SGreen]

Balazs Bagi <[EMAIL PROTECTED]> wrote on 08/24/2005 05:10:55 AM:

> Hi there, I'm kind of new to joining two tables.  Please bear with me
> with this simple example.  I have three tables.
> 
> Guests is a list of guests that are coming to the party.  The primary
> key of this table is 'id' and the foreign key that ties the guests to
> the registered user of the site is 'user_id'.
> 
> Invites is a list of inviations sent out, with a primary key of 'id'
> and a foreign key of 'guest_id' that ties it in with the guest that
> the invite was sent to.
> 
> Gifts is like invites, but is a list of gifts that have been received
> by the guest. Primary key is 'id' and the foreign key is once again
> 'guest_id'.
> 
> Now, if I want to run a query that pulls all of the gifts that belong
> to any guests that in turn belong to the registered user, I would do
> something like:
> 
> SELECT firstname,lastname,guests.id,giftname,giftdate FROM
> guests,gifts WHERE (guests.id = invites.guest_id) AND (guests.user_id
> = '$user_id')
> 
> $user_id is our session variable.
> 
> Ok that's fine, it gives me all of the guests first names and last
> names and the name of the gifts and dates they were received, but only
> of the guests that the registered user is "owner" of, ie guests that
> belong to that user.
> 
> *** MY PROBLEM  *
> 
> What kind of query would I run if I want to delete a gift that belongs
> to the registered user?  Currently, the only way to determine if a
> gift belongs to the registered user is to first see if the gift came
> from a guest that belongs to the registered user.  It's what I refer
> to as the once-removed table.  I need to make a binary comparison in a
> situation where there are three separate tables, instead of just two. 
> I know this is beginner stuff, and I hope I'm articulating my problem
> without being too confusing(!) but I really want to get a grasp on
> this, and none of the books I have read have specific enough examples
> to guide me.
> 
> I am trying to envision something like:
> 
> DELETE FROM gifts WHERE guests.user_id = '$user_id' AND guests.id =
> gifts.guest_id AND gift.id = '2'
> 
> Of course I get an error.  Do I need to start with "DELETE FROM
> gifts,guests..." but then how will it know that I'm only actually
> deleting from gifts??
> 
> Thank you so much in advance,
> B
> 

This is an RTFM situation (especially since you are new):
According to http://dev.mysql.com/doc/mysql/en/delete.html there are two 
ways to make a DELETE query that uses multiple tables. I personally prefer 
the first form:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
   tbl_name[.*] [, tbl_name[.*] ...]
   FROM table_references
   [WHERE where_definition]

In your case, you want to delete from gift (the DELETE clause) but only 
those record that have matching records in the guests table (the table 
references part of the FROM clause). Your query will resemble this (I 
refuse to use the comma-separated list form of making INNER JOINS):

DELETE gifts
FROM gifts
INNER JOIN user
on gifts.guest_id = guests.id
WHERE user.user_id = ... ;

replace the ellipsis (...)  with an actual user id. Unless your user.id 
field is a string, you will NOT need the single quotes around that value.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query Help

[Arno Coetzee]

Jason Chan wrote:


Jason Chan wrote:

 


I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA

I want to find out students who have got A in both Maths and Chem
How the SQL look like?








   


select s.StudentID , s.StudentName
from Student as s , SubjectGrade as sj
where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject =
'Maths' or sj.Subject = 'Chem')

I think your query will return student 3 as well




 


My apologies. Misunderstood the question...

--
Arno Coetzee
Developer
Flash Media Group
Office : 2712 342 7595
Mobile : 2782 693 6180


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

Re: Query Help

[Jason Chan]

Thanks Scott!
"Scott Noyes" <[EMAIL PROTECTED]> ???
news:[EMAIL PROTECTED] ???...
> SELECT s.StudentID, StudentName
> FROM Student s
>   JOIN StudentGrade sg1 USING (StudentID)
>   JOIN StudentGrade sg2 USING (StudentID)
> WHERE
>   sg1.Subject = 'Maths' AND sg1.Grade = 'A'
>   AND sg2.Subject = 'Chem' AND sg2.Grade = 'A'

> So i have to write 3 join if I have 3 conditions and so on, right?

Yes, if you use this approach, you'll need another JOIN and another
two conditions in the WHERE clause.

If you use the IN()...HAVING approach, then you just need to add the
new subject to the IN() function and change the HAVING COUNT(*) = 2 to
= 3.  (As was pointed out, this approach won't work if you have two
Maths grades and no Chem grades.)

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





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

Re: Query Help

[Michael Stassen]

Jason Chan wrote:

> I have a student Table and a SubjectGrade table
>
> Create Table Student(
> StudentID INT NOT NULL,
> StudentName VARCHAR(30)
> )
>
> Create Table SubjectGrade(
> StudentID INT NOT NULL,
> Subject VARCHAR(30) NOT NULL,
> Grade CHAR(1)
> )
>
> let's say have following record in SubjectGrade
>
> 1MathsA
> 1PhysB
> 1ChemA
> 2MathsA
> 2ChemA
> 3BioC
> 3ChemA
>
> I want to find out students who have got A in both Maths and Chem
> How the SQL look like?

Philippe Poelvoorde wrote:

> Michael Stassen wrote:
Alternatively, use an IN list for the subject, then use a HAVING 
clause to limit the results to students with 2 matching rows, like this:


  SELECT StudentID, StudentName
  FROM Student s
  JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
  WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
  GROUP BY s.StudentID
  HAVING COUNT(*) = 2;


What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)


Then the table has redundant rows!

But seriously, that's a different question -- one which would require more 
information to answer.  In other words, based on the table description, I 
assume the combination of StudentID and Subject is unique in table 
SubjectGrade.  While 'Maths', 'Phys', and 'Chem' sound categorical, I expect 
they were simplifications of actual course names, and the question amounts to 
finding students with 'A's in 2 specific courses.  If the Subject is in fact 
categorical, so that a given student may have several grades (rows) for the 
same Subject, then I think we need more details.  What, exactly, are thee 
requirements?  Do we want students with at least one 'A' in each category? 
Students with all 'A's in each?  Students with an 'A' average in each? 
Something else?


The advantage of the GROUP BY version of the query (if my assumptions are 
correct) is that it generalizes better than the self-join.  For example, to 
find students with an 'A' in 'Bio', 'Maths', 'Phys', and 'Chem', you'd have to 
join the SubjectGrade table 2 more times in the self-join version, which gets 
unwieldy and inefficient.  The GROUP BY version is easy to change, however:


  SELECT StudentID, StudentName
  FROM Student s
  JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
  WHERE sg.Subject IN ('Maths', 'Chem', 'Bio', 'Phys')
AND sg.Grade = 'A';
  GROUP BY s.StudentID
  HAVING COUNT(*) = 4;

You could also find students who have gotten 'A's in exactly 3, or at least 3, 
of those 4 subjects simply by modifying the HAVING clause to COUNT(*)=3, or 
COUNT(*)>=3, respectively.


On the other hand, to find a student with an 'A' in 'Chem' and a 'B' in 
'Phys', for example, I think you'd probably use the self-join version, as you 
need to tie the grade to the subject (though you could tie them with CONCAT in 
the GROUP BY version, I suppose).


Michael




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

Re: Query Help

[Scott Noyes]

> SELECT s.StudentID, StudentName
> FROM Student s
>   JOIN StudentGrade sg1 USING (StudentID)
>   JOIN StudentGrade sg2 USING (StudentID)
> WHERE
>   sg1.Subject = 'Maths' AND sg1.Grade = 'A'
>   AND sg2.Subject = 'Chem' AND sg2.Grade = 'A'

> So i have to write 3 join if I have 3 conditions and so on, right?

Yes, if you use this approach, you'll need another JOIN and another
two conditions in the WHERE clause.

If you use the IN()...HAVING approach, then you just need to add the
new subject to the IN() function and change the HAVING COUNT(*) = 2 to
= 3.  (As was pointed out, this approach won't work if you have two
Maths grades and no Chem grades.)

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

Re: Query Help

[Jason Chan]

So i have to write 3 join if I have 3 conditions and so on, right?

"Scott Noyes" <[EMAIL PROTECTED]> ???
news:[EMAIL PROTECTED] ???...
> Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
> s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject =
'Chem'
> and sg.Grade = 'A'

Take a close look at the WHERE clause: "sg.Subject = 'Maths' and
sg.Subject = 'Chem'" will never return a result - how could the
subject be two different things at the same time?

Here's one way that should work:

SELECT s.StudentID, StudentName
FROM Student s
  JOIN StudentGrade sg1 USING (StudentID)
  JOIN StudentGrade sg2 USING (StudentID)
WHERE
  sg1.Subject = 'Maths' AND sg1.Grade = 'A'
  AND sg2.Subject = 'Chen' AND sg2.Grade = 'A'

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





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

Re: Query Help

[Jason Chan]

I havn't write my schema clearly , (StudentID, Subject) is the key of
SubjectGrade

"Philippe Poelvoorde" <[EMAIL PROTECTED]> ???
news:[EMAIL PROTECTED] ???...

> Alternatively, use an IN list for the subject, then use a HAVING clause
> to limit the results to students with 2 matching rows, like this:
>
>   SELECT StudentID, StudentName
>   FROM Student s
>   JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
>   WHERE sg.Subject IN ('Maths', 'Chem')
> AND sg.Grade = 'A';
>   GROUP BY s.StudentID
>   HAVING COUNT(*) = 2;
>

What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)

-- 
Philippe Poelvoorde
COS Trading Ltd.

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





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

Re: Query Help

[Michael Stassen]

Arno Coetzee wrote:


Jason Chan wrote:


I have a student Table and a SubjectGrade table





I want to find out students who have got A in both Maths and Chem
How the SQL look like?


select s.StudentID , s.StudentName
from Student as s , SubjectGrade as sj
where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 
'Maths' or sj.Subject = 'Chem')


This will work, but you'll get 2 rows per student, one for each subject.

Michael

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

Re: Query Help

[Jason Chan]

Jason Chan wrote:

>I have a student Table and a SubjectGrade table
>
>Create Table Student(
>StudentID INT NOT NULL,
>StudentName VARCHAR(30)
>)
>
>Create Table SubjectGrade(
>StudentID INT NOT NULL,
>Subject VARCHAR(30) NOT NULL,
>Grade CHAR(1)
>)
>
>let's say have following record in SubjectGrade
>
>1MathsA
>1PhysB
>1ChemA
>2MathsA
>2ChemA
>3BioC
>3ChemA
>
>I want to find out students who have got A in both Maths and Chem
>How the SQL look like?
>
>
>
>
>
>
>
>
select s.StudentID , s.StudentName
from Student as s , SubjectGrade as sj
where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject =
'Maths' or sj.Subject = 'Chem')

I think your query will return student 3 as well




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

Re: Query Help

[Philippe Poelvoorde]

Alternatively, use an IN list for the subject, then use a HAVING clause 
to limit the results to students with 2 matching rows, like this:


  SELECT StudentID, StudentName
  FROM Student s
  JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
  WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
  GROUP BY s.StudentID
  HAVING COUNT(*) = 2;



What if the student has 2 result in maths (or 2 in chemistry) ??? ;-)

--
Philippe Poelvoorde
COS Trading Ltd.

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

Re: Query Help

[gerald_clark]

Jason Chan wrote:


I am using mysql 4.0.25 with no subquery support : (

"Jason Chan" <[EMAIL PROTECTED]> ¦b¶l¥ó news:[EMAIL PROTECTED]
¤¤¼¶¼g...
I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA

I want to find out students who have got A in both Maths and Chem
How the SQL look like?


 


SELECT StudentName
FROM Student
INNER JOIN SubjectGrade sg1 ON Student.StudentID=sg1.StudentID
INNER JOIN SubjectGrade sg2 ON sg1.StudentID=sg2.StudentID
WHERE sg1.Subject='Maths' AND sg1.Grade='A'
   AND sg2.Subject='Chem'  AND sg2.Grade='A';


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

Re: Query Help

[Arno Coetzee]

Jason Chan wrote:


I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA

I want to find out students who have got A in both Maths and Chem
How the SQL look like?






 


select s.StudentID , s.StudentName
from Student as s , SubjectGrade as sj
where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 
'Maths' or sj.Subject = 'Chem')


--
Arno Coetzee
Developer
Flash Media Group
Office : 2712 342 7595
Mobile : 2782 693 6180


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

Re: Query Help

[Scott Noyes]

> Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
> s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem'
> and sg.Grade = 'A'

Take a close look at the WHERE clause: "sg.Subject = 'Maths' and
sg.Subject = 'Chem'" will never return a result - how could the
subject be two different things at the same time?

Here's one way that should work:

SELECT s.StudentID, StudentName 
FROM Student s 
  JOIN StudentGrade sg1 USING (StudentID)
  JOIN StudentGrade sg2 USING (StudentID)
WHERE 
  sg1.Subject = 'Maths' AND sg1.Grade = 'A'
  AND sg2.Subject = 'Chen' AND sg2.Grade = 'A'

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

Re: Query Help

[Michael Stassen]

Jason Chan wrote:

I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA

I want to find out students who have got A in both Maths and Chem
How the SQL look like?


Use a self-join on SubjectGrade, requiring an A in Maths on the left and an A 
in Chem on the right, like this:


  SELECT StudentID, StudentName
  FROM Student s
  JOIN SubjectGrade sg1 ON s.StudentID = sg1.StudentID
  JOIN SubjectGrade sg2 ON s.StudentID = sg2.StudentID
  WHERE sg1.Subject = 'Maths' AND sg1.Grade = 'A'
AND sg2.Subject = 'Chem' AND sg2.Grade = 'A';

Alternatively, use an IN list for the subject, then use a HAVING clause to 
limit the results to students with 2 matching rows, like this:


  SELECT StudentID, StudentName
  FROM Student s
  JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
  WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Grade = 'A';
  GROUP BY s.StudentID
  HAVING COUNT(*) = 2;

Change "GROUP BY s.StudentID" to "GROUP BY s.StudentName" if you want the 
results in alphabetical order by name instead of in ID order.


Michael


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

Re: Query Help

[Dan Julson]

Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where 
s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem' 
and sg.Grade = 'A'

The key here is using the aliases for the tables.  The linking of the tables 
comes in the s.StudentID = sg.StudentID statement.


I am using mysql 4.0.25 with no subquery support : (

"Jason Chan" <[EMAIL PROTECTED]> �b�l��
 news:[EMAIL PROTECTED] �g...
I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA

I want to find out students who have got A in both Maths and Chem
How the SQL look like?






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

-- 
Have a great day,

Dan Julson
Application Software Engineer
API Outsourcing
1355 Mendota Heights Rd.
Mendota Heights, MN 55120
651-675-2628 (work)
952-456-2067 (mobile)

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

Re: Query Help

[Jason Chan]

I am using mysql 4.0.25 with no subquery support : (

"Jason Chan" <[EMAIL PROTECTED]> ¦b¶l¥ó news:[EMAIL PROTECTED]
¤¤¼¶¼g...
I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1MathsA
1PhysB
1ChemA
2MathsA
2ChemA
3BioC
3ChemA

I want to find out students who have got A in both Maths and Chem
How the SQL look like?






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





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

Re: Query HELP!

[SGreen]

m i l e s <[EMAIL PROTECTED]> wrote on 08/08/2005 05:05:46 PM:

> Shawn,
> 
> Took me a bit to digest what you were sayin but if I get it the way 
> you splain'd it
> then the following should work:
> 
> ++
> 
> SELECT
> cmc_search_members.PropertyName AS pNAME,
> cmc_search_members.PropertyEmail AS pEMAIL,
> cmc_user_count.user_id,
> MAX(cmc_user_count.date_time) AS pDATE,
> COUNT(cmc_user_count.user_id) AS pHITS
> FROM cmc_search_members
> INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = 
> cmc_user_count.user_id
> GROUP BY pNAME, pEMAIL
> ORDER BY pDATE DESC
> 
> ++
> 
> If I want a NON-CUMULATIVE result, ie:
> 
> pNAME   +  pHITS +  pDATE
> ---
> fillmore+  198   +  08/08/2005
> mannor inn  +  56+  08/08/2005
> seacrest+  23+  08/08/2005
> ---
> fillmore+  102   +  08/07/2005
> mannor inn  +  89+  08/07/2005
> seacrest+  19+  08/07/2005
> 
> etc.
> 
> Then Im assuming the statement above will NOT produce this result ?
> 
> What'd be great is if I could get that in Alphabetical order as 
> well...I tried adding a
> ORDER BY pDATE DESC, pNAME ASC but DESC doesn't help.
> 
> Which is what I was lookin for.
> 
> 
 
If you want your results broken down by dates, then you have to group on 
some sort of date value. Try this:

SELECT
 cmc_search_members.PropertyName AS pNAME,
 cmc_search_members.PropertyEmail AS pEMAIL,
 cmc_user_count.date_time AS pDATE,
 COUNT(cmc_user_count.user_id) AS pHITS
FROM cmc_search_members
INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = 
 cmc_user_count.user_id
GROUP BY PDATE DESC, pNAME, pEMAIL;



For more details: http://dev.mysql.com/doc/mysql/en/select.html

#

If you use GROUP BY, output rows are sorted according to the GROUP BY 
columns as if you had an ORDER BY for the same columns. MySQL has extended 
the GROUP BY clause as of version 3.23.34 so that you can also specify ASC 
and DESC after columns named in the clause:

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC



That query will break down, by date, all of the hits for any pNAME+pEMAIL 
combination. If we are not lucky enough that `cmc_user_count`.`date_time` 
contains only date values but instead it contains dates+times then we need 
to strip the time elements out in order to get just a daily grouping. It 
would look something like this:

SELECT
 cmc_search_members.PropertyName AS pNAME,
 cmc_search_members.PropertyEmail AS pEMAIL,
 DATE(cmc_user_count.date_time) AS pDATE,
 COUNT(cmc_user_count.user_id) AS pHITS
FROM cmc_search_members
INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = 
 cmc_user_count.user_id
GROUP BY PDATE DESC, pNAME, pEMAIL;

(see http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html for 
other options)

Is this whole GROUP BY thing starting to make a little more sense now?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query HELP!

[m i l e s]

Shawn,

Took me a bit to digest what you were sayin but if I get it the way  
you splain'd it

then the following should work:

++

SELECT
   cmc_search_members.PropertyName AS pNAME,
   cmc_search_members.PropertyEmail AS pEMAIL,
   cmc_user_count.user_id,
   MAX(cmc_user_count.date_time) AS pDATE,
   COUNT(cmc_user_count.user_id) AS pHITS
FROM cmc_search_members
INNER JOIN cmc_user_count ON cmc_search_members.Property_ID =  
cmc_user_count.user_id

GROUP BY pNAME, pEMAIL
ORDER BY pDATE DESC

++

If I want a NON-CUMULATIVE result, ie:

pNAME   +  pHITS +  pDATE
---
fillmore+  198   +  08/08/2005
mannor inn  +  56+  08/08/2005
seacrest+  23+  08/08/2005
---
fillmore+  102   +  08/07/2005
mannor inn  +  89+  08/07/2005
seacrest+  19+  08/07/2005

etc.

Then Im assuming the statement above will NOT produce this result ?

What'd be great is if I could get that in Alphabetical order as  
well...I tried adding a

ORDER BY pDATE DESC, pNAME ASC but DESC doesn't help.

Which is what I was lookin for.



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