Re: Need Help Writing Simple Query

[Mark Phillips]

On Sun, Jul 25, 2010 at 8:49 PM, Nguyen Manh Cuong 
cuong.m...@vienthongso.com wrote:

 Hi Mark,
 Please test this query:
 select test1.*, (select name from test2 where test2.id=test1.`v_id` limit
 1) as name_1,
 (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2
 from test1;

 - test1 table:
 col1v_idh_id
 America 1   2

 - test2 table:
 id  name
 2   SAM
 1   UNCLE

 - Original Message -
 From: Mark Phillips m...@phillipsmarketing.biz
 To: Mysql List mysql@lists.mysql.com
 Sent: Monday, July 26, 2010 8:29:00 AM
 Subject: Need Help Writing Simple Query

 I have been away from sql for awhile, and can't seem to figure out how to
 write a simple query for two tables.

 Table 1 has many columns, two of which are hID and vID. Table 2 has two
 columns, ID and name. The hID and vID in table 1 correspond to the IDs in
 table 2. I want to make a query so I get all the columns from table 1, but
 substitute the names from table 2 for the hID and vID values. For example,

 Table 1:
 col 1, col 2, hID, vID, col 3
 AB1 2 C

 Table 2:
 ID, name
 1fred
 2sam

 Query result:
 col1, col 2, hName, vName, col 3
 A   Bfred  sam   C

 Thanks!

 Mark

 --
 Best Regards,
 Cuongmc.

 --
 Nguyen Manh Cuong
 Phong Ky Thuat - Cong ty Vien Thong So - VTC
 Dien thoai: 0912051542
 Gmail : philipscu...@gmail.com
 YahooMail : philipscu...@yahoo.com


Thanks! That did the trick.

Mark

Need Help Writing Simple Query

[Mark Phillips]

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C

Thanks!

Mark

Re: Need Help Writing Simple Query

[Nguyen Manh Cuong]

Hi Mark,
Please test this query:
select test1.*, (select name from test2 where test2.id=test1.`v_id` limit 1) as 
name_1, 
(select name from test2 where test2.id=test1.`h_id` limit 1) as name_2 
from test1;

- test1 table:
col1v_idh_id
America 1   2

- test2 table: 
id  name
2   SAM
1   UNCLE

- Original Message -
From: Mark Phillips m...@phillipsmarketing.biz
To: Mysql List mysql@lists.mysql.com
Sent: Monday, July 26, 2010 8:29:00 AM
Subject: Need Help Writing Simple Query

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C

Thanks!

Mark

-- 
Best Regards,
Cuongmc.

-- 
Nguyen Manh Cuong
Phong Ky Thuat - Cong ty Vien Thong So - VTC
Dien thoai: 0912051542
Gmail : philipscu...@gmail.com
YahooMail : philipscu...@yahoo.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: Need Help Writing Simple Query

[John List]

On 07/25/2010 09:29 PM, Mark Phillips wrote:

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C
  


select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1,
Table2 as hTable,
Table2 as vTable
where hId = hTable.ID and vID = vTable.ID

or

select a, b, c,
hTable.name as hName,
vTable.name as vName
from Table1
left join Table2 as hTable on hID = hTable.ID
left join Table2 as vTable on vID = vTable.ID




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

need help with delete query

[Ben Liu]

I'm trying to delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run a series
of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant tables:
order_items (individual items in a particular order)
 item_id
 ord_id

orders (order details such as person's name, address, phone number, etc)
 ord_id
 cust_id

I have set all shopping carts or initiated/incomplete orders so that
orders.cust_id=-1

I wish to clear all shopping carts so I need to delete two sets of items:
1) All records in order_items where order_items.ord_id=orders.ord_id and
orders.cust_id=-1
2) All records in orders where orders.cust_id=-1

I can do #2 easily enough, but I can't figure out how to write the delete
query for #1.

I've tried:

DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND
orders.cust_id=-1

DELETE FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_idWHERE
orders.cust_id=-1


Neither of them seem to work.

Thanks for any help.

~Ben

Re: need help with delete query

[Ben Liu]

Thanks Brent, good tip. Works like a charm.

On Jun 14, 2007, at 7:42 PM, Brent Baisley wrote:

Here's a little trick. Get your DELETE query working as a SELECT.  
Then replace everything before FROM with DELETE tablename.


SELECT order_items.ord_id FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_id WHERE
orders.cust_id=-1

...becomes...

DELETE order_items FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_id WHERE
orders.cust_id=-1

You may have to tweak it a little, but the DELETE tablename FROM  
option is something many people miss. Although I'm pretty sure it's  
covered in the manual.



On 6/14/07, Ben Liu [EMAIL PROTECTED] wrote: I'm trying to  
delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run  
a series

of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant tables:
order_items (individual items in a particular order)
  item_id
  ord_id

orders (order details such as person's name, address, phone number,  
etc)

  ord_id
  cust_id

I have set all shopping carts or initiated/incomplete orders so that
orders.cust_id=-1

I wish to clear all shopping carts so I need to delete two sets  
of items:
1) All records in order_items where  
order_items.ord_id=orders.ord_id and

orders.cust_id=-1
2) All records in orders where orders.cust_id=-1

I can do #2 easily enough, but I can't figure out how to write the  
delete

query for #1.

I've tried:

DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND
orders.cust_id=-1

DELETE FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_idWHERE
orders.cust_id=-1


Neither of them seem to work.

Thanks for any help.

~Ben




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

Re: need help with delete query

[Reinhardt Christiansen]

- Original Message - 
From: Ben Liu [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Thursday, June 14, 2007 3:11 PM
Subject: need help with delete query



I'm trying to delete a subset of items in a table. The problem is, I don't
want to query for the subset first, store the results and then run a 
series

of delete queries. I believe this is a rather basic issue that has a
well-accepted, simple solution which I am ignorant of. There are two
relevant tables:
order_items (individual items in a particular order)
 item_id
 ord_id

orders (order details such as person's name, address, phone number, etc)
 ord_id
 cust_id

I have set all shopping carts or initiated/incomplete orders so that
orders.cust_id=-1

I wish to clear all shopping carts so I need to delete two sets of 
items:

1) All records in order_items where order_items.ord_id=orders.ord_id and
orders.cust_id=-1
2) All records in orders where orders.cust_id=-1

I can do #2 easily enough, but I can't figure out how to write the delete
query for #1.

I've tried:

DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND
orders.cust_id=-1

DELETE FROM order_items LEFT JOIN orders ON
order_items.ord_id=orders.ord_idWHERE
orders.cust_id=-1


Neither of them seem to work.

Thanks for any help.


First of all, let me explain that I have been away from database work for 
about a year now and I have not kept up with developments in MySQL. However, 
I have been working with databases for many years, including many years with 
DB2 and a fair bit of work with MySQL until last summer. So, based on that 
experience, I'm going to offer you my opinions for what they're worth.


First of all, the first DELETE that you cited should work. I don't see any 
reason why it wouldn't except possibly that there are no rows that satisfy 
the query. It should be easy to determine if there are any qualifying rows: 
simply turn the query into a SELECT and see if any rows satsify the query. 
Run:


SELECT * FROM order_items
WHERE order_items.ord_id=orders.ord_id
AND orders.cust_id=-1

If you get no rows from that, then that's why your query failed. In that 
case, investigate the UDPATE queries that were supposed to be setting the 
cust_id to -1 and see what's wrong with them.


As for the second DELETE, I am dubious that this could ever work but I'm not 
100% certain. That query LOOKS like a join and, in DB2 at least, you can 
never delete rows from a join. Now, MySQL may tolerate that syntax and not 
interpret it as a join so you may want to confirm this with someone familiar 
with the version of MySQL you are using.


So, in a nutshell, the first DELETE should work fine but you may not have 
the data there that will allow it to do anything.


--
Rhino 



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

Need help with a query

[Mark Phillips]

I am running mysql 4.0.24 on Debian sarge.

I have a table with two columns, team and division, both varchar(255). 
There are some errors in the table where division has a value but team is 
blank. Given that I am getting new data, and the data entry folks may create 
a record with a blank division and a team, I thought I would avoid any issues 
with team or division being blank as follows:

SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!=''

That returns what I expected - all division-team fields have data

Then I decided to order the output, so I added an ORDER BY clause and some 
parenthesis to make the sql more readable:

SELECT DISTINCT division, team FROM registered WHERE (team!='' OR 
DIVISION!='') ORDER BY division, team

But, I still get records with a blank team field (even if I remove the 
parenthesis). I finally found a solution, but I do not understand it:

SELECT DISTINCT division, team FROM registered WHERE (team!='' AND 
DIVISION!='') ORDER BY division, team

Why does the ORDER BY clause require an AND in the WHERE clause to work 
correctly?? I do not understand the logic.

Thanks for any insight you can share with me.

-- 
Mark Phillips
Phillips Marketing, Inc
[EMAIL PROTECTED]
602 524-0376
480 945-9197 fax

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

Re: Need help with a query

[Michael Stassen]

Mark Phillips wrote:

I am running mysql 4.0.24 on Debian sarge.

I have a table with two columns, team and division, both varchar(255). 
There are some errors in the table where division has a value but team is 
blank. Given that I am getting new data, and the data entry folks may create 
a record with a blank division and a team, I thought I would avoid any issues 
with team or division being blank as follows:


You should change your app to enforce your rules.  That is, your app should 
prevent your data entry folks from entering incomplete records.  Otherwise, it's 
garbage in, garbage out.



SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!=''

That returns what I expected - all division-team fields have data


I doubt it.  You've joined your two conditions with OR, so your WHERE 
condition will be true for any row with at least one of the two conditions met. 
 Only a row with *both* fields blank would be excluded.  Remember,


  NOT(A OR B) = NOT(A) AND NOT(B)

so you should have used AND.  You see?  A row you don't want has

  team = '' OR DIVISION = ''

so a row you do want has

  NOT(team = '' OR DIVISION = '')

which is equivalent to

  team != '' AND DIVISION != ''

Then I decided to order the output, so I added an ORDER BY clause and some 
parentheses to make the sql more readable:


SELECT DISTINCT division, team FROM registered WHERE (team!='' OR 
DIVISION!='') ORDER BY division, team


The parentheses are irrelevant.  The ORDER BY cannot have changed which rows 
were returned.  Perhaps the ordering facilitated noticing the unwanted results.


But, I still get records with a blank team field (even if I remove the 
parenthesis). I finally found a solution, but I do not understand it:


SELECT DISTINCT division, team FROM registered WHERE (team!='' AND 
DIVISION!='') ORDER BY division, team


As I explained above.

Why does the ORDER BY clause require an AND in the WHERE clause to work 
correctly?? I do not understand the logic.


ORDER BY has nothing to do with it.


Thanks for any insight you can share with me.


Michael


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

Re: Need help with a query

[Mark Phillips]

On Monday 23 January 2006 03:33 pm, Michael Stassen wrote:
 Mark Phillips wrote:
  I am running mysql 4.0.24 on Debian sarge.
  
  I have a table with two columns, team and division, both varchar(255). 
  There are some errors in the table where division has a value but team is 
  blank. Given that I am getting new data, and the data entry folks may 
create 
  a record with a blank division and a team, I thought I would avoid any 
issues 
  with team or division being blank as follows:
 
 You should change your app to enforce your rules.  That is, your app should 
 prevent your data entry folks from entering incomplete records.  Otherwise, 
it's 
 garbage in, garbage out.

