Query To Ignore Results If Condition True
I am having trouble defining a query that ignores rows if a query if true IE.
Column 1 = 1,2,3,4,5
Column 2 = a,b,a,a,c
For example: I search for results from column 1 and if '3=a', ignore all 'a' rows thus returning the result b=>2, c=>5.
View Complete Forum Thread with Replies
Related Forum Messages:
How To Ignore "Query Was Empty" Results
Today i just got into a bit of a rage. Both google and mysql search don't tell me what option to set to suppress 'query was empty' errors. I use multi queries where sometimes there is an empty query (on purpose).
View Replies !
Ignore Differences In Results
In the following query a result is returned for each different "b.source". I don't want this to happen and simply for records to be returned, ignoring this, expressing the min/max date for the account_id....
View Replies !
How To Order Results Depending To The Satisfied Condition
how can I order query results depending on the condition that the specific result satisfy, for example: SELECT * FROM table WHERE cond_1 OR cond_2 suppose that I need to order the query results positioning first the rows that satisfy cond_1 (no matter if cond_2 is satisfied) and then the others. any ideas?
View Replies !
Delete Query Always Returning True
If there perhaps a setting or something I'm doing wrong. $db = db::GetInstance(); $query = $db->query("DELETE FROM ens.`galleries` WHERE `id` = '$id' "); $db->query("DELETE FROM ens.`news_gallery` WHERE `gallery_id` = '$id' "); $db->query("DELETE FROM ens.`gallery_images` WHERE `gallery_id` = '$id' "); return $query; That will always return true, even if query has nothing to delete.
View Replies !
Running A Query That Returns True Or False
Is there a specific type of query that can be ran to simply check if a field/value exists, and return true/false rather than returning the entire row? For instance, I want to check to see if "pie" exists as a value under the field "apple" in the "table" table. SELECT * FROM `table` WHERE `apple`='pie' Rather than returning an entire row, or even the value of apple for example (which would be the string "pie"), is there a way to have mysql simply return a boolean "true" instead? Thanks.
View Replies !
Ignore Spaces In Query
What's the best way to run a query so that spaces in the fields are ignored? For example the following queries.... SELECT * FROM mytable WHERE username = "JohnBobJones" SELECT * FROM mytable WHERE username = "John Bob Jones" would find the following entries: John Bob Jones JohnBob Jones JohnBobJones etc I'm using Perl so removing spaces from the original query string is no problem. It's the MySQL side of things I'm not sure about.
View Replies !
Ignore Duplicate Query Items
This formcode retrieves faxes from a DB but some are the same and I do not want those in my list... how do I tell it to ignore duplicates?PHP Code: $sql = "select c.fax as fax ". "from #__comprofiler as c ". "left join #__users as u on c.user_id = u.id ". " where (c.user_id = u.id) and (u.block =0) and (c.fax != '') and (u.usertype != 'Super Administrator') and (u.name != 'guest') ". " order by c.fax";
View Replies !
Mysql Query Browser...how Do I Ignore " Within A SET Statement?
I'm trying run this in mySQL query browser Update xcart_classes x Set classtext = "5 Year Protection <a href="javascript: void(0);" onclick="javascript: window.open('etp.htm','HELP_POPUP','width=406,height=261,toolbar=no,status=no,scrollbars=yes,resizable=no,menubar=no,location=no,direction=no');" class="PopupHelpLink">Learn More</a>" where Classtext = "Add 5-Year Extended Protection"; Obviously it fails, and I'm pretty sure it's because of the double quotes on my SET statement...any idea on how to avoid that?
View Replies !
More Than 1 Condition In Same Query
I want to select the table and then order it by the start-date which is one of the column and check if the the start date is gone as in start-date > current_time(the time when the query is performed) and then report the relevant top 3 results!
View Replies !
Query Condition Didn't Get Logged
In slow query log query condition didn't logged. Only query with table name omitting condition get logged. What is the reason. How to log the entire query. The following query takes 2s to execute and is logged in slow query log. The original query is : Select count(*) from markingqc where persondate between '2007-07-09 00:00:00.000' and '2007-07-09 23:59:59.999' and PersonName='admin' Logged query is: SELECT * FROM markingqc; How to get the condition in the log?
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 !
Incorporate An Additional WHERE Condition (was "Help With My Query Please?")
I’m trying to incorporate an additional statement in the below query, which works: $sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id FROM room_booking_two b WHERE ((b.arrive BETWEEN '$d1' AND '$d2') OR (b.depart BETWEEN '$d1' AND '$d2')) ORDER BY b.roomID, b.arrive"; Now I want to add the line: WHERE b.property_id=".$_GET['property_id']) So I thought it would work like this: $sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id FROM room_booking_two b WHERE b.property_id=".$_GET['property_id']) AND ((b.arrive BETWEEN '$d1' AND '$d2') OR (b.depart BETWEEN '$d1' AND '$d2')) ORDER BY b.roomID, b.arrive"; But that does not seem to work. Can anyone please advise?
View Replies !
True Vs 1 Vs 1=1
In a where clause, which of the above is the fastest and most portable? I have things such as: WHERE somefield = 5 and ( 1 or someOtherField = 1 or someOtherField = 2 ) I originally used just 1 (or 0 for false) because I figured that was the safest way, and (not much) less processing than 1=1, but I was told to use true/false for portability. I ended up changing a bunch of my queries to use this and now a bunch of people are having MySQL errors (true is not a field). Anyway, should I change back to using 1s and 0s or should I use 1=1 and 1=0?
View Replies !
TRUE Binary
I have a Status column, stating whether a particular record is active or not. Right now, the way I have it is set that the column is an ENUM('Y','N')... The bad thing is when I try to access the database from some other programming language, like C++, my "binary" column isn't really such... It is not a TRUE binary column. Does MySQL have any kind of TRUE binary? I mean, right now i've got a couple of workarounds, but they're not as effficient, or as pretty as i'd like them to be
View Replies !
Query Won't Use Index On "OR" Condition
I have a table full of teams: Code: team_id, name And a table full of fixtures: Code: fixture_id, home_id, away_id, date The fixtures table is now quite large and this week I added indexes on the "home_id" and "away_id" columns (in addition to the existing primary key on fixture_id). I run a query which joins on the following: Code: JOIN fixtures ON (fixtures.home_id = teams.team_id OR fixtures.away_id = teams.team_id) But it does not use my indexes for the join.... it therefore scans around 40,000 rows. If I remove the "OR fixtures.away_id = teams.team_id" and just join the home ones, the number of rows scanned goes down to 11. Is there a reason that the "OR" operator stops the index being used? Do I need to re-write my sql so that it searches 11 rows twice (one home, one away) instead of doing the "OR" clause?
View Replies !
Hibernate.show_sql True
This statement hibernate.show_sql true switches on or off the writing of SQL statement console, but how can I stop hibernate writing into the server.log file of JBOSS?
View Replies !
AutoReconnect=true Not Working
I have a web service installed on a Tomcat 6.0.18 using a MySQL Server 5.1 as persistance. The connection url has a autoReconnect=true included. Whenever the web service is not accessed for more than 8 hours, the connection to the database fails? How come? Isn't that what autoReconnect was suppose to do? Everything else works fine regarding communicating with the database. And the error from the web service states that I probably should either extend the timeout period or use the autoReconnect=true property. I do not want to extend the timeout period as I think it imposes a bad solution. I would rather use the autoReconnect property but it does not work. What do I do?
View Replies !
ORDER BY With A Value 'menu=true'
I need an order by clause on a roles table that has the role and also if it should be ordered first. So what I gotta do is order by the field menu = true then by the field id. The menu field tells the UI if we should show the role in the role menu, so if it's set to true then it shows at the role menu if it's false then we don't show it. For example I have the IDs 24 34 56 17 22 And the IDs 56 and 22 have menu = true and the other have menu equal to false. I need to figure out how to first order by the menu = true and then by the Id so it would produce this result: 22 56 24 34 17
View Replies !
Type True False
Which Type should i use when i want a value of a field as true and false. Should i use enum or tinyint ? or anyother which is mych more suitable. I will be using CakePHP for development.
View Replies !
Return True From Stored Procedure
I have never created a stored procedure so please bear with me. I wish to create a stored procedure where if a condition is met true is returned, else false is returned, something like: ...
View Replies !
Address True/false Values
I am trying to convert a dbf table to mysql table. The dbf table has values for a certain field as T or F representing true or false entered depending on the value of a checkbox in the interface. What would be the datatype for this column in Mysql's table so that the user when checks a checkbox on the interface a value true or T(preferably) is entered in the database(Mysql).
View Replies !
Putting In And Taking Out True/false Instead Of 1/0
In mysql 5.0, as far as I understand it, BOOLEAN is an alias for tinyint(1) How far does that aliasing go? When you INSERT, does it have to be a 1 or 0, or can it be a true/false? Same with SELECT, does it have to return 1/0, or can it be true/false? If it matters, I'm working with PHP.
View Replies !
Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?
View Replies !
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Query Results By Pages
I would like to display query results in pages of say 20 results each. e.g. a table with two columns, serial# and descrition, with a few thousand entries. What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in? e.g say serial#=3211 is in row 64, display rows 60->79.
View Replies !
Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now: ProductLine=`mysql -u www -s support <<EOF SELECT ProductLine FROM Documents WHERE ID = '$ID'; EOF` DocType=`mysql -u www -s support <<EOF SELECT DocType FROM Documents WHERE ID = '$ID'; EOF` DocName=`mysql -u www -s support <<EOF SELECT DocName FROM Documents WHERE ID = '$ID'; EOF` FileName=`mysql -u www -s support <<EOF SELECT FileName FROM Documents WHERE ID = '$ID'; EOF` Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this: SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID'; I don't know how to take that output and put it into my shell variables.
View Replies !
Saving Query Results
Is there any way to save the results of a query without the quote marks? When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.
View Replies !
Query Giving Me Results I Don't Want.
query: SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP( shop_orders.date ) AS date, sales_reps.stage, sales_reps.status, pi.fname AS pfname, pi.lname AS plname, sales_reps.date_to_shop, users.builder, users.division, shop_assignments.editor_id, shopper.fname AS sfname, shopper.lname AS slname, ei.shop_result, ei.good_tape FROM sales_reps LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id LEFT JOIN users ON users.user_id = shop_orders.builder_id LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id WHERE sales_reps.status = 'Rejected' AND ( ei.shop_result != 'Trip x1' OR ei.shop_result != 'Trip x2' OR ei.shop_result != 'Non-Reg Trip x1' OR ei.shop_result != 'Non-Reg Trip x2' OR ei.shop_result != 'Office Closed' ) ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC It's not filtering out the records with the shop_result field matching what I've listed.
View Replies !
Optimization For True / False Values (tinyint(1))
I search through mysql forums but couldn't find a concise place for this topic. I have a generic table storing a user_ID and some true/false values like 'is_logged_in' etc. I've discovered that the best way, storage wise, to store those true/false values is through a tinyint(1) with values of 0/1 marking false/true. I'm wondering what would probably make sense for unique auto incrementing user_ID's. Assume that it may need to get into the millions of users. Any thoughts? Right now I've got it set to int(10) unsigned.
View Replies !
How To Get Distinct Hour(s) In Query Results?
I need to write a query that will count the number of records that have been written for each hour of the day. This must be done entirely in SQL, and I am not an SQL expert. I know how to break the hour out of a timestamp using the hour() function, but I'm unclear on how to get the breakdown like I described above.
View Replies !
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
View Replies !
Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together. $query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName) The output is this(when I run a query in mysql) UserName..............SUM(AcctOutputOctets) test..................... 345566 Basically i'm adding up all the users entries and totaling their Usage. Now the question. I want to write that to another table named mtotacct. format like this. Username..............TotalOctets. I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.
View Replies !
Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows: SELECT MIN(Freq_Min) FROM datatable; and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....
View Replies !
Use Query-results As Delayed Insert?
Can I use the results of one query like: -> SELECT user_id FROM tbl_customers And use the results to fill the VALUES-statement in another table like: -> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>) If so, could someone complete my second SQL-statement, since I'm staring at my CRT for over 2 evenings and I cannot get it to work.
View Replies !
Query Is Not Returning Desired Results.
My query is not returning desired results. I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name? the query PHP <?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?> There are six tables involved: 1. table_categories (cat_id medint, cat_title varchar) 2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint) 3. table_forums (forum_id smallint, cat_id medint, forum_name varchar) 4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint) 5. table_posts_text (post_id medint, post_subject varchar, post_text text) 6. table_users (user_id medint, username varchar)
View Replies !
|
TRACKER
