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

[Hal�sz S�ndor]

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

Well, the latter line is now redundant.

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


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

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

[Daevid Vincent]

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

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

Anyways, pretty standard situation:

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

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

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

I need this data in TWO ways:

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

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

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

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

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

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

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

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

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

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

Thanks,

d


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

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

[Daevid Vincent]

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

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

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

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

EXPLAIN says:

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

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

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


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

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

[Daevid Vincent]

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

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

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

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

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

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

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

Hopefully this helps someone else.

d.

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

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

Query help please

[Tompkins Neil]

Hi all,

I've the following query :

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

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

Cheers
Neil

Fwd: Query help please

[Tompkins Neil]

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

Cheers
Neil

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


Hi all,

I've the following query :

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

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

Cheers
Neil

Query help, please..

[Anders Norrbring]

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


I have this table structure:

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

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


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


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


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



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

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: DELETE query help please?

[Yoge]

This should work
DELETE Item FROM Item,ItemTag WHERE Item.ProductID =ItemTag.ItemID AND 
ItemTag.TagID = '168'


Mark Kelly wrote:

Hi

I want to delete from the 'Item' table all the items identified by the 
folowing query:


SELECT 
Item.ProductID 
FROM 
Item, ItemTag 
WHERE 
ItemTag.TagID = '168' 
AND 
ItemTag.ItemID = Item.ProductID;


but I'm not sure how to go about it. Can anyone help?

Thanks

Mark


  


--
Yoge,
AdventNet, Inc.
925-965-6528
[EMAIL PROTECTED]
site24x7.com


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

Re: DELETE query help please?

[Mark Kelly]

Hi.

On Thursday 05 July 2007 17:35, you wrote:
  I want to delete from the 'Item' table
  all the items identified by the folowing query:

 If you have MySQL 5+, you can do it using a sub-query:

 DELETE FROM
   Item
 WHERE
   ProductID IN (
 SELECT
     Item.ProductID
 FROM
     Item, ItemTag
 WHERE
     ItemTag.TagID = '168'
 AND
     ItemTag.ItemID = Item.ProductID
 );

I'm on 4.1 due to host restrictions, but for both versions the manual page 
on subquery syntax says

Another restriction is that currently you cannot modify a table and select 
from the same table in a subquery. This applies to statements such as 
DELETE, INSERT, REPLACE, UPDATE

which seems to me would stop your suggestion working. Am I misunderstanding 
(perfectly possible, as I'm a long way from expert)?

Thanks for the quick reply anyway,

Mark

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

DELETE query help please?

[Mark Kelly]

Hi

I want to delete from the 'Item' table all the items identified by the 
folowing query:

SELECT 
Item.ProductID 
FROM 
Item, ItemTag 
WHERE 
ItemTag.TagID = '168' 
AND 
ItemTag.ItemID = Item.ProductID;

but I'm not sure how to go about it. Can anyone help?

Thanks

Mark


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

RE: DELETE query help please?

[Chris Boget]

 I want to delete from the 'Item' table 
 all the items identified by the folowing query:

If you have MySQL 5+, you can do it using a sub-query:

DELETE FROM 
  Item 
WHERE 
  ProductID IN (
SELECT 
Item.ProductID
FROM 
Item, ItemTag
WHERE 
ItemTag.TagID = '168' 
AND 
ItemTag.ItemID = Item.ProductID
);

thnx,
Chris


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

Re: DELETE query help please?

[gary]

The following query should work if I understand what you're attempting 
correctly. Use at your own risk though ;)


DELETE FROM Item USING Item, ItemTag WHERE ItemTag.ItemID = 
Item.ProductID AND ItemTag.TagID = '168';


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

Bash script array from MySQL query - HELP Please!!!

[Ben Benson]

 

I'm having problems getting a monitoring script to run.  

 

I've put the troublesome bit in a separate script just to test, and it goes
like this:

 

declare -a HNdeclares the array HN

 