You are absolutely correct. However, it is not my app nor do I control how the 
data is input into it. I just get a dump of the data to work with. :-(
 
  SELECT DISTINCT division, team FROM registered WHERE team!='' OR 
DIVISION!=''
  
  That returns what I expected - all division-team fields have data
 
 I doubt it.  You've joined your two conditions with OR, so your WHERE 
 condition will be true for any row with at least one of the two conditions 
met. 
   Only a row with *both* fields blank would be excluded.  Remember,
 
NOT(A OR B) = NOT(A) AND NOT(B)
 
 so you should have used AND.  You see?  A row you don't want has
 
team = '' OR DIVISION = ''
 
 so a row you do want has
 
NOT(team = '' OR DIVISION = '')
 
 which is equivalent to
 
team != '' AND DIVISION != ''
 
I feel so stupid. Not sure why I missed that - it is so basic! 

Well, that is my first Home Simpson of the week Doh!

Thanks !

  Then I decided to order the output, so I added an ORDER BY clause and some 
  parentheses to make the sql more readable:
  
  SELECT DISTINCT division, team FROM registered WHERE (team!='' OR 
  DIVISION!='') ORDER BY division, team
 
 The parentheses are irrelevant.  The ORDER BY cannot have changed which rows 
 were returned.  Perhaps the ordering facilitated noticing the unwanted 
results.
 
  But, I still get records with a blank team field (even if I remove the 
  parenthesis). I finally found a solution, but I do not understand it:
  
  SELECT DISTINCT division, team FROM registered WHERE (team!='' AND 
  DIVISION!='') ORDER BY division, team
 
 As I explained above.
 
  Why does the ORDER BY clause require an AND in the WHERE clause to work 
  correctly?? I do not understand the logic.
 
 ORDER BY has nothing to do with it.
 
  Thanks for any insight you can share with me.
 
 Michael
 
 

-- 
Mark Phillips
Phillips Marketing, Inc
[EMAIL PROTECTED]
602 524-0376
480 945-9197 fax

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

Re: Need Help with a query

[Gleb Paharenko]

Hello.



You may use these queries:



select flight_id

,baseline*tan(radians(angle)) as attitude

from flights

where (baseline*tan(radians(angle))) =

( select max(baseline*tan(radians(angle)))

from flights  f2);



+---+-+

| flight_id | attitude|

+---+-+

| 2 | 119.17535925942 |

+---+-+



mysql select flight_id, baseline*tan(radians(angle)) as attitude from

flights where (baseline*tan(radians(angle)))= (select

min(baseline*tan(radians(angle))) from flights  f2);

+---+-+

| flight_id | attitude|

+---+-+

| 8 | 72.426658110531 |

+---+-+



However, you should be aware about rounding errors and possibly 

different results in 5.0 and older versions. See:

  http://dev.mysql.com/doc/refman/5.0/en/precision-math.html









Mark Phillips [EMAIL PROTECTED] wrote:

 I have a table with several columns. The ones of interest are flight_id, 

 angle, and baseline. I want to find the flight_ids for the flights with the 

 maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)).

 

 For example, 

 Flights

 +++---+

 | flight_id | angle| baseline  |

 +++---+

 |   1 | 37.0 | 100.0 |

 |   2 | 50.0 | 100.0 |

 |   3 | 48.0 | 100.0 |

 |   4 | 40.0 | 100.0 |

 |   5 | 44.0 | 100.0 |

 |   6 | 40.0 | 100.0 |

 |   7 | 45.0 | 100.0 |

 |   8 | 44.0 |  75.0 |

 |   9 | 57.8 |  75.0 |

 +++---+

 

 The result I am looking for are:

 

 Maximum altitude:

 +++

 | flight_id | altitude |

 +++

 |   2 | 119.17536 | 

 |   9 | 119.17536| 

 +++

 

 Minimum altitude:

 +---+---+

 | flight_id | altitudeM |

 +---+---+

 | 8 |  72.42666 |

 +---+---+

 

 Thanks for any help you can provide!

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Need Help with a query

[Mark Phillips]

I have a table with several columns. The ones of interest are flight_id, 
angle, and baseline. I want to find the flight_ids for the flights with the 
maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)).

For example, 
Flights
+++---+
| flight_id | angle| baseline  |
+++---+
|   1 | 37.0 | 100.0 |
|   2 | 50.0 | 100.0 |
|   3 | 48.0 | 100.0 |
|   4 | 40.0 | 100.0 |
|   5 | 44.0 | 100.0 |
|   6 | 40.0 | 100.0 |
|   7 | 45.0 | 100.0 |
|   8 | 44.0 |  75.0 |
|   9 | 57.8 |  75.0 |
+++---+

The result I am looking for are:

Maximum altitude:
+++
| flight_id | altitude |
+++
|   2 | 119.17536 | 
|   9 | 119.17536| 
+++

Minimum altitude:
+---+---+
| flight_id | altitudeM |
+---+---+
| 8 |  72.42666 |
+---+---+

Thanks for any help you can provide!

-- 
Mark Phillips
Phillips Marketing, Inc
[EMAIL PROTECTED]
602 524-0376
480 945-9197 fax

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

Fw: Need Help with a query

[Rhino]

Oops, I meant to copy the list on this reply so that others could 
benefit


Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: Mark Phillips [EMAIL PROTECTED]
Sent: Sunday, December 11, 2005 6:19 PM
Subject: Re: Need Help with a query




- Original Message - 
From: Mark Phillips [EMAIL PROTECTED]

To: MYSQL List mysql@lists.mysql.com
Sent: Sunday, December 11, 2005 12:38 PM
Subject: Need Help with a query



I have a table with several columns. The ones of interest are flight_id,
angle, and baseline. I want to find the flight_ids for the flights with 
the
maximum and minimum altitudes, where 
altitude=baseline*tan(radians(angle)).


For example,
Flights
+++---+
| flight_id | angle| baseline  |
+++---+
|   1 | 37.0 | 100.0 |
|   2 | 50.0 | 100.0 |
|   3 | 48.0 | 100.0 |
|   4 | 40.0 | 100.0 |
|   5 | 44.0 | 100.0 |
|   6 | 40.0 | 100.0 |
|   7 | 45.0 | 100.0 |
|   8 | 44.0 |  75.0 |
|   9 | 57.8 |  75.0 |
+++---+

The result I am looking for are:

Maximum altitude:
+++
| flight_id | altitude |
+++
|   2 | 119.17536 |
|   9 | 119.17536|
+++

Minimum altitude:
+---+---+
| flight_id | altitudeM |
+---+---+
| 8 |  72.42666 |
+---+---+

Thanks for any help you can provide!

I do wish posters to this list would get in the habit of volunteering 
which version of MySQL they are using, particularly for SQL questions!


The answer to almost every SQL question is it depends on which version of 
MySQL you are using. It's very tedious to give the answer for every 
version MySQL, as in: If you're using Version 3.x, the answer is A. If 
you're using Version 4.0.x the answer is B. If you're using Version 4.1.x, 
the answer is C. etc.


[By the way, I don't mean to single you out with this mini-rant; it's just 
a general observation.]


Therefore, I'm going to assume you are using Version 4.1 or higher; in 
other words, you use a version which supports subqueries. If you are on an 
earlier version, please reply to the list and explain which version you 
are on. Perhaps someone will be willing to show you alternatives that will 
work for you.


I should also explain that I am _not_ on a version of MySQL which supports 
subqueries. However, my main database is DB2 which does support subqueries 
and the SQL used by DB2 and MySQL is very very similar so this _untested_ 
answer should be pretty close to what you need.


I think the best answer to your question is to use subqueries. I'm going 
to express the answer in pseudocode first to give you a general sense of 
the answer, then give you something that should be pretty close to a final 
answer that will work on your system.


Pseudocode (for maximum altitude):

select flight_id, baseline*tan(radians(angle)) as max_altitude
from Flights
where baseline*tan(radians(angle)) in (subquery that gets largest altitude 
from table)


In real SQL, that should end up looking like this:

select flight_id, baseline*tan(radians(angle)) as max_altitude
from Flights
where baseline*tan(radians(angle)) in (select 
max(baseline*tan(radians(angle))) from Flights)


To get the query for the minimum altitude, use the exact same query except 
replace the max function with the min function in the subquery and change 
the 'as' for the outer query from 'max_altitude' to 'min_altitude'.


For what it's worth, I got slightly different numbers in DB2 so I did not 
have a tie for maximum altitude: my maximum altitude was for flight 2 at 
119.17535 (versus 119.09797 for flight 9). Perhaps the tan() or radians() 
functions in DB2 are slightly more precise?


The 'in' that introduces the subqueries could potentially be replaced by 
'=' but 'in' is safer. If you use 'in' and there is more than one flight 
tied for the highest altitude, the query works fine. If you use '=' and 
there is more than one flight tied for the highest altitude, the query 
will almost certainly fail - it does in DB2! - because '=' implies that 
only one row in the outer query can have the maximum altitude; therefore 
the query fails if more than one row matches.


The most tedious part of these queries is typing the 
'baseline*tan(radians(angle))' expression. You might expect that there 
would be some shortcut that would enable you to type the expression only 
once and then make repeated references to it. Sometimes that is possible 
but without a suitable version of MySQL handy, I don't want to propose any 
possible ways to do that since I can't be sure they'll work. Perhaps 
people with newer versions of MySQL can suggest shortcuts that reduce the 
amount of typing.


Rhino

Fwd: Re: Need Help with a query

[Mark Phillips]

I forgot to copy the list as well

Mark

--  Forwarded Message  --

Subject: Re: Need Help with a query
Date: Sunday 11 December 2005 06:47 pm
From: Mark Phillips [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]

Rhino,

My apologies for leaving out the version of mysql. I agree 1000% with your
rant - it was an oversight on my part.  I have mysql 4.0.24 on my development
machine and 4.1.11 on my production machine.

Thanks for the query - it works very well in 4.1.11. I think in 4.0.24 I need
to use a temporary table.

Thanks again!

Mark

On Sunday 11 December 2005 04:19 pm, you wrote:
 - Original Message -
 From: Mark Phillips [EMAIL PROTECTED]
 To: MYSQL List mysql@lists.mysql.com
 Sent: Sunday, December 11, 2005 12:38 PM
 Subject: Need Help with a query

 I have a table with several columns. The ones of interest are flight_id,
  angle, and baseline. I want to find the flight_ids for the flights with
  the
  maximum and minimum altitudes, where
  altitude=baseline*tan(radians(angle)).
 
  For example,
  Flights
  +++---+
 
  | flight_id | angle| baseline  |
 
  +++---+
 
  |   1 | 37.0 | 100.0 |
  |   2 | 50.0 | 100.0 |
  |   3 | 48.0 | 100.0 |
  |   4 | 40.0 | 100.0 |
  |   5 | 44.0 | 100.0 |
  |   6 | 40.0 | 100.0 |
  |   7 | 45.0 | 100.0 |
  |   8 | 44.0 |  75.0 |
  |   9 | 57.8 |  75.0 |
 
  +++---+
 
  The result I am looking for are:
 
  Maximum altitude:
  +++
 
  | flight_id | altitude |
 
  +++
 
  |   2 | 119.17536 |
  |   9 | 119.17536|
 
  +++
 
  Minimum altitude:
  +---+---+
 
  | flight_id | altitudeM |
 
  +---+---+
 
  | 8 |  72.42666 |
 
  +---+---+
 
  Thanks for any help you can provide!

 I do wish posters to this list would get in the habit of volunteering which
 version of MySQL they are using, particularly for SQL questions!

 The answer to almost every SQL question is it depends on which version of
 MySQL you are using. It's very tedious to give the answer for every
 version MySQL, as in: If you're using Version 3.x, the answer is A. If
 you're using Version 4.0.x the answer is B. If you're using Version 4.1.x,
 the answer is C. etc.

 [By the way, I don't mean to single you out with this mini-rant; it's just
 a general observation.]

 Therefore, I'm going to assume you are using Version 4.1 or higher; in
 other words, you use a version which supports subqueries. If you are on an
 earlier version, please reply to the list and explain which version you are
 on. Perhaps someone will be willing to show you alternatives that will work
 for you.

 I should also explain that I am _not_ on a version of MySQL which supports
 subqueries. However, my main database is DB2 which does support subqueries
 and the SQL used by DB2 and MySQL is very very similar so this _untested_
 answer should be pretty close to what you need.

 I think the best answer to your question is to use subqueries. I'm going to
 express the answer in pseudocode first to give you a general sense of the
 answer, then give you something that should be pretty close to a final
 answer that will work on your system.

 Pseudocode (for maximum altitude):

 select flight_id, baseline*tan(radians(angle)) as max_altitude
 from Flights
 where baseline*tan(radians(angle)) in (subquery that gets largest altitude
 from table)

 In real SQL, that should end up looking like this:

 select flight_id, baseline*tan(radians(angle)) as max_altitude
 from Flights
 where baseline*tan(radians(angle)) in (select
 max(baseline*tan(radians(angle))) from Flights)

 To get the query for the minimum altitude, use the exact same query except
 replace the max function with the min function in the subquery and change
 the 'as' for the outer query from 'max_altitude' to 'min_altitude'.

 For what it's worth, I got slightly different numbers in DB2 so I did not
 have a tie for maximum altitude: my maximum altitude was for flight 2 at
 119.17535 (versus 119.09797 for flight 9). Perhaps the tan() or radians()
 functions in DB2 are slightly more precise?

 The 'in' that introduces the subqueries could potentially be replaced by
 '=' but 'in' is safer. If you use 'in' and there is more than one flight
 tied for the highest altitude, the query works fine. If you use '=' and
 there is more than one flight tied for the highest altitude, the query will
 almost certainly fail - it does in DB2! - because '=' implies that only one
 row in the outer query can have the maximum altitude; therefore the query
 fails if more than one row matches.

 The most tedious part of these queries is typing the
 'baseline*tan

Need help with a query..

[Cory Robin]

I need to speed up a search, big time.

I have an application that searches for records on a date field.  If it
doesn't find an exact date match, it keeps searching adjacent days until it
finds a certain amount of records.

The problem now is, I'm using my application to loop through and run
multiple queries and it's dog ass slow..I'm hoping that one of you SQL
gurus can point me in the right direction to create a query that will work
it out for me.  Here's the logic the best I can explain..

I want to return a minimum of 15 records..  I'm searching for records on or
around 2005-10-01

