MYSQL :: Subquery (not Exists)

SUBQUERY Vs DEPENDENT SUBQUERY
I have a table called `accounts`, that contains columns:

account_name VARCHAR (128)
account_no VARCHAR (16)
account_hash VARCHAR (32)
balance DECIMAL(10,2)
There is an INDEX on account_hash and there's currently 128 rows in the table.

I have a table called `activity` that contains columns:....

View Replies !

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)

View Replies !

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:

View Replies !

IF NOT EXISTS
i want to know if there anything in mysql to achieve the task below.

IF NOT EXISTS(SELECT * FROM category_master WHERE Category_Name = 'Business to Business') INSERT INTO category_master (Category_Id, Category_Name) VALUES (1,'Business to Business')

or

INSERT INTO category_master (Category_Id, Category_Name) VALUES (1,'Business to Business') WHERE NOT EXISTS(SELECT * FROM category_master WHERE Category_Name = 'Business to Business')

View Replies !

If Exists
find it quite dificult to overcome the differences to T-SQL (SQL server). Right now I'm trying to add a new record to table 2, but I only want to do so if the corresponding id in table 1 exists. In T-SQL the code would be:

IF EXISTS (SELECT id FROM table1 WHERE id = 1)
BEGIN
INSERT INTO table2
(id2, name)
VALUES
(99, 'test')
END

But I get a error when I try to run this. I think it has got something to do with how I use the IF EXISTS statement, since it works fine wihtout it, but I don't know what.

View Replies !

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)

View Replies !

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

View Replies !

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?

View Replies !

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.

View Replies !

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 ?

View Replies !

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.

View Replies !

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.

View Replies !

Insert If Not Exists?
im trying to squeeze two queries into one basically:
i wanna be able to INSERT a new row if a SELECT subquery returns nothing

View Replies !

'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

View Replies !

Don't INSERT If Already Exists
Is it possible to insert a new row into a table if there is no conflict with keys otherwise update the old row with new information.I dont want to delete the old row if it exsists, just update some columns (or 1 column will be fine)

View Replies !

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.

View Replies !

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)

View Replies !

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?

View Replies !

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?).

View Replies !

Column Exists
I have an ASP .net solution which uses SQL DataBasing, it is quite flexible in which databasing syntax it uses, I have implemented this for the MSSQL

if not exists (select * from syscolumns
where id=object_id('Task') and name='Name')
ALTER TABLE Task ADD Name ntext;

I need somthing similar for MySql because the problem im facing is that its coming up with a syntax error saying it cannot create duplicate columns, what i need to be able to do is

Check for an existing column, if it exists we dont do anything, else we create the column.

ALTER TABLE Task ADD Name varchar(200);

View Replies !

Insert Row If ID Exists
I have two tables customers and orders. I t is a related database one customer ccan have many orders. I would like to insert orders into my order table only if my order.customerID field matches the customer.customerID field. If not then an error message should be displayed.

View Replies !

'#sql_780_0 Already Exists'
I was creating a table called 'patients' when I got this error message 'mysql#sql_780_0 already exists'. If I change the name slightly, ie 'new_patients', it will create fine as long as I don't use just 'patients'.

View Replies !

UPDATE IF EXISTS
I would like to do something like:

UPDATE table SET col1 = 'value1', col2 = 'value2', col3 = 'value3' WHERE col4 = 'value4';
IF NOT EXISTS INSERT INTO table(col1, col2, col3, col4) VALUES ('value1', 'value2', 'value3', value4);

View Replies !

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 .

View Replies !

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

View Replies !

User Already Exists
I want to add a new user but I don't know whether a user already exists How can I check whether a user already exists? ...

View Replies !

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.

View Replies !

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.

View Replies !

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...

View Replies !

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.

View Replies !

JOIN Or EXISTS Or ...?
Code:
table PEOPLE
peopleID, Name

