Using IN With An Empty SUBQUERY
UPDATE bb_topics SET topic_status = 1 WHERE topic_id IN (SELECT topic_id FROM bb_topics WHERE forum_id = 1 AND topic_start_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND topic_status = 0);
If the subquery returns an empty dataset I get a syntax error. Is there any way to test if the subquery is null or exist. I have tried EXISTS and IFNULL with no success.
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Empty Set
I'm learning how to use MySQL at the moent and according to my book I needed to load data into the table president I created from a file, so I gave the following command: LOAD DATA LOCAL INFILE "C:/samp_db/insert_president.sql" INTO TABLE president; Reply: Query OK, 42 rows affected, 294 warnings (0.01 sec) Records: 42 Deleted: 0 Skipped: 0 Warnings: 294 When I now try to search for data I get the following result, this is the way I'm trying to do it. SELECT last_name, first_name FROM president WHERE last_name="ROOSEVELT"; Reply: Empty set (0.00 sec) This is a part of the file: DELETE FROM president; INSERT INTO president VALUES ('Washington','George',NULL,'Wakefield','VA','1732-02-22','1799-12-14'); INSERT INTO president VALUES ('Adams','John',NULL,'Braintree','MA','1735-10-30','1826-07-04'); etc. Could any one see what I am doing wrong here?
View Replies !
Empty Password
Does the mysql v4.1.7 allow empty passwords for the root user? cuz i noticed that without a password my program turns up a java.sql.Exception then access denied.
View Replies !
Delieting Empty DBs
Somehow I can not delete empty databases. When I drop a database, it deletes all tables but not the DB. When trying to drop DB again, it tells me query ok, but the DB still exists. Sometims when droppting a DB, it deletes all but one table.... when I try to delete this table then, I get file not found errors... Is there a trick to force deletion of a DB... no matter if there is something corrupt or whatever?
View Replies !
How To Specify An Empty Set For An IN Clause
I'm not actually a newbie with MySQL (at least I wouldn't say so), but there are so many forums and yet none of them seem to be the correct one other than this one. I'm doing a SELECT query with an IN clause and another with a NOT IN clause. I want to be able to specify an empty set for these clauses, but I can't figure out the 'proper' way to do this. I tried using [NOT] IN () but this is not valid syntax. I tried using [NOT] IN (NULL) but this always returns the empty set since NULL is considered 'unknown' information and never matches positive or negative. I am now using [NOT] IN ('emptyset') on a numeric column which never matches,
View Replies !
NULL And Empty
Here is the setup.. I have a master-slave setup. Columns that are null in the master show up as NULL during as select as expected, but on the slave they show up as "empty". I get the same results when I query for ' is null'. I've also noticed on the master integers are right justified in the column while on the slave they are left justified..
View Replies !
All Databases Seem Empty
I have a system that uses Mysql I connect as root to mysql from console to see in the database sip When I query for something, it seems that always is empty. But I'm pretty sure that is not empty at all because ibdata1 is big and ib_logfile0 is always updated (look at the date) -rw-rw---- 1 mysql mysql 10485760 Dec 5 17:54 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Dec 12 09:26 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Dec 3 20:37 ib_logfile1 drwx------ 2 mysql mysql 4096 Dec 3 20:37 mysql srwxrwxrwx 1 mysql mysql 0 Dec 12 09:26 mysql.sock drwx------ 2 mysql mysql 12288 Mar 12 2008 sip drwx------ 2 mysql mysql 4096 Dec 3 20:37 test
View Replies !
Empty Database
i have a mysql database on centos 5.2,and i installed phpmyadmin web interface.to start nagios my database is large thats why nagios is taking tooo long time to start ,so i want empty that database for this one what are the options for me does export the database means emptying the database,will it make database empty? how can i make my database empty?
View Replies !
Value Of Empty Cells?
If you try to access an empty cell of a database with a SQL database query, and you assign a PHP variable to the cell value, what is the value of that PHP variable? Zero? Null? so...$variable == 0 ?? or maybe, $variable == NULL ??
View Replies !
Process Table Is Empty
We use MySQL 4.0.13, Redhat 7.3(kernel 2.4.18.3). Apache 1.3.27 Mysql is alive and there are process but I don't see in ps (process table) and very slow web receive.
View Replies !
No Return Value From Empty Table
I'm new here so I don't know if this has been discussed earlier. I have a problem with the following: SELECT IF(column = 'string', 'a', 'b') AS test FROM table; It works just fine if the table is populated. However, if the table is empty (which this one very well can be from time to time) I get no return value at all. Is there any way I can get MySQL to return "test = b" if the table is empty?
View Replies !
How To Check If A Table Is Empty?
I want to check if a table is empty or not? In my table (i.e test) have no records and id is a primary key as well as auto increment column. i have using to find this "select count(id) from test" It gives 1 . ALso using others query but only get answer 1
View Replies !
Empty MySQL Table
I have done a search but can't find the answer to this question: MySQL - 4.1.21-standard phpMyAdmin - 2.8.0.2 I tried to "empty" one of the tables in a recipe script to remove the number of "views" that appeared on the page. It seemed to work, but when I went back to the website, the number of views didn't change. Is there something I am missing?
View Replies !
Filter By An Empty Column
I am trying to get the results from all the rows where the email column is empty but it won't work. If I change the column from empty to something like 'NA' it works fine but I need the column to be empty as I use it for all the email addresses of the people & dont want NA in the email address. Here is my sql statement is it correct as I don't get an error but I dont get any results either & there is about 30 email addresses in there & also about 25 that are empty.
View Replies !
Retrun Empty Record
i execute these query but these return empy result although record exist between these dates SELECT * FROM nisanj_users WHERE reminder_datum BETWEEN '2009-02-20' AND '2009-02-10' or SELECT * FROM nisanj_users WHERE reminder_datum < '2009-02-20' AND reminder_datum > '2009-02-10' i want to get all records between two dates from mysql
View Replies !
Query Returns Empty Set
i have this query: Code: select p.url,s.tipo,pw.url as wrapper from secciones s join plantillas p on s.plantilla=p.clave join plantillas pw on p.wrapper=pw.clave where s.clave=1 it works fine, the problem is that from time to time the second join condition won't match, because by design the field p.wrapper is optional, if that happens the query returns 'empty set' even if the where condition and the first join condition do match, is it possible to modify that query so it needs to match only the first join and the where clause to return the result set (i.e. make the second join optional)?
View Replies !
Select From 2 Tables If 1 Is Empty
I want to collect data from 2 tables without any relation between them in one query. PHP Code:  $query = "SELECT f.str,c.numb FROM ".TABLE1." AS f ,".TABLE2." AS c WHERE  c.cid = 1 "; If TABLE1 and TABLE2 are both not empty, query returns results from both tables. If TABLE1 is not empty and TABLE2 is empty query returns results from TABLE1. If TABLE1 is empty and TABLE2 is NOT, query returns no results at all. UNION doesn´t help because I need result set which distinguish columns from both tables (using php). How can I return results from TABLE 2 if TABLE 1 is empty?
View Replies !
DATABASES Returns Empty Set
I have mysql installed and many projects working off it. Now when i go into command line and type show databases; it says empty set? I have a database on my pc. why isnt it working threw command line????? Its saying empty set.
View Replies !
Update Empty Field / Row
Can we update row when there is an empty field? here is an example (attached): How can I update the x? Let's say x = 6 Like: UPDATE table SET [all_empty_fields]='x' WHERE [field_before_that] ='5';
View Replies !
Java Empty Resultset
I create some java code to get data from a table. Everything is fine. Except, that the ResultSet is empty, i.e. it contains no data, it is not null, though. BUT the table has data in it, if I execute the sql-statement directly in mysql, it works fine. Here is my code: Code:
View Replies !
Order By Another Collumn If Empty
I've got a Table with a few fields, three of them are company, name and lname. My problem is, not every record has a company and I want to sort a list in either company or name + lname depending on if the company field is filled. Code:
View Replies !
How To Create A New Empty Database
i am just trying my hands on MYSQL. I have installed MySQL server 5.0 , MySQL Administrator , MySQL Database Manager My purpose i to setup a simple database and from there do some simple administration , i am more of a developer but i do want to know MySQL setups When i tried to create a new database instance it shows <Local> so i entered the db name , port# (btw is there any default port# ?) and a user name & pwd but it complains of "-4 XSERVER might be inactive" Could anyone help to get me started with creating my first MySQL database ?
View Replies !
Searching With Empty Fields
Can you tell me what SQL would enable me to search with empty fields? For example using a search form with username, firstname & surname, allow users to search for a person with 'joe' as a firstname but if they leave the surname field blank it will return ALL 'Joes' in the database regardless of surname and username. Similarly search for a surname without the other two? In MS Access, it will return all records using blank fields but in MySQL it won't. I could program an if statement to cover all possible combinations but surely there is a better way?
View Replies !
JOIN And Empty Rows
SELECT INTERFACE.iface_key,INTERFACE.size,MY_ACTIVITY.my_activity_value FROM INTERFACE JOIN MY_ACTIVITY ON MY_ACTIVITY.iface_key = INTERFACE.iface_key WHERE INTERFACE.entry_key = 30; What can I do to get all rows, even if there is no iface_key in the MY_ACTIVITY table. At the moment I get no results. I would like to something like +-----------+------+-------------------+ | iface_key | size | my_activity_value | +-----------+------+-------------------+ | 88 | 167 | NULL | | 89 | 166 | NULL | +-----------+------+-------------------+
View Replies !
Empty Moodle Database
I have to truncate all the tables in a Moodle database to restore a backed-up installation. Only there are some 185 tables in the database, so I'd expect there to be something more straightforward than to do truncate tblname 185 times.
View Replies !
Selecting Non Empty Values
I have a table and some of the rows has an empty value in one of the columns. It is blank (in other words, it is not "NULL"). How do I only select the rows that have a value and are not empty. The SELECT WHERE value IS NOT NULL doesn't work, and I understand why since I am putting in blank values.
View Replies !
Sets And The Empty String
I have a field with a data type ENUM. I have set it as NOT NULL with a default value. It appears ENUM allows an empty string as a value, which is strange as I thought the whole point of ENUM is to limit the range of values. I would like it to not allow empty strings and instead, go to the default value.
View Replies !
Random Empty Records
I run a WAMP webserver and host an online entry form for a local running race. I'm getting random empty records inserted into the database. For every 50 or so legitimate entries, I get a blank record or two. A few people have said they signed up, but their infomation is not in the database. At first I thought it was user error, but now I'm not so sure. Has anyone else experienced a problem with random blank records inserted in thier database? Any guesses?
View Replies !
Selecting Field That Aren't Empty
Im trying to run a query to select everything except blank fields however when i run. SELECT clientnumber, LastName, firstname, workphone, email FROM clients WHERE email=NOT NULL; It pulls back "0 rows fetched"
View Replies !
Show Empty Sets
I am wondering if there is a possibility to show empty data sets. Assuming I am having a table with 80 records having uncontinious IDs between 0 and 100 I would like to generate a list showing 100 records with empty fields (just the ID) where there is an empty set. Is there any way to do this?
View Replies !
How To Insert Empty Dates ?
We are migrating a huge software to MySql 5.0. It currently uses a SqlServer 2000 database. A lot of requests were badly written and sometimes insert dates as an empty string '' (not a null, an empty string=''). Empty values are not accepted for date columns by MySql. We tryied several option of sqlmode, but could not manage to make MySql accept these empty dates or transform them as NULL. Modifying all these requests would be a huge job, so we can't go in this way. The only solution would be to have MySql behave like Sql Server.
View Replies !
Removing Empty Fields
I have an application that splits up blocks of text into sentences based on punctuation, and sometimes, like if a user types in "I'm so stoked!!" those two "!"s get turned into empty parts of the array. I'd just like to run a little command at the end of my storage page that deletes all the empty fields in the db, that way maintenence is happening while it's being used rather than having to run a seperate script to maintain the db. So far I have: $query = "SELECT * FROM sentences WHERE sentence IS NULL"; code...
View Replies !
Empty String Vs. NULLL
I have just spent hours figuring out what my problem was, when I realized that an empty string is not the same as NULL. But I'm curious, is there a way to coerce empty strings into NULL? Or, is there a version of COALESCE that works with empty strings? The following code exemplifies my problem: create table test ( id int auto_increment primary key, data text); insert into test set data='' insert into test set id=id; select coalesce(data, 'no data exists') from test; +----------------------------------+ | coalesce(data, 'no data exists') | +----------------------------------+ | NULL | | no data exists | +----------------------------------+ I particularly would like an empty string to evaluate as null because I am dealing with user form submissions. It would be an extra step to convert to actual null (not empty string), or to check each field and not include in my insert if that field is empty. I was tricked so easily since empty string displays as null. Is there a way to turn that off so an empty string looks like an empty string? What is common practice in this scenario?
View Replies !
!empty() Speedy Equivalent
Very simple one, I'm in the optimization phase and have a bite-sized question if anyone can help-- // Trying to select all news where there is an image // The field 'image' stores the image's filename SELECT * FROM news WHERE image != '' My assumption is that this query is slow, naturally, compared to adding a new field called has_image with a value of 0 or 1 and running: SELECT * FROM news WHERE has_image = 1 Is this a sound assumption that the latter would be faster than the former? It does seem a little redundant though to add a new field. Is there a 'best' way to accomplish this task?
View Replies !
NULL Vs Empty String
Does a null value in a field with no value use less space than an empty string? What are the pros/cons of using a null value over an empty string?
View Replies !
SELECTING Field - Is Not Empty
can you do a select based on whether a field is empty or not / true or not basically, i want to do something like $sql = SELECT * FROM table WHERE field IS TRUE or $sql = SELECT * FROM table WHERE field IS NOT EMPTY and then in PHP: i can just do something like $check = mysql_query("$sql"); if(mysql_num_rows($check) > '0') { //do something }
View Replies !
Sum() And Subquery
SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2 FROM `NetworkTraffic` LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId WHERE NetworkTraffic.protId=2 AND date between "2005-10-11" AND "2005-10-18" GROUP BY domainTable.name I am using the left-join above to merge two tables (NetworkTraffic and domaintable) on domainId to find which domain to present. By doing this i summarize the download and upload from NetworkTraffic between 2005-10-11 and 2005-10-18, however i want to be able to summarize the traffic from one day, say 2005-10-12 in column 3 and 4 (Download2, Upload2). Is it possible to make a subquery in the sum-function or should i take another approach? I am using mysql server 4.0.18-max-debug
View Replies !
LIKE ANY + Subquery
I'm trying to get the following statement to work: SELECT * FROM discountItems di WHERE di.name LIKE ANY (SELECT lsw.word FROM ifDefinedSearchWords dsw join ifLinkedSearchWords lsw on lsw.fIFEntityID = dsw.fIFEntityID WHERE dsw.word like 'schoggi') It is supposed to find some words in a subquery as one row, and then search another table for records matching any of those words. I get the following error message: #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 .....
View Replies !
|
TRACKER
