MYSQL :: Is There A Query To Check Directly If A Datarecord Exists?

Check If Column Exists
I want to check if a column exists in a select statement. I've done searches on google and have not found what I need.

If a column exists, include the column in the query, else not. Here's an example of what I might want to do, but it doesn't work:

select cat_title, if(column_exists('subhead')) then subhead from wf_category where cat_id=5

I know I can do a separate select statement to check for this, but I'd rather save the overhead and do it all in one statement.

Check If An Entry Exists
I have a table which records information on ratings that each user has given each movie, it looks a little like this:

Table = USER_VOTES

USER_ID MOVIE_ID VOTE
1 2 5
2 3 5
3 4 5
3 5 5
4 3 5

I an using a simple SELECT command to find if a user has already voted, ie. if a row already exists with their USER_ID and MOVIE_ID. I am having a little trouble with this SELECT statement and wonder if there is a better way of doing this simple check. My code looks like this:

$sql = "SELECT vote FROM user_votes WHERE movie_id='$id' && user_id='$usrID'";
if ($result = mysql_query($sql, $db)) {
//if vote already exists display error
$error = "You have already voted for this moive
(your rating was ".mysql_result($result,0,"vote").").";
} else {
//if no vote exists process vote...
}

If a user has not voted before I get an error like this:
Unable to jump to row 0 on MySQL result index 2

To me it appears that mysql_query() does not return false when no results are found. Is there a command which does check results and return false or is there a much easier way to check, perhaps there could be some checking code in the actual SQL query?

Check If Record Exists
is there a quick and easy way to check if a record exists?
At teh moment I do a select statement then count the rows retrieved, if its greater than 0 I know a record exists. Anyone know of a simpler way or is this it?

Check If A Table Exists
is there any way to check if a table exists in mysql? i am using a graph program in vb, but if the table it is trying to graph doesnt exist it breaks down! i need some sort of check so as the offending table will not be added to the query if it doesnt exist?

How Do I Check If An Table Index Exists Via PHP ?
I was wondering if it's possible to verify if an table index already exists or not? As it's possible to add indexes multiple times.

Check If Row Exists In Anotehr Table
Is there a way to check if a certain value exists in another table without using something like

SELEC * FROM tbl WHERE someid = 'somevalue';

and seeing if it returns false

I ask this because I'm running a kind of search query where it selects all records from a table where this field is this, that field is that, etc.

So for example

SELECT * FROM members WHERE id='this' || name='that' || height='67' || etc..etc...

now one of the attributes is a zip code.

In another table, i just ahve a list of like a billion zip codes. Ifi want to check if the zip code exists in this list in this other table, how would i do so while preservingn the above format in one call?

Update Value Directly In The Same Query
Is it possible to update a value directly, adding text to it, without getting it first, adding the text in php and then do the update.

for example:
value in field users: ",45,"

and then adding ,34, to the field directly by doing something like:
update table set users='users,34,'

so the value in the field afterwards will be ",45,,34,"?

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

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<br>";
}
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<br>"; }
}
else
{
//its empty... lets whine about it
echo "no <br>";
}

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

How Do I Test If A Value Exists In A Table Column With A Query?
I know this is probably something that should be obvious or easy, but I can't figure out how to test whether a value exists or not.

For example, in table "bookgroup" in the "meetingdate" column (which contains dates for meetings), how could I test whether the date "20040307" exists? I don't want to return a value in the same row... I simply want to return a boolean TRUE or FALSE value... like 1 if it does exist, and 0 if it doesn't. Can I do this without creating another column specifically for this purpose? Is there a query which can do this?

All I have so far is

FROM `bookgroup`
WHERE meetingdate='20040307'

but I don't know where to go from there.

Using A Query To Exclude Records From One Table, If The Field Value Exists In Another
I'm very new to SQL, and wondered if someone could help me out:

I am building a query which contains two tables. One table has details of everything everyone owns.

The other table is a list of things which people own, but that I don't want to see.

Is it possible to design the query so that I see all of what everyone owns, except the items listed in the other table?

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)