Select * from table_x where row_date = '2005-10-01'
/* at this point if matched records are = 15 then simply return the records
on that date..  If not..*/
Select * from table_x where row_date = '2005-09-31'

Select * from table_x where row_date = '2005-10-02'

And so on until it finds = 15 records or it searches through 5 days (+- 3
on search date)

I hope this makes sense..  I'm new to all this stuff.

Eventually I'm going to do the same thing for times as well..

Thanks in advance for any help!

Cory Robin
SkyVantage



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

Re: Need help with a query..

[Alec . Cawley]

Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:

 I need to speed up a search, big time.
 
 I have an application that searches for records on a date field.  If it
 doesn't find an exact date match, it keeps searching adjacent days until 
it
 finds a certain amount of records.
 
 The problem now is, I'm using my application to loop through and run
 multiple queries and it's dog ass slow..I'm hoping that one of you 
SQL
 gurus can point me in the right direction to create a query that will 
work
 it out for me.  Here's the logic the best I can explain..
 
 I want to return a minimum of 15 records..  I'm searching for records on 
or
 around 2005-10-01
 
 Select * from table_x where row_date = '2005-10-01'
 /* at this point if matched records are = 15 then simply return the 
records
 on that date..  If not..*/
 Select * from table_x where row_date = '2005-09-31'
 
 Select * from table_x where row_date = '2005-10-02'
 
 And so on until it finds = 15 records or it searches through 5 days (+- 
3
 on search date)
 
 I hope this makes sense..  I'm new to all this stuff.
 
 Eventually I'm going to do the same thing for times as well..

Heres a suggestion:

