MYSQL :: Subquery (not Exists)

Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query.

First Query
select

ACCOUNT_ID,
ACCOUNT_TYPE_C,
PAT_ID

from PAT_ACCT_CVG

where ACCOUNT_TYPE_C in (120103,120104,120101)

Second Query

SELECT

PAT_CVG_FILE_ORDER.PAT_ID,
PAT_CVG_FILE_ORDER.LINE,
COVERAGE.COVERAGE_ID,
COVERAGE.CVG_EFF_DT,
COVERAGE.CVG_TERM_DT

FROMPAT_CVG_FILE_ORDER
LEFT OUTER JOIN COVERAGE
ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID

Where coverage.payor_id = ?'

Third Query

select

TRAN.ORIG_SERVICE_DATE
TRAN.TRAN_TYPE,
TRAN.INSURANCE_AMOUNT

from Tran

where TRAN.TRAN_TYPE = 1
and TRAN.INSURANCE_AMOUNT > 0
and TRAN.proc_ID in 1008,1009

(now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)

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.

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?

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.

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.

Checking If Row Exists Problem
I building a form through Dreamweaver 8, where I want a person to enter once a day, and not have multiple entries from the same email address. I wrote a query to find rows where the email has already been entered today. However, just like my dating life, it doesn't work out as planned. It still allows multiple entries by the same email address. I've tried different ways of writing the IF clause ( !=0, >0) but I still don't have it right. I've echoed $_SESSION['today'] and it displays the right date, so at least I know that is right. Thanks in advance to everyone who reads this.

==================================

$today = date("m-d-y");
$_SESSION['today'] = $today;

$today_rsGetEntries = $_SESSION['today'];
$email_rsGetEntries = $_POST['email'];

$query_rsGetEntries = sprintf("SELECT * FROM entries WHERE email_address = '%s' AND submitted_date = '%s'", $email_rsGetEntries,$today_rsGetEntries);
$rsGetEntries = mysql_query($query_rsGetEntries, $db) or die(mysql_error());
$row_rsGetEntries = mysql_fetch_assoc($rsGetEntries);
$totalRows_rsGetEntries = mysql_num_rows($rsGetEntries);

if($totalRows_rsGetEntries >= 1)
{
$errmsg = 'You have already entered today. Enter again tomorrow.'
}

==================================

Function To See If A Table Exists
I need to do a check to see if a table exists or not, any mysql functions that do this?

Finding If A Table Exists
Is there a statement I can pose to determine if a given table even exists
within a MySQL database?

Querying If A Table Exists
I am familiar wth the "IF EXISTS" clause to drop tables before recreating them. However, I am wondering I there is a way to do that in a select statement.

I'm trying to do something like this:

