Re: Select question

[Ralf Hüsing]

Matthew Stuart schrieb:

 I've got this statement to select the last two entries in my db:

 SELECT top 2 *
 FROM Content
 ORDER BY ContentID desc

 and it works fine because it selects the last two items entered into 
the db. However, I only want to be able to select item 2 rather than 
both 1 and 2. How do I do that?


Hi Mat,

TOP 2 is not MySQL?

However, MySQL knows LIMIT [1] which is more powerful, try:

  SELECT *
  FROM Content
  ORDER BY ContentID DESC
  LIMIT 1,1

regards
  -Ralf

[1]: http://dev.mysql.com/doc/refman/5.0/en/select.html


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

RE: Select question

[Jerry Schwartz]

Regards,

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

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

 -Original Message-
 From: Matthew Stuart [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 25, 2007 6:55 AM
 To: MySQL email support
 Subject: Select question

 I've got this statement to select the last two entries in my db:

 SELECT top 2 *
 FROM Content
 ORDER BY ContentID desc

 and it works fine because it selects the last two items entered into
 the db. However, I only want to be able to select item 2 rather than
 both 1 and 2. How do I do that?

 Thanks

 Mat


[JS] What does adding LIMIT 2,1 do? I'm not sure what SELECT top 2 *
means.

Regards,

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

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com





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

Re: Select question

[Peter Brawley]

Erich,

Is there a way to set a prefix for each table so that
the results come out like tablename.column?

Use a scripting or application language to automate  parameterise query 
generation. SQL is just a partial computing language.


PB

-

Erich C. Beyrent wrote:

I have three tables, all of which have a 'name' column.

If I do:

select table1.*, table2.*, table3.* from 

I'll end up with a result set that has three 'name' fields, but no way 
to distinguish which table the field belongs to.


I know I can select individual columns like:

select table1.name as foo, table2.name as bar ...

but I need all the columns from each table and that will be very 
tedious.  Is there a way to set a prefix for each table so that the 
results come out like tablename.column?


-Erich-



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

Re: SELECT question - query for records over a series of dates

[Dan Tappin]

I think you might be one to something here... is there such a thing  
as a while loop in MySQL?  i.e. can I fill a table with data via a  
MySQL query?  I guess I could do it via PHP...


I could create a temp table with one column of dates for the range I  
am looking for and then LEFT JOIN my log table and match the dates.


Having a dedicated table would work but would be kind of a waste of  
space / resources.  These queries will not be run that often.


Dan T

On Aug 16, 2005, at 10:26 PM, Michael Stassen wrote:


Add a table:

  CREATE TABLE `dates` (`date` DATE,
 UNIQUE KEY `date_idx` (`date`)
   );

Insert one row into dates for each day.  Now you can use something  
like this:


  SELECT dates.date, COUNT(*) as hits
  FROM dates
  LEFT JOIN table on dates.date = DATE(table.date_impression)
  WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16'
  GROUP BY dates.date;

Populating the dates table initially is a small (one-time) pain.   
You could keep it filled with a once-a-day script to insert the  
current date.


Michael




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

Re: SELECT question - query for records over a series of dates

[Michael Stassen]

Dan Tappin wrote:
I think you might be one to something here... is there such a thing  as 
a while loop in MySQL?  i.e. can I fill a table with data via a  MySQL 
query?  I guess I could do it via PHP...


I could create a temp table with one column of dates for the range I  am 
looking for and then LEFT JOIN my log table and match the dates.


Having a dedicated table would work but would be kind of a waste of  
space / resources.  These queries will not be run that often.


Dan T


No while loop, but this can be done in mysql, so long as you already have a 
table with enough rows.  For example, to create and fill a dates table, 
starting with 1995-01-01 and ending with 2005-12-31:


  # create the table with 2 extra columns, one of which is auto_increment:
  CREATE TABLE dates (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  date DATE,
  junk INT,
  UNIQUE date_idx (date)
 );

  # add enough rows to the table to cover the desired date range:
  INSERT INTO dates (junk) SELECT id FROM big_table LIMIT 4018;

  # use the auto_increment generated ids as offsets from the start
  # date to fill the date column:
  UPDATE DATES SET date = '1994-12-31' + INTERVAL id DAY;

  # drop the now useless, extra columns:
  ALTER TABLE dates
   DROP COLUMN id,
   DROP COLUMN junk;

Voila, dates has one row for each day from 1995-01-01 to 2005-12-31.

This example MyISAM table with 10 years worth of rows in it takes up 63,461 
bytes on my disk, including the index.  If that's a waste of space / 
resources, I think it's time to buy more disk.  You certainly could create 
such a table on the fly, with just the rows you need, as a temporary table, 
but that will be relatively slow compared to simply using a pre-existing, 
dedicated table to satisfy your queries.  Besides, at that small size, I 
don't really see the downside.


Michael

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

Re: SELECT question - query for records over a series of dates

[Daniel Kasak]

Dan Tappin wrote:

I have a table full of data... a log of sorts.  Each row has a  
timestamp.


I want to generate some reports based on this data.

For example I want a COUNT(*) of the rows for each day for the past  
week, 30 days, 12 months etc.


I have no problem generating the query but I am stuck on a creative  
way to deal with the periods with no data.  For example:


SELECT COUNT(*) as hits,
DATE(date_impression) as date
FROM
table
GROUP BY date

+---+-+
|hits   |date |
+---+-+
|39 | 2005-08-12  |
|27 | 2005-08-13  |
|38 | 2005-08-15  |
|28 | 2005-08-16  |
+---+-+

Now the problem is that there could be days with no data (the 14th in  
my example).  Ideally I want to show the last 7 days (or what ever  
period I want) and show the COUNT(*) including the days with no data  
like...


+---+-+
|hits   |date |
+---+-+
|39 | 2005-08-12  |
|27 | 2005-08-13  |
| 0 | 2005-08-14  |
|38 | 2005-08-15  |
|28 | 2005-08-16  |
+---+-+

Now I can manipulate the data afterwards (i.e. look for empty rows  
via PHP where this is going to end up...) but it would be much easier  
to get the data direct from MySQL.  Is there any (easy) way to do  
this in MySQL?


Dan T


Not that I know of.

When I have to do things like this, I write a script which does a 
separate query per day. Unfortunately PHP's date functions are pretty 
horrible, so I also use mysql as a calculator to find the next day in my 
loop. This is terribly inefficient, but very easy, and our server is 
high powered and under no load :) Maybe someone has a better idea though.


