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.
View Complete Forum Thread with Replies
Related Forum Messages:
Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57 # User@Host: counter[counter] @ localhost [] # Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647 SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7; mysql> select count(*) from _1_log; +----------+ | count(*) | +----------+ | 111824 | +----------+ 1 row in set (0.00 sec)
View Replies !
Check If Table Is Exist Or Not
May this question exist....But i confused...I want to check whether the table is exist or not .... Like i run a project , on that project i create a mysql tables while run time.... on that i want to chech a table is exist or not... if employee table is exist .... exit; else create table employee
View Replies !
Implementing The CHECK Clause`
I am working with mySQL version 4.1.14-max on an Apple Mac. I have been tring to finish up a homework assignment to create the following domain: CREATE DOMAIN Id INTEGER CHECK ( VALUE > 999 AND VALUE < 99999); and can't seem to get it to work. I also try addeding the CHECK clause directly into the table, which worked. But, the table is not enforcong the range requierments. After searching the web, I found that mySQL will parse and ignore the CHECK clause when used with CREATE TABLE. So, I have no idea how to restricted the range on the Id attribute.
View Replies !
Check Empty Table In Where Clause?
the following where clause (expurgated) suits my needs right up until the table emailman is empty (which it frequently is) and then it returns an empty set (which is bad;-) this is actually an AND emailman... but don't I think the rest is relevant. WHERE emailman.related_id != contacts.id How can I modify this to allow for emailman to be empty? Something like... AND (emailman.related_id != contacts.id || NOT EXISTS (emailman.id) )
View Replies !
Joins To Rows That Don't Exist
I want to join two tables together, but if the secondary table doesn't have a row that matches, I want the primary table's row to still be returned, with the values for the secondary row being their default values. How do I accomplish that? For example: if I have two tables: t1: defaults:(a=0,b=0) +---+---+ | a | b | +---+---+ | 1 | 3 | | 2 | 4 | +---+---+ t2: defaults:(a=0,c=0) +---+---+ | a | c | +---+---+ | 1 | 7 | | 3 | 8 | +---+---+ I want to execute a query like: SELECT t1.a,t1.b,t2.c FROM t1 JOIN t2 USING(a) But I want the result set to be +---+---+---+ | a | b | c | +---+---+---+ | 1 | 3 | 7 | | 2 | 4 | 0 | +---+---+---+
View Replies !
Incrementing Rows That May Not Exist
I'm sure there's a very simple solution to this, I just haven't been able to find one I understand yet. I have a table with 3 columns, the first two are used as an index, and the third is a counter. So far so good:
View Replies !
Selecting Rows While Using Values From These Rows In The Where Clause
I have a table which includes 3 columns: id: just an id name: a name parent: the id of the row which this row is a child of. In my example I want to select the rows with the id 5, it's parent, and the parent's parent. I found a syntax that workes in this case: SELECT `name`,`id` FROM `locations` WHERE `id`='5' or `id`=(SELECT `parent` FROM `locations` WHERE `id`='5') or `id`=(SELECT `parent` FROM `locations` WHERE `id`=(SELECT `parent` FROM `locations` WHERE `id`='5')) However my question now is if this syntax could be shortened while still working, and also if there is a way to allow for rows to be selected that do not have a parent( value 0 in my table) or that only have a parent but no grandparent.
View Replies !
Selecting Rows From One Table With A Key That Doesn't Exist In Another.
I'm trying to select rows from one table that does not have a key in another table. For instance: I have a hospital table with two columns, id and name. I have a patients table with three columns, id, name, and locationid. Now I want to find those hospitals that have no patients. So I want to get those hospitals whos id does not equal any of the locationid's in the patients table.
View Replies !
Check Rows Exists?
That way, if there are no attachments, I'm not checking the attachment row after, saving a query. I looked into EXISTS, right? Example of how its done now Code: $db->query("SELECT * FROM POST LIMIT 15"); while ($result = $query->fetch_object()) { $attachments = $db->query("SELECT * FROM Attachment WHERE post_id = ".$result->id); Example of how I hope it can be done Code: $db->query("SELECT p.*, a.id as attachment FROM POST p LEFT OUTER JOIN Attachment ON a.post_id = p.id LIMIT 15"); while ($result = $query->fetch_object()) { if ($result->attachment > 0 ) { $attachments = $db->query("SELECT * FROM Attachment WHERE post_id = ".$result->id);}
View Replies !
Update Script, But Check If The Value Is The Same On Other Rows
I have a nice user script. In a particular section there is the ability to edit the users on the databse. What I am concerned about is that people may edit the username or email and find its the same username or email that another user has, this can screw up the login system especially two usernames being the same. So is it possible to make it so that if the email/username is the same in another row to give an error, Please note that the input fields for their email and username are already filled in so if they dont come to edit it them they will still output there original username/address.
View Replies !
Check For One Piece Of Data In Multiple Rows.
I have a table: (arbitrary names bellow) main_id info info info id1 id2 id3 id4 id5 id6 id7 id8 id9 info ---- id1-4 will always have info 5-9 may be null. I need to do a select that will find if certain data is in id1-9 and return all rows where this matches. SELECT * FROM `recipes` WHERE id1 =1032 OR id2 =1032 OR id3 =1032 OR id4 =1032 OR id5 =1032 OR id5 =1032 OR id6 =1032 OR id8 =1032 OR id9 =1032 ORDER BY class LIMIT 0 , 30 is a bit cumbersome. is there a better way to do this query? Also... I am not concerned about speed when row is added to a table, but want speed when this query is made. is there a good index structure to make? And if data is already in, is there a way to make it re-index the table?
View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account. For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done? I run MySQL 4.0.17.
View Replies !
Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The thing is, in my system, I have a set of users who have a 'quota' limiting their usage. They can create n number of tables and put m number of rows in it, but at the end, their 'usage' can not exceed, lets say, x MB. My problem is, how to I evaluate the space being used, given the fact that I can pull all data for a user by including a user ID in the where clause. In other words, I need to find size of the result set containing all user's rows. I am using mysql jdbc driver and not sure if there is any API that deals with the physical size of the resultset. resultset.getFetchSize() talks in terms of rows v/s the actual space they take.
View Replies !
Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working
View Replies !
Delete Rows From Multiple Tables (AND & WHERE Clause)
I have a database containing the folllowing tables; PUPIL id ... ROA pupil_id ... MESSAGE id pupil_id ... REPLY message_id ... How can I delete all the rows from all tables containing the same pupil id and all replies to messages from the same pupil? I managed it using AND clauses in WHERE but if there is no data in say the MESSAGE table then it doesn't work.
View Replies !
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
View Replies !
Query To Check Space Used
SELECT TABLE_NAME as 'Name table', TABLE_ROWS as 'NB lines',ROW_FORMAT as 'Format Table' . , Max_data_length as 'Max_Data_Length', Data_length as 'Data_Length'....
View Replies !
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.
View Replies !
Applying A WHERE Clause To Several Rows On A Joined Table To Select A Single Row
I have one table which is users for example id | username and another table called ratings for example user_id | title | rating and I want to select users who have rated more than one thing... I can do this easily if I want to select users who have rated just one title using the following query SQL Code: SELECT * FROM users INNER JOIN ratings ON users.id = ratings.user_id WHERE ratings.title="blah" GROUP BY users.id but cannot think of a way to do this for more than one rating. Doing SQL Code: SELECT * FROM users INNER JOIN ratings ON users.id = ratings.user_id WHERE ratings.title="blah" AND ratings.title="blahblah" GROUP BY users.id
View Replies !
Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured. A & B Table A, has column: Product (product code) as primary key Table B doesn't. Apart from that they have the exact same fields. There's also a column: supplier I want to Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes. mysql version 4.0.27
View Replies !
Mail Subdomain Check Query
I have table called mail_domain. It contains fields named as domain_id varchar(50) primary key, create_date timestamp, update_date timestamp Now suppose one entry in is as below yahoo.co.jp,'2008-08-08','2008-09-08' In my application user enters email id and I have to check whether the domain part of that email id is in mail_domain table or not. I can isolate domain part in php code. But problem is if user enters a email id with subdomain, I cant check for it in mail_domain table. e.g. If user enters 'mail.yahoo.co.jp' then I should be able to retrieve row whose id is 'yahoo.co.jp'
View Replies !
Check Query For Syntax Prior To Execution
I was wondering if it was at all possible to check an SQL query prior to execution for syntax errors? A method which wouldn't alter the data to which the SQL statement related to. I'm guessing you could perform a transaction of sorts, but is there another way?
View Replies !
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)
View Replies !
IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS.. I'm trying to do this: insert into table2 (field1) select field1 from table1 where field1 not in (select field1 from table2) delete from table1 where field1 in (select field1 from table2) 1. Insert field1 from table1 into table2 if it doesn't already exist there... 2. Once moved, delete from table1.
View Replies !
Nested Query With In Clause
I have 2 tables, tbl_usr and tbl_client tbl_usr int_usr_id str_password int_client_id tbl_client int_client_id str_login I want to return int_user_id from tbl_usr with 1 query supplying just str_login and str_password. This is what I have:Code: SELECT tbl_client.int_client_id AS clientID FROM tbl_client WHERE tbl_client.str_login = 'test' IN (SELECT int_user_id FROM tbl_usr WHERE str_password = 'password' AND int_client_id = clientID)
View Replies !
Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5 this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent). I want to select all the rows that has :7: in the sections field.
View Replies !
Using STRCMP In WHERE Clause For A Query
Code: SELECT group_name FROM groups_table WHERE STRCMP('group_name', 'GUESTS') AND STRCMP('group_name', 'BOTS'); But that didn't work. I worked around the problem in php by putting the group names I don't want in an array and then using !in_array() to filter out the unwanted groups, but i assume that is less efficient. Am I correct that it is less efficient to do this in php, and what was I doing wrong on the mysql query? [edit - it just occurred to me that maybe 0 is not false in mysql so that I should have said Code: SELECT group_name FROM groups_table WHERE STRCMP('group_name', 'GUESTS') <> 0 AND STRCMP('group_name', 'GUESTS') <> 0; Is that the secret (I'm not around my pc now so I can't check yet). And even if that is the answer, is it better to use SMPCMP or php's !in_array() to do this filter?
View Replies !
SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem: SELECT * FROM footballers WHERE sname LIKE '%$criteria%' however, if I want to join two tables and still use the LIKE clause how would the syntax look?? My guess below was incorrect. any help would be appreciated. thank you WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)
View Replies !
Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc). When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query. I tried it under phpmyadmin, and once again I get zero rows. Why do I get inconsistent results? Here's the query:
View Replies !
Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000. I have need to use a reference in the outer from clause in a subquery in the select clause. Consider the following example: Select (select b.baitID from b where b.entrydate curdate()) as wantedBaitIDs from bait_tbl b; My actual need is more complex than this as part of it is a rough cross tab. If I try to define the table in the alias, not only do I lose whatever benefits there are in the particular join I would use in the outer from clause but would also require the join to be defined in each subquery, requiring it to be examined each time it is used. To be absolutely clear, in this example I want to use bait_tbl with the alias of b in the subquery. In my actual query I reference the same table twice with a different join set for each. I need to reference a particular alias as that has the join set I need.
View Replies !
Query A Single Table, But Check A 2nd Table First
I have two tables. tblUsers and tblOrders. tblUsers contains the user information, userID, userName, userPassword, etc. tblOrders contains a list of all the orders placed by the users, it includes a userID column to link an order to a user. What I want to do is run a query and return a list of users who have NOT ordered anything. Because tblOrders has a userID column, I can pull up a list of users who have ordered. But I want to do the opposite.
View Replies !
Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC; It generates somewhere around 2.9m rows. I want the last 10: SELECT expensive query ORDER BY field DESC LIMIT 10 But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10 We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?
View Replies !
Several Rows From One Query
I've this sql-query... $sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']"; The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...
View Replies !
Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like: Total Prizes ____________________ 100 of 850 ......
View Replies !
Eliminate Rows In A Query (without Many OR)
This is my query : SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas There must be a more efficeint way to find what i want than this : AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 Something like cas.id is not (list of values)...
View Replies !
Query Help, Comparing Rows
Suppose I have the following data: +----+----------+-----+-----+-----+-----+-----+-----+-----+ | Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat | +----+----------+-----+-----+-----+-----+-----+-----+-----+ | 11 | 11:20:00 | F | T | T | T | F | F | F | | 12 | 11:45:00 | F | T | T | T | F | F | F | | 14 | 12:10:00 | F | T | T | T | F | F | F | | 15 | 12:35:00 | F | T | T | T | T | T | F | | 17 | 13:00:00 | F | T | T | T | T | T | T | | 18 | 13:25:00 | F | T | T | T | T | T | T | | 19 | 13:50:00 | F | T | T | T | T | T | T | | 20 | 11:28:00 | F | T | T | T | T | T | F | | 21 | 11:53:00 | F | T | T | T | T | T | F | | 22 | 12:18:00 | F | T | T | T | T | T | F | +----+----------+-----+-----+-----+-----+-----+-----+-----+ I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.
View Replies !
How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent... I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update. Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.
View Replies !
SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do SELECT * FROM case_studies It outputs all rows. The query i am usign is: select programs.program_title, programs.id, case_studies.id, case_studies.title, case_studies.author, case_studies.timestamp from case_studies, programs WHERE programs.id = case_studies.id Any ideas?
View Replies !
Average Query With 2 Rows From Same Table
My table: "answer" answerID answer(int) questionID(int) userID(int) answer1 is questionID = 1 answer2 is questionID = 2 WHERE userID is the same for both answer1 and answer2 I want the average of answer1/answer2: AVG(ans1/ans2), but how?
View Replies !
Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out. The query I'm running is too slow...can anyone tell me what I can do to speed it up..if I can at all? I was wondering if because ZipListMatrix has 3.3 million rows that 8 seconds is all the faster it's going to be. Any help is greatly appreciated! I have already "optimized" the tables.
View Replies !
Get Rows From Multiple Tables In One Query
Is there a way to select rows from multiple tables in one query? Say I have the following tables and columns:Storestore_idItemitem_iditem_store_id I want to get a store by it's id + all the items associated with that store id. Do I have to make two separate queries for this? One to get the store, and another to get all the items for that store.
View Replies !
Query Browser :: How To View Rows?
I'm having a hard time with the Query Browser. I can't figure out how to view the rows. When I click on the table in the Browser, nothing shows up and at the bottom there is a message saying Library Error Number 3. I am sure this is something simple but it's making me tear my hair out.
View Replies !
Performace Question. 525,600 Rows In One Query
I am wondering which would be the best option. I have a database holding prices for every minute of trading. This means that getting a year of price data for graphing would mean that I am potentially requesting 525,600 (more on a leap year) rows. The graph only needs to have a resolution of one point per day, so should I: 1. query 525600 rows and then take one price per day 2. create a query to only take one price per day ?
View Replies !
|
TRACKER
