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

[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

[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

[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

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

[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

[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

[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

[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

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

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

[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

[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

[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

[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

[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

[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

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

* 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

[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

[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

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

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

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

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

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

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

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

[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

[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