--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

Re: SELECT question - query for records over a series of dates

[Michael Stassen]

Dan Tappin wrote:

I have a table full of data... a log of sorts.  Each row has a  timestamp.

I want to generate some reports based on this data.

For example I want a COUNT(*) of the rows for each day for the past  
week, 30 days, 12 months etc.


I have no problem generating the query but I am stuck on a creative  way 
to deal with the periods with no data.  For example:


SELECT COUNT(*) as hits,
DATE(date_impression) as date
FROM
table
GROUP BY date

+---+-+
|hits   |date |
+---+-+
|39 | 2005-08-12  |
|27 | 2005-08-13  |
|38 | 2005-08-15  |
|28 | 2005-08-16  |
+---+-+

Now the problem is that there could be days with no data (the 14th in  
my example).  Ideally I want to show the last 7 days (or what ever  
period I want) and show the COUNT(*) including the days with no data  
like...


+---+-+
|hits   |date |
+---+-+
|39 | 2005-08-12  |
|27 | 2005-08-13  |
| 0 | 2005-08-14  |
|38 | 2005-08-15  |
|28 | 2005-08-16  |
+---+-+

Now I can manipulate the data afterwards (i.e. look for empty rows  via 
PHP where this is going to end up...) but it would be much easier  to 
get the data direct from MySQL.  Is there any (easy) way to do  this in 
MySQL?


Dan T


Add a table:

  CREATE TABLE `dates` (`date` DATE,
 UNIQUE KEY `date_idx` (`date`)
   );

Insert one row into dates for each day.  Now you can use something like this:

  SELECT dates.date, COUNT(*) as hits
  FROM dates
  LEFT JOIN table on dates.date = DATE(table.date_impression)
  WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16'
  GROUP BY dates.date;

Populating the dates table initially is a small (one-time) pain.  You could 
keep it filled with a once-a-day script to insert the current date.


Michael

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

RE: SELECT question

[Jay Blanchard]

[snip]
I know to most of you this will seem like a mundane
question, but I was hoping someone can tell me how to
select the last record in a table that meets certain
criteria. Like to see who hosted the last party in
CHicago.

SELECT host FROM PARTY
WHERE city=chicago;

PARTY
ID |host | city | st |
237|1256 | Chicago  | IL |
244|945  | Chicago  | IL |
355|2987 | Boston   | MA |

I need a query that would give me the one with highest
ID i.e. host no. 945? 
[/snip]

SELECT host 
FROM party
WHERE city = 'Chicago'
ORDER BY ID DESC
LIMIT 1

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

Re: SELECT question

[DreamWerx]

ORDER BY host DESC LIMIT 1


On Wed, 29 Dec 2004 14:18:02 -0800 (PST), Richard Reina
[EMAIL PROTECTED] wrote:
 I know to most of you this will seem like a mundane
 question, but I was hoping someone can tell me how to
 select the last record in a table that meets certain
 criteria. Like to see who hosted the last party in
 CHicago.
 
 SELECT host FROM PARTY
 WHERE city=chicago;
 
 PARTY
 ID |host | city | st |
 237|1256 | Chicago  | IL |
 244|945  | Chicago  | IL |
 355|2987 | Boston   | MA |
 
 I need a query that would give me the one with highest
 ID i.e. host no. 945?
 
 Thanks for any help.
 
 Richard
 
 --
 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: Select Question