What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this:

SELECT COUNT(*) AS exists FROM mytable WHERE ...
Sometimes I just select the first row and check later in php how many rows have been returned:

SELECT some_col FROM mytable WHERE ... LIMIT 1
But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example:

SELECT id,
name,
(SELECT COUNT(*) FROM mytable WHERE ...) AS exists
FROM othertable
WHERE surname='xxx'
Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.

Using MyISAM Directly
Has anybody had experience accessing MySQL tables via MyISAM library?
I'm working on an application that has to have both SQL-like and DBM-like
(file-level) access to the table data. I thought that using MyISAM
directly would be a good idea. If anybody has any better suggestions, I am
all ears.

Also, I was wondering if it's possible to use Berkeley DB tools and
libraries to access MySQL data files, which are set to use BDB as the
storage engine. If it is, can somebody point me at some examples (except
what's in the source already).

Using MyISAM Directly
Has anybody had experience accessing MySQL tables via MyISAM library?
I'm working on an application that has to have both SQL-like and DBM-like
(file-level) access to the table data. I thought that using MyISAM
directly would be a good idea. If anybody has any better suggestions, I am
all ears.
Also, I was wondering if it's possible to use Berkeley DB tools and
libraries to access MySQL data files, which are set to use BDB as the
storage engine. If it is, can somebody point me at some examples (except
what's in the source already).

Joining Tables Which Aren't Directly Related
I'm struggling to get my head round a SQL query and wonder if you might be able to help (I'm using MySQL version 4.1.20-1).

I have three tables: accounts, items and invoices.

CREATE TABLE accounts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
accounts_id INT UNSIGNED,
...
PRIMARY KEY (id),
FOREIGN KEY (accounts_id) REFERENCES accounts (id) ON DELETE SET NULL
) ENGINE = InnoDB;

CREATE TABLE invoices (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
items_id INT UNSIGNED,
...
PRIMARY KEY (id),
FOREIGN KEY (items_id) REFERENCES items (id) ON DELETE CASCADE
) ENGINE = InnoDB;
Each account can have multiple items and each item can have multiple invoices. Hope you're with me so far?

So here's the question. If I have an account ID, how do I get a list of invoice IDs for that account when accounts and invoices aren't directly related?

Associative Table Or A Directly Foreign Key
I am working with hibernate and i want to map my Objects to tables structre.

There is two ways to map relation:
1. with associative table
2. directly foreign key (i mean without associative table)

Can you please explain what are the advantages for each way?

Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio?
I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like

INSERT INTO sometable VALUES (1,'image.jpg');

The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP.

Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.

How To Insert Images Directly Into Mysql Table
Im working on a page for photographer. And he wants some amount of photos to have there and change it whenever he would like to.

I thought i put all the photos in mysql db and i would pull it out to webpage using variables. But Im stuck already in the first step -

STORING THOSE IMAGES INSIDE A TABLE OF DB.

I read about BLOB and avr_row_length and max_rows, but nothing is working. The image is stored in many rows and the output is just strange symbols.

I also read those long, hard-mind taking scripts how to upload image using php. But i dont want to slowstep through php, uploading file etc, I just want something like:

LOAD DATA INFILE 'image.img' into table tbl_name;

and then using php on my page to display it.

Connect To Sqlserver Directly From Unix Prompt
How can I connect directly to sqlserver from a Unix Prompt? I am using DataDirect in the ODBC connectivity. Is there any way whether I can connect to SQLServer from the prompt,just a select stmt will do.

For eg: if I use select * from table|sqlplus -s usernmae/password@hostname

MySQL Administrator Backup Directly To Local Database
In MySQL administrator is there a way to backup directly to my local database from a remote host. I would ideally have this done automatically once a day.

I like to test locally and don't want to have to restore the database locally each time a backing from the remote site is done.

