MYSQL :: Technically An If Statement In A Query?

Statement In A Query
Is it possible to do an if statement in a query? or something like it?
I have a schedule in a table. For the time, I need it to output local time and eastern time. I have the local date and time in a "datetime" column. So depending on where the event is, I need too add to the time in order to produce an eastern time as well.
Right now I'm using this:

DATE_FORMAT(DATE_ADD(sked.date, INTERVAL +2 HOUR),'%l:%i%p') AS eastt

But I need to change the interval number depending on where the event is being held.
Does anyone know how? or is there an easier way to accomplish this task?

Using IF Statement In MySQL Query
I have the following code in one of my scripts.

PHP

$result = @mysql_query("SELECT * FROM Table WHERE id='$id'");
$row = mysql_fetch_array($result);
if ($row['name'] != "") {/* do nothing here */}
elseif ($row['email'] != "") {$row['name'] = $row['email'];}
else {$row['name'] = "<anonymous>";}

Like you can see I'm using an IF statement outside the MySQL query to define the variable $row['name']. But I know that I can use an IF statement inside the query instead of outside it.

QUERY One Table And Update Another In ONE Statement?
ZIP_CODE:
zip_code
latitude
longitude

customers:
customer_id
...
zip_code
latitude
longitude

The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.

Downgrade To Non Sub Query, But How To Change This Mysql Statement?
I've to downgrade my mysql script from sub-query(ver 4x) --to-> non sub-query(ver 3.32)

I manage to changed below sub-query --> non subquery...

Mysql Query Browser...how Do I Ignore " Within A SET Statement?
I'm trying run this in mySQL query browser

Update xcart_classes x
Set classtext = "5 Year Protection <a href="javascript: void(0);" onclick="javascript: window.open('etp.htm','HELP_POPUP','width=406,height=261,toolbar=no,status=no,scrollbars=yes,resizable=no,menubar=no,location=no,direction=no');" class="PopupHelpLink">Learn More</a>"
where Classtext = "Add 5-Year Extended Protection";
Obviously it fails, and I'm pretty sure it's because of the double quotes on my SET statement...any idea on how to avoid that?

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

Help With This SQL Statement.
I have a list of employees with titles and their office locations. What I want to do is to count the number of senior engineers out of the total number of regular and associate engineers at the location. All I can figure out is how to get the total number of engineers per locatoin.
What is the SQL statment that will give me the total number of "Senior Engineers" and "Total Number of Engineers" per "office"?
Here is my attempt which is totally wrong:
SELECT
COUNT ( title = "Senior Engineer" ),
COUNT ( title = "Senior Engineer" + title = "Engineer" + title = "Associate Engineer" ),
office
FROM
employees
WHERE
title = "Associate Engineer"
OR title = "Engineer"
OR title = "Senior Engineer"
GROUP BY
office
I want to the output to be like:
Number Senior Engineers, Total Engineers, Office
4, 20, Oregon
1, 10, Texas

IF Statement
I have read http://dev.mysql.com/doc/refman/5.0/...statement.html but unable to understand it. Can someone give a simple example?
I tried this but it gives me the error "You have an error in your SQL syntax; check ..... right syntax to use near 'IF 10>5 THEN ...'"
MySQL
IF 10>5 THEN SELECT 'Greater'ELSE SELECT 'Smaller'END IF;
What I am doing wrong?
I am on MySQL 5.0.20, WinXP.

Can This Be Done In One SQL Statement?
My Roster table contains rosterId as the key, userName as the field I want.
My Article table contains articleId as the key, rosterId as a foreign key, synopsis, article and posted as the fields I want
My Comments table contains id as the key, rosterId and articleId as foreign keys and posted as the field I want.

I want only one row returned that
Given the aritcleId
Contains the Roster.userName for the Article, the Article.synopsis, the article.posted (date), the Roster.userName for the Comments and the Comments.posted
where
1) The Comments row contains the latest posted (date) for the rows the Comments.articleId that matches the articleId above
2) uses the Comments.rosterId from the Comments table to extract the Roster.userName (for the Comment) AND
3) uses the articleId to obtain the Article.rosterId (which will be different than the Comments.rosterId) to get the Roster.username (for the article).