[Michael Stassen]

If you have mysql 4.1, you can use GROUP_CONCAT().
  SELECT id, GROUP_CONCAT(f2) FROM yourtable GROUP BY id;
See the manual for details 
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html.

Michael
Feghhi, Jalil wrote:
Is there a way to convert the following result set:
id  f2
--- ---
1   b
1   c
to:
id  f2
--  --
1   b,c
Using a select or any other functions? Basically, I want to put f2 fields together 
when ids are the same.
Thanks,
-Jalil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select Question

[SGreen]

Check out the GROUP_CONCAT() function, see if this help:

http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Feghhi, Jalil [EMAIL PROTECTED] wrote on 10/05/2004 11:20:21 AM:

 
 Is there a way to convert the following result set:
 
 id f2
 ---   ---
 1  b
 1  c
 
 to:
 
 id  f2
 --  --
 1  b,c
 
 Using a select or any other functions? Basically, I want to put f2 
 fields together when ids are the same.
 
 Thanks,
 
 -Jalil
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: Select Question

[Giulio]

If your MySQL version is 4.1 or later,
you could try GROUP_CONCAT
the query should be ( not tested ):
select id,GROUP_CONCAT(f2) from tablename group by id
regards,
 Giulio
Il giorno 05/ott/04, alle 17:20, Feghhi, Jalil ha scritto:
Is there a way to convert the following result set:
id  f2
--- ---
1   b
1   c
to:
id  f2
--  --
1   b,c
Using a select or any other functions? Basically, I want to put f2 
fields together when ids are the same.

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


Cantoberon Multimedia srl
http://www.cantoberon.it
Tel. 06 39737052
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT question

[Andre Matos]

Hi Roger,

I took the suggestion from Brad Eacker and use BETWEEN and now works without
problem. However, I decided to do a couple more tests and what I found was
that the problem occurs on MySQL version 4.0.18-standard using InnoDB on
Linux but does not occur on Mac OS X using the same MySQL version.

Andre


On 9/20/04 5:52 PM, Roger Baklund [EMAIL PROTECTED] wrote:

 * Andre Matos
 I am performing a SELECT and I am getting 0 rows when I run the SELECT
 direct in the MySQL database and getting 1 when I run using PHP.
 This is my
 select:
 
 SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND
 (ScanStatusID  90 OR ScanStatusID  98);
 
 Looks ok.
 
 I realized latter analyzing this select that I made a mistake using OR at
 this point: (ScanStatusID  90 OR ScanStatusID  98), it should be AND.
 
 Are you sure about that?
 
 However, in both cases, I am still getting 0 rows from the database, which
 is correct.
 
 My problem is using the PHP to run the SELECT, if I use OR using
 the PHP, I
 got 1 as a result, and if I use AND I got 0 as a result.
 
 This is correct, if you have one record with ScanStatusID in the range
 90-98.
 
 Is anyone can tell me what is going on?
 
 You seem to be misinterpreting how logical expressions work. A SQL select
 statement is a description of the (sub-)set of data you wish to retrieve
 from the database. This description often includes a WHERE clause,
 describing wanted records, which again often includes a logical expression.
 The expression is built up by operands and operators. The logical operators
 relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes
 one operand, the the result is the opposite of the operand. NOT true is
 false, and NOT false is true. The other two operators, AND and OR, need two
 operands, one on each side. For the AND operator, BOTH sides of the operator
 must be true for this part of the expression to be true. For the OR
 operator, ANY of the sides of the operator must be true for that part of the
 expression to be true.
 
 So, for your expression above, you can not say ...ScanStatusID  90 AND
 ScanStatusID  98..., because ScanStatusID can not be below 90 AND above 98.
 ScanStatusID is a single number, it can be below 90 OR above 98. Not both at
 the same time.
 
 --
 Roger

-- 
Andre Matos
[EMAIL PROTECTED]



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

Re: SELECT question

[Roger Baklund]

* Andre Matos
 I am performing a SELECT and I am getting 0 rows when I run the SELECT
 direct in the MySQL database and getting 1 when I run using PHP.
 This is my
 select:

 SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND
 (ScanStatusID  90 OR ScanStatusID  98);

Looks ok.

 I realized latter analyzing this select that I made a mistake using OR at
 this point: (ScanStatusID  90 OR ScanStatusID  98), it should be AND.