HN=(`echo SELECT url FROM hosts | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to a
new element in the array

 

echo ${#HN} echo's number of elements in array

 

for ((i=0;i${#HN};i++)); do

 

echo ${HN[${i}]}  echo value of each element.

 

done

 

Seems simple enough yeah?! Well if I run echo SELECT url FROM hosts |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.

 

Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by 'url'
it seems to generate 569 elements! 

 

Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!

 

Many thanks in advance,

 

Ben Benson

 

Re: Bash script array from MySQL query - HELP Please!!!

[BJ Swope]

I would look at the 15th URL to see if there are specials in there that are
breaking the hash somehow.

On 5/22/07, Ben Benson [EMAIL PROTECTED] wrote:




I'm having problems getting a monitoring script to run.



I've put the troublesome bit in a separate script just to test, and it
goes
like this:



declare -a HNdeclares the array HN



HN=(`echo SELECT url FROM hosts | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to
a
new element in the array



echo ${#HN} echo's number of elements in array



for ((i=0;i${#HN};i++)); do



echo ${HN[${i}]}  echo value of each element.



done



Seems simple enough yeah?! Well if I run echo SELECT url FROM hosts |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.



Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by
'url'
it seems to generate 569 elements!



Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!



Many thanks in advance,



Ben Benson







--
We are all slave to our own paradigm. -- Joshua Williams

If the letters PhD appear after a person's name, that person will remain
outdoors even after it's started raining. -- Jeff Kay

Problem Query - Help Please

[Paul Maine]

When I execute the following query I get duplicate product_id's as shown
below:

SELECT * FROM product, product_category_xref, category WHERE
product_parent_id=''
AND product.product_id=product_category_xref.product_id
AND category.category_id=product_category_xref.category_id
AND product.product_publish='Y'
AND product.product_special='Y'  ORDER BY product_name DESC\G


Results ( As you can see product_id 4139 occurs twice and I desire the
product_id's to be unique in this query)

I have also included the descriptions of the tables.

I would appreciate someone assisting me with a query that works correctly.

*** 1. row ***
product_id: 4199
 vendor_id: 1
 product_parent_id: 0
   product_sku: ToBeAs
product_s_desc: bComing Soon! Preorder Today!/bbr
A series of essays on the influential thinkers and ideas in modern times.
  product_desc: bComing Soon! Preorder Today!/bbr
iBy R.J. Rushdoony/i. This monumental work is a series of essays on the
influential thinkers and ideas in modern times. The author begins with De
Sade, who self-consciously broke with any Christian basis for morality and
law. Enlightenment thinking began with nature as the only reality, and
Christianity was reduced to one option among many. It was then, in turn,
attacked as anti-democratic and anti-freedom for its dogmatic assertion of
the supernatural. Literary figures such as Shelly, Byron, Whitman, and more
are also examined, for the Enlightenment presented both the intellectual and
the artist as replacement for the theologian and his church. Ideas, such as
the spirit of the age, truth, reason, Romanticism, persona, and Gnosticism
are related to the desire to negate God and Christian ethics. Reading this
book will help you understand the need to avoid the syncretistic blending of
humanistic philosophy with the Christian faith.
pPaperback, 230 pages, and indices.
   product_thumb_image: 62c16392f436313324d9922ecf2f5a30.jpg
product_full_image: d99c1de85355c6bc853102a4d85065b3.jpg
   product_publish: Y
product_weight: 0.
product_weight_uom: pounds
product_length: 0.
 product_width: 0.
product_height: 0.
   product_lwh_uom: inches
   product_url:
  product_in_stock: 0
product_available_date: 0
   product_special: y
   product_discount_id: 0
  ship_code_id: NULL
 cdate: 1057785021
 mdate: 1059273555
  product_name: To Be As God: A Study of Modern Thought Since the
Marquis De Sade
  product_discount_use:
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
product_id: 4199
  product_list: NULL
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
 vendor_id: 1
 category_name: BOOKS
  category_description:
  category_thumb_image: NULL
   category_full_image: NULL
  category_publish: Y
  menu_image_1:
  menu_image_2:
  menu_image_3:
page_image:
 cdate: 1028759226
 mdate: 1028759226
  category_flypage:
   category_browsepage:
*** 2. row ***
product_id: 4139
 vendor_id: 1
 product_parent_id: 0
   product_sku: Victims
product_s_desc: The decline of Americas public education - how and
why.
  product_desc: iBy Samuel L. Blumenfeld./i Americas most
effective critic of public education shows us how Americas public schools
were remade by educators who used curriculum to create citizens suitable for
their own vision of a utopian socialist society. This collection of essays
will show you how and why Americas public education declined. You will see
the educator-engineered decline of reading skills. The author describes the
causes for the decline and the way back to competent education methodologies
that will result in a self-educated, competent, and freedom-loving populace.
pPaperback, 266 pages, and index.
   product_thumb_image: 63ad73b92ddd18d83eb6942914bcf277.jpg
product_full_image: 1440c376576aba8783f183ff145c248b.jpg
   product_publish: Y
product_weight: 0.
product_weight_uom: pounds
product_length: 0.
 product_width: 0.
product_height: 0.
   product_lwh_uom: inches
   product_url:
  product_in_stock: 0
product_available_date: 0
   product_special: y
   product_discount_id: 0
  ship_code_id: NULL
 cdate: 1056405288
 mdate: 1061947639
  product_name: The Victims of Dick and Jane
  product_discount_use:
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
product_id: 4139
  product_list: NULL
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
 vendor_id: 1
 category_name: BOOKS
  category_description:
  category_thumb_image: NULL
   

RE: Problem Query - Help Please

[Jim Smith]

 When I execute the following query I get duplicate
 product_id's as shown
 below:

 SELECT * FROM product, product_category_xref, category WHERE
 product_parent_id=''
 AND product.product_id=product_category_xref.product_id
 AND category.category_id=product_category_xref.category_id
 AND product.product_publish='Y'
 AND product.product_special='Y'  ORDER BY product_name DESC\G


 Results ( As you can see product_id 4139 occurs twice and I desire the
 product_id's to be unique in this query)

 I have also included the descriptions of the tables.

 I would appreciate someone assisting me with a query that
 works correctly.

Product 2139 has two different categories BOOKS and EDUCATION and therefore
appears twice.


   category_id: 7920cfab5c630ca88ceabcfda6b3848d
product_id: 4139
  product_list: NULL
   category_id: 7920cfab5c630ca88ceabcfda6b3848d
 vendor_id: 1
 category_name: BOOKS

   category_id: 4ee8c8513ee84c95c8eb7f24e63d7222
product_id: 4139
  product_list: NULL
   category_id: 4ee8c8513ee84c95c8eb7f24e63d7222
 vendor_id: 1
 category_name: EDUCATION

If you need to show all the categories you will probably need to retrieve
thos separately
 and build a list programatically to give something like

 categories: EDUCATION, BOOKS


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

mySQL query help please

[Peter M. Perchansky]

Greetings:

RE:  mySQL query help please

INSERT INTO Customer_Equipment SELECT Customer.ID, Server_ID, 
Configuration, Equipment_Type, Group_ID, Location, Rack_Location, 
Network_Status_Name, Creator_ID, Primary_IP_Address FROM 
Customer_Equipment, Customer WHERE SUBSTRING(Server_ID, 5,4) = 
Customer.User_ID;

The select statement works perfectly, but when I try to combine the select 
statement with an INSERT INTO statement (I've done this in the past), I get 
the error message:

ERROR 1066: Not unique table/alias: 'Customer_Equipment'

What does this error message mean, and how should I accomplish my insert 
into statement so I can use the results of the select statement above?

Thank you.

Peter M. Perchansky,  President/CEO
Dynamic Net, Inc.
Helping companies do business on the Net
420 Park Road; Suite 201
Wyomissing  PA  19610
Non-Toll Free:  1-610-736-3795
Personal Email: [EMAIL PROTECTED]
Company Email:  [EMAIL PROTECTED]
Web:http://www.dynamicnet.net/
http://www.manageddedicatedservers.com/
http://www.wemanageservers.com/



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mySQL query help please

[Jay Blanchard]

[snip]
INSERT INTO Customer_Equipment SELECT Customer.ID, Server_ID,
Configuration, Equipment_Type, Group_ID, Location, Rack_Location,
Network_Status_Name, Creator_ID, Primary_IP_Address FROM
Customer_Equipment, Customer WHERE SUBSTRING(Server_ID, 5,4) =
Customer.User_ID;

The select statement works perfectly, but when I try to combine the select
statement with an INSERT INTO statement (I've done this in the past), I get
the error message:

ERROR 1066: Not unique table/alias: 'Customer_Equipment'
[/snip]

You cannot INSERT into a table that you have SELECT 'ed from;

INSERT INTO Customer_Equipment SELECT ...
FROM Customer_Equipment, Customer 

HTH!

Jay
sql, mysql, query



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mySQL query help please

[Peter M. Perchansky]

Greetings Jay:

RE:  mySQL query help please

I love rules that don't make sense ;-)

So if the end result is to have the records selected appended to the 
Customer_Equipment table, I have to make a temporary table based on the 
characteristics of the Customer_Equipment table... then insert from that 
temporary table.  Correct?

Thank you.

At 12:29 PM 6/24/2002 -0500, you wrote:
[snip]
INSERT INTO Customer_Equipment SELECT Customer.ID, Server_ID,
Configuration, Equipment_Type, Group_ID, Location, Rack_Location,
Network_Status_Name, Creator_ID, Primary_IP_Address FROM
Customer_Equipment, Customer WHERE SUBSTRING(Server_ID, 5,4) =
Customer.User_ID;

The select statement works perfectly, but when I try to combine the select
statement with an INSERT INTO statement (I've done this in the past), I get
the error message:

 ERROR 1066: Not unique table/alias: 'Customer_Equipment'
[/snip]

You cannot INSERT into a table that you have SELECT 'ed from;

INSERT INTO Customer_Equipment SELECT ...
FROM Customer_Equipment, Customer 


Peter M. Perchansky,  President/CEO
Dynamic Net, Inc.
Helping companies do business on the Net
420 Park Road; Suite 201
Wyomissing  PA  19610
Non-Toll Free:  1-610-736-3795
Personal Email: [EMAIL PROTECTED]
Company Email:  [EMAIL PROTECTED]
Web:http://www.dynamicnet.net/
http://www.manageddedicatedservers.com/
http://www.wemanageservers.com/



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mySQL query help please

[Jay Blanchard]

[snip]
So if the end result is to have the records selected appended to the 
Customer_Equipment table, I have to make a temporary table based on the 
characteristics of the Customer_Equipment table... then insert from that 
temporary table.  Correct?
[/snip]

BINGO! Goofy rules can be frustrating :)

Jay
sql, mysql, query (goofy spam rule)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query help please!

[PinkeshP]

I need help writing query that would give me parent categories of catID from 
categories table. 
For example, if catID=030 then it should give me:
Birthday | Special Birthday | Special Birthday
If catID=028
Birthday | General Birthday | NULL

desc categories
+---+--++
| catID | parentID | catName|
+---+--++
|   001 |  000 | Birthday   |
|   002 |  000 | Get Well   |
|   003 |  000 | Special Occasions  |
|   038 |  029 | 40th   |
|   037 |  029 | 30th   |
|   036 |  029 | 21st   |
|   035 |  029 | 16th   |
|   029 |  001 | Special Birthday   |
|   028 |  001 | General Birthday   |
|   030 |  029 | Inspirational  |
|   045 |  001 | Children's Birthday|
|   046 |  045 | 1st|
+---+--++

Pinkesh

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query help please!

[Todd Williamsen]

Do you have another table with all the birthdates in it?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, March 05, 2002 12:05 PM
To: [EMAIL PROTECTED]
Subject: Query help please!


I need help writing query that would give me parent categories of catID
from categories table. 
For example, if catID=030 then it should give me:
Birthday | Special Birthday | Special Birthday
If catID=028
Birthday | General Birthday | NULL

desc categories
+---+--++
| catID | parentID | catName|
+---+--++
|   001 |  000 | Birthday   |
|   002 |  000 | Get Well   |
|   003 |  000 | Special Occasions  |
|   038 |  029 | 40th   |
|   037 |  029 | 30th   |
|   036 |  029 | 21st   |
|   035 |  029 | 16th   |
|   029 |  001 | Special Birthday   |
|   028 |  001 | General Birthday   |
|   030 |  029 | Inspirational  |
|   045 |  001 | Children's Birthday|
|   046 |  045 | 1st|
+---+--++

Pinkesh

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query help please!

[Todd Williamsen]

One more thing... What are you using to pull the data?  PHP?  Perl?
Etc?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, March 05, 2002 12:05 PM
To: [EMAIL PROTECTED]
Subject: Query help please!


I need help writing query that would give me parent categories of catID
from categories table. 
For example, if catID=030 then it should give me:
Birthday | Special Birthday | Special Birthday
If catID=028
Birthday | General Birthday | NULL

desc categories
+---+--++
| catID | parentID | catName|
+---+--++
|   001 |  000 | Birthday   |
|   002 |  000 | Get Well   |
|   003 |  000 | Special Occasions  |
|   038 |  029 | 40th   |
|   037 |  029 | 30th   |
|   036 |  029 | 21st   |
|   035 |  029 | 16th   |
|   029 |  001 | Special Birthday   |
|   028 |  001 | General Birthday   |
|   030 |  029 | Inspirational  |
|   045 |  001 | Children's Birthday|
|   046 |  045 | 1st|
+---+--++

Pinkesh

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php