All tables use the innodb engine.

I understand how to do this in multiple selects, but suspect it could be done in one.

Yes? No?

SQL Statement
I am using the following statement to pull data from the table for any data older than a certain date but I would also like to immediately delete the data the I just pulled from the table into the dump file from the table. Can anyone help me out? I am new to MySQL.

SELECT * FROM `radacct` WHERE `AcctStartTime` < "2005-06-01 00:00:00" INTO OUTFILE '/usr/backups/radacct_output.txt' fields terminated by '|';

SQL Statement
I would like to ask you what to read to understand this "<> for queries.

SELECT polltext, votecount FROM poll_data WHERE pollid = '$pollid' AND polltext <> ''

What does polltext <> '' do?

IF STATEMENT
I am trying to create a statement that checks a table to see if an entry exists and if it does run one select statement and if the the check comes back with 0 then run another select statement

This is what I have but it doesn't seem to run ? Am I way off ? I can post the exact detailed code but since I am a new to mySQL syntax I am sure it is something basic.

IF (SELECT COUNT(*) FROM table_Y WHERE field_X = 'string') = 1 THEN
SELECT .........
FROM ...........
WHERE ...........
ELSE
SELECT .........
FROM ...........
WHERE ..........
END IF

LIKE Statement?
run this script:

CREATE TABLE test (name varchar(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// insert "if 'o": [i], [f], [space], [backslash], [single quote], [o]
insert into test values ('if ''o');

// the next doesn't return the row
select * from test where name like '%if ''o%';

Why doesn't MySQL find that just inserted record?

Like Statement
I am trying to do something I am not sure we can do...
In fact, I would like to test that a field is included in a string.

An example could be helpfull :

Instead of doing this :

SELECT ... FROM myTable WHERE field LIKE '%string%'

I would like to do something like this :

SELECT ... FROM myTable WHERE 'string' LIKE "%"+field+"%"

I already tried whith concatenation but it doesn't work.

IF Else Statement
Im having trouble finding out the correct structure of MySQL IF Else control structures because I am so familiar with T-SQL. Can anybody help me with this basic structure converting from T-SQL to MySQL.

This would work in T-SQL
------------------------
if(select a from tableA)=1
Begin
select x as var
end
else
begin
select y as var
end

But whats the MySql equivilent??

USE Statement
i've installed/configured mysql 4.0.21-standard! everything it's fine except that I don't know what privilege do I have to set to a new user so he can use the "USE" statement!

SET Statement ?
When i give the following command in command prompt it works fine, but when i use query analyser it doesnt , it gives me null , i dont know why ..

when executed via command prompt

mysql> SET @name='abc'
mysql> SELECT @name;

i get output as 'abc' as excpected

where as when i use the same thing in query anaylsyer , i get the result as 'NULL';

any setting that i need to do in query analyser ?

SQL Statement
what is wrong with the following statement;

select bookings.key1,bookings.surname,bookings.firstname, bookings.bookingseqno,bookings.filetype
from bookings where (bookings.surname='sanderson')
and bookings.bookingseqno IN(select bookingseqno from
bookingcharacteristics where chartype=2 and
(bookingcharacteristics.charvalue = 2))
order by bookings.key1, bookings.bookingseqno

I keep getting a syntax error on this statement, but it works with
MSSQL server.

It is saying I have a syntax error and showing this tidbit in the
error message box; Syntax error near 'select bookingseqno from
bookingcharacteristics where chartype.

Sql Statement
this is the error msg it keeps coming up

MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

and this is the SQL statement
SQL INSERT INTO booking_info ("Customer First Name","Customer Last Name","Phone Number","Mobile Phone number","E-mail Address","Customers Address", Service, Problems,"Time","Date","Month","Year") VALUES ( 'Prince ' , 'Asamoah' , 96379915 , 0421474641 , 'princekwame@hotmail.com' , 'sdasd' , 'Computers - Application Software' , 'Windows Update/Installation' , '9:30 am' , 03 , March , 2004 )
DATASOURCE customer_booking_info
VENDORERRORCODE -3010
SQLSTATE 07002 .

LIKE Statement
I have a search box which users can enter a phrase and it searches for items LIKE what they enetered, using the following command:

SELECT product_name FROM products WHERE product_name LIKE '$searchitem' ORDER BY end_time

But this only seems to return items identical to product_name.

SQL Statement
i have a problem with a sql statement over 2 tables. I work with DB mysql and php 4.

My first example:

SELECT DISTINCT bi.* FROM tbl_bibliothek bi
WHERE bi.id_bibli_group =1
AND bi.id_bibli = (SELECT bitra.id_bibli_transfrer FROM tbl_bibliothek_transfer bitra
WHERE bitra.status_bibli_transfer = 'done'
AND bitra.id_bibli = bi.id_bibli)
order by bi.name_bibli;

And here the exception when i run this statement:

You have an error in your SQL syntax near 'SELECT bitra.id_bibli_transfer FROM tbl_bibliothek_transfer bitra WHERE bitra.status_bibl' at line 3.

My fist ask: Why this exeception? Why is the syntax false?

But unfortunately the statement does not fulfill yet times my requirements.

I have two tables tbl_bibliothek and tbl_bibliothek_transfer.
Only values from tbl_bibliothek must be indicate.
ForeignKey is tbl_bibliothek_transfer.id_bibli
1 dataset in tbl_bibliothek can have NULL datasets in tbl_bibliothek_transfer, n datasets in tbl_bibliothek_transfer

My requirements:
A: where is not dataset in tbl_bibliothek_transfer exist (in relation of the 1 dataset in tbl_bibliothek).
B: when all datasets from tbl_bibliothek_transfer (in relation of the 1 dataset in tbl_bibliothek) have the value 'tbl_bibliothek_transfer.status_bibli_transfer = 'done' '

Sql Statement
I have a sql :

insert into customer(acnum, doj, rh, lh) select 'RH000000-01', doj, sum(if(lcode like 'BTL%',1,0)), sum(if(lcode like 'BTR%',1,0)) from thetree group by doj order by doj
above line finely works on consol,

I created above sql using following PHP code;

$inssql="insert into customer(acnum, doj, rh, lh) select '".$row['acnum']."', doj, sum(if(lcode like '".$row['lcode']."L%',1,0)), sum(if(lcode like '".$row['lcode']."R%',1,0)) from thetree group by doj order by doj";

but above sql doesn't produce any result in PHP.

What is wrong with this? or how shall I rewrite the code to product the result?

SQL Statement
How do I write a SQL statement which be able to display the records order by Lot Number and Available Status, for instance:

Lot 001 S
Lot 002 S
Lot 003 S
Lot 006 S
Lot 008 S
Lot 010 S

Lot 004 P
Lot 005 P
Lot 009 P!

Where Statement
I have slq code here

SELECT employeekin.*, relationtype.*, state.*
FROM (employeekin INNER JOIN relationtype ON employeekin.relatypeid = relationtype.relatypeid) INNER JOIN state ON employeekin.stateid = state.stateid

what i wanna to do is to insert the below code into the above code..i can't manage to find how?

WHERE emp_no = #URL.emp_no#

Sql Statement!
I have my table (called data):

id integer(primary key)
rt integer
type varchar(100)
login varchar(100) foreign key

Here it is another table (called partition):
id integer foreign key references data(id)
idpartition integer

I want to write a sql statement which does this:
I want data.rt, partition.idpartition where login='whatIwant1' AND type='whatIwant2'.

I would like that with kind of statement, the dbms returns all what a 'login' has, not only that tuple where the type matches.
I tried with "union" but I am not able to do it.

SQL Statement
I am working with three tables right now
a Teams table which lists Team names and their ID's
In another table I have games listed with Visiting teams and Home teams IDs, as well gameDate, Status, etc.
I am working on a schedule page for a website using PHP. I am pulling all the informatin from the games table to use on the scheule but I need to replace the Team ID's with Team Names. Obviously You do this with a Join, and I can get it to work for the visiting team OR the home team one or the other. I cannot get it to grab BOTH.

IF Statement
give me an example of IF being used in a query? All the stuff on the net just gives the structure of IF taken from the mySQL site which is not much.
What I am trying to do is have a variable to order by, set it using IF ELSE statement, then do ORDER BY that var all in the same query as grabbing the info that I want to sort. Is that possible? Or should I rather just thave a separate field in DB that will contain that var and do ORDER BY it, which is a lot simpler.

VB6 With SQL Statement
My SQL statement gives and error when the user enter the value in the textbox having the apostrophe character.

if the value of my textbox is (men's clothes)

strtemp = "INSERT INTO DE(DESCRIPTION) VALUES('" & text1.text & "')"

sconn.execute (strtemp) //EXECUTE SQL COMMAND

i get the error

You have an error in your SQL Syntax. Check the manual that corresponds to your MySQL Server version for the right syntax to use near 's';

Sql Statement
What is wrong w/ my sql statement?
I am sure the current principalID shows up as at least 1 row from the table, but it is not returning anything.

$sql = "SELECT * FROM bonds WHERE principalID = '$_SESSION[principal][id]'";

IF Statement
HI Can any one see where im going wrong with this. The last IF statement itsnt working
Code:

IF (a.APPLE_ID >0 ,'Pear', IF(o.ORANGE_ID >0 ,'Orange', IF(p.PEAR_ID >0 ,'Pear','')))

Sql Statement
I tried running the following sql statement.

SELECT * FROM prin_own INNER JOIN owners ON owners.id = prin_own.ownersID where prin_own.principalID = 9

I am postive that there are matching rows for prin_own.ownersID and owners.id
I am also positive there is a prin_own.principalID that = 9

If I am certain of the above, why would the above sql statement return 0 rows when I run it directly through phpMyAdmin?

Sql Statement
Code:
SELECT *
FROM cards
WHERE (cards.worth =1
AND cards.deny =0)
AND NOT
EXISTS (

SELECT alttext
FROM user_inventory
WHERE cards.alttext = user_inventory.alttext
) ORDER BY RAND() LIMIT 1
The code i'm using is shown below. Basically what i'm trying to do is select a random card from the cards table that doesn't exist in the user_inventory table. Works fine without the order by rand() in there, but as soon as i add it in the page doesn't load, either takes ages, or just shows as loading but not actually doing anything

Statement
if i want to sum if TST.stateid = 1 how would i put it into this clause...

'this gets forward month deals
&"SUM( IF( 100*Year(dealdeldate)+Month(dealdeldate) > 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countfwdealuse "

SQL Statement
what's wrong with this SQL Statement, (or the bit of PHP that constructs it?

$transferQuery = "INSERT INTO ".$theTable." (PERSON_ID,ARTICLE_ID,PERSON_TITLE,FIRST_NAME,MIDDLE_NAME,LAST_NAME,BIRTH_DATE,BIRTH_PLACE,DEATH_DAT E,DEATH_PLACE) VALUES (".$theRow["PERSON_ID"].",LAST_INSERT_ID(),'".$theRow["PERSON_TITLE"]."','".$theRow["FIRST_NAME"]."','".$theRow["MIDDLE_NAME"]."','".$theRow["LAST_NAME"]."',#".$theRow["BIRTH_DATE"]."#,'".$theRow["BIRTH_PLACE"]."',#".$theRow["DEATH_DATE"]."#,'".$theRow["DEATH_PLACE"]."')";

SQL Statement
i have been able to do this previously using Oracle and would rerally like to know if MYSQL can support this type of query. If so then what the hell am i doing wrong lol

ok heres the query i am trying to run:

select *
from promo_products
where promo_id not in (select promo_id from promo_products where date_to = '31/12/2999');

IF Statement
If there a way to do an IF statement within a MySQL select query? I have this query that gets a vendor's code and name from the database and CONCAT it to put the code into "()" and add the vendor name to the end. Is there a way to write it so the "()" will not be included if the vendor does not have a code set

SQL Statement
I am trying to write an sql statement that replaces a string in a column
from one value to another
The table contains email addresses and some start with 'www.name@domain.com'
I want to be able to remove the www. part of the string

Where Statement
Hi all im trying to write a select for a search function:

i have 37 fields in a single table
i want 4 search criteria eg:
type, name, town and postcode.

i need to to
SELECT * From sussex Where 'list of fields'
like 'criteria1' or
like 'criteria2' or
like 'criteria3' or
like 'criteria4'

i get the like part, but i cant work out how to get it to search all fields in the table.
Do i need to list all the fields or is there an easier way.

SQL Statement
I have problem with this Microsoft SQL statement:

DELETE FROM tblWebMenu WHERE id=3
AND NOT EXISTS
(SELECT * FROM tblWebMenu WHERE parentID = 3)

It doesn't work in MYSQL. Any idea how to make it work in mysql?

Statement
+------------+-------+
| employeeID | skill |
+------------+-------+
| 7513 | C |
| 7513 | Perl |
| 7513 | Java |
| 9842 | DB2 |
| 6651 | VB |
| 6651 | Java |
| 9006 | NT |
| 9006 | Linux |
+------------+-------+

how do i write the select statement for employees who does not know Java? Select * from table where skill <> 'Java' still includes employeeID 7513.

SQL Statement
I have two tables:
`User` has 2 colns, user_id (int,pk) and name (char)
`Logins` has 3 colns, `logins_id` (int,pk), `user_id` (int) and `connecttime` (datetime).

Every user has a single entry in `User` and zero or more entries in `Logins`. What is the smallest number of SQL statements to be able get the top 2 (or fewer) connecttime entries from `Logins` for each user_id?

BTW: I'm on MySQL V4.1.12a-nt

Like Statement
I am trying to do something I am not sure we can do...
In fact, I would like to test that a field is included in a string.
Instead of doing this :

SELECT ... FROM myTable WHERE field LIKE '%string%'

I would like to do something like this :

SELECT ... FROM myTable WHERE 'string' LIKE "%"+field+"%"

I already tried whith concatenation but it doesn't work. If someone
knows a way to do it,

SQL Statement
I would like to create a query that looks through a sales database (fields: YEAR, PARTNO, SALESAMOUNT) and selects all records for which there is a year missing. In other words, if part# 555 was sold in 2000, 2001, 2004, and 2005, I want all 4 of those records inserted into a new database as well as an other part that has missing years. Can this be done with an SQL statement or will I have to use another method to create this database of parts with missing years?

WHERE Statement
My query retreives image names from an Images Table for metro areas within a state.

CODESELECT imageState, imageName
FROM Images
WHERE imageState= #URL.state#

Statement Help
I try for hours to get this update statement straight and I just can't see the error:
UPDATE voting_profil as p, voting_mitglieder as m set p.yahoo='gfj', m.geschlecht='weiblich',p.msn='ppppppp',p.icq='gfj', m.land='Deutschland', p.familienstand='fghj', p.sternzeichen='gfj',p.suche='fghj', p.hobby='fghj',p.treff='fg', p.sex='fgj', p.motto='fghj', p.po_eig='',p.ne_eig='', p.partner_eig='', p.sonstiges='' where m.mit_id='1' and p.mit_id=m.mit_id

It worked before, so I guess I just missed

Sql Statement
I have a news and events database and I would like to be able to display the news items on my web page by the most current, say everything in the last month, and also automatically archive the rest so if they are older than a month, they are displayed on the archive page.
At the moment I have a field named STATUS which contains items called CURRENT, ARCHIVE and STORE. This allows me to manually update my pages but it would be more efficient if I could have a stored procedure that would do it automatically.

Sql Statement
I am being a bit monday morning dumb here...

PHP Code:

$sql = $command." 'Z:/dir/subdir/testimport/av_profl.csv' INTO TABLE av_profl FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY 'rn'";

I can't work out where I need to escape characters in order to make it a valid string

SQL Statement
MYSQL Version 4.0.15
Can some anyone tell me what is wrong with the following statement;

select bookings.key1,bookings.surname,bookings.firstname, bookings.bookingseqno,bookings.filetype
from bookings where (bookings.surname='sanderson')
and bookings.bookingseqno IN(select bookingseqno from
bookingcharacteristics where chartype=2 and
(bookingcharacteristics.charvalue = 2))
order by bookings.key1, bookings.bookingseqno

I keep getting a syntax error on this statement, but it works with
MSSQL server.
It is saying I have a syntax error and showing this tidbit in the
error message box; Syntax error near 'select bookingseqno from
bookingcharacteristics where chartype'