Are you sure about that?

 However, in both cases, I am still getting 0 rows from the database, which
 is correct.

 My problem is using the PHP to run the SELECT, if I use OR using
 the PHP, I
 got 1 as a result, and if I use AND I got 0 as a result.

This is correct, if you have one record with ScanStatusID in the range
90-98.

 Is anyone can tell me what is going on?

You seem to be misinterpreting how logical expressions work. A SQL select
statement is a description of the (sub-)set of data you wish to retrieve
from the database. This description often includes a WHERE clause,
describing wanted records, which again often includes a logical expression.
The expression is built up by operands and operators. The logical operators
relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes
one operand, the the result is the opposite of the operand. NOT true is
false, and NOT false is true. The other two operators, AND and OR, need two
operands, one on each side. For the AND operator, BOTH sides of the operator
must be true for this part of the expression to be true. For the OR
operator, ANY of the sides of the operator must be true for that part of the
expression to be true.

So, for your expression above, you can not say ...ScanStatusID  90 AND
ScanStatusID  98..., because ScanStatusID can not be below 90 AND above 98.
ScanStatusID is a single number, it can be below 90 OR above 98. Not both at
the same time.

--
Roger


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

Re: SELECT question

[beacker]

Andre Matos writes:
 SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND
 (ScanStatusID  90 OR ScanStatusID  98);

 I realized latter analyzing this select that I made a mistake using OR at
 this point: (ScanStatusID  90 OR ScanStatusID  98), it should be AND.

The second rendition (ScanStatusID  90 AND ScanStatusID  98) will return
no rows every time.  Since ScanStatusID can not be less than 90 and greater
than 98 at the same time :)

 Something that I've found to improve readability of these kinds of
tests is to use BETWEEN(a, b)  And if you need to exclude a range the
use of NOT BETWEEN(a, b).  This way it becomes obvious what you are
looking for.
 Brad Eacker ([EMAIL PROTECTED])



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

RE: SELECT Question

[Barry Byrne]

 -Original Message-
 From: Jean-Pierre Schwickerath [mailto:[EMAIL PROTECTED]

 Hello Mumba, Hello Barry,

   How do I select out and filter only rows that match
   both 16 and 62 in the KEYW_ID col?  IE.  The query
   would return only 119 and 108?
 
  I'm sure this could be done more effeciently other ways, possibly with
  a sub select if available, but something like this would probably
  work:
 
  SELECT temp1.*
  FROM table_name AS temp1
  LEFT JOIN table_name AS temp2
  ON temp1.tbl_idx=temp2.tbl_idx
  WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62)
  OR (temp1.keyw_id=62 AND temp2.keyw_id=16);
 

 I'm not sure which one is more efficient but I'd do it this way:

 SELECT * FROM table
 WHERE KEYW_ID = 16 OR KEYW_ID = 62
 GROUP BY TBL_IDX
 HAVING COUNT(TBL_IDX) = 2;

Neat - but I think this would give a false positive if there were two rows
with keyw_id = 16 or with keyw_id=62. If you know for certain that the
tbl_idx and keyw_id pair are unique in the table, then all would be fine.

 - Barry


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

RE: SELECT Question

[Barry Byrne]

 -Original Message-
 From: Mumba Chucks [mailto:[EMAIL PROTECTED]


 I've been given a table to work with, and I'm not
 meant to change it:

 -
 | TABLE_NAME  | TBL_IDX | KEYW_ID |
 -
 | PROPERTIES  | 108 | 16  |
 -
 | PROPERTIES| 119 | 16  |
 -
 | PROPERTIES| 108 | 62  |
 -
 | PROPERTIES| 119 | 16  |
 -
 | PROPERTIES| 135 | 16  |
 -
 | PROPERTIES| 135 | 17  |
 -

 How do I select out and filter only rows that match
 both 16 and 62 in the KEYW_ID col?  IE.  The query
 would return only 119 and 108?

I'm sure this could be done more effeciently other ways, possibly with a sub
select if available, but something like this would probably work:

SELECT temp1.*
FROM table_name AS temp1
LEFT JOIN table_name AS temp2
ON temp1.tbl_idx=temp2.tbl_idx
WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62)
OR (temp1.keyw_id=62 AND temp2.keyw_id=16);

 - Barry


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

Re: SELECT Question

[Jean-Pierre Schwickerath]

Hello Mumba, Hello Barry, 

  How do I select out and filter only rows that match
  both 16 and 62 in the KEYW_ID col?  IE.  The query
  would return only 119 and 108?
 
 I'm sure this could be done more effeciently other ways, possibly with
 a sub select if available, but something like this would probably
 work:
 
   SELECT temp1.*
   FROM table_name AS temp1
   LEFT JOIN table_name AS temp2
   ON temp1.tbl_idx=temp2.tbl_idx
   WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62)
   OR (temp1.keyw_id=62 AND temp2.keyw_id=16);
 