ex: select if(exists('mytable'), 'Found Table', 'Unknown Table);

Create Table If Not Exists
I'm having trouble with this. My impression was that nothing would be done using this form, but it appears that the select clause is adding rows in to th existing table, but my intent was to only create a table once in my code:

CREATE TABLE IF NOT EXISTS z_set_1000_1999
(
SELECT rid, mid, rg
FROM ts2
WHERE rid>=1000 and rid<= 1999
ORDER BY rid, mid
);

and then refer to the table on later references to it, using:

SELECT rid, mid, rg FROM z_set_1000_1999 ORDER BY rid, mid;"

What appears to be happening is that the CREATE is adding duplicate rows to the existing table. The second time through, the table size doubled. Is that supposed to happen; insert the rows if the table exists? If so, how do I test if the table exists so I can go directly to the SELECT and bypass any additional creates or inserts?

Since I am creating a large number of tables like this, I thought it would be beneficial to query an existing tables rather than search a larger table every time, since so many of the queries are identical.

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?

Checking A Row Exists Before Adding A New One
I am running several stored procedures which will in turn add data to a summary table.

As the arrival of the data is unpredictable, anyone of the scripts could be executed at any time, so rather than running the data in a specified order I want the data to be added as it arrives. So I'd link to implement something along the lines of: Code:

Update WHERE Field Exists
is there a way , in MySQL to do this:

i have a table with rows id,name,passwd,nickname

Check if row with `name`='harry' exists
if so, UPDATE with new data

if not, INSERT a new record

now i'm doing this with a php if else construct, but there must be an easier way.

Checking To See If A User Exists.
I am running MySQL 5.0.32.

I have discovered (by rummaging through the tables and a bit of trial and error!) that the root user can discover the usernames of the other users of the system with the following simple statements:

use mysql;
select User from user;

The question is; can I rely on these two statements to work in all versions MySQL? Or is there a statement dedicated to this task? In the MySQL manual for v5.0, under Account Management Statments (http://dev.mysql.com/doc/refman/5.0/en/account-management-sql.html) I can't find a statement to do this.

Get Distinct If Certain Data Exists
I am running 4.1.14max and I have the follow query I need to create:

I have a table named [project] and a table named [element]. There are many elements for each project. [element] has a column named 'datecomp' showing when that element was completed. I need to select all of the project information as well as the element.datecomp field, but here's where I'm running into trouble - I need to select only distinct projects and if any of the elements have a datecomp of NULL then I need the datecomp field to show up as NULL for that project but if all elements have a datecomp entered I need the newest datecomp to show up as datecomp for that project.

So in other words - show me all projects that have atleast one element (or many of them) incompleted as being an incomplete project, and also show me all projects where all elements are completed and tell me when the last element in that project was completed.

Code:

How To INSERT A Record When NOT EXISTS
How to INSERT a Record when NOT EXISTS

My current code has a compile error:

DECLARE MyVariable integer;

IF NOT EXISTS (SELECT * FROM MyTable WHERE RecordID = 1) THEN
INSERT INTO MyTable (Column1) VALUES ('Some value')
SET MyVariable = LAST_INSERT_ID()
END IF

MySQL tells me that this has a syntax error. Help!

How can i implement this properly? What's the best way to do it?

SELECT Syntax With EXISTS NOT()
2 tables are concerned: user_games and games_details. i want all games in my result which do not yet exist for the specific user (respectively have no games_id in the user_games table) games_details.id is autoincrement and user_games.games_id is set to this correctly.

But i still get no result and no error with the following code:

$sql = "SELECT id, name
FROM games_details
WHERE NOT EXISTS(
SELECTgames_id
FROMuser_games
WHEREuser_games.user_id = '".$_SESSION['uid']."');";

Checking If A Table Exists
Basically what I need it to do, is when you open the page it checks to see if a table exists in the MySQL. Then if it does NOT exist redirect to a page where you begin the steps to install it. If it DOES exist then it will redirect to a page showing that information.

How To Avoid NOT EXISTS In MySQL 4.0.26
I tried to use NOT EXISTS but recently found out that it is supported since 4.1. Now I'm trying to avoid it but I can't figure out how to do this. The Problem is that I have a table with (among outhers) a column SessionId and action.

Action may be opened and closed. Now I want to get all those sessions which are in state open, i.e. which have no line with action = closed. My first attempt was:

select sessionId
from audit AS a
where action = 'opened' AND
NOT EXISTS (SELECT * FROM audit AS b WHERE b.sessionId = a.sessionId AND b.action = 'closed')

Could anybody give me a hint how to get an equivalient query without a subquery?

If Exists Then Update, Otherwise Insert
provide a very simple if, then, else clause in MySQL?
If a customer named 'bob' is already in the database, I want to update his id. If he is not already in the database, I want to insert him.
What would be the best way to do this?
I get errors for the following:
IF EXISTS
(SELECT 1 FROM customers WHERE name = 'bob')
THEN
UPDATE customers
SET id = '007' WHERE name = 'bob';
ELSE
INSERT INTO customers(id, customer)
VALUES('007', 'bob');
END IF

Index Exists Before Trying To Create It
Is there a way to tell if an index exists before trying to create it within an update script?  We have an update script that we need to run that creates new tables and alters existing tables from the mysql command prompt.  Is there a way to put an IF NOT EXISTS as part of a call to add an index like you can for creating a table?  I was just trying to handle error conditions so that the script could finish running instead of erroring out and halting if the index already existed.

sample script "updates.sql"

CREATE TABLE IF NOT EXISTS `SomeNewTable` (
  `id` char(255) NOT NULL default '',
  `description` char(30) NOT NULL default '',
  PRIMARY KEY  (`id`),
) ENGINE=InnoDB;

ALTER TABLE `database`.`ExistingTable1` ADD INDEX `NewIndex`(`some_column`, `another_column`);
ALTER TABLE `database`.`ExistingTable2` ADD INDEX `SomeNewIndex` (`some_column`);
ALTER TABLE `database`.`ExistingTable3` ADD INDEX `AnotherNewIndex` (`some_column`);

from the MySQL prompt
mysql> source updates.sql

If NewIndex already existed on ExistingTable1, this script would never finish and try to add the other indexes.  That's what I'm trying to get around.

Table Exists In Queries
How do I alter a MySQL insert, update or select statement to fail gracefully, without producing an error, if the table doesn't exist?

I know it sounds weird, but I have a table that's used for logging certain non-critical information. I have an external process that periodically backs up this table. It backs up the table by renaming it with a number after it (for example, table "log" becomes table "log_1", "log_2", etc) and they are organized so that it only saves the last 10 and keeps the names consistant, etc. But that's beside the point.

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?

NOT EXISTS.... Problem With Tables Linked
I'm creating a solution where I want to be able to control which users can use each page.
This is how database is designed (in short).
PHP
pages
id | page_title
users
id | username
pages2users
page_id | user_id
Now I want to display which pages a certain user can NOT use.
I've tried this query but it does not work.
PHP
$user_id = addslashes($_GET['user_id']);
sql = "
SELECT * FROM pages p
WHERE NOT EXISTS ( SELECT p2u.* FROM pages2users p2u WHERE p2u.user_id = '$user_id' )
ORDER BY p.page_title
";
$result = $db->query($sql);
while ($row = $result->fetch()) {
echo $row['page_title'].'<br/>'
}
But that gives me nothing... How do I fix this?

Adding New Field To Table That Already Exists??
here is the syntax i used to create a table with certain fields:

CREATE TABLE article (
id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
section_id int(10) unsigned DEFAULT '0' NOT NULL,
category_id int(10) unsigned DEFAULT '0' NOT NULL,
approved enum('N','Y') DEFAULT 'N' NOT NULL,
title varchar(100) NOT NULL,
author varchar(50) NOT NULL,
email varchar(50) NOT NULL,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (id),
KEY id (id, section_id, category_id)
);

now since i have done that i want to add a few new fields:

INSERT INTO TABLE article(
score ??? unsigned DEFAULT '0' NOT NULL,
genre varchar(50) NOT NULL,
fuser varchar(50) NOT NULL,
);

firsty, is the above syntax correct (i just want to add new fields, not delete any existing ones or their content)? secondly for the field 'score' that will be a 2 digit number but not an integer (will have decimal place) - what do i put instead of ??? there?