select * from table_x 
where  row_date between date_sub(now(), interval 3 day) AND 
date_add(now(), interval 3 day) 
order by abs(time_to_sec(datediff(created, now( 
limit 15 ;

This does times relative to now(), but I am sure you can generalise it.
The first line specifies the desired fields
The second selects (in principle) all the records within your largest 
target window
The third orders them by closeness to your target time
and the last says you only want 15 of them.

This version is based on exact seconds from the target time (now() in my 
case): the version which works in whole days would only be slightly 
different.

Alec


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

Re: Need help with a query..

[SGreen]

[EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM:

 Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:

  I need to speed up a search, big time.
 
  I have an application that searches for records on a date field.  If 
it
  doesn't find an exact date match, it keeps searching adjacent days 
until
 it
  finds a certain amount of records.
 
  The problem now is, I'm using my application to loop through and run
  multiple queries and it's dog ass slow..I'm hoping that one of you
 SQL
  gurus can point me in the right direction to create a query that will
 work
  it out for me.  Here's the logic the best I can explain..
 
  I want to return a minimum of 15 records..  I'm searching for records 
on
 or
  around 2005-10-01
 
  Select * from table_x where row_date = '2005-10-01'
  /* at this point if matched records are = 15 then simply return the
 records
  on that date..  If not..*/
  Select * from table_x where row_date = '2005-09-31'
 
  Select * from table_x where row_date = '2005-10-02'
 
  And so on until it finds = 15 records or it searches through 5 days 
(+-
 3
  on search date)
 
  I hope this makes sense..  I'm new to all this stuff.
 
  Eventually I'm going to do the same thing for times as well..

 Heres a suggestion:

 select * from table_x
 where  row_date between date_sub(now(), interval 3 day) AND
 date_add(now(), interval 3 day)
 order by abs(time_to_sec(datediff(created, now(
 limit 15 ;

 This does times relative to now(), but I am sure you can generalise it.
 The first line specifies the desired fields
 The second selects (in principle) all the records within your largest
 target window
 The third orders them by closeness to your target time
 and the last says you only want 15 of them.

 This version is based on exact seconds from the target time (now() in my
 case): the version which works in whole days would only be slightly
 different.

 Alec

Only one problem with your solution, LIMIT tells how many records AT MOST 
to return, he wants to get 15 AT LEAST and stop appending records once he 
gets over 15 total results.

I can't seem to make a query (in reply to his problem) to return AT LEAST 
15 rows without some sort of iteration or flow control involved in the 
process. Neither on of which is available in MySQL SQL until 5.0+. Since 
he didn't say which version he is using I am assuming a target version of 
4.1 or less for the solution. Does anyone else have a non-scripted 
solution?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Need help with a query..

[Alec . Cawley]

[EMAIL PROTECTED] wrote on 16/06/2005 16:29:46:

 
 
 [EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM:
 
  Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:
 
   I need to speed up a search, big time.
  
   I have an application that searches for records on a date field.  If 
it
   doesn't find an exact date match, it keeps searching adjacent days 
until
  it
   finds a certain amount of records.
  
   The problem now is, I'm using my application to loop through and run
   multiple queries and it's dog ass slow..I'm hoping that one of 
you
  SQL
   gurus can point me in the right direction to create a query that 
will
  work
   it out for me.  Here's the logic the best I can explain..
  
   I want to return a minimum of 15 records..  I'm searching for 
records on
  or
   around 2005-10-01
  
   Select * from table_x where row_date = '2005-10-01'
   /* at this point if matched records are = 15 then simply return the
  records
   on that date..  If not..*/
   Select * from table_x where row_date = '2005-09-31'
  
   Select * from table_x where row_date = '2005-10-02'
  
   And so on until it finds = 15 records or it searches through 5 days 
(+-
  3
   on search date)
  
   I hope this makes sense..  I'm new to all this stuff.
  
   Eventually I'm going to do the same thing for times as well..
 
  Heres a suggestion:
 
  select * from table_x
  where  row_date between date_sub(now(), interval 3 day) AND
  date_add(now(), interval 3 day)
  order by abs(time_to_sec(datediff(created, now(
  limit 15 ;
 
  This does times relative to now(), but I am sure you can generalise 
it.
  The first line specifies the desired fields
  The second selects (in principle) all the records within your largest
  target window
  The third orders them by closeness to your target time
  and the last says you only want 15 of them.
 
  This version is based on exact seconds from the target time (now() in 
my
  case): the version which works in whole days would only be slightly
  different.
 
  Alec
 
 Only one problem with your solution, LIMIT tells how many records AT
 MOST to return, he wants to get 15 AT LEAST and stop appending 
 records once he gets over 15 total results. 
 
 I can't seem to make a query (in reply to his problem) to return AT 
 LEAST 15 rows without some sort of iteration or flow control 
 involved in the process. Neither on of which is available in MySQL 
 SQL until 5.0+. Since he didn't say which version he is using I am 
 assuming a target version of 4.1 or less for the solution. Does 
 anyone else have a non-scripted solution? 

That wasn't the 
way I read it And so on until it finds = 15 records or it searches 
through 5 days - within I interpret as wanting all the records within 5 
days up to a limit of 15. I presume that if 15 records are found, those 
closes to the target time are preferred.

Alec

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

Fw: Need help with a query..

[SGreen]

(forwarded to the list - I am not the OP)
- Forwarded by Shawn Green/Unimin on 06/16/2005 11:45 AM -

James Black [EMAIL PROTECTED] wrote on 06/16/2005 11:44:36 AM:

 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 [EMAIL PROTECTED] wrote:
  [EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM:
 
 
 Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22:
 
 
 I need to speed up a search, big time.
 
 I have an application that searches for records on a date field.  If
 
  it
 
 doesn't find an exact date match, it keeps searching adjacent days
 
  until
 
 it
 
 finds a certain amount of records.
 
 The problem now is, I'm using my application to loop through and run
 multiple queries and it's dog ass slow..I'm hoping that one of 
you
 
 SQL
 
 gurus can point me in the right direction to create a query that will
 
 work
 
 it out for me.  Here's the logic the best I can explain..
 
 I want to return a minimum of 15 records..  I'm searching for records
 
  on
 
 or
 
 around 2005-10-01
 
 Select * from table_x where row_date = '2005-10-01'
 /* at this point if matched records are = 15 then simply return the
 
 records
 
 on that date..  If not..*/
 Select * from table_x where row_date = '2005-09-31'
 
 Select * from table_x where row_date = '2005-10-02'
 
 And so on until it finds = 15 records or it searches through 5 days
 
  (+-
 
 3
 
 on search date)
 
 I hope this makes sense..  I'm new to all this stuff.
 
 Eventually I'm going to do the same thing for times as well..
 
 
 Heres a suggestion:
 
 
 select * from table_x
 where  row_date between date_sub(now(), interval 3 day) AND
 date_add(now(), interval 3 day)
 order by abs(time_to_sec(datediff(created, now(
 limit 15 ;
 
 
 This does times relative to now(), but I am sure you can generalise 
it.
 The first line specifies the desired fields
 The second selects (in principle) all the records within your largest
 target window
 The third orders them by closeness to your target time
 and the last says you only want 15 of them.
 
 
 This version is based on exact seconds from the target time (now() in 
my
 case): the version which works in whole days would only be slightly
 different.
 
 
 Alec
 
 
  Only one problem with your solution, LIMIT tells how many records AT 
MOST
  to return, he wants to get 15 AT LEAST and stop appending records once 
he
  gets over 15 total results.

 I didn't think about the AT LEAST 15 rows.

 I have two ideas:
 1) Do an order by and just fetch each day until at least 15 is reached.
 2) Two queries, the first involves a GROUP BY, 'SELECT row_date,
 count(*) FROM table_x GROUP BY row_date' and see which date you need to
 do the select for, then just get all the rows from that date forward.

 - --
 Love is mutual self-giving that ends in self-recovery. Fulton Sheen
 James Black[EMAIL PROTECTED]
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.1 (MingW32)
 Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

 iD8DBQFCsZ5kikQgpVn8xrARAh/aAJ9bp1rPVdD+oBfuc3iDourPwRFIugCfR2qF
 oQvoGEWlRTpktAakMJA2Q5A=
 =8bF7
 -END PGP SIGNATURE-

Re: Need help with a query..

[Frank Bax]

At 03:09 AM 6/16/05, Cory Robin wrote:


I need to speed up a search, big time.

I have an application that searches for records on a date field.  If it
doesn't find an exact date match, it keeps searching adjacent days until it
finds a certain amount of records.

The problem now is, I'm using my application to loop through and run
multiple queries and it's dog ass slow..I'm hoping that one of you SQL
gurus can point me in the right direction to create a query that will work
it out for me.  Here's the logic the best I can explain..

I want to return a minimum of 15 records..  I'm searching for records on or
around 2005-10-01

Select * from table_x where row_date = '2005-10-01'
/* at this point if matched records are = 15 then simply return the records
on that date..  If not..*/
Select * from table_x where row_date = '2005-09-31'

Select * from table_x where row_date = '2005-10-02'

And so on until it finds = 15 records or it searches through 5 days (+- 3
on search date)

I hope this makes sense..  I'm new to all this stuff.

Eventually I'm going to do the same thing for times as well..

Thanks in advance for any help!



Plus/minus 3 days makes a total of 7 days, not 5 days.  I would try 
something like:


Select * from table_x where row_date  between '2005-09-28' and '2004-10-04' 
order by abs(datediff('2005-10-01',rowdate) LIMIT 15;


It is not clear if you are looking for
A) one day with 15 records
B) the 15 records closest to your target date.
C) the 15 records closest to target date, plus all records for dates in 
that result of 15 records.


My example query does (B).  If you want (A) or (C), lease out the LIMIT 
clause and do that code in your application - might also be possible with 
subselects, but let's make sure that;s what you want before going 
there.  Knowing what version you have is crucial, because availability of 
date/time functions varies widely in currently available releases. 



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

Re: MySQL (SQL) Newbie.. Need help with a Query

[mfatene]

hi,
that's the same. If you use between, mysql do the rest for you :

mysql explain SELECT * FROM passengers WHERE
- reservation_date_time = '2005-01-01 12:10:00'
- AND reservation_date_time = '2005-05-01 12:10:00';
++-++---+---++-+--+--+--+
| id | select_type | table  | type  | possible_keys | key| key_len | ref
 | rows | Extra|
++-++---+---++-+--+--+--+
|  1 | SIMPLE  | passengers | range | reserv| reserv |   9 |
NULL |1 | Using where; Using index |
++-++---+---++-+--+--+--+
1 row in set (0.01 sec)

mysql explain SELECT * FROM passengers WHERE
- reservation_date_time between '2005-01-01 12:10:00'AND '2005-05-01
12:10:00';
++-++---+---++-+--+--+--+
| id | select_type | table  | type  | possible_keys | key| key_len | ref
 | rows | Extra|
++-++---+---++-+--+--+--+
|  1 | SIMPLE  | passengers | range | reserv| reserv |   9 |
NULL |1 | Using where; Using index |
++-++---+---++-+--+--+--+
1 row in set (0.00 sec)

Mathias


Selon Cory Robin [EMAIL PROTECTED]:

 I'm trying to return all records between two dates..  The fields are
 datetime fields...

 Which is better?  The following or using BETWEEN? (A little lost here)

 SELECT * FROM passengers WHERE
 reservation_date_time = '2005-01-01 12:10:00'
 AND reservation_date_time = '2005-05-01 12:10:00';



 --
 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: MySQL (SQL) Newbie.. Need help with a Query

[mfatene]

resubmitted
Selon [EMAIL PROTECTED]:

 hi,
 that's the same. If you use between, mysql do the rest for you :

 mysql explain SELECT * FROM passengers WHERE
 - reservation_date_time = '2005-01-01 12:10:00'
 - AND reservation_date_time = '2005-05-01 12:10:00';

++-++---+---++-+--+--+--+
 | id | select_type | table  | type  | possible_keys | key| key_len |
 ref
  | rows | Extra|

++-++---+---++-+--+--+--+
 |  1 | SIMPLE  | passengers | range | reserv| reserv |   9 |
 NULL |1 | Using where; Using index |

++-++---+---++-+--+--+--+
 1 row in set (0.01 sec)

 mysql explain SELECT * FROM passengers WHERE
 - reservation_date_time between '2005-01-01 12:10:00'AND '2005-05-01
 12:10:00';

++-++---+---++-+--+--+--+
 | id | select_type | table  | type  | possible_keys | key| key_len |
 ref
  | rows | Extra|

++-++---+---++-+--+--+--+
 |  1 | SIMPLE  | passengers | range | reserv| reserv |   9 |
 NULL |1 | Using where; Using index |

++-++---+---++-+--+--+--+
 1 row in set (0.00 sec)

 Mathias


 Selon Cory Robin [EMAIL PROTECTED]:

  I'm trying to return all records between two dates..  The fields are
  datetime fields...
 
  Which is better?  The following or using BETWEEN? (A little lost here)
 
  SELECT * FROM passengers WHERE
  reservation_date_time = '2005-01-01 12:10:00'
  AND reservation_date_time = '2005-05-01 12:10:00';
 
 
 
  --
  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]

MySQL (SQL) Newbie.. Need help with a Query

[Cory Robin]

I'm trying to return all records between two dates..  The fields are
datetime fields...

Which is better?  The following or using BETWEEN? (A little lost here)

SELECT * FROM passengers WHERE
reservation_date_time = '2005-01-01 12:10:00'
AND reservation_date_time = '2005-05-01 12:10:00';



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

Re: MySQL (SQL) Newbie.. Need help with a Query

[Sebastian]

Cory Robin wrote:


I'm trying to return all records between two dates..  The fields are
datetime fields...

Which is better?  The following or using BETWEEN? (A little lost here)

SELECT * FROM passengers WHERE
reservation_date_time = '2005-01-01 12:10:00'
AND reservation_date_time = '2005-05-01 12:10:00';
 



i think you should be using BETWEEN

WHERE your_field BETWEEN '2005-01-01 12:10:00' AND '2005-05-01 12:10:00'

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

Re: MySQL (SQL) Newbie.. Need help with a Query

[Michael Stassen]

Sebastian wrote:


Cory Robin wrote:


I'm trying to return all records between two dates..  The fields are
datetime fields...

Which is better?  The following or using BETWEEN? (A little lost here)

SELECT * FROM passengers WHERE
reservation_date_time = '2005-01-01 12:10:00'
AND reservation_date_time = '2005-05-01 12:10:00';
 


i think you should be using BETWEEN

WHERE your_field BETWEEN '2005-01-01 12:10:00' AND '2005-05-01 12:10:00'


They are identical to mysql, but I think the BETWEEN version is easier 
to read for most of us humans.


Michael

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

Re: Need help in basic query

[Anoop kumar V]

well - that was what I tried first - but that does not work because that 
returns the latest date for which the task_id has a record as Assignment. 
(It is like it picks up the max date from all records that have 
name_rec_type as Assignment)

But I wanted was if the date corresponding to the name_rec_type as 
Assignment is the max(date) then return that task_id.

I hope I have made myself clear...

Thanks,
Anoop

On 6/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 Hi,
 Try just :
 
 SELECT id_secr_rqst task_id, MAX(dt_aud_rec) AS latest
 FROM isr2_aud_log WHERE
 name_rec_type = 'Assignment' AND id_secr_rqst
 ='TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF'
 GROUP BY id_secr_rqst
 ;
 
 ++-+
 | task_id | latest |
 ++-+
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
 ++-+
 1 row in set (0.02 sec)
 
 Mathias
 
 
 Selon Anoop kumar V [EMAIL PROTECTED]:
 
  Hi mysql-ers,
 
  I need help in a basic query:
 
  I have this table:
 
  select * from isr2_aud_log where
  id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
  --and name_rec_type = 'Assignment'
  order by id_secr_rqst, dt_aud_rec
 
 
 
 ++-+-+
  | id_secr_rqst | dt_aud_rec | name_rec_type |
 
 
 ++-+-+
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
  Submission |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
  Exception Requested |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
  Exception Resource |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 |
  Director Approval |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 |
  Assignment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 |
  Risk Assessment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 |
  Assignment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 |
  SERB Approval |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
  Assignment |
 
 
 ++-+-+
 
  and i am using this query:
 
  SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest,
  t1.dt_aud_recAS date1
  FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst =
  t2.id_secr_rqst
  AND t1.name_rec_type = 'Assignment' AND
  t1.id_secr_rqst IN (
  'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
  GROUP BY t2.id_secr_rqst HAVING date1 = latest
 
  What I expected to get is the id_secr_rqst which has the last 
 name_rec_type
  = 'Assignment'
  In this case there is only one id_secr_rqst and it has the last
  name_rec_type as 'Assignment'. But I do not seem to get consistent 
 results.
  As I am using an older version of mysql I do not have the liberty to use
  subqueries and will have to do everything using joins only.
 
  The problem I am facing is that this query only sometimes returns rows 
 and
  most of the time I get an empty result set. This table does not have any
  primary keys.
 
  Can somebody please point out what is the mistake I am doing - I think 
 it
  just needs a tweak here and there (I hope..)
 
  Thanks,
  Anoop
 
  --
  Thanks and best regards,
  Anoop
 
 
 
 


-- 
Thanks and best regards,
Anoop

Need help in basic query

[Anoop kumar V]

Hi mysql-ers,

I need help in a basic query:

I have this table:

select * from isr2_aud_log where 
id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
--and name_rec_type = 'Assignment'
order by id_secr_rqst, dt_aud_rec
 
++-+-+
| id_secr_rqst | dt_aud_rec | name_rec_type |
++-+-+
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
Submission |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
Exception Requested |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
Exception Resource |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | 
Director Approval |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | 
Assignment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | 
Risk Assessment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | 
Assignment |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | 
SERB Approval |
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | 
Assignment |
++-+-+

and i am using this query:

SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest,
t1.dt_aud_recAS date1
FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = 
t2.id_secr_rqst 
AND t1.name_rec_type = 'Assignment' AND 
t1.id_secr_rqst IN (
'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
GROUP BY t2.id_secr_rqst HAVING date1 = latest

What I expected to get is the id_secr_rqst which has the last name_rec_type 
= 'Assignment'
In this case there is only one id_secr_rqst and it has the last 
name_rec_type as 'Assignment'. But I do not seem to get consistent results.
As I am using an older version of mysql I do not have the liberty to use 
subqueries and will have to do everything using joins only. 

The problem I am facing is that this query only sometimes returns rows and 
most of the time I get an empty result set. This table does not have any 
primary keys.

Can somebody please point out what is the mistake I am doing - I think it 
just needs a tweak here and there (I hope..)

Thanks,
Anoop

-- 
Thanks and best regards,
Anoop

Re: Need help in basic query

[Anoop kumar V]

OK - I have found the cause of the inconsistency - 

Whenever I have more than one record which has name_rec_type as 'Assignment' 
I do not get any results (I get an empty result)
But if I have only one Assignment record then it returns the correct row.

Question is: How can I overcome this - I just need the id_secr_rqst if and 
only if it has the latest name_rec_type as 'Assignment' - I do not care what 
the earlier records contained.

help please,
Anoop

On 6/2/05, Anoop kumar V [EMAIL PROTECTED] wrote:
 
 Hi mysql-ers,
 
 I need help in a basic query:
 
 I have this table:
 
 select * from isr2_aud_log where 
 id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
 --and name_rec_type = 'Assignment'
 order by id_secr_rqst, dt_aud_rec
  
 
 ++-+-+
 | id_secr_rqst | dt_aud_rec | name_rec_type |
 
 ++-+-+
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
 Submission |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
 Exception Requested |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
 Exception Resource |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | 
 Director Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | 
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | 
 Risk Assessment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | 
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | 
 SERB Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | 
 Assignment |
 
 ++-+-+
 
 and i am using this query:
 
 SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest, 
 t1.dt_aud_rec AS date1 
 FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = 
 t2.id_secr_rqst 
 AND t1.name_rec_type = 'Assignment' AND 
 t1.id_secr_rqst IN (
 'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
 GROUP BY t2.id_secr_rqst HAVING date1 = latest
 
 What I expected to get is the id_secr_rqst which has the last 
 name_rec_type = 'Assignment'
 In this case there is only one id_secr_rqst and it has the last 
 name_rec_type as 'Assignment'. But I do not seem to get consistent results.
 As I am using an older version of mysql I do not have the liberty to use 
 subqueries and will have to do everything using joins only. 
 
 The problem I am facing is that this query only sometimes returns rows and 
 most of the time I get an empty result set. This table does not have any 
 primary keys.
 
 Can somebody please point out what is the mistake I am doing - I think it 
 just needs a tweak here and there (I hope..)
 
 Thanks,
 Anoop
 
 -- 
 Thanks and best regards,
 Anoop 




-- 
Thanks and best regards,
Anoop

Re: Need help in basic query

[Anoop kumar V]

SOLVED:

I changed my query to include max(t1.dt_aud_rec) instead of t1.dt_aud_rec.

I had guessed that it required just a tweak here and there...

Does anybody have any other suggestions apart from this??

Thanks,
Anoop

On 6/2/05, Anoop kumar V [EMAIL PROTECTED] wrote:
 
 OK - I have found the cause of the inconsistency - 
 
 Whenever I have more than one record which has name_rec_type as 
 'Assignment' I do not get any results (I get an empty result)
 But if I have only one Assignment record then it returns the correct row.
 
 Question is: How can I overcome this - I just need the id_secr_rqst if and 
 only if it has the latest name_rec_type as 'Assignment' - I do not care what 
 the earlier records contained.
 
 help please,
 Anoop
 
 On 6/2/05, Anoop kumar V [EMAIL PROTECTED] wrote:
  
  Hi mysql-ers,
  
  I need help in a basic query:
  
  I have this table:
  
  select * from isr2_aud_log where 
  id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
  --and name_rec_type = 'Assignment'
  order by id_secr_rqst, dt_aud_rec
   
  
  ++-+-+
  | id_secr_rqst | dt_aud_rec | name_rec_type |
  
  ++-+-+
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
  Submission |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
  Exception Requested |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | 
  Exception Resource |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | 
  Director Approval |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | 
  Assignment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | 
  Risk Assessment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | 
  Assignment |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | 
  SERB Approval |
  | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | 
  Assignment |
  
  ++-+-+
  
  and i am using this query:
  
  SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest, 
  t1.dt_aud_rec AS date1 
  FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = 
  t2.id_secr_rqst 
  AND t1.name_rec_type = 'Assignment' AND 
  t1.id_secr_rqst IN (
  'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') 
  GROUP BY t2.id_secr_rqst HAVING date1 = latest
  
  What I expected to get is the id_secr_rqst which has the last 
  name_rec_type = 'Assignment'
  In this case there is only one id_secr_rqst and it has the last 
  name_rec_type as 'Assignment'. But I do not seem to get consistent results.
  As I am using an older version of mysql I do not have the liberty to use 
  subqueries and will have to do everything using joins only. 
  
  The problem I am facing is that this query only sometimes returns rows 
  and most of the time I get an empty result set. This table does not have 
  any 
  primary keys.
  
  Can somebody please point out what is the mistake I am doing - I think 
  it just needs a tweak here and there (I hope..)
  
  Thanks,
  Anoop
  
  -- 
  Thanks and best regards,
  Anoop 
 
 
 
 
 -- 
 Thanks and best regards,
 Anoop 
 



-- 
Thanks and best regards,
Anoop

Re: Need help in basic query

[mfatene]

Hi,
Try just :

SELECT id_secr_rqst task_id, MAX(dt_aud_rec) AS latest
FROM isr2_aud_log WHERE
name_rec_type = 'Assignment' AND id_secr_rqst
='TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF'
GROUP BY id_secr_rqst
;

++-+
| task_id| latest  |
++-+
| TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
++-+
1 row in set (0.02 sec)

Mathias


Selon Anoop kumar V [EMAIL PROTECTED]:

 Hi mysql-ers,

 I need help in a basic query:

 I have this table:

 select * from isr2_aud_log where
 id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
 --and name_rec_type = 'Assignment'
 order by id_secr_rqst, dt_aud_rec


++-+-+
 | id_secr_rqst | dt_aud_rec | name_rec_type |

++-+-+
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
 Submission |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
 Exception Requested |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 |
 Exception Resource |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 |
 Director Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 |
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 |
 Risk Assessment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 |
 Assignment |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 |
 SERB Approval |
 | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 |
 Assignment |

++-+-+

 and i am using this query:

 SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest,
 t1.dt_aud_recAS date1
 FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst =
 t2.id_secr_rqst
 AND t1.name_rec_type = 'Assignment' AND
 t1.id_secr_rqst IN (
 'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF')
 GROUP BY t2.id_secr_rqst HAVING date1 = latest

 What I expected to get is the id_secr_rqst which has the last name_rec_type
 = 'Assignment'
 In this case there is only one id_secr_rqst and it has the last
 name_rec_type as 'Assignment'. But I do not seem to get consistent results.
 As I am using an older version of mysql I do not have the liberty to use
 subqueries and will have to do everything using joins only.

 The problem I am facing is that this query only sometimes returns rows and
 most of the time I get an empty result set. This table does not have any
 primary keys.

 Can somebody please point out what is the mistake I am doing - I think it
 just needs a tweak here and there (I hope..)

 Thanks,
 Anoop

 --
 Thanks and best regards,
 Anoop




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

Need help forming SQL query

[Mike Zornek]

I have the following query which will get me all of the emails for my
current membership:

SELECT email.email_address
FROM member, email
WHERE 
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
ORDER BY email.email_address

I also have a query which will get me the email addresses of anyone who has
updated their profile (and thus has a row in updatehistory)

SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE 
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;

How would I get the emails for every member who does NOT have a row in
updatehistory?

~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com


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

Re: Need help forming SQL query

[Rhino]

You should *ALWAYS* indicate which version of MySQL you are using when you
ask this sort of question; the answers is almost always it depends: if you
are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x do
Z. It's a lot of work to list all of those options and most people won't do
it.

It would also help if you provided information about what columns occurred
in each table so that potential responders are clear on what information is
in each of your tables. Including a few typical sample rows is also very
helpful for some of us, although some people can envision the data clearly
with only the column names.

Rhino


- Original Message - 
From: Mike Zornek [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 20, 2005 11:01 AM
Subject: Need help forming SQL query


 I have the following query which will get me all of the emails for my
 current membership:

 SELECT email.email_address
 FROM member, email
 WHERE
 member.member_primary_email_id = email.email_id
 AND member.member_standing != Dropped
 ORDER BY email.email_address

 I also have a query which will get me the email addresses of anyone who
has
 updated their profile (and thus has a row in updatehistory)

 SELECT DISTINCT email.email_address
 FROM member, email, updatehistory
 WHERE
 member.member_primary_email_id = email.email_id
 AND member.member_standing != Dropped
 AND member.member_id = updatehistory.member_id_editor
 ORDER BY email.email_address;

 How would I get the emails for every member who does NOT have a row in
 updatehistory?

 ~ Mike
 -
 Mike Zornek
 Web Designer, Media Developer, Programmer and Geek
 Personal site: http://MikeZornek.com


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



 -- 
 No virus found in this incoming message.
 Checked by AVG Anti-Virus.
 Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005





-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005


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

Re: Need help forming SQL query

[Mike Zornek]

On 1/20/05 11:16 AM, Rhino [EMAIL PROTECTED] wrote:

 You should *ALWAYS* indicate which version of MySQL you are using when you
 ask this sort of question; the answers is almost always it depends: if you
 are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x do
 Z. It's a lot of work to list all of those options and most people won't do
 it.
 
 It would also help if you provided information about what columns occurred
 in each table so that potential responders are clear on what information is
 in each of your tables. Including a few typical sample rows is also very
 helpful for some of us, although some people can envision the data clearly
 with only the column names.
 
 Rhino

Ok, noted. I'll include this info with any future questions.

i'm using MySQL 4.0.17-log

Email:

+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default | Extra
|
+---+--+--+-+-+-
---+
| email_id  | smallint(5) unsigned |  | PRI | NULL|
auto_increment |
| email_type| enum('Home','Work')  |  | | Work|
|
| email_address | varchar(50)  |  | UNI | |
|
| member_id | smallint(5) unsigned |  | MUL | 0   |
|
| email_private | tinyint(1)   |  | | 0   |
|
+---+--+--+-+-+-

Update History:

++--+--+-+-+
+
| Field  | Type | Null | Key | Default |
Extra  |
++--+--+-+-+
+
| updatehistroy_id   | smallint(5) unsigned |  | PRI | NULL|
auto_increment |
| updatehistroy_datetime | datetime | YES  | | NULL|
|
| member_id_edited   | smallint(5) unsigned |  | | 0   |
|
| member_id_editor   | smallint(5) unsigned |  | | 0   |
|
| updatehistory_details  | text |  | | |
|
++--+--+-+-+

 - Original Message -
 From: Mike Zornek [EMAIL PROTECTED]
 To: mysql@lists.mysql.com
 Sent: Thursday, January 20, 2005 11:01 AM
 Subject: Need help forming SQL query
 
 
 I have the following query which will get me all of the emails for my
 current membership:
 
 SELECT email.email_address
 FROM member, email
 WHERE
 member.member_primary_email_id = email.email_id
 AND member.member_standing != Dropped
 ORDER BY email.email_address
 
 I also have a query which will get me the email addresses of anyone who
 has
 updated their profile (and thus has a row in updatehistory)
 
 SELECT DISTINCT email.email_address
 FROM member, email, updatehistory
 WHERE
 member.member_primary_email_id = email.email_id
 AND member.member_standing != Dropped
 AND member.member_id = updatehistory.member_id_editor
 ORDER BY email.email_address;
 
 How would I get the emails for every member who does NOT have a row in
 updatehistory?
 
 ~ Mike
 -
 Mike Zornek
 Web Designer, Media Developer, Programmer and Geek
 Personal site: http://MikeZornek.com
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 -- 
 No virus found in this incoming message.
 Checked by AVG Anti-Virus.
 Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
 
 
 
 

~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com


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

Re: Need help forming SQL query

[SGreen]

Mike Zornek [EMAIL PROTECTED] wrote on 01/20/2005 11:01:38 AM:

 I have the following query which will get me all of the emails for my
 current membership:
 
 SELECT email.email_address
 FROM member, email
 WHERE 
 member.member_primary_email_id = email.email_id
 AND member.member_standing != Dropped
 ORDER BY email.email_address
 
 I also have a query which will get me the email addresses of anyone who 
has
 updated their profile (and thus has a row in updatehistory)
 
 SELECT DISTINCT email.email_address
 FROM member, email, updatehistory
 WHERE 
 member.member_primary_email_id = email.email_id
 AND member.member_standing != Dropped
 AND member.member_id = updatehistory.member_id_editor
 ORDER BY email.email_address;
 
 How would I get the emails for every member who does NOT have a row in
 updatehistory?
 
 ~ Mike
 -
 Mike Zornek
 Web Designer, Media Developer, Programmer and Geek
 Personal site: http://MikeZornek.com
 
 

First, I need to you recognize that listing tables with commas in the FROM 
clause creates an implicit INNER JOIN between the tables.  That means that 
your query 

SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;

IS EQUIVALENT to this more explicitly defined query:

SELECT DISTINCT email.email_address
FROM member
INNER JOIN email
ON member.member_primary_email_id = email.email_id
INNER JOIN updatehistory
ON member.member_id = updatehistory.member_id_editor
WHERE member.member_standing != Dropped
ORDER BY email.email_address;

In order to detect non-matches between two tables, you perform an outer 
join between them and look for the records that don't match. They will be 
easy to spot because the engine will put NULLs into every column of the 
optional table for those rows that don't match up with the required 
table. In a LEFT JOIN, the table on the left of the clause is the required 
table and the one on the right is the optional table. Reverse that for 
RIGHT JOINs.

With that knowledge in hand, we will now change the query to detect which 
rows of member (required) do not match any rows in updatehistory 
(optional). We change the JOIN on that table from INNER JOIN to LEFT JOIN 
and look for NULL values where there shouldn't be any by adding another 
condition to the WHERE clause (they should only exist if rows between the 
two tables didn't meet your ON conditions(s), correct?). We don't need to 
change anything else.

SELECT DISTINCT email.email_address
FROM member
INNER JOIN email
ON member.member_primary_email_id = email.email_id
LEFT JOIN updatehistory
ON member.member_id = updatehistory.member_id_editor
WHERE member.member_standing != Dropped
AND updatehistory.member_id_editor IS NULL
ORDER BY email.email_address;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Need help forming SQL query

[Rhino]

That's a great start but you haven't shown the Member table. It would also
be very useful if you included a few sample rows from each table. For
example, I'm looking at the member_id_edited and member_id_editor columns so
that I can create tiny sample tables like yours and I have no idea what
values will typically go in those columns.

By the way, did you know that there are typos in the first two column names
of the Update_History table? updatehistroy should be spelled
updatehistory

Rhino

- Original Message - 
From: Mike Zornek [EMAIL PROTECTED]
To: MySQL mysql@lists.mysql.com
Cc: Rhino [EMAIL PROTECTED]
Sent: Thursday, January 20, 2005 11:26 AM
Subject: Re: Need help forming SQL query


 On 1/20/05 11:16 AM, Rhino [EMAIL PROTECTED] wrote:

  You should *ALWAYS* indicate which version of MySQL you are using when
you
  ask this sort of question; the answers is almost always it depends: if
you
  are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x
do
  Z. It's a lot of work to list all of those options and most people
won't do
  it.
 
  It would also help if you provided information about what columns
occurred
  in each table so that potential responders are clear on what information
is
  in each of your tables. Including a few typical sample rows is also very
  helpful for some of us, although some people can envision the data
clearly
  with only the column names.
 
  Rhino

 Ok, noted. I'll include this info with any future questions.

 i'm using MySQL 4.0.17-log

 Email:


+---+--+--+-+-+-
 ---+
 | Field | Type | Null | Key | Default | Extra
 |

+---+--+--+-+-+-
 ---+
 | email_id  | smallint(5) unsigned |  | PRI | NULL|
 auto_increment |
 | email_type| enum('Home','Work')  |  | | Work|
 |
 | email_address | varchar(50)  |  | UNI | |
 |
 | member_id | smallint(5) unsigned |  | MUL | 0   |
 |
 | email_private | tinyint(1)   |  | | 0   |
 |

+---+--+--+-+-+-

 Update History:


++--+--+-+-+
 +
 | Field  | Type | Null | Key | Default |
 Extra  |

++--+--+-+-+
 +
 | updatehistroy_id   | smallint(5) unsigned |  | PRI | NULL|
 auto_increment |
 | updatehistroy_datetime | datetime | YES  | | NULL|
 |
 | member_id_edited   | smallint(5) unsigned |  | | 0   |
 |
 | member_id_editor   | smallint(5) unsigned |  | | 0   |
 |
 | updatehistory_details  | text |  | | |
 |

++--+--+-+-+

  - Original Message -
  From: Mike Zornek [EMAIL PROTECTED]
  To: mysql@lists.mysql.com
  Sent: Thursday, January 20, 2005 11:01 AM
  Subject: Need help forming SQL query
 
 
  I have the following query which will get me all of the emails for my
  current membership:
 
  SELECT email.email_address
  FROM member, email
  WHERE
  member.member_primary_email_id = email.email_id
  AND member.member_standing != Dropped
  ORDER BY email.email_address
 
  I also have a query which will get me the email addresses of anyone who
  has
  updated their profile (and thus has a row in updatehistory)
 
  SELECT DISTINCT email.email_address
  FROM member, email, updatehistory
  WHERE
  member.member_primary_email_id = email.email_id
  AND member.member_standing != Dropped
  AND member.member_id = updatehistory.member_id_editor
  ORDER BY email.email_address;
 
  How would I get the emails for every member who does NOT have a row in
  updatehistory?
 
  ~ Mike
  -
  Mike Zornek
  Web Designer, Media Developer, Programmer and Geek
  Personal site: http://MikeZornek.com
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
  -- 
  No virus found in this incoming message.
  Checked by AVG Anti-Virus.
  Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
 
 
 
 

 ~ Mike
 -
 Mike Zornek
 Web Designer, Media Developer, Programmer and Geek
 Personal site: http://MikeZornek.com



 -- 
 No virus found in this incoming message.
 Checked by AVG Anti-Virus.
 Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005





-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005


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

need help with a query

[Redmond Militante]

hi

i need advice on a query i'm trying to do.  i'm trying to find entries with that are 
about to expire.  entries expire if their date of submission is older than 60 days.  i 
want to find all entries with a date of submission greater than 30 days, or those that 
are going to expire within a month 

i'm using this as part of my query to find entries that are about to expire: ' ... 
DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY) =NOW()' 

am i doing this the right way?

thanks
redmond

-- 
Redmond Militante
Software Engineer / Medill School of Journalism
FreeBSD 5.2.1-RELEASE-p9 #0: Thu Jul 1 14:36:26 CDT 2004 i386
 8:00PM  up 28 days,  3:36, 4 users, load averages: 0.00, 0.29, 0.51


pgpAimRBp7GPQ.pgp
Description: PGP signature

RE: need help with a query

[David Perron]

Are you assuming that all months have 30 days?  You can use the same syntax
with INTERVAL 1 MONTH
I would also format the date comparison to use the same precision that the
DATE_ADD function outputs.

So,

DATE_ADD(table.date, INTERVAL 1 MONTH) = DATE_FORMAT(CURDATE(), '%Y-%m-%d')

I might be off on the 1 MONTH part, but I think it's the right track.

-Original Message-
From: Redmond Militante [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 25, 2004 6:03 PM
To: [EMAIL PROTECTED]
Subject: need help with a query

hi

i need advice on a query i'm trying to do.  i'm trying to find entries with
that are about to expire.  entries expire if their date of submission is
older than 60 days.  i want to find all entries with a date of submission
greater than 30 days, or those that are going to expire within a month 

i'm using this as part of my query to find entries that are about to expire:
' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY) =NOW()' 

am i doing this the right way?

thanks
redmond

--
Redmond Militante
Software Engineer / Medill School of Journalism FreeBSD 5.2.1-RELEASE-p9 #0:
Thu Jul 1 14:36:26 CDT 2004 i386  8:00PM  up 28 days,  3:36, 4 users, load
averages: 0.00, 0.29, 0.51


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

Re: need help with a query

[Michael Stassen]

Redmond Militante wrote:
hi
i need advice on a query i'm trying to do.  i'm trying to find
entries with that are about to expire.  entries expire if their date
of submission is older than 60 days.  i want to find all entries with
a date of submission greater than 30 days, or those that are going to
expire within a month
i'm using this as part of my query to find entries that are about to
expire: ' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY)
=NOW()' 

am i doing this the right way?
thanks
redmond
Almost.  First, you say that your column is a DATE, but you are comparing to 
NOW(), which returns a DATETIME.  That's OK (mysql will convert), but it 
would be slightly better to compare to CURDATE(), as it returns a DATE.

More importantly, you do not want to compare a function of a column to a 
constant, because then an index on the column is of no use, forcing a full 
table scan.  If at all possible, move the function to the constant side of 
the comparison, as a function of a constant is a constant.

In other words,
  datecol + 30 days = today
is equivalent to
  datecol = today - 30 days
but the former forces a table scan while the latter could use an index on 
datecol.

So, in your case, you should use
  WHERE ... $dbtable3.savedemployers2 = CURDATE() - INTERVAL 30 DAY
Finally, note that this would also return entries which have already 
expired, if they are still there.  To leave those out, change the condition to

  $dbtable3.savedemployers2 BETWEEN CURDATE() - INTERVAL 60 DAY
AND CURDATE() - INTERVAL 30 DAY
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: need help with a query

[Michael Stassen]

David Perron wrote:
Are you assuming that all months have 30 days?  You can use the same syntax
with INTERVAL 1 MONTH
True.
I would also format the date comparison to use the same precision that the
DATE_ADD function outputs.
Why would you do that?  The date column contains a DATE.  CURDATE() returns 
a DATE.  DATE_ADD() returns a DATE.  Comparing DATEs is straightforward. 
DATE_FORMAT returns a string, however, so your query compares a DATE to a 
string.  At best, the difference is optimized away.  At worst, an extra 
conversion takes place.  Formatting is for presentation, not comparison.

So,
DATE_ADD(table.date, INTERVAL 1 MONTH) = DATE_FORMAT(CURDATE(), '%Y-%m-%d')
I might be off on the 1 MONTH part, but I think it's the right track.
Again, compare columns to (functions of) constants.  Do not run the column 
through a function, if at all possible.  Thus, this should be

  table.date = CURDATE() - INTERVAL 1 MONTH
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: need help with a query

[Redmond Militante]

awesome.  thank you!


[Wed, Aug 25, 2004 at 06:27:38PM -0700]
This one time, at band camp, David Perron said:

 
 Are you assuming that all months have 30 days?  You can use the same syntax
 with INTERVAL 1 MONTH
 I would also format the date comparison to use the same precision that the
 DATE_ADD function outputs.
 
 So,
 
 DATE_ADD(table.date, INTERVAL 1 MONTH) = DATE_FORMAT(CURDATE(), '%Y-%m-%d')
 
 I might be off on the 1 MONTH part, but I think it's the right track.
 
 -Original Message-
 From: Redmond Militante [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, August 25, 2004 6:03 PM
 To: [EMAIL PROTECTED]
 Subject: need help with a query
 
 hi
 
 i need advice on a query i'm trying to do.  i'm trying to find entries with
 that are about to expire.  entries expire if their date of submission is
 older than 60 days.  i want to find all entries with a date of submission
 greater than 30 days, or those that are going to expire within a month 
 
 i'm using this as part of my query to find entries that are about to expire:
 ' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY) =NOW()' 
 
 am i doing this the right way?
 
 thanks
 redmond
 
 --
 Redmond Militante
 Software Engineer / Medill School of Journalism FreeBSD 5.2.1-RELEASE-p9 #0:
 Thu Jul 1 14:36:26 CDT 2004 i386  8:00PM  up 28 days,  3:36, 4 users, load
 averages: 0.00, 0.29, 0.51
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

-- 
Redmond Militante
Software Engineer / Medill School of Journalism
FreeBSD 5.2.1-RELEASE-p9 #0: Thu Jul 1 14:36:26 CDT 2004 i386
11:00PM  up 28 days,  6:36, 3 users, load averages: 0.22, 0.18, 0.30


pgpC8omL1KwJU.pgp
Description: PGP signature

Re: need help for a query

[Stephen E. Bacher]

select name 
from mytable a 
where changedate  
 (select  changedate 
 from mytable b 
 where a.name=b.name 
 and a.changedate != b.changedate);

or:

select name
from mytable a
where exists 
  (select * from mytable b
   where a.name=b.name
   and a.changedate  b.changedate);

Or, especially if your MySQL is earlier than 4.1 and you
don't have subqueries:

(also untested)

select t1.name
  from mytable t1, mytable t2
 where t1.name = t2.name
   and t1.date = 'd1' and t2.date = 'd2' 
   and t1.changeDate  t2.changeDate

 - seb

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

need help for a query

[Claire Lee]

Hi,
Here's a table of mine

namedate changeDate
n1d1 cd1
n2d1 cd3
n2d2 cd1
n4d1 cd2
n1d2 cd5
n5d1 cd4
n6d2 cd2

I need to select every name for which the changeDate
corresponding to d1 is greater than the changeDate
corresponding to d2. Any way I can use one statement
to do this?

Thank you very much.

Claire




__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

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

Re: need help for a query

[Matt Warden]

Hi Claire,


On Mon, 23 Aug 2004 14:52:29 -0700 (PDT), Claire Lee [EMAIL PROTECTED] wrote:
 Hi,
 Here's a table of mine
 
 namedate changeDate
 n1d1 cd1
 n2d1 cd3
 n2d2 cd1
 n4d1 cd2
 n1d2 cd5
 n5d1 cd4
 n6d2 cd2
 
 I need to select every name for which the changeDate
 corresponding to d1 is greater than the changeDate
 corresponding to d2. Any way I can use one statement
 to do this?

Yes, of course. You seem to be suggesting that there will only be two
records with the same name in the table. In that case, something like
this (although this is untested):

select name 
from mytable a 
where changedate  
 (select  changedate 
 from mytable b 
 where a.name=b.name 
 and a.changedate != b.changedate);

or:

select name
from mytable a
where exists 
  (select * from mytable b
   where a.name=b.name
   and a.changedate  b.changedate);

I personally like the latter, form-wise. I suspect it might be
marginally faster, too.

If my above assumption isn't the case, we need more information on
what happens when there are 3 records (do you want any record which
has changedate greater than another record of the same name? or only
the highest? etc.).

Also, mind your NULLs. You might need to edit the above query
depending on how you want to handle NULLs in changedate.


Good luck,

-- 
Matt Warden
Miami University
Oxford, OH
http://mattwarden.com


This email proudly and graciously contributes to entropy.

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

need help in sql query

[Kapoor, Nishikant]

Hello List,

I have a problem that I am looking your help for. Would greatly appreciate it. Here is 
what I am trying to do:

create table C (cId tinyint(4), cName varchar(10));
insert into C values (1,'Cat01'), (2,'Cat02'), (3,'Cat03'), (4,'Cat04');

create table SC (scId tinyint(4), cId tinyint(4), scName varchar(10));
insert into SC values (1, 1, 'SubCat01'), (2, 1, 'SubCat02');

create table T (tId tinyint(4), scId tinyint(4), cId tinyint(4), topic varchar(50));
insert into T values (1, 1, 1, 'Topic01'), (2, 1, 1, 'Topic02');

I am using the following sql query:

SELECT C.cId, C.cName, SC.scId, SC.scName, count(T.scId) as tCount
FROM C left outer join SC on (C.cId = SC.cId),
 SC SC1 left outer join T on (SC1.scId = T.scId
  and C.cId = T.cId
  and SC.scId = T.scId)
GROUP BY SC.scId
ORDER BY C.cId;

I am expecting to see an output like this:

cId  cName scId  scNametCount
-
1Cat01   1   SubCat012
1Cat01   2   SubCat020
2Cat02  NULL   NULL  0
3Cat03  NULL   NULL  0
4Cat04  NULL   NULL  0

BUT, what I am getting is

cId  cName scId  scNametCount
-
1Cat01   1   SubCat012
1Cat01   2   SubCat020
2Cat02  NULL   NULL  0

i.e. Cat03  Cat04 do not show up. I don't know whay. I think, I am missing something 
obvious. Could any of you please help?

Thanks,
Nishi


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

Re: need help in sql query

[SGreen]

You were so very close to getting what you wanted!

What is causing the problem is the comma (,) in your FROM clause. MySQL 
permits two methods of declaring an INNER JOIN. The first is by using the 
keyphrase INNER JOIN the second is with a comma in your table list. Here 
is how to rephrase your query to return what you wanted:

SELECT C.cId, C.cName, SC.scId, SC.scName, count(T.scId) as tCount
FROM C 
LEFT JOIN SC 
ON C.cId = SC.cId
LEFT JOIN T 
ON SC.scId = T.scId
GROUP BY SC.scId, C.cName, SC.scId, SC.scName
ORDER BY C.cId;

You also needed to list all of the other un-aggregated columns in your 
GROUP BY. MySQL will return without an error but EVERY OTHER sql dialect I 
have used will require those other column names so it is an excellent idea 
to get into the habit of writing them out every time.

Also, since the table SC contains a pointer to C, and T contains a pointer 
to SC, you don't need to include a pointer to C on T.

create table T (tId tinyint(4), scId tinyint(4), topic varchar(50));

(in other words) If a topic belongs to a subcategory, and a subcategory 
belongs to a category, the topic also belongs to that category. There is 
no need to restate that in your data. Including a column for the category 
id on the topic table could actually _create_ a problem if you assigned a 
topic to a category and to a subcategory that weren't related to each 
other (the subcategory did not belong to the category).

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Kapoor, Nishikant [EMAIL PROTECTED] wrote on 
07/21/2004 03:19:48 PM:

 Hello List,
 
 I have a problem that I am looking your help for. Would greatly 
 appreciate it. Here is what I am trying to do:
 
 create table C (cId tinyint(4), cName varchar(10));
 insert into C values (1,'Cat01'), (2,'Cat02'), (3,'Cat03'), (4,'Cat04');
 
 create table SC (scId tinyint(4), cId tinyint(4), scName varchar(10));
 insert into SC values (1, 1, 'SubCat01'), (2, 1, 'SubCat02');
 
 create table T (tId tinyint(4), scId tinyint(4), cId tinyint(4), 
 topic varchar(50));
 insert into T values (1, 1, 1, 'Topic01'), (2, 1, 1, 'Topic02');
 
 I am using the following sql query:
 
 SELECT C.cId, C.cName, SC.scId, SC.scName, count(T.scId) as tCount
 FROM C left outer join SC on (C.cId = SC.cId),
  SC SC1 left outer join T on (SC1.scId = T.scId
   and C.cId = T.cId
   and SC.scId = T.scId)
 GROUP BY SC.scId
 ORDER BY C.cId;
 
 I am expecting to see an output like this:
 
 cId  cName scId  scNametCount
 -
 1Cat01   1   SubCat012
 1Cat01   2   SubCat020
 2Cat02  NULL   NULL  0
 3Cat03  NULL   NULL  0
 4Cat04  NULL   NULL  0
 
 BUT, what I am getting is
 
 cId  cName scId  scNametCount
 -
 1Cat01   1   SubCat012
 1Cat01   2   SubCat020
 2Cat02  NULL   NULL  0
 
 i.e. Cat03  Cat04 do not show up. I don't know whay. I think, I am 
 missing something obvious. Could any of you please help?
 
 Thanks,
 Nishi
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

need help with a query

[Anders Gjermshus]

Hi.

I'm having trouble with a query. I'm wondering if it is possible at all.

I'm making a poll for my webpage. I have two tables:

 

Poll and poll_votes

 

Table poll contains data about the poll

Poll_Votes contains all the votes. What I want is to pull out data about the
poll and if the user has voted or not.

 

I have tried many different queries, but I haven't managed to get it to
work. Can someone help me. 

 

My poll_votes table has this structure:

Poll_id

User_id

Time

 

I hope someone can help me.

 

- Anders Gjermshus

 

Re: need help with a query

[Ligaya Turmelle]

sounds like you need a join.

Select * from Poll, poll_votes where (Poll.poll_id = poll_votes.poll_id) and
(poll_votes.user_ID = WHATEVER);

But I'm still a beginner so

Respectfully,
Ligaya Turmelle

Anders Gjermshus [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 Hi.

 I'm having trouble with a query. I'm wondering if it is possible at all.

 I'm making a poll for my webpage. I have two tables:



 Poll and poll_votes



 Table poll contains data about the poll

 Poll_Votes contains all the votes. What I want is to pull out data about
the
 poll and if the user has voted or not.



 I have tried many different queries, but I haven't managed to get it to
 work. Can someone help me.



 My poll_votes table has this structure:

 Poll_id

 User_id

 Time



 I hope someone can help me.



 - Anders Gjermshus







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

Re: Need help with a query

[Roger Baklund]

* Soheil Shaghaghi
 Hello everyone,
 I need help with MySQL coding in php please if anyone can help.

I can try. :)

 I have 3 tables:
 -users, where the user info is stored.
 -awards: contains the list of all the awards for each user
 -award_types: contains different types of award
 The tables are at the bottom of the page.

 What I need to do is look at these tables when a user id is being
 viewed and display the awards image that the user has won.
 A user can have multiple awards.

Ok... and what is the problem?

The SQL could be something like this:

SELECT award_type, award_image
  FROM award_types,awards
  WHERE
award_types.id = awards.award_id AND
awards.chosen = 'enabled' AND
awards.user_id = $userid


(Not sure about the chosen = 'enabled', just looked like that was what you
wanted from your example data.)

--
Roger


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

Need help with a query

[Soheil Shaghaghi]

Hello everyone,
I need help with MySQL coding in php please if anyone can help.


I have 3 tables:
-users, where the user info is stored.
-awards: contains the list of all the awards for each user
-award_types: contains different types of award
The tables are at the bottom of the page.

What I need to do is look at these tables when a user id is being viewed and
display the awards image that the user has won.
A user can have multiple awards.


CREATE TABLE `award_types` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `award_type` varchar(255) NOT NULL default '',
  `award_image` varchar(250) default NULL,
  `gender` enum('m','f') NOT NULL default 'm',
  `order_by` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`id`)
)

#
# Dumping data for table `award_types`
#

INSERT INTO `award_types` VALUES (1, 'November 2003', nov.gif, 'm', 0);
INSERT INTO `award_types` VALUES (2, 'December 2003', dec.gif, 'm', 1);
INSERT INTO `award_types` VALUES (3, 'January 2004', jan.gif, 'm', 2);
INSERT INTO `award_types` VALUES (4, 'February 2004', feb.gif, 'm', 3);


CREATE TABLE `awards` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL default '0',
  `award_id` bigint(20) NOT NULL default '0',
  `chosen` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `id` (`id`),
  KEY `chosen` (`chosen`)
)

#
# Dumping data for table `awards`
#

INSERT INTO `awards` VALUES (1, 1, 1, 'enabled');
INSERT INTO `awards` VALUES (3, 1, 2, 'enabled');


CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `username` varchar(16) NOT NULL default '',
  `password` varchar(16) NOT NULL default '',
  `hint` varchar(100) NOT NULL default '',
  `realname` varchar(48) NOT NULL default '',
  `description` text NOT NULL,
  `age` tinyint(2) unsigned NOT NULL default '0',
  `user_type` tinyint(3) unsigned NOT NULL default '0',
  `state` varchar(32) NOT NULL default '',
  `country` varchar(32) NOT NULL default 'United_States.gif',
  `email` varchar(48) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `quote` varchar(255) NOT NULL default '',
  `image` enum('here','there') NOT NULL default 'there',
  `image_url` varchar(144) NOT NULL default '',
  `image_ext` varchar(4) NOT NULL default '',
  `image_status` enum('enabled','disabled','queued','approved') NOT NULL
default 'enabled',
  `total_comments` int(10) unsigned NOT NULL default '0',
  `subscribed` enum('yes','no') NOT NULL default 'yes',
  `md5key` varchar(32) NOT NULL default '',
  `signup` varchar(14) NOT NULL default '',
  `timestamp` timestamp(14) NOT NULL,
  `is_approved` enum('0','1') NOT NULL default '1',
  `total_files` smallint(6) NOT NULL default '0',
  `last_logged` datetime NOT NULL default '-00-00 00:00:00',
  `city` varchar(32) NOT NULL default '0',
  `address` varchar(64) default NULL,
  `zip` varchar(32) default NULL,
  `talent` varchar(32) default NULL,
  `phone` varchar(32) default NULL,
  `height` varchar(10) default NULL,
  `weight` varchar(10) default NULL,
  `education` varchar(32) default NULL,
  `hobby` varchar(32) default NULL,
  `topregion` enum('enabled','disabled') NOT NULL default 'disabled',
  `top30` enum('enabled','disabled') NOT NULL default 'disabled',
  `top10` enum('enabled','disabled') NOT NULL default 'disabled',
  `top` enum('enabled','disabled') NOT NULL default 'disabled',
  `total_ratings` smallint(5) unsigned default '1',
  `total_points` mediumint(9) unsigned default '10',
  `average_rating` decimal(6,4) default '10.',
  `art` text,
  `language` text,
  `sport` text,
  `skill` text,
  `experience` text,
  `club` text,
  `lastname` varchar(250) default NULL,
  `secondid` varchar(32) default NULL,
  `chosens` enum('enabled','disabled') default 'disabled',
  `email_status` enum('enabled','disabled','queued','approved') NOT NULL
default 'enabled',
  `chosen` varchar(20) NOT NULL default 'enabled',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `md5key` (`md5key`),
  UNIQUE KEY `username_2` (`username`),
  UNIQUE KEY `email_2` (`email`),
  KEY `sex` (`user_type`),
  KEY `timestamp` (`timestamp`),
  KEY `signup` (`signup`),
  KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `subscribed` (`subscribed`)
)

#
# Dumping data for table `users`
#

INSERT INTO `users` VALUES (1, 'username', 'password', 'firstname',
'lastname', '', 19, 11, 'City', 'Sweden.gif', '[EMAIL PROTECTED]',
'http://', '', 'here', '', 'jpg', 'approved', 0, 'yes',
'69f404925df883e0e5579d65b7768e7c', '20031007135005', 20031102044856, '1',
1, '2003-10-11 03:20:19', 'Stockholm', '', '', '', '', '176', '60', '2', '',
'disabled', 'disabled', 'disabled', 'disabled', 1717, 5410, '3.1508', NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'enabled', 'enabled', 'enabled');




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

Need help with a query..

[Tibby]

Hi ppl,

I have already tried the 'rtfm', but it just didn't help.

I have a table:

+-++--+
| key  | desc| value |
+-++--+
|   1   | book|   4 |
|   2   | book|   7 |
|   3   | book|   5 |
|   4   | pen |   4 |
|   5   | pen |   5 |
|   6   | pen |   7 |
+-++--+

..and I want to get this with a single query:

+-++--+
| key  | desc| value |
+-++--+
|   2   | book|   7 |
|   6   | pen |   7 |
+-++--+

I need to get only one row from col. DESC, the one with the highest VALUE.
With one query...

Appreciate any suggestions, really.

Tibby

Re: Need help with a query..

[Chuck Gadd]

Tibby wrote:

..and I want to get this with a single query:

+-++--+
| key  | desc| value |
+-++--+
|   2   | book|   7 |
|   6   | pen |   7 |
+-++--+
I need to get only one row from col. DESC, the one with the highest VALUE.
With one query...


select `desc`, max(value) from mytable
group by `desc`




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

Re: Need help with a query..

[Aleksandar Bradaric]

Hi,

 I have already tried the 'rtfm', but it just didn't help.

But it's right there :)

  3.5.2 The Row Holding the Maximum of a Certain Column

 ..and I want to get this with a single query:

 +-++--+
 | key  | desc| value |
 +-++--+
 |   2   | book|   7 |
 |   6   | pen |   7 |
 +-++--+

select key, desc, value
from your_table t1
where value = (select max(value) from your_table where desc = t1.desc)


Take care,
Aleksandar


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

Re[2]: Need help with a query..

[Aleksandar Bradaric]

Hi,

 select key, desc, value
 from your_table t1
 where value = (select max(value) from your_table where desc = t1.desc)

 Anyway, when i execute this query, i get an error near 'select
 max(value)'... :(

It's  because the subselects are supported from version 4.1.
If  you use older MySQL version then it's not possible to do
it with a single query :(


Take care,
Aleksandar



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

Re: Need help with a query..

[Roger Baklund]

* Aleksandar Bradaric 
  select key, desc, value
  from your_table t1
  where value = (select max(value) from your_table where desc = t1.desc)
 
  Anyway, when i execute this query, i get an error near 'select
  max(value)'... :(
 
 It's  because the subselects are supported from version 4.1.

Yes.

 If  you use older MySQL version then it's not possible to do
 it with a single query :(

Yes, it is. :)

URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html 

See the MAX-CONCAT trick.

-- 
Roger



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

Re: Need Help With MySQL Query

[Michael T. Babcock]

Veysel Harun Sahin wrote:


select vanNumber, sum(grossPay) from usertableDaily group by vanNumber; 


The above is the correct query, to save yourself some time.  As for your 
problem:

But when I execute I get this:
 Resource id#3
 Resource id#4




This means you're using a resource response from a query, not the data 
in the query.  Don't forget to do a mysql_fetch_array or mysql_fetch_row 
on the resource before using it.

As a test:

$res = mysql_query(...);
print $res;
while ($row = mysql_fetch_array($res)) {
   print $row;
   print $row['id'];
}

--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



-
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

Need Help With MySQL Query

[Guru Geek]

hello,

I've performed searches on this site and php.net to try and figure out
why this is occuring.  I can't find any instance in my searches that
helped me.  So, I'm posting my very first question to this list

Here's my table ($usertableDaily):

vanNumber|grossPay
-
  | 1000
  | 500
  |100
  |100

Here's my query:
SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber =
$vanList[$count]

The query appears inside a loop hence the $vanList[$count] variable.  I
have a txt file that contains the van numbers.  I read that file and
then run through the loop and for each van number I execute the above
statement.  Yes, I've trimmed off any extra blank spaces before and
after the 'read' van numbers...

I want to SUM the grossPay for each van number.  Here's what it should
be:
 $1500
 $200

But when I execute I get this:
 Resource id#3
 Resource id#4

Anyone got any idea why this is happening?

Thanks in advance,
Roger



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

[Veysel Harun Sahin]

select vanNumber, sum(grossPay) from usertableDaily group by vanNumber;

[EMAIL PROTECTED] wrote:


hello,

I've performed searches on this site and php.net to try and figure out
why this is occuring.  I can't find any instance in my searches that
helped me.  So, I'm posting my very first question to this list

Here's my table ($usertableDaily):

vanNumber|grossPay
-
  | 1000
  | 500
  |100
  |100

Here's my query:
SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber =
$vanList[$count]

The query appears inside a loop hence the $vanList[$count] variable.  I
have a txt file that contains the van numbers.  I read that file and
then run through the loop and for each van number I execute the above
statement.  Yes, I've trimmed off any extra blank spaces before and
after the 'read' van numbers...

I want to SUM the grossPay for each van number.  Here's what it should
be:
 $1500
 $200

But when I execute I get this:
 Resource id#3
 Resource id#4

Anyone got any idea why this is happening?

Thanks in advance,
Roger



-
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

 


--

Veysel Harun Sahin
[EMAIL PROTECTED]




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

[Simon Windsor]

Hi

Interesting problem, normally to get a total by type, you would have a query 
like, 

select van, sum(pay) from ($usertableDaily) group by van;

However you are individually quering each total, your approach is correct, but 
slower.

The problem you have though is the return of

 Resource id#3
 Resource id#4

So, to begin 

- What version of MySQL
- What OS
- What table type
- Have you checked the table for corruption

I would guess that you have some sort of table corruption. Please try 

repair table ($usertableDaily)

and if that does fix it, email back with the rest of the data.

All the best

Simon

On Saturday 15 February 2003 10:09 am, Guru Geek wrote:
 hello,

 I've performed searches on this site and php.net to try and figure out
 why this is occuring.  I can't find any instance in my searches that
 helped me.  So, I'm posting my very first question to this list

 Here's my table ($usertableDaily):

 vanNumber|grossPay
 -
   | 1000
   | 500
   |100
   |100

 Here's my query:
 SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber =
 $vanList[$count]

 The query appears inside a loop hence the $vanList[$count] variable.  I
 have a txt file that contains the van numbers.  I read that file and
 then run through the loop and for each van number I execute the above
 statement.  Yes, I've trimmed off any extra blank spaces before and
 after the 'read' van numbers...

 I want to SUM the grossPay for each van number.  Here's what it should
 be:
  $1500
  $200

 But when I execute I get this:
  Resource id#3
  Resource id#4

 Anyone got any idea why this is happening?

 Thanks in advance,
 Roger



 -
 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

-- 
Simon Windsor
Email: [EMAIL PROTECTED]
Tel: 01454 617689
Mob: 07720 447385


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

[Jerry]

Are  you referencing the result set correctly in php ?

How are you dealing with what MySQL returns ?

 Looks like the info is there, you just not getting it out of the result
set.


 -
 Jerry @
 MetalCat.Net
 -

- Original Message -
From: Guru Geek [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 15, 2003 10:09 AM
Subject: Need Help With MySQL Query


 hello,

 I've performed searches on this site and php.net to try and figure out
 why this is occuring.  I can't find any instance in my searches that
 helped me.  So, I'm posting my very first question to this list

 Here's my table ($usertableDaily):

 vanNumber|grossPay
 -
   | 1000
   | 500
   |100
   |100

 Here's my query:
 SELECT SUM(grossPay) FROM $usertableDaily WHERE vanNumber =
 $vanList[$count]

 The query appears inside a loop hence the $vanList[$count] variable.  I
 have a txt file that contains the van numbers.  I read that file and
 then run through the loop and for each van number I execute the above
 statement.  Yes, I've trimmed off any extra blank spaces before and
 after the 'read' van numbers...

 I want to SUM the grossPay for each van number.  Here's what it should
 be:
  $1500
  $200

 But when I execute I get this:
  Resource id#3
  Resource id#4

 Anyone got any idea why this is happening?

 Thanks in advance,
 Roger



 -
 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

Need help with a query, joins, etc.

[Chad Day]

I had a query that was working fine before for a
sports project .. basically, 3 tables ..  one stored
goal data, one stored team data, and the other player
data.  To get all the scoring data (player names,
jersey #'s), I left joined to the player table 3
different times (on GOAL, ASSIST1, and ASSIST2).

My problem now is, I've split the player information
up into 2 different tables for other purposes, and
need to pull this information from both, but I would
seemingly have to double the number of left joins to
do it, and I'm not sure if that's even possible now.

The query (broken)

select teams.TEAMID, teams.TEAMNAME, goals.GOALID,
goals.SCORER as GSID, goals.ASSIST1 as GA1ID,
goals.ASSIST2 as GA2ID, goals.PERIOD, goals.TIME,
goals.TYPE, p1.JERSEYNUM as SCORERNUM,
ol_g_u_1.FIRSTNAME as SCORERF, ol_g_u_1.LASTNAME as
SCORERL, p2.JERSEYNUM as ASSIST1NUM,
ol_g_u_2.FIRSTNAME as ASSIST1F, ol_g_u_2.LASTNAME as
ASSIST1L, p3.JERSEYNUM as ASSIST2NUM,
ol_g_u_3.FIRSTNAME as ASSIST2F, ol_g_u_3.LASTNAME as
ASSIST2L FROM ol_global.users, ol_icehockey.teams,
ol_icehockey.goals LEFT JOIN ol_icehockey.players as
p1 ON goals.SCORER = p1.USERNUM LEFT JOIN
ol_icehockey.players as p2 ON goals.ASSIST1 =
p2.USERNUM LEFT JOIN ol_icehockey.players as p3 ON
goals.ASSIST2 = p3.USERNUM where GAMEID = '$GAMEID'
AND teams.TEAMID = '$TEAMID' AND teams.TEAMID =
p1.TEAMID order by PERIOD, TIME DESC


Now, this is obviously broken because ol_g_u_* aren't
defined yet (would be left joins to the users table 3
times) .. due to the situation, I'm not sure if I'm
even using the right kind of joins anymore.  Can
someone offer some help or point me in the right
direction on adding those other joins in there?

Thanks,
Chad




__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.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

Need help optimizing a query

[david]

I need to optimize the following query:

SELECT user, sum(in), sum(out) FROM stats WHERE from='20020205'
AND to='20020206' GROUP BY user;

In,out are bigint, user is varcher(20) and from,to are timestamp

And I want to speed up this type of query, but I have not been able to
to get it to use an index, can anyone please tell me how to optimize
this.

- David Darville

-
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: Need help with a query ...

[Anvar Hussain K.M.]

Hi John Kelly,

This is not a perfect solution but may be useful to you. But still with two 
queries!
 From Mysql prompt issue these two queries.

SELECT  @maxcat := max(Category) FROM table
WHERE category = 'Sports:Football:Players' OR
category = 'Sports:Football' OR category = 'Sports';

Select * from table where category = @maxcat;

Note that abc is greater than ab
This will not be affected by the depth of category.
Anvar.

At 06:15 PM 02/11/2001 -0800, you wrote:
Hi, I have a MySQL table with a column that contains some of a web site
directory's category names in the format:

Sports:Football:Players

I am trying to build a query that that locates all records that match the
above category name OR if none exist its parent Sports:Football OR if none
exist its parent Sports. The top level category, in this case Sports,
will always have at least one matching record.

I know I can do this with multiple queries by checking the previous query's
result, but I am trying to build a query that does it all in one lookup to
avoid lots of lookups in deep categories. Something along the logical lines
of ...

SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE
category = 'Sports:Football' OR IF NONE category = 'Sports'

... of course the above query does not work but if anyone knows of how to
accomplish something similar in one query I would much appreciate it.

Thanks!



-
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

Need help with a query ...

[John Kelly]

Hi, I have a MySQL table with a column that contains some of a web site
directory's category names in the format:

Sports:Football:Players

I am trying to build a query that that locates all records that match the
above category name OR if none exist its parent Sports:Football OR if none
exist its parent Sports. The top level category, in this case Sports,
will always have at least one matching record.

I know I can do this with multiple queries by checking the previous query's
result, but I am trying to build a query that does it all in one lookup to
avoid lots of lookups in deep categories. Something along the logical lines
of ...

SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE
category = 'Sports:Football' OR IF NONE category = 'Sports'

... of course the above query does not work but if anyone knows of how to
accomplish something similar in one query I would much appreciate it.

Thanks!



-
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: Need help with a query ...

[Michael]

You need to use the LIKE command I think. The MySQL manual should give you
all the details. If you need further help I can just write the whole query
out for you. Or was the problem that you only want to worry about the
if..then stuff if needed? If so could you tell what your pulling back from
that table?

The principal mark of genius is not perfection but originality, the
opening of new frontiers.
   -- Arthur Koestler

*^*^*^*
Michael McGlothlin [EMAIL PROTECTED]
http://mlug.missouri.edu/~mogmios/projects/

On Fri, 2 Nov 2001, John Kelly wrote:

 Hi, I have a MySQL table with a column that contains some of a web site
 directory's category names in the format:
 
 Sports:Football:Players
 
 I am trying to build a query that that locates all records that match the
 above category name OR if none exist its parent Sports:Football OR if none
 exist its parent Sports. The top level category, in this case Sports,
 will always have at least one matching record.
 
 I know I can do this with multiple queries by checking the previous query's
 result, but I am trying to build a query that does it all in one lookup to
 avoid lots of lookups in deep categories. Something along the logical lines
 of ...
 
 SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE
 category = 'Sports:Football' OR IF NONE category = 'Sports'
 
 ... of course the above query does not work but if anyone knows of how to
 accomplish something similar in one query I would much appreciate it.
 
 Thanks!
 
 
 
 -
 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: Need help with a query ...

[John Kelly]

Hi, thanks for your response.  I don't see how the LIKE command would help
me pull records that matched the category name or its parent category if
none exist, or its parent category if none exists. Perhaps you could provide
a example. I want something that follows this logic ...

SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE
category = 'Sports:Football' OR IF NONE category = 'Sports'

Regarding the purpose of this query, it will be performed in every
page/category in a web site directory looking to see if an advertisement is
available for the current category, if not, it needs to look for one
assigned to the parent category, so on and so on until it reaches the top
level category for which there will always be an ad.

Any suggestions/examples appreciated!

John

- Original Message -
From: Michael [EMAIL PROTECTED]
To: John Kelly [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, November 02, 2001 7:09 PM
Subject: Re: Need help with a query ...


 You need to use the LIKE command I think. The MySQL manual should give you
 all the details. If you need further help I can just write the whole query
 out for you. Or was the problem that you only want to worry about the
 if..then stuff if needed? If so could you tell what your pulling back from
 that table?

 The principal mark of genius is not perfection but originality, the
 opening of new frontiers.
-- Arthur Koestler

 *^*^*^*
 Michael McGlothlin [EMAIL PROTECTED]
 http://mlug.missouri.edu/~mogmios/projects/

 On Fri, 2 Nov 2001, John Kelly wrote:

  Hi, I have a MySQL table with a column that contains some of a web site
  directory's category names in the format:
 
  Sports:Football:Players
 
  I am trying to build a query that that locates all records that match
the
  above category name OR if none exist its parent Sports:Football OR if
none
  exist its parent Sports. The top level category, in this case
Sports,
  will always have at least one matching record.
 
  I know I can do this with multiple queries by checking the previous
query's
  result, but I am trying to build a query that does it all in one lookup
to
  avoid lots of lookups in deep categories. Something along the logical
lines
  of ...
 
  SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF
NONE
  category = 'Sports:Football' OR IF NONE category = 'Sports'
 
  ... of course the above query does not work but if anyone knows of how
to
  accomplish something similar in one query I would much appreciate it.
 
  Thanks!
 
 
 
  -
  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: Need help with a query ...

[Bob Hall]

On Fri, Nov 02, 2001 at 06:15:08PM -0800, John Kelly wrote:
 Hi, I have a MySQL table with a column that contains some of a web site
 directory's category names in the format:
 
 Sports:Football:Players
 
 I am trying to build a query that that locates all records that match the
 above category name OR if none exist its parent Sports:Football OR if none
 exist its parent Sports. The top level category, in this case Sports,
 will always have at least one matching record.
 
 I know I can do this with multiple queries by checking the previous query's
 result, but I am trying to build a query that does it all in one lookup to
 avoid lots of lookups in deep categories. Something along the logical lines
 of ...
 
 SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE
 category = 'Sports:Football' OR IF NONE category = 'Sports'
 
 ... of course the above query does not work but if anyone knows of how to
 accomplish something similar in one query I would much appreciate it.

Sir, the problem is that relational databases use set logic, and you 
are trying to find a solution in sequential logic.

You need three tables. I'll call them Cat1, Cat2, and Cat3, but you 
should rename them to whatever makes sense to you. They will hold, 
respectively, 'Sports', 'Football', and 'Players'. (Or 'Business', 
'Industry', and 'Firms'. Or whatever.)

The table structure would be something like:
Cat1(Cat1ID, Category)
Cat2(Cat1ID, Cat2ID, Category)
Cat3(Cat2ID, Cat3ID, Category)

A sample row in Cat1 would be (1, 'Sports')
A sample row in Cat2 would be (1, 1, 'Football')
A sample row in Cat3 would be (1, 1, 'Players')

(If you're not concerned about economizing on storage, you could
eliminate the IDs and have each child record refer to the parent 
category instead of the parent ID. In that case, you don't need 
the Cat1 table, which serves only to match a category with its
ID.)

The query would be
SELECT Cat1.Category, Cat2.Category, Cat3.Category
FROM (Cat1 LEFT JOIN Cat2 ON Cat1.Cat1ID = Cat2.Cat1ID)
 LEFT JOIN Cat3 ON Cat2.Cat2ID = Cat3.Cat2ID
WHERE Cat1.Category = 'Sports' 
  AND (Cat2.Category = 'Football' OR Cat2.Category IS NULL) 
  AND (Cat3.Category = 'Players' OR Cat3.Category IS NULL);

If you want the output in the 'Sports:Football:Players' form, you 
can rewrite the SELECT clause as
SELECT Concat(Cat1.Category, 
  If(IsNull(Cat2.Category), '', ':'), Cat2.Category,
  If(IsNull(Cat3.Category), '', ':'), Cat3.Category)

Disclaimer: I haven't run this, so I might have gotten some of the 
details wrong.

Bob Hall

-
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