MYSQL :: What Query To Check If Any Rows Exist Satisfying WHERE Clause?

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

Check If Field Already Exist
How can I check if a field already exist in a table? If it doesn't exist I want
to add it with ALTER TABLE ADD...

Implementing The CHECK Clause`
I am working with mySQL version 4.1.14-max on an Apple Mac. I have been tring to finish up a homework assignment to create the following domain:

CREATE DOMAIN Id INTEGER CHECK ( VALUE > 999 AND VALUE < 99999);

and can't seem to get it to work. I also try addeding the CHECK clause directly into the table, which worked. But, the table is not enforcong the range requierments. After searching the web, I found that mySQL will parse and ignore the CHECK clause when used with CREATE TABLE. So, I have no idea how to restricted the range on the Id attribute.

Check Empty Table In Where Clause?
the following where clause (expurgated) suits my needs right up until the table emailman is empty (which it frequently is) and then it returns an empty set (which is bad;-)
this is actually an AND emailman... but don't I think the rest is relevant.

WHERE emailman.related_id != contacts.id How can I modify this to allow for emailman to be empty? Something like...

AND (emailman.related_id != contacts.id || NOT EXISTS (emailman.id) )

Using A Limit Clause, But Return The Number Of Rows Of The Query Without The Limit
I have heard of a cool feature that mysql provides a way to return the number of rows of an sql statement that contains a LIMIT as if the LIMIT had not been there.

I search the mysql manual, but could not find anything.

Incrementing Rows That May Not Exist
I'm sure there's a very simple solution to this, I just haven't been able to find one I understand yet.

I have a table with 3 columns, the first two are used as an index, and the third is a counter. So far so good:

Joins To Rows That Don't Exist
I want to join two tables together, but if the secondary table doesn't have a row that matches, I want the primary table's row to still be returned, with the values for the secondary row being their default values. How do I accomplish that?

For example:

if I have two tables:

t1: defaults:(a=0,b=0)
+---+---+
| a | b |
+---+---+
| 1 | 3 |
| 2 | 4 |
+---+---+

t2: defaults:(a=0,c=0)
+---+---+
| a | c |
+---+---+
| 1 | 7 |
| 3 | 8 |
+---+---+

I want to execute a query like:
SELECT t1.a,t1.b,t2.c FROM t1 JOIN t2 USING(a)

But I want the result set to be
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 3 | 7 |
| 2 | 4 | 0 |
+---+---+---+

Selecting Rows From One Table With A Key That Doesn't Exist In Another.
I'm trying to select rows from one table that does not have a key in another table.

For instance:

I have a hospital table with two columns, id and name.

I have a patients table with three columns, id, name, and locationid.

Now I want to find those hospitals that have no patients. So I want to get those hospitals whos id does not equal any of the locationid's in the patients table.

Update Script, But Check If The Value Is The Same On Other Rows
I have a nice user script. In a particular section there is the ability to edit the users on the databse.

What I am concerned about is that people may edit the username or email and find its the same username or email that another user has, this can screw up the login system especially two usernames being the same.

So is it possible to make it so that if the email/username is the same in another row to give an error, Please note that the input fields for their email and username are already filled in so if they dont come to edit it them they will still output there original username/address.

Check For One Piece Of Data In Multiple Rows.
I have a table: (arbitrary names bellow)

main_id
info
info
info
id1
id2
id3
id4
id5
id6
id7
id8
id9
info

----
id1-4 will always have info 5-9 may be null. I need to do a select that will find if certain data is in id1-9 and return all rows where this matches.

SELECT *
FROM `recipes`
WHERE id1 =1032 OR id2 =1032 OR id3 =1032 OR id4 =1032 OR id5 =1032 OR id5 =1032 OR id6 =1032 OR id8 =1032 OR id9 =1032
ORDER BY class LIMIT 0 , 30

is a bit cumbersome. is there a better way to do this query?

Also... I am not concerned about speed when row is added to a table, but want speed when this query is made. is there a good index structure to make? And if data is already in, is there a way to make it re-index the table?

How To Select Distinct Rows Using The WHERE Clause
I have an app at work that will only allow me to customize the WHERE clause on a SQL statement.

Currently the SQL Statement is hard coded something like this:

SELECT * FROM `ERRORS`

Is there a way to do a DISTINCT in the WHERE clause that would return the same results as:

SELECT DISTINCT `MEMBER_NUMBER` FROM `ERRORS`

Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working

Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.

Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account.

For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done?

I run MySQL 4.0.17.

Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' that contains a field representing the topic's author. I then have another table called 'ignorelist' which has two fields; one represents another user and the other a reference to the aforementioned author. There can be many ignored authors for any one user.

For any given user, I'd like all the rows from the forumtopics table where there is no match for (user, author) held in the ignorelist table. Obviously it's easy to check for existence, but can the opposite be done in a single query?

I run MySQL 4.0.17.

Check Query Please
This is giving me an mysql error. Why?

DELETE FROM subscribers,list_subscribers WHERE subscribers.id = list_subscribers.ls_sub_id AND list_subscribers.ls_list_id = 1

Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured.

A & B

Table A, has column: Product (product code) as primary key

Table B doesn't.

Apart from that they have the exact same fields.

There's also a column: supplier

I want to

Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples

So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes.

mysql version 4.0.27

Check Before Insert Query
if (!empty($determin))
{
//do something when its not empty

$query = "SELECT * FROM `JS` WHERE `wrdnprc` LIKE '$mkrs' LIMIT 1";
$good_query=mysql_query($query) or die(mysql_error());

if (mysql_num_rows($good_query) == 1 )

{
echo "record found #2 ";
}
else
{
$query = "INSERT INTO `JS` ( `id` , `wrdprc` , `wrdnprc` , `pgld` ) VALUES ('', '$mkrs', '2', '0') ";
$query=mysql_query($query) or die(mysql_error());

echo "adding record #2 "; }
}
else
{
//its empty... lets whine about it
echo "no ";
}

most of the work is being done, which is good thing BUT the query will INSERT regardless of the if good_query statement.

How To Check For Date/time Ranges Within Record (check For Schedule Conflicts)
You want to check scheduling conflicts and you have a record like:

appointments(table):
apptID
beginningDate
endingDate
beginningTime
endingTime

It's easy enough to check if a time is within that record. Say you want to
check if 8:00am to 10:00am is available, you would use this:

SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)

BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The above
query would not find it.

Another question is what if the appointment is more than two days. Say, it's
from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on Tuesday.

(I might be able to generate a date range using PHP, don't know if that's
the best way)

Is There A Query To Check Directly If A Datarecord Exists?
Is there a query to check directly if a datarecord exists?

IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS..

I'm trying to do this:

insert into table2 (field1) select field1 from table1 where field1 not in
(select field1 from table2)
delete from table1 where field1 in (select field1 from table2)

1. Insert field1 from table1 into table2 if it doesn't already exist
there...
2. Once moved, delete from table1.

Any help would be greatly appreciated.

SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem:

SELECT * FROM footballers WHERE sname LIKE '%$criteria%'

however, if I want to join two tables and still use the LIKE clause how would the syntax look??

My guess below was incorrect. any help would be appreciated. thank you

WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)

Which Query Is Used For Minus Clause
Can u give me the ans which query is used for minus clause. in MySql?

Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5

this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent).

I want to select all the rows that has :7: in the sections field.

SUM Query And Comparing Values In WHERE Clause...
Here is my query:

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000.

I have need to use a reference in the outer from clause in a subquery in
the select clause. Consider the following example:

Select (select b.baitID from b where b.entrydate curdate()) as
wantedBaitIDs from bait_tbl b;

My actual need is more complex than this as part of it is a rough cross
tab. If I try to define the table in the alias, not only do I lose
whatever benefits there are in the particular join I would use in the
outer from clause but would also require the join to be defined in each
subquery, requiring it to be examined each time it is used.

To be absolutely clear, in this example I want to use bait_tbl with the
alias of b in the subquery. In my actual query I reference the same
table twice with a different join set for each. I need to reference a
particular alias as that has the join set I need.

Can I Check The Value Of An Alias(i Think Its Called An Alias) In A Query.
I was wondering can I check the value of an alias(i think its called an alias) in a query.

At the moment I have this but its not working:

MySQL Code:

SELECT * , ( DAYOFYEAR( ToDate ) - DAYOFYEAR( FromDate ) ) AS NumDays
FROM `tbl_courses`
WHERE NumDays < 3 AND NumDays > 2 AND FromDate >= &#55614;&#57159;-10-17'
ORDER BY FromDate

Several Rows From One Query
I've this sql-query...

$sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']";

The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...

Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC;
It generates somewhere around 2.9m rows. I want the last 10:

SELECT expensive query ORDER BY field DESC LIMIT 10

But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of

SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10
We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?

SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do
SELECT * FROM case_studies
It outputs all rows.
The query i am usign is:
select programs.program_title,
programs.id, case_studies.id,
case_studies.title, case_studies.author,
case_studies.timestamp from case_studies,
programs WHERE programs.id
= case_studies.id
Any ideas?

Query Help, Comparing Rows
Suppose I have the following data:

+----+----------+-----+-----+-----+-----+-----+-----+-----+
| Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
| 11 | 11:20:00 | F | T | T | T | F | F | F |
| 12 | 11:45:00 | F | T | T | T | F | F | F |
| 14 | 12:10:00 | F | T | T | T | F | F | F |
| 15 | 12:35:00 | F | T | T | T | T | T | F |
| 17 | 13:00:00 | F | T | T | T | T | T | T |
| 18 | 13:25:00 | F | T | T | T | T | T | T |
| 19 | 13:50:00 | F | T | T | T | T | T | T |
| 20 | 11:28:00 | F | T | T | T | T | T | F |
| 21 | 11:53:00 | F | T | T | T | T | T | F |
| 22 | 12:18:00 | F | T | T | T | T | T | F |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.

How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent...

I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update.

Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.

Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work)

$sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";

Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images....

SQL
SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

How Do I Update Multiple Rows In One Query?
How do I update multiple rows in 1 query? I try not to do a loop of update queries.

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as

Name Raj Rina Tina
Marks 80 90 70

I tried the --vertical option of mysql and G option in the query.

for example when i tried

select *from studentsG

it displayed

*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70

but i want to display

Name Raj Rina Tina
Marks 80 90 70

Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's.

$sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )";
return $sql2;
$res = mysql_query($sql2, $connDB) or die(mysql_error());
$row = mysql_fetch_assoc($res);
based on a test zip code gives a result like

SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 )
the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table.

In my db table lon and lat are decimal(10,6) type with a default value of 0.000000

Use Where In Query So It Returns Rows That Has Less Than 10 Characters In The Word
I want to return rows that has less than 10 characters in the word that is used in the where clause sort of thing.

I can't really explain so I will give you an example, which is obviously wrong.

Quote:

mysql_query("select games from gaming where gamename < 10 characters");

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
 or die ("Error With Connection");
 echo("connected ");
 $db_sel = mysql_select_db("game",$conn)
 or die ("Error With Database");
 $check = "select * from users where 'username' = '$username'";
 $db_sel = mysql_query($check,$conn)
 or die (mysql_error());