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

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

[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

[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

[Larry Martell]

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

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

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

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

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

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

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

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

Re: Query Help

[Richard Reina]

Perfect! Thank you Larry et all.

Have a great weekend.


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

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

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

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

[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

[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

[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

[Rick James]

Might need some type coercion:

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

 -Original Message-
 From: Paul Halliday [mailto:paul.halli...@gmail.com]
 Sent: Tuesday, July 31, 2012 3:27 PM
 To: Haluk Karamete
 Cc: MySQL
 Subject: Re: query help
 
 On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete
 halukkaram...@gmail.com wrote:
  I've got a text field called source_recid. It stores half string half
  number like strings in it.
 
  Example
 
  shop.orders.32442
 
  the syntax is DATABASENAME.TABLENAME.RECID
 
  My goal is to scan this col and find out the biggest RECID ( the
  integer) in it.
 
  So, in a case like this
 
  shop.orders.32442
  shop.orders.82000
  shop.orders.34442
 
  It would be the record whose source_recid  is shop.orders.82000. Why?
  Cause
  82000 happens to be the largest integer.
 
  What SQL statement would get me that record?
 
  One option to this is to create a new column ( the_ids ) and move all
  the integers in it and then run something like this
 
  select source_recid from mytable where source_recid like
 'shop.orders.%'
  order by the_ids DESC LIMIT 1
 
  Is there a way to pull this off without going thru this step?
 
 Would substring work?
 
 SELECT SUBSTRING(recid, 13, column size) AS numbers FROM table ORDER
 BY numbers DESC
 
 
 --
 Paul Halliday
 http://www.pintumbler.org/
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


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

Re: query help

[Vikas Shukla]

Hi,

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

Regards,

Vikas Shukla


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

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

 Would substring work?

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


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

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

Re: query help

[Paul Halliday]

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

 Example

 shop.orders.32442

 the syntax is DATABASENAME.TABLENAME.RECID

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

 So, in a case like this

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

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

 What SQL statement would get me that record?

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

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

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

Would substring work?

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


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

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

Re: 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,,,

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

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

[Singer X.J. Wang]

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

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

 Little help...

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

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

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

 Probably simple - maybe ;-)

 Don

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



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

Re: Query help...

[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

[Claudio Nanni]

Hi Neil,

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

this should work

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

   group_concat(email_address,',') as list_of_used_emails

to the select fields.


Claudio




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

 Hi

 I've the following basic table

 login_id
 email_address
 ip_address

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

 Thanks,
 Neil




-- 
Claudio

RE: Query help

[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

[Tompkins Neil]

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

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


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

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

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

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

 will give you the IP addresses as well.

 Regards,

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

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

RE: Query help

[Jerry Schwartz]

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

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

Regards,

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

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


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

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

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


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

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

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

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

 will give you the IP addresses as well.

 Regards,

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

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











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

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

[Ananda Kumar]

Tompkins,
Which field stores the result of matches.

regards
anandkl

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

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

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

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

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

 Cheers
 Neil

Re: Query help

[Tompkins Neil]

These two fields

home_goals and away_goals

Cheers
Neil


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

 Tompkins,
 Which field stores the result of matches.

 regards
 anandkl

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

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

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

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

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

 Cheers
 Neil

Re: Query help

[Ananda Kumar]

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

regards
anandkl



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

 These two fields

  home_goals and away_goals

 Cheers
 Neil


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

 Tompkins,
 Which field stores the result of matches.

 regards
 anandkl

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

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

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

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

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

 Cheers
 Neil

Re: Query help

[Tompkins Neil]

For sure here is some sample data

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

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

Let me know if you need any more info.

Cheers
Neil



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

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

 regards
 anandkl



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

 These two fields

  home_goals and away_goals

 Cheers
 Neil


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

 Tompkins,
 Which field stores the result of matches.

 regards
 anandkl

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

 Hi,

 I've the following fields within a table :

 fixtures_results_id
 home_teams_id
 away_teams_id
 home_goals
 away_goals
 home_users_id
 away_users_id

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

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

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

 Cheers
 Neil

Re: query help

[Travis Ard]

Something like this might work:

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

where b.domainid is null;

-Travis

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


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



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




Any help would be appreciated.



Thanks

Steve




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

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

[Phillip Baker]

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

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

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

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

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

Blessed Be

Phillip

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


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

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


 Table 1
 Product_id Product_Name

 Table 2
 Category_id, Category_name

 Table 3
 Product_id, Category_id

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

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

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

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

[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

[Richard Reina]

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


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

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

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

RE: query help

[Martin Gainty]

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

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

 

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

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

 

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

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

 

would be interested to know which solution works best 

 

Vielen Danke,
Martin 
__ 
Verzicht und Vertraulichkeitanmerkung

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

 

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

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

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

[Johan De Meersman]

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

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



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

 Hi All,

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

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

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

 A simple example, if the table had values

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

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

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

 Regards



 --
 John Daisley

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

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




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

Re: Query Help!

[Jo�o C�ndido de Souza Neto]

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

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


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

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

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

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

 A simple example, if the table had values

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

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

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

 Regards



 -- 
 John Daisley

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

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



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

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

[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

[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

[DaWiz]

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


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

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


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


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

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


gives me a count of 3.

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


Thanks,

Richard

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




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

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 b...@meltel.com
To: mysql@lists.mysql.com
Sent: Tuesday, February 10, 2009 12:31 PM
Subject: Query Help


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



 I have two tables

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

 Log contains login, host, datetime of last login





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





 





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

RE: Query Help

[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

[ddevaudreuil]

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

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

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

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

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

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

Hope that helps.

Donna


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

RE: Query Help

[Ben Wiechman]

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

The explain for that looks... interesting. 

Thanks

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


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

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

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

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

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

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

Hope that helps.

Donna





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

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

[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

[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]; mysql@lists.mysql.com

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]; mysql@lists.mysql.com
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: mysql@lists.mysql.com; [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 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: mysql@lists.mysql.com; [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: mysql@lists.mysql.com; [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

[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]; mysql@lists.mysql.com
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: mysql@lists.mysql.com; [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 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

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

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

[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

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

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

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

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

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

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

[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

[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

[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

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

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

mysqlSELECT 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

[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())%40 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

[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())%40 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

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

[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

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

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

[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

[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

[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

[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

[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

[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

[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

[Michael Stassen]

Arno Coetzee wrote:


Jason Chan wrote:


I have a student Table and a SubjectGrade table


snip


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]

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

[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

[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

[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

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

[SGreen]

m i l e s [EMAIL PROTECTED] wrote on 08/08/2005 03:10:21 PM:

 
 Hi,
 
 IS the following query counting cumulative (see below **) pHITS or is 
 it counting individual counts for each user for a particular day.  Im 
 not skilled enough to answer this question myself.  My instinct says 
 that its counting cumulative values and NOT individual counts for 
 each property name.
 
 SELECT
 search_members.Property_Name AS pNAME,
 search_members.Property_Email AS pEMAIL,
 MAX(user_count.u_datetime) AS pDATE,
 **COUNT(user_count.u_userid) AS pHITS
 FROM search_members INNER JOIN user_count ON 
 search_members.Property_ID = user_count.u_userid
 GROUP BY pNAME, pEMAIL, search_members.Property_ID
 ORDER BY pDATE DESC
 
 I have a feeling that the COUNT line should be something similar to 
 select distinct statement
 
 Any ideas ?
 
 Sincerely,
 
 M i l e s.
 


Your original query,reformatted for explanatory purposes:

SELECT 
search_members.Property_Name AS pNAME,
search_members.Property_Email AS pEMAIL,
MAX(user_count.u_datetime) AS pDATE,
COUNT(user_count.u_userid) AS pHITS
FROM search_members 
INNER JOIN user_count 
ON search_members.Property_ID = user_count.u_userid
GROUP BY 
pNAME
, pEMAIL
, search_members.Property_ID
ORDER BY pDATE DESC

You wanted to know what this query is calculating, right?

Let's look first at your select terms: terms 1 and 2 are direct field 
values, terms 3 and 4 are the results of aggregate functions.

Now let's look at what you are grouping by:  pNAME, pEMAIL, and 
search_members.Property_id.

if you had done a GROUP BY *only on* pNAME and pEMAIL, you would have seen 
each pair of values appear only once in your output along with the last 
time they did *something* (not sure what it was), the pDate value,  and 
how many times they did it, the pHITS value. However you are also 
computing those statistics _ for each property_ID _. So if the same 
pNAME+pEMAIL pair had performed whatever it was they had to do to generate 
some pDate and pHITS values for more than one Property_ID value, then you 
will see one pNAME+pEMAIL pair listed for EACH PROPERTY ID to which the 
statistics apply.

One way to see this more clearly is to add the Property_ID column into the 
SELECT portion of your query.

SELECT 
search_members.Property_Name AS pNAME,
search_members.Property_Email AS pEMAIL,
search_members.Property_ID,
MAX(user_count.u_datetime) AS pDATE,
COUNT(user_count.u_userid) AS pHITS
FROM search_members 
INNER JOIN user_count 
ON search_members.Property_ID = user_count.u_userid
GROUP BY 
pNAME
, pEMAIL
, search_members.Property_ID
ORDER BY pDATE DESC

Now you should be able to physically see why the pNAME+pEMAIL pairs were 
duplicating in what may have appeared to be randomly and without reason. I 
am not sure if I answered your question but this seemed like the most 
likely issue for confusion.

Is it cumulative? Yes. Is it per day? No. Is it per 
Name+Email+Property_ID? Yes.

HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query HELP!

[Peter Brawley]

Miles,

IS the following query counting cumulative (see below **) pHITS or is  
it counting individual counts for each user for a particular day.  Im  
not skilled enough to answer this question myself.  My instinct says  
that its counting cumulative values and NOT individual counts for  each 
property name.


SELECT
 search_members.Property_Name AS pNAME,
 search_members.Property_Email AS pEMAIL,
 MAX(user_count.u_datetime) AS pDATE,
 COUNT(user_count.u_userid) AS pHITS
FROM search_members
 INNER JOIN user_count ON  search_members.Property_ID = 
user_count.u_userid

GROUP BY pNAME, pEMAIL, search_members.Property_ID
ORDER BY pDATE DESC

Is that query a bit confused, or is it me? If there's just one 
Property_Name and one Property_Email per property_Id in the 
search_members table, there's not a need to GROUP BY pname and pemail, 
and the query seems intended to return the latest user_count.u_datetime 
and the count of non-null values of user_count.u_userids for every 
search_members.property_Id. If there can be multiple names  emails per 
property_ID, the query will break down the counts by name, email then 
property_id, which would seem bizarre :-) .


PB



m i l e s wrote:



Hi,

IS the following query counting cumulative (see below **) pHITS or is  
it counting individual counts for each user for a particular day.  Im  
not skilled enough to answer this question myself.  My instinct says  
that its counting cumulative values and NOT individual counts for  
each property name.


SELECT
search_members.Property_Name AS pNAME,
search_members.Property_Email AS pEMAIL,
MAX(user_count.u_datetime) AS pDATE,
**COUNT(user_count.u_userid) AS pHITS
FROM search_members INNER JOIN user_count ON  
search_members.Property_ID = user_count.u_userid

GROUP BY pNAME, pEMAIL, search_members.Property_ID
ORDER BY pDATE DESC

I have a feeling that the COUNT line should be something similar to  
select distinct statement


Any ideas ?

Sincerely,

M i l e s.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005


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