I'm not sure which one is more efficient but I'd do it this way:

SELECT * FROM table 
WHERE KEYW_ID = 16 OR KEYW_ID = 62
GROUP BY TBL_IDX
HAVING COUNT(TBL_IDX) = 2;


Jean-Pierre
-- 
Powered by Linux From Scratch - http://schwicky.net/
PGP Key ID: 0xEE6F49B4 - AIM/Jabber: Schwicky - ICQ: 4690141

Nothing is impossible... Everything is relative!

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

Re: Select question

[Diana Soares]

Note that you're ordering by goals, not MAX(goals). 
That's why you're not getting the results expected.

Try:

SELECT manager.name, position, MAX(goals) as goals
FROM roster JOIN reference JOIN manager 
WHERE manager.idn=reference.idn AND reference.idp=roster.idp 
  AND position like 'F'
GROUP BY manager.name ORDER BY goals desc;


On Fri, 2003-02-07 at 19:14, C. Reeve wrote:
 Hi again,
 
 After some struggling, I have managed to get the problem below 99% working,
 the problem now is that I can't get them in descending order. Here is my
 select statement.
 
$query = select manager.name, position, MAX(goals) from roster join
 reference join manager where
manager.idn=reference.idn and reference.idp=roster.idp and position like
 'F'
GROUP BY manager.name order by goals desc;
 
 Using the example below, this is what I get:
 
 Bill 70
 John   48
 Fred   87
 
 This is what I want:
 
 Fred   87
 Bill 70
 John   48
 
 TIA
 - Original Message -
 From: C. Reeve [EMAIL PROTECTED]
 To: MySQL List [EMAIL PROTECTED]
 Sent: Friday, February 07, 2003 1:57 PM
 Subject: Select question
 
 
  Hi,
 
   I have a database with 3 names in it. In each of these names is 5
  categories  that have numbers in them. I want to be able to do a select
 and
  get the top  number from each category for each name and display them from
  most to least.  I have checked all the docs on the select statement, but
  this is escaping me  at the moment.
 
   TIA
 
   i.e
 
   This is what is in the database.
 
   Bill  3620504670
  John2630324846
  Fred8740196242
 
  This is what I want to be able to show:
 
  Fred   87
  Bill 70
  John   48
 
-- 
Diana Soares


-
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: Select question

[C. Reeve]

Hi again,

After some struggling, I have managed to get the problem below 99% working,
the problem now is that I can't get them in descending order. Here is my
select statement.

   $query = select manager.name, position, MAX(goals) from roster join
reference join manager where
   manager.idn=reference.idn and reference.idp=roster.idp and position like
'F'
   GROUP BY manager.name order by goals desc;

Using the example below, this is what I get:

Bill 70
John   48
Fred   87

This is what I want:

Fred   87
Bill 70
John   48

TIA
- Original Message -
From: C. Reeve [EMAIL PROTECTED]
To: MySQL List [EMAIL PROTECTED]
Sent: Friday, February 07, 2003 1:57 PM
Subject: Select question


 Hi,

  I have a database with 3 names in it. In each of these names is 5
 categories  that have numbers in them. I want to be able to do a select
and
 get the top  number from each category for each name and display them from
 most to least.  I have checked all the docs on the select statement, but
 this is escaping me  at the moment.

  TIA

  i.e

  This is what is in the database.

  Bill  3620504670
 John2630324846
 Fred8740196242

 This is what I want to be able to show:

 Fred   87
 Bill 70
 John   48


 SPAM bypass:  sql, query, queries, smallint




-
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: select question

[Adolfo Bello]

Use the CONCAT function

Adolfo

 -Original Message-
 From: tag [mailto:[EMAIL PROTECTED]] 
 Sent: Friday, December 06, 2002 3:57 AM
 To: [EMAIL PROTECTED]
 Subject: select question
 
 
 HI,
 I need to do a select query that can do the following:
 select * from table where col1 like hex(somestring);
 My problem is HOW do I get the % in there???
 The Mysql Server is 4.0.4 and the table has a blob field with 
 hex stored in it  Thanks Tonino
 
 
 -
 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: select question

[Roger Baklund]

* tag
 I need to do a select query that can do the following:
 select * from table where col1 like hex(somestring);

This was a bit confusing... :)

hex(somestring) will always return 0, unless the string is a numerical
value:

mysql select hex('65'),hex(65),hex(A);
+---+-+--+
| hex('65') | hex(65) | hex(A) |
+---+-+--+
| 41| 41  | 0|
+---+-+--+
1 row in set (0.00 sec)

 My problem is HOW do I get the % in there???

I think you should not use the hex() function in this case.

 The Mysql Server is 4.0.4 and the table has a blob field with hex
 stored in it 