table PHOTOS
fileName, peopleID
So in my PEOPLE table I have:

Code:
1, John
2, Mike
3, Miriam
In my PHOTOS table I have:

Code:
IMG222, 1
IMG324, 1
IMG236, 3
IMG323, 3
IMG423, 2

John, IMG222
Mike, IMG423
Miriam, IMG 236

View Replies !

Exists Query
I'm doing some user validation, so I just want to make sure a user id exists on the database. in the past i've been doing
SELECT * FROM USERS where userID=SOMENUMBER

and at one point i put two and two together and decided that
SELECT userID from Users where userID=SOMENUMBER

was faster. I was just wondering if that is teh easiest way/fastest way or should i do a count(*). The thing is I run all my queries through a script that returns them in a nice array structure for me...so I can do count($resultSet) == 1
But so basically is there and easy/fast way to tell that a userid is in a tuple?

View Replies !

Test If Value Already Exists In DB
So, for example, I have a table like this:

Code:
-----------------------------
| id | pointer |
-----------------------------
| 1 | hello world |
| 2 | 2 |
| 3 | hello again |
-----------------------------
There is a new user and they have not specified a pointer - so the new record's "pointer" field should default to '4' (because that's the next available id).

If the user HAS specified a "pointer" BUT the pointer which they specified already exists then I don't want it to be inserted - instead I need to notify the user so they will change their "pointer".

I imagine the PHP to be something like this:

PHP Code:
$pointer = 'hello world'; // just an example.if(pointerAlreadyExists($pointer)) {Â Â // Notify user.} else {Â insertIntoDB($pointer);}

View Replies !

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

View Replies !

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.

View Replies !

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?

View Replies !

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.

View Replies !

How To Drop Column If Already Exists...?
I would like to know how to 'Add a column' if the column already exists.

Is there a way to do 'Alter table drop column 'column_name' if exists, add column 'column_name'......'

View Replies !

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?

View Replies !

Record Exists In One Table But Not The Other
I have two tables sharing the same column "location_code". I join these two tables often via these columns. How do I query the database to find out if table2 doesn't have all the same matching records in table1?

I need something to show me every record in table1 that doesn't have a corresponding record in table2 by joining table1.location_code with table2.location_code

View Replies !

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?

View Replies !

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);

View Replies !

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

View Replies !

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?

View Replies !

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:

View Replies !

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.

View Replies !

Can't Find File When It Exists!
I have a simple LOAD DATA INFILE command:
LOAD DATA INFILE "/home/datafiles/prices/test.txt"
REPLACE INTO TABLE database.tbl_prices
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '
';

It says '/home/datafiles/prices/test.txt' can't be found when it clearly exists. I have opened up the permissions for both the file individual as well as the entire directory. This is bizarre.

I recently moved to msyql 5.0.51a whereas before I was using 5.0.22, where the same command line worked fine.

View Replies !

How To Check If A DB AND Table Exists
I am trying to do is create some code which will allow me to check if a table of a certain name exists in a database. If the table DOES exist, then I want the code to then insert values into the table. If the table does not exist, then the table should be created, and then the values entered.

I am trying to make it as automated as possible, with a minimum of user involvement as possible.

here is my code so far;

<?php

$username=1;
$punishment=2;
$offence=3;
mysql_connect(localhost, root, panda);

if exists(SELECT * FROM information_schema.tables WHERE table_name = 'naughtylist')

{
insert into naughtylist (username, offence, punishment) VALUES ('$username','$offence','$punishment');
}

[else

begin {

CREATE TABLE `naughtylist`.`watchlist` (
`username` VARCHAR(16) NOT NULL,
`offence` TEXT NOT NULL,
`punishment` TEXT NOT NULL,
PRIMARY KEY (`username`)
)
ENGINE = InnoDB;

insert into naughtylist (username, offence, punishment) VALUES ('$username', '$offence', '$punishment')

]
}
end

?>

View Replies !