RE: basic query question

you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic,

MySQL and ASP.Net

Has anyone connected to a mysql database using ASP.NET If so can you point me in the right direction. I can't find anything on that query, Table, SQL Roger Karnouk - Before posting, please check: http://www.mysq

RE: Sum function question

It can be done but it involves quite a few statements and the use of temporary variables and a temporary table. assuming that your table is defined as follows CREATE TABLE `tble` ( `keyf` char(1) default NULL, `Field1` char(8) default NULL ) do this: set @k=''; set @field=''; create temp

RE: GROUP by multiple columns [id:fns]

SELECT a, b, c, SUM(d) FROM a GROUP BY a,b this will not work however because c isn't contained either in the group by or a function. Try either: SELECT a, b, c, SUM(d) FROM a GROUP BY a,b,c or SELECT a, b, max(c), SUM(d) FROM a GROUP BY a,b depending on your needs -Original Message-

RE: joining 2 identical tables

Depending on which version of mysql you are using you could use Union http://www.mysql.com/doc/U/N/UNION.html or you could use a Merge table http://www.mysql.com/doc/M/E/MERGE.html another option would be to place the results of both queries into a temporary table and query that. -Origina

RE: Must use '#' in fieldnames

if you just need to call your field a35#70 for retrieval purposes why don't you call it something else and alias it in your select statements. select col1 as 'a35#70' from test; if this doesn't help perhaps explain why you absolutely need your field named a35#70. it will make it easier to find

RE: selecting rows where all rows meet a criterion

Put the min clause in a having statement ex. select * from words group by count having min(count) > 50; -Original Message- From: David Hugh-Jones [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 23, 2001 4:25 PM To: Haapanen, Tom Cc: [EMAIL PROTECTED] Subject: RE: selecting rows where

Dropping Tables like . . .

Is there a way to drop all tables that match a certain pattern, kind of like using a LIKE statement in DROP statement. if this worked it would be perfect drop table like 'TT%'; in other words dropping all the tables in a database that start with 'TT&#

Altering an index?

do this the fastest way possible. I am using 3.23.34 on a windows machine Thanks Roger Karnouk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com

RE: unique problem?

I think this is more what you are looking for UPDATE shpr_rcvr SET directions = REPLACE(directions,'#','No. ' where directions = '#'; -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 5:26 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Su

RE: three-way join

create table magazine (Mag_id int primary key, Name char(30)); create table customer (cust_id int primary key, name char(30)); create table bridge (cust_id int not null, mag_id int not null, primary key(mag_id,cust_id)); insert into customer values (10,'John'),(24,'Luke'),(43,'Tony'),(25,'Ma

RE: Tighly packed table

select max(length(firstname)) from TableName; -Original Message- From: Michael Stearne [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 4:38 PM To: Christopher Thompson Cc: [EMAIL PROTECTED] Subject: Re: Tighly packed table Christopher Thompson wrote: > At 04:10 PM 1/24/2002

RE: Please Help with Pattern Matching

Try this: select schdays from courses where schdays Regexp"[MWF]"; -Original Message- From: Douglas Brantz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 2:59 PM To: [EMAIL PROTECTED] Subject: Please Help with Pattern Matching I have a big problem with pattern matching; Why

converting bigint to int and keeping last bit

Bigint int 2147483648 should become -2147483648 2147483649 should become -2147483647 Is there an easy way to do this if not does anyone know an update query that I could run to convert the numbers before I do the alter? Roger Karnouk

Viewing data from the previous row

120 120 2 60 180 3 125305 4 40 345 I'd like to be able to do this in one select statement is this even possible? Roger Karnouk [EMAIL PROT

Query speed

cond query without slowing down the first. Roger Karnouk - 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 <[EM

RE: Query speed

ger kanrouk -Original Message- From: Braxton Robbason [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 11:55 AM To: Roger Karnouk; [EMAIL PROTECTED] Subject: RE: Query speed seems to me that the first query uses your primary key index. Since you have specified qualifications on crci

RE: Update problem, or more likely and problem understanding how to do updates.

I don't believe you need the second set update Pictures set Date Posted = '2001-05-09', Notes = 'Test' where ID ='1'; I think that should work -Original Message- From: Barry L. Jeung [mailto:[EMAIL PROTECTED]] Sent: Friday, May 11, 2001 3:56 PM To: [EMAIL PROTECTED] Subject: Update pro

RE: Excluding Data from a Query with PHP

SELECT * FROM activities WHERE month='$month' AND year='$year' and username != 'myuen' -Original Message- From: Kelly Alexander Zia [mailto:[EMAIL PROTECTED]] Sent: Monday, May 14, 2001 1:45 PM To: mySQL Subject: Excluding Data from a Query with PHP Hi I've got a question on how to exc

CrossTab or Pivot

$ | +--+---+-++ if so does anybody know the Syntax I have looked for it in the docs but to no avail. Roger Karnouk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com

Updating one table from another

Is it possible to update the values in one table from another table where certain criteria match between the tables in an update query in MySQL. Roger Karnouk - Before posting, please check: http://www.mysql.com/manual.php

Left Join Not Right

E TABLE `T1` ( `crcid` int(10) unsigned NOT NULL default '0', `sum` int(10) unsigned NOT NULL default '0', `item` mediumtext NOT NULL, PRIMARY KEY (`crcid`) ) TYPE=MyISAM Roger Karnouk MediaHouse Software Inc. [EMAIL PROTECTED] http://www.mediahouse.com MediaHouse Softw

Is this a bug?

| 0 | | | 1412536101 | 1 | 0 | | | 2725724903 | 1 | 0 | | | 3304808295 | 1 | 0 | | | 1323923799 | 1 | 0 | | ++-++--+ does anyone know why this is and how I can fix this problem. I am running 3.23.34 on Windows 2000 Roger Karnouk

RE: Importing spreadsheet data into MySQL

7;,' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; This should work I guess -Original Message- From: Chris L. Gray [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 06, 2001 10:36 AM To: Roger Karnouk Cc: [EMAIL PROTECTED] Subject: Re: Importing spreadsh

RE: SELECT DISTINCT

There are no duplicates in your results Distinct is on the whole record not on the individual Items for example: | Xerox N-2125 | Xerox DC255 | is not the same as | Xerox N40| Xerox DC255 | because the first field is different and | Xerox N-2125 | Xerox DC255 | is not the same as | Xerox

RE: Collapsing queries

this is as close as I could come to what you want. it involves 2 steps set a variable set @var:= ''; then query select address.msg_id, content.msg_text, @var:= CONCAT_WS(',',@var,address.address) as addresses from content, address where content.msg_id = address.msg_id; this returns a list but