I doubt if this is the case... hex is a representation, not a format. If you
really have hex strings stored in the table, you wouldn't need a blob,
because all characters are ascii (0-9 + a-f).

You probably have binary data stored in your blob, and you can view it using
hex representation, but you must search on the binary values, not on the hex
representation. Something like this:

  SELECT * FROM table WHERE col1 LIKE x#2\\1\_@!\æøå\0%;

You must escape the characters , ', \ and ascii 0 with a preceeding \.
(This also aplies to % and _ when you want to search for them using LIKE).
In other words, the above string is really: x#2\1_@!æøå + ascii 0 + %.

Languages supporting mysql have a special funtion for this, called
mysql_escape_string() in the C API, quote() in perl DBI.

HTH,

--
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: select question

[Roger Baklund]

* Tonino Greco
 Thanks - but I got it working :
 select * from table where col1 like concat(%, hex(somestring), %);
 the hex(somestring) - returns :736F6D65737472696E67

* me
 hex(somestring) will always return 0, unless the string is
 a numerical value

Sorry for that, this was changed in 4.0.1, hex(string) now returns a hex
representation of the string, like you said. In 3.23 it would only convert
numbers.

If col1 really contains hex strings, there is no need to use BLOB, because
hex strings are not binary, though they may _represent_ binary data. And, of
course, they take twice as much space to store, compared to the binary data.

Beware that you can get unpredictable results in some cases with the method
you describe above. For instance, if you search for the letter B, it will
be executed as ... LIKE %42%. If the actual data for a row is D A, it
will be stored as 442041, and you will get a match for %42%, even if it
does not contain the letter B.

--
Roger
sql


-
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: Select question

[Egor Egorov]

Elin,
Tuesday, September 10, 2002, 12:03:21 PM, you wrote:

ER I have a table called users with the columns firstname and lastname. I would
ER like to do a search on the fullname and have tried:

ER select * from users where (firstname + ' ' + lastname) = John Smith

ER which returns all rows for some reason and not only the rows with users
ER named John Smith (which SQL Server does). Any ideas?

Take a look at CONCAT()/CONCAT_WS() functions:
 http://www.mysql.com/doc/en/String_functions.html



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




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

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

Re: Select question

[Nicholas Stuart]

You would have to do something like:
SELECT * FROM users WHERE CONCAT(firstname,  , lastname) = John Smith
That should get you what you want.
If your taking your DB from MS SQL to MySQL only a few queries will port
directly over. You have to be careful that you follow the MySQL syntax and
functions as they are slightly different.
-Nick


 Hi,

 I have a table called users with the columns firstname and lastname. I
would
 like to do a search on the fullname and have tried:

 select * from users where (firstname + ' ' + lastname) = John Smith

 which returns all rows for some reason and not only the rows with users
 named John Smith (which SQL Server does). Any ideas?

 Kind regards, Elin




 -
 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: SELECT Question.

[BD]

At 01:22 AM 3/3/2002 , you wrote:
I need to pull a variable number of fields from a table from the last
inputted fields.  For example, instead of doing something like

  SELECT * FROM table

I am looking for a way to do something like this(hypothetical, I don't
really know what I should do).

  SELECT LAST_TEN_FIELDS FROM table

which would then pull the most recent ten rows from the table.  The reason
why I am asking this mailling list is because I am writing a small program
using PHP and want to get the ten most recent fields.  However, since it
is PHP and the script is going to have high traffic, I need to know the
most efficient way of pulling the last ten fields.  I know I can do it
through PHP by sorting out the results, but I want the whole thing to be
as streamlined as possible and don't want to have the PHP script in 4
months chugging away for hours trying to work with the data of 1000 fields
pulled from mySQL.

This is the structure I have in mind for the table, and this is not 100%
official, but was the way I had initially planned it to go.

id INT NOT NULL AUTO_INCREMENT,
data VARCHAR(200),
PRIMARY KEY(id)

The script is going to take a little bit of data, and is only going to
need to display the most recent(top ten probably) results entered, which
is why I need to know if there is an efficient way to do this in mySQL
without causing thousands of chunks of data to have to be handled by PHP.

All help is appreciated,

-Eric

Eric,
 You could try something like:

select * from table order by id desc limit 10;

The reverse sort will get you there. Just make sure the column is indexed 
to make it fast, which in your case it is. :)

Brent

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.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

Re: SELECT question.

[Scott Alexander]