How To Connect Directly To Mysql Server Port (3306) Using TCP/IP Protocol
I want to develop an application in LabWindows/CVI (ANSI C developpment software for instrumentation) from which I could connect directly to mysql server port (3306) using TCP/IP protocol. This developpment environment allows me to use functions to connect, read and write to a certain port using TCP/IP protocol. I must say I'm new to using MySQL and I searched the manual without any response to my question.

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

Where Exists In Set
I'm trying to select a bunch of accounts that have an account code matching one in a set.

I tried using a command like this:

SELECT * FROM `astaccount` WHERE accountcode EXIST IN SET(?', ?', ?');

Which didn't work. But this did:

mysql> SELECT accountcode,uid FROM `astaccount` WHERE FIND_IN_SET(accountcode, ?,71456,71457');
+-------------+-----+
| accountcode | uid |
+-------------+-----+
| 71451 | 5 |
| 71456 | 10 |
| 71457 | 10 |
+-------------+-----+
3 rows in set (0.00 sec)

Not Exists
I have three tables; Customer, Equipment and Category. Each customer has many pieces of Equipment, each of which belongs to a category; commercial, industrial or domestic. What I'm trying to do is display customers who have only sent in commercial equipment. Having read some examples on the Internet, I'm thinking about doing a NOT EXISTS query.

The inner part of the query is getting all company names which have sent in equipment which is other than commercial:

Trouble With Exists
This query looks perfectly fine but I keep getting an error.

MySQL
SELECT jobId
FROM jobs
WHERE EXISTS (
SELECT jobId
FROM workOrders
WHERE workOrders.jobId=jobs.jobId
AND workOrders.status <> 7 AND workOrders.status <> 6
)
Here is the error

#1064 - You have an error in your SQL syntax near 'EXISTS (
SELECT jobId
FROM workOrders
WHERE workOrders.jobId=jobs.jobId
' at line 3
Does anyone see anything wrong with this?
I'm starting to think I'm brain dead.

Subquery (not Exists)
I've got some sort of syntax problem that doesn't seem to make a lot
of sense. I'm developing a Categories Theory application and because
of that I need to make big, and by "big" I mean HORRIBLY HUGE queries.
That one has 54 lines and 3 subqueries (only the first one is shown so
that I won't scare people off :)).

The thing is, I don't seem to be getting the hang of how to do
subqueries. The syntax seems fine... But it'll still always say the
same thing:

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 'exists ( select * from objeto c, morfismo f1, morfismo f2
(It's MySQL 4.0.11a-gamma)

The only difference I see between the code below and the mysql.com
documentation is that my subqueries aren't alone in their "where"
clauses... Aside from that, they seem pretty much okay. Am I missing
something?.....

ADD Column If NOT Exists - Possible?
Is there anyway to write a table maintenance script that will add a column if it doesn't already exist.

I need to maintain many identical tables at different sites, and want a refresh script that will always ensure all the default columns have been created.

IF EXISTS Clause?
Why does "DROP INDEX IF EXISTS" not work?

when "DROP PROCEDURE IF EXISTS" and "DROP TABLE IF EXISTS" etc. does work??

Here is my code

alter table test drop index if exists `index_test`;

It gives me errors. Am I doing somethign wrong?

If Row Exists Then Update If Not Add New Row.
I am trying to make a script which gets a list of Ports and IPs from a remote mysql DB. With each result from the DB it is used to ping the servers to generate the stats..

I first tried doing this and let it scan the servers all on one page. I found this was a very slow result.. about 5 minutes to scan around 20 servers. My other option now is to get a cron job every 10 minutes to launch a script which scans the servers and then adds the result into a DB.

This is where i get stuck! I want this all to be dynamic so I dont have to add and remove ips when the customer changes his mind about his CS source port. What I want to do is: If row exists then update that record with the status of the server, if the row doesnt exists then make one.

The eventual displaying and scanning is in theory quite easy but the MYSQL (is record there) is where I get lost.. wondering if there is a shortcut without me needed to do a sql statement to see if it exsists.

IF EXISTS Statement
I have an error in my EXISTS statement.

IF EXISTS table SELECT * FROM table WHERE I_I_i_i_i = '15472'

The table may not have been created yet, is there a way to tell with the EXIST statement before I do and querys on it?

ERROR USING NOT EXISTS
I am getting the following 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 'EXISTS (SELECT * FROM t2)' at line 1

SELECT UCASE(lastname) FROM t2 WHERE NOT EXISTS '(SELECT * FROM t1)';

I am using version 4.0.18. What I am trying to do is get all the lastnames from t2 that are not in t1 .

Using 'NOT EXISTS' In MySQL
My SQL knowledge is not great, but this query below worked in Access and I have not been able to get it to work with MySQL. Basically I want to pull all the rows in table1 that do not have corresponding rows in table2:

SELECT T1.USR_ID,
  T1.CO_NM,
  T1.FRST_NM,
  T1.LST_NM
FROM DB2_USRS T1
WHERE NOT EXISTS (
                  SELECT ' ' FROM DB2_CLIENT_DM T2
                  WHERE T1.USR_ID = T2.USR_ID
                 )
ORDER BY T1.CO_NM, T1.LST_NM

Anything obvious that anybody can see why this would work in Access and not MySQL?

Insert If Not Exists
how can I insert a data on db ONLY if not just exists? Must I use two queries (one to check it and one to insert the data?).

Database Exists?
What's the SQL statement to test if a database by name, exists?

EXISTS JOIN?
3 tables:

tblUsers : id, username
tblProjects : id, projectname
tblUserProjects: user_id, project_id

The usual setup: one project can have many users, one user can be assigned to many projects.

OK: for a "project details" page, I need to list ALL users. But I need a column telling me if the user has been assigned to the project.

WHERE EXISTS Does Not Work.
SELECT id,name FROM tcesports_groups WHERE EXISTS (SELECT * FROM tcesports_teams WHERE tcesports_teams.gid=tcesports_groups.id);

i get this:

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 'EXISTS (SELECT * FROM tcesports_teams WHERE tcesports_teams.gid

Strucuture of the both tables:
tcesports_teams
-------------------
gid smallint(6)
img varchar(100)
mod mediumint(9)
pos smallint(6)

tcesports_groups
-------------------
id smallint(5)
name varchar(100)

currently the entries for id or gid are just 2digits long (1-99) so im a bit confused what throws this error. im using mysql 4.0 branch

NOT EXISTS Syntax
I am trying to do a very seemingly simple query that selects a field based on whether or not that field exists in another table. I only want it to display the ones that don't exist in the 2nd table.

My query looks like this:

SELECT name FROM managers WHERE NOT EXISTS

(SELECT * FROM members_data, managers WHERE members_data.listname = managers.name)

but it gives me a syntax error near EXISTS (error #1064). I am very new to mysql so any help would be appreciated. I'm running 4.0.17 so I thought I could use NOT EXISTS but maybe I am wrong?

I also tried:

SELECT name FROM managers WHERE NOT EXISTS

(SELECT listname FROM members_data WHERE members_data.listname = managers.name)

And get the same error.

Mulitiple EXISTS
I am doing my first subquery statements, but I'm finding the queries are taking longer than expected (ie.too long). Is there any way to optimize the following statement? Any help greatly appreciated:

select distinct * from tbl_members where EXISTS (SELECT * FROM tbl_memberGroups WHERE tbl_memberGroups.id=tbl_members.id AND tbl_memberGroups.mgroups_id=1) AND EXISTS (SELECT * FROM tbl_memberGroups WHERE tbl_memberGroups.id=tbl_members.id AND tbl_memberGroups.mgroups_id=3) AND EXISTS (SELECT * FROM tbl_memberGroups WHERE tbl_memberGroups.id=tbl_members.id AND tbl_memberGroups.mgroups_id=2)

Exists And In Clause
where to use "exists" and where to use "in" clause...they sound similar...i am totally confused..will you please make me clear ?

IF EXISTS Statement
I know that the following statement is incorrect because I'm posting it here. I believe that most of it is correct but I need help in tweaking it so that it can detect if that table does not exist and then create it if it has to and insert the data. Can anyone help me out with that?
Code:

IF EXISTS (SELECT '2008-07-11' FROM `AAC$WC|Daily`) UPDATE `AAC$WC|Daily` SET date='2008-07-11', open='0.07', high='0.07', low='0.07', close='0.07', volume='43000', adjclose='0.07' ELSE INSERT INTO 'AAC$WC|Daily' (date, open, high, low, close, volume, adjclose) VALUES('2008-07-11', '0.07', '0.07', '0.07', '0.07', '43000', '0.07');

Date Exists
what is the function to check whether a date, eg 2005-02-44, exists?
in php, there is a checkdate() function.

Table Exists
Is there a way to find out if a field in a table exists before trying to send data to it?
then if not exists create it.

'EXISTS' Statement
I have a query that isn't working. In the query, I am dealing with three tables:
--"orders": a list of orders our company has received.
--"domains": a list of domains the order can be tied to.
--"contacts": a list of contacts the order can be tied to.
--"documents": documents can be tied to either a contact or a domain.

The "orders" table has fields called "domain_id" and "contact_id" which tie a particular order to a particular domain and contact. My basic query (i.e., with no interaction with the "documents" table) works fine. The problem is that I want to have our records searchable by whether an order has documents attached to it. I am trying to use a query that basically says "Select all orders and contact information for any order that has a document tied to the order's domain_id or the order's contact_id." Here is the the query I am using:

Code:

SELECT orders.*
FROM orders, contacts
WHERE orders.org_contact_id=contacts.id
AND EXISTS (
SELECT *
FROM documents
WHERE documents.domain_id=orders.domain_id
OR documents.contact_id=orders.org_contact_id
)

I get the following error:
#1064 - 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 'EXISTS (SELECT * FROM documents WHERE documents.domain_id=order

Checking If Something Exists
I need some PHP code which will check the database for something, and if it doesn't exist, It says not found...

Index Exists
Is there a way to tell if an index exists before attempting to build it? I use this PHP code to create an index on my table:

ALTER TABLE `$table` ADD INDEX ( `Surname` , `Given` , `Age` )

where $table is the table name passed in by PHP. I create several other indexes using similar code. Sometimes this routine is aborted before all of the indexes are complete. When I run it again, I would like to know if a given index already exists, and not build it if it exists. Is there a mySQL query for this? Something along the lines of
IF NOT EXISTS (ADD INDEX ( `Surname` , `Given` , `ComputedAge` ))
but I assume this is incorrect syntax? I've never used EXISTS.
Likewise, can I check for the existence of an index before doing DROP INDEX? I'd rather not attempt to drop something that doesn't exist, as it gives me an error.

Record Exists
I currently a large set of tables, each of which have the fields Year and Date (in Julian format). In my user interface, I want the user to be able to type a date, and then the backend should query the database to see if there are any tables with records that fall in this range. The front end would then list the appropriate tables on the screen.

To facilitate this, I am currently going through each table and performing something like a "SELECT COUNT(*) FROM tablename WHERE DATE = date AND YEAR = year". I then check if the number of records in the result set is greater than 1, and if it is, I list the table name.My question is, is there any way to tell MySQL to stop searching the table after only finding ONE match since this is all I would really need. This would greatly improve speed and performance.

INSERT WHERE NOT EXISTS
I'm trying to insert some data into a table and wish to confirm the insert
is good rather than creating an error. I have tried the following (which
works on Sybase)

INSERT customers
(full_name)
VALUES
(''Big Tech Enterprises')
WHERE NOT EXISTS ( SELECT c.*
FROM customers c
WHERE c.full_name = ''Big Tech
Enterprises')

Can anyone advise the correct syntax for MySQL?

Table Field Exists?
Is it possible to know whether a particular table field exista in a table or not?

Secondly is it possible to display all the table fields existing in the table.

I have a table called articles inside a database named mysql
I want to display all the table fields present in articles or check whether a particular field exists.

Is There A Query To Check Directly If A Datarecord Exists?

Is there a query to check directly if a datarecord exists?