On 30 May 2001, at 10:14, Paul DuBois wrote:

 At 9:41 AM -0700 5/30/01, Richard Reina wrote:
 I am stuck on a select query and was wondering if someone could help.
  I have I've written a database app. that helps me run my business
 (trucking).  I need however to write a query that shows me all of the
 loads that are delivered but not billed (invoiced).  Which means that
 I have to select the loads that are delivered but do not have an
 entry in the INVOICED table -- since an entry is made in the INVOICED
 table whenever a load is billed.
 
 I know the query below won't work.  Can someone please help me fix
 it?
 
 SELECT l.load_no l.date FROM loads l, invoiced i
 WHERE l.dlvr_date  0
 AND l.load_no != i.load_no
 
 This is a job for LEFT JOIN.
 
 SELECT l.load_no, l.date
 FROM loads l LEFT JOIN invoiced i ON l.load_no = i.load_no
 WHERE i.load_no IS NULL


Can you in sql also have FROM table_a, table_b LEFT JOIN table_c 
ON  WHERE 

For example my query is 

my $query = SELECT message.message_id, message.user, 
message.for_user, message.sent_date_time, message.subject, 
message.id
FROM subject_project, message LEFT JOIN read_message ON 
message.message_id = read_message.message_id
WHERE read_message.user = '$user' AND 
message.sent_date_time  $new_messages_from_date AND 
message.id = subject_project.id
AND subject_project.location_id = 1
AND read_message.user IS NULL ORDER BY 
message.sent_date_time ;

Until now I have just selected the messages that have been sent for 
the past 30 days. Then checking for each message with another 
query have they read the message or not. If not display it. 

The solution suggested by Paul worked fine for another part of my 
message system where I don't need to the subject_project table.

An alternative would be to keep the location_id field also in the 
message table. Any ideas are welcome.

Scott
_
scott alexander
tietoverkkosuunnittelija
humak amk - finland
+358(0)407505640

-
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: SELECT question.

[Rafal Jank]

Richard Reina wrote:
 
 I am stuck on a select query and was wondering if someone could help.  I
 have I've written a database app. that helps me run my business
 (trucking).  I need however to write a query that shows me all of the
 loads that are delivered but not billed (invoiced).  Which means that I
 have to select the loads that are delivered but do not have an entry in
 the INVOICED table -- since an entry is made in the INVOICED table
 whenever a load is billed.
 
 I know the query below won't work.  Can someone please help me fix it?
 
 SELECT l.load_no l.date FROM loads l, invoiced i
 WHERE l.dlvr_date  0
 AND l.load_no != i.load_no
 
Maybe something like this (I havent't tried this) :
select l.load_no l.date FROM loads l left join invoiced i on
(l.load_no=i.load_no) where i.load_no is null and l.dlvr_date  0 

-- 
_/_/  _/_/_/  - Rafa Jank [EMAIL PROTECTED] -
 _/  _/  _/  _/   _/ Wirtualna Polska SA   http://www.wp.pl 
  _/_/_/_/  _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625
   _/  _/  _/ ==*  http://szukaj.wp.pl *==--

-
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: SELECT question.

[Ravi Raman]

hi.
left join the two tables then check for NOT NULL in one of the fields from
invoiced that will be empty if they haven't been invoiced:

SELECT l.load_no l.date
FROM loads l LEFT JOIN invoiced i on l.load_no = i.load_no
where i.invoiced_on_this_date IS NULL

obviously, substitute a real column for invoiced_on_this_date...
HTH.

-ravi.

-Original Message-
From: Richard Reina [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 12:42 PM
To: [EMAIL PROTECTED]
Subject: SELECT question.


I am stuck on a select query and was wondering if someone could help.  I
have I've written a database app. that helps me run my business
(trucking).  I need however to write a query that shows me all of the
loads that are delivered but not billed (invoiced).  Which means that I
have to select the loads that are delivered but do not have an entry in
the INVOICED table -- since an entry is made in the INVOICED table
whenever a load is billed.

I know the query below won't work.  Can someone please help me fix it?

SELECT l.load_no l.date FROM loads l, invoiced i
WHERE l.dlvr_date  0
AND l.load_no != i.load_no


Thanks,

Richard

-
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: SELECT question.

[Mohammad Shoja]

 I am stuck on a select query and was wondering if someone could help.  I
 have I've written a database app. that helps me run my business
 (trucking).  I need however to write a query that shows me all of the
 loads that are delivered but not billed (invoiced).  Which means that I
 have to select the loads that are delivered but do not have an entry in
 the INVOICED table -- since an entry is made in the INVOICED table
 whenever a load is billed.
 
 I know the query below won't work.  Can someone please help me fix it?
 
 SELECT l.load_no l.date FROM loads l, invoiced i
 WHERE l.dlvr_date  0
 AND l.load_no != i.load_no
 
 
 Thanks,
 
 Richard

Because MySQL doesnt support subquery I think you have to breake your sql
and bring your logic into your bussiness layer.
Maybe you can find better solution but at least this can resolve your
problem.

regards
--
Mohammadreza Shojatalab  
European Bioinformatics InstituteTel: +44 (0)1223 494 669  
  
EMBL Outstation  Fax: +44 (0)1223 494 468  
  
Wellcome Trust Genome Campus E-Mail: [EMAIL PROTECTED]   
  
Hinxton, Cambridge   URL: http://www.ebi.ac.uk/~shoja  
  
CB10 1SD, UK


-
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: SELECT question.

[Paul DuBois]

At 9:41 AM -0700 5/30/01, Richard Reina wrote:
I am stuck on a select query and was wondering if someone could help.  I
have I've written a database app. that helps me run my business
(trucking).  I need however to write a query that shows me all of the
loads that are delivered but not billed (invoiced).  Which means that I
have to select the loads that are delivered but do not have an entry in
the INVOICED table -- since an entry is made in the INVOICED table
whenever a load is billed.

I know the query below won't work.  Can someone please help me fix it?

SELECT l.load_no l.date FROM loads l, invoiced i
WHERE l.dlvr_date  0
AND l.load_no != i.load_no

This is a job for LEFT JOIN.

SELECT l.load_no, l.date
FROM loads l LEFT JOIN invoiced i ON l.load_no = i.load_no
WHERE i.load_no IS NULL



Thanks,

Richard


-- 
Paul DuBois, [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: SELECT question.

[Johnson, Gregert]

SELECT l.load_no, l.date
FROM loads l
LEFT OUTER JOIN invoiced i ON l.load_no = i.load_no
WHERE l.dlvr_date  0
  AND i.load_no IS NULL;

-- Greg Johnson

-Original Message-
From:   Richard Reina [mailto:[EMAIL PROTECTED]]
Sent:   Wednesday, May 30, 2001 12:42 PM
To: [EMAIL PROTECTED]
Subject:SELECT question.

I am stuck on a select query and was wondering if someone could help.  
I
have I've written a database app. that helps me run my business
(trucking).  I need however to write a query that shows me all of the
loads that are delivered but not billed (invoiced).  Which means that I
have to select the loads that are delivered but do not have an entry in
the INVOICED table -- since an entry is made in the INVOICED table
whenever a load is billed.

I know the query below won't work.  Can someone please help me fix it?

SELECT l.load_no l.date FROM loads l, invoiced i
WHERE l.dlvr_date  0
AND l.load_no != i.load_no


Thanks,

Richard

-
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: SELECT question.

[Richard Reina]

Thank you to all who responded.  A left join was definately the answer.

Richard

Roger Karnouk wrote:
 
 Try this
 
 SELECT l.load_no l.date FROM loads l left join invoice i
 on (l.load_no = i.load_no)
 where i.load_no is null
 AND l.dlvr_date  0;
 
 -Original Message-
 From: Richard Reina [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, May 30, 2001 12:42 PM
 To: [EMAIL PROTECTED]
 Subject: SELECT question.
 
 I am stuck on a select query and was wondering if someone could help.  I
 have I've written a database app. that helps me run my business
 (trucking).  I need however to write a query that shows me all of the
 loads that are delivered but not billed (invoiced).  Which means that I
 have to select the loads that are delivered but do not have an entry in
 the INVOICED table -- since an entry is made in the INVOICED table
 whenever a load is billed.
 
 I know the query below won't work.  Can someone please help me fix it?
 
 SELECT l.load_no l.date FROM loads l, invoiced i
 WHERE l.dlvr_date  0
 AND l.load_no != i.load_no
 
 Thanks,
 
 Richard
 
 -
 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: Select Question

[John Dean]

Hi
The short answer is no, but you could get around this by using JOIN
Sub-queries are not yet supported

At 21:39 12/04/2001 -0400, James Gonthier wrote:
The following works ok in SQL Server, can it work in mySQL?

select * from users where id in (select userid from group where name='sales')


TIA,

Jim Gonthier

---
The Fastest Browser on Earth now for FREE!!
Download Opera 5 for Windows now! Get it at
http://www.opera.com/download/
---


-
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

Regards
John

-- 

MySQL Development Team
__  ___  __   __
   /  |/  /_ __/ __/ __ \/ /   John Dean [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\/ Mansfield, England, UK
___/









-
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: Select Question

[Benjamin Pflugmann]

Hi.

On Thu, Apr 12, 2001 at 09:39:46PM -0400, [EMAIL PROTECTED] wrote:
 The following works ok in SQL Server, can it work in mySQL?
 
 select * from users where id in (select userid from group where name='sales')

Sub-selects are not (yet) supported by MySQL. But you can rewrite that
query to (assuming userid is unique within one group):

SELECT  *
FROMusers u, group g
WHERE   u.id = g.userid AND
g.name = 'sales'

Please read the fine manual. This is already is explained at several
places, one of them being the tuturial.


Bye,

Benjamin.


-
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