Sql Query To Exclude Specific Data From Resultset
I have got two tables,one customer with fields cust_id,email and another with field cust_id,domname,expirydate. i have this query to combine these two tables to give me email and the expirydate as
$sql = "SELECT l.email,s.expirydate from customer l, domain s where l.cust_id = s.cust_id ";
I need to exclude those expirydates whose domname contains .np in the text.
View Complete Forum Thread with Replies
Related Forum Messages:
Exclude Duplicate Data
Hello MySQL Gurus - I have 3 tables: all_customers, opt_out_customers, and opt_in_customers Let's say there's an id column and an email_address column in each. I'm sending a newsletter - I want to send it to: all_customers except where opt_out or opt_in opt_in except where in all_customers (so as not to send it twice) I have to use MySQL 4.0. (no sub-selects, etc.) I had previously tried with a 'LEFT JOIN' but it only seemed to work to exclude one or the other (e.g. where NULL). Might a 'UNION' work here?
View Replies !
How Would I Exclude Certain Categories Within My Query?
My client has a cart for which I've made a custom homepage. We use this query to display the 3 newest items on the homepage: $query = 'SELECT * FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` DESC LIMIT 3'; Now, my client has several categories which he does not want the items of to be displayed on the homepage. In the database the column is "category_id" which is within the same table as in the query shown above. How would I exclude several categories from being displayed via the query above? ....
View Replies !
ResultSet Is From UPDATE. No Data.
I have a probleme executing a query using a PreparedStatement, it results in a SQLException with the message "ResultSet is from UPDATE. No Data.". Below I added the profiler log which indicates that the statement-id changed between [PREPARE] and [QUERY]. Shouldn't it be [PREPARE] and [EXECUTE] anyway? INFO: Profiler Event: [PREPARE] at org.jboss.resource.adapter.jdbc.BaseWrapperManaged Connection.prepareStatement(BaseWrapperManagedConn ection.java:360) duration: 62 ms, connection-id: 0, statement-id: 43, resultset-id: -1, message: SELECT name,value FROM Attribute WHERE foreignuuid=? 2006-09-18 10:47:30,176 INFO [STDOUT] (Timer-4) 18.09.2006 10:47:30 N/A N/A INFO: Profiler Event: [QUERY] at org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(WrappedPreparedStatement.java: 296) duration: 32 ms, connection-id: 0, statement-id: 44, resultset-id: 0, message: SELECT name,value FROM Attribute WHERE foreignuuid='c01b997f0a0dfd8e018a4aeffbfeb45f' I'm using JDK 1.5.0_06, mysql-connector-java-3.1.13-bin.jar with JBoss 4.0.2 and mysql 4.1.10 on W2K. I'm using JNDI to get a DataSource and retrieve a Connection object. Can anybody think of reason why the statement-id changes? Is this change the reason for the SQLException mentioned above?
View Replies !
Specific Data
Ive been struggling with this for some time, wonder if someone can help, I have a database table column that has some information within it that I do not want, however there are over 100,000 records and it'll take some time to update them all - I'm trying to find the sql command to update the info currently I have: PHP Code: REPLACE * from table WHERE `column` LIKE '%data_not_needed%' I want to replace it with nothing so it simply takes the not require word / data out from that column - what else can I add into that sql command to show this?
View Replies !
Select Only Specific Data In The Fields How?
I have a table called cclp_players now that table has 3 fields for instance and has data see below, ------------------------------- | player_id |team_id|division_id | ------------------------------- | 001 | hp | golem | | 002 | ibm | cestino | | 003 | sun | mac os | | 004 | del | bim os | | 005 | acer | ms os | Now i want to SELECT the table either by team_id or by division_id. And let say i wanted to SELECT team_id by sun. What is the right expression or combination of SELECT command for this?
View Replies !
Pulling All Data From Specific Month
I want to pull out all the data entries I've made for a specific month by using a $_GET method (archived.php?month=october). My field time rows look like "1161065143". How can I do a mysql_query for this? Here's what I have so far: mysql_query("SELECT * FROM entrynote WHERE time = '$date' SORTED BY time DESC
View Replies !
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
Question About A Specific Query
imagine I've got user that can have visual preference (enabled or disabled) saved on a table. Before a user make change of a preference, there is no row associate to that user and by default, the preference is enabled. Now, is it possible to do a query that will output user that have preference set to enabled (those who have update their preference) while also outputing those who have no row in the table preference? I know it may be not super clear, but I need to know before working on that script. So here a sample to make sure it is all clear: TABLE USER (id - name - email) 1 - NameA - a@a.com 2 - NameB - b@b.com 3 - NameC - c@c.com TABLE PREFERENCE (id - label) 1 - Show warning 2 - Show help TABLE USER_PREFERENCE (user_id - preference_id - status) 1 - 1 - Disable 2 - 1 - Enable So if I want to output user email that show warning, it should give me: b@b.com, c@c.com My problem so far is, how to retreive user that have a user_preference row enabled, and user that have no user_preference row in the same query!
View Replies !
Increment The Specific Field By 1 In A Query
I still do the process of incrementing a specific field in a stand alone apps by query 1: get its value query 2: update value +1 But, i realize that this is really a bad idea when this would be used in web applications... specially the database is in another webhost....(very slow response) i tried (using php function) Expand|Select|Wrap|Line Numbers
View Replies !
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
View Replies !
MySQL Admin - Lost Thread On Specific Like Query?
mySQL win32 (4.0.22) I am using mySQL Control Admin and seeing a lockup on a thread specific to a like query. select * from table1 where field1 like 'http://h%' limit 100 This request will sit (thread) forever and never return any records. (status : sending...) However : select * from table1 where field1 like 'http://h%' limit 100 returns records immediately, no problems... I have already run : /mysql/bin/myisamchk --sort-recover dbtable1, no errors, no problems... How can I tell what this thread is really doing or if any additional problems exist with this table using myisamchk?
View Replies !
Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following: ----------------------------------- SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC while(FetchRowAssoc()) { SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id echo cat_it, cat_name, itemcount, blah... etc. } ------------------------------------ I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table. Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category? "SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.
View Replies !
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
View Replies !
Exclude Join
I Need to pull a job number from table "A" and table "B" as long as that same job number is not included in table "C" I can not for the life of me figure this out. Does anyone have any suggestions? Table "A" | jobnumber |due_date Table "b" | jobnumber | pack_num Table "C" | packnum
View Replies !
Exclude Rows
For example SELECT * FROM dater JOIN dater2dater ON (dater2dater.dater1_id = dater.dater_id OR dater2dater.dater2_id = dater.dater_id) Well that would get all the daters that the user has rated. How would I get only daters that the user hasn't rated?
View Replies !
How Do I Exclude From Table?
I have a table A and a view B that is a subset of A. Table A has 2 columns, ticket and link. Every row has a unique ticket#. When a transaction closes a ticket, that row's link value will have the ticket# of the matching trans. To get View B, All I had to do was select where link is not 0. How would I get the rest of Table A? JOINS giving me a headache. How would I select from Table A whose link is not the ticket in View B.
View Replies !
Exclude Duplicates . . .
I have a table that stores ipnumbers of visitors who have triggered a "trap" and are thus banned from the site. It also contains those who have restored themselves by following a certain procedure. When restored, they are not removed from the table, so it has duplicates, since visitors may be banned and restored multiple times. I would like a query that returns only ipnumbers that are truly still banned - so I need to only return one ipnumber for each visitor and I do not want any ipnumber returned if the visitor has been unbanned. I tried: SELECT `ip` , `action` FROM `trap` WHERE `action` = 'banned' AND `action` = 'unbanned' ORDER BY 'ip` ASC But it returns 0 records.
View Replies !
COUNT Row But Exclude 0
I have a table called 'userrating' with 3 INT fields in it as shown with sample data: rating 0 3 rating2 2 4 rating3 0 0 I want it to count each row of each field in seperate queries BUT ONLY if there is not a '0' in that row. So the count of: rating = 1 rating2 = 2 rating3 = 0 I have a start put i don't know how to check for zeros in the rows, here is what i have for the first field rating: $ratinginfos = $db->query_read("SELECT *, COUNT(*) AS totalvotes, SUM(rating) AS total FROM " . TABLE_PREFIX . "userrating WHERE userid = $userinfo[userid] AND active=1 GROUP BY userid ORDER BY dateline DESC");
View Replies !
Join To Exclude Records?
I have two tables joined on email address. I would like to include all the values in table 1 that do not exist in table 2 (basically, the opposite of an inner join).
View Replies !
How To Exclude Particular Records In MySQL?
My PHP script has a SQL statement as shown below that works fine, but it is slow and inefficient. I need to speed it up by using one sql statement instead of using the sub-query. The solution I need is to eliminate the use of the NOT IN clause ie SELECT DISTINCT CONCAT(fname,' ',lname) AS realname, staffid FROM staff WHERE team = 'umi' AND staffid NOT IN (SELECT staff.staffid FROM staff, calendar WHERE calendar.staffid = staff.staffid AND calendar.type IN ('arl','lsl') AND (calendar.start BETWEEN '$begin' AND ADDDATE('$begin', 6) OR '$begin' BETWEEN calendar.start AND calendar.end) ) where $begin is a PHP variable that has a value like this '2007-11-20" etc. What I want is that it excludes staffid that is already defined in the calendar table by start and end fields. I tried to make it more efficient, but I could not solve.
View Replies !
Exclude Records In Between Two Dates
Basically, I have two tables: create table dresses ( id int unsigned auto_increment primary key, reference_number varchar(16) ); create table bookings ( id int unsigned auto_increment primary key, dress_id int unsigned, booked_from date, booked_to date, reason varchar(32) ); Now I can select everything from dresses with all my required criteria, but what I need done is that the select statement excludes any dresses that are within a specified time frame. For arguments sake: I need a dress from '2006-01-10' to '2006-01-14'. I want to see all the dresses in my database that arent currently booked out inbetween those two date. So it would be something along the lines of: SELECT dresses.id,reference_number from dresses,bookings WHERE (booked_from NOT BETWEEN('2006-01-10') AND ('2006-01-14')) AND (booked_to NOT BETWEEN('2006-01-10') AND ('2006-01-14')) ; Problem comes in with the relationship in-between dresses and bookings clearly. How do I make it display all the dresses?
View Replies !
Using The NOT Operator To Exclude Fields
I want to be able to select all fields from a table except certain fields. I came across the NOT operator in the MySQL ref. manual, and I wanted to check about the syntax of that operator. Would this syntax work: SELECT NOT id1, id2, id3, id4 FROM table Or would this work: SELECT NOT id1, NOT id2, NOT id3, NOT id4 FROM table If neither, what is the shortest way that would work?
View Replies !
SELECT And Exclude Rows
SELECT * FROM myTable ORDER BY DATE DESC The result of this query is: 17/08/2008 17:11:38 CERTALDO BOSCO 17/08/2008 16:58:01 CERTALDO BOSCO 17/08/2008 15:05:27 CERTALDO BOSCO 17/08/2008 14:48:31 CERTALDO BOSCO 17/08/2008 14:43:38 CERTALDO BOSCO 17/08/2008 09:43:56 SUBBIANO CAPOLONA 17/08/2008 00:16:13 ROSIA DOCCIO...
View Replies !
Making Random Call But Exclude Something
I did made up a box where it querys 1 random content from the table. It looks like this: SELECT * FROM e107_pcontent WHERE content_heading AND content_icon AND content_id ORDER BY RAND() LIMIT 1 It works perfect but my problem is my contents also do have categorys and those are stored inside e107_pcontent too, just like an casual content. In order to avoid displaying categorys when it outputs a random content I wanted to exclude my categorys. How do I do it? I believe there are ways to do since they share the same information like normal contents, so I was thinking maybe I could make something up like an order "if content_id is number 1 skip the call and show next entry, if content_id is number 4 skip the call and show next entry" you know?
View Replies !
Exclude Double Cols In JOIN
I have two Tables: ----------------- Workers ----------------- Cols: ----------------- ID Name AdressID EditedBy EditDate ----------------- ----------------- Adresses ----------------- Cols: ----------------- ID Street Town EditedBy EditDate ----------------- Now I JOIN this two and want to exclude the "Double" Cols: ID, EditedBy and EditDate. How can I do this without writeing every single Col?
View Replies !
Using ResultSet From One Selection In Another?
here's my dilemma: I have 2 databases. One has an ID that corresponds to a column in the second. currently, i do this: select ID from table where (some criteria) then, in the second table: select count(*) from table2 where ID2 = 'result from first'; is it possible to just pipe the results from the first criteria to the second so i can do this with one query instead of 2?
View Replies !
Can I Get Info About What Is In A Resultset?
I have a php class function that builds a query (on a single table) and invokes it to produce a resultset. Another function, would like to discover what is in that resultset so that it can display it nicely (ie appropriate name and widths for columns). I found the the SHOW command is able to give this sort of information for tables, but I could not find a way of getting the same infor for resultset's.
View Replies !
Order Of Resultset With IN
SELECT * FROM table WHERE id IN (4,2,3,1) always returns a result set with the id field in ascending order. Is it possible to return the result set in the order given to IN. (4,2,3,1 instead of 1,2,3,4)
View Replies !
Get MySQL To NOT Sort My Resultset
The following query works, but MySQL sorts the results set: SELECT STRINGTEXT FROM WEBSTRINGS WHERE GUI=0 AND LANGID='GB' AND TOKENID IN (312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62, 63,87,88,89,90,208,210,249,309,310,311); This means that when I grab this in my recordset the data for TOKENID 312 is at the end rather than being the first one, eg I expected my resultset to come back in the following order of requests: 312,47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,6 3,87,88,89,90,208,210,249,309,310,311 but it comes back as: 47,48,49,50,51,52,53,54,55,56,57,58,60,61,62,63,87 ,88,89,90,208,210,249,309,310,311,312 Is there anyway to get MySQL to not do this for this query? I really need them to come back as is.
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 !
Position In Resultset As Column
I want to make a SELECT query that has a column which contains the Position of the row. I can't explain that very well so let me show it to you: SELECT * FROM ( SELECT positionInThisResultSet, COL1,COL2,COL3 FROM Table ORDER BY COL1 ) ORDER BY COL3 So basically what I want is to sort by one column and have the position in the result set be a column in another result set that has a different order. the only way I could think of would be saving the position in the table which would be stupid because the position changes when data is added or edited.
View Replies !
Url Parameter In Mysql Resultset
how i can make a hyperlink in my mysql resultset. Se a screendump below. Here you can see links to different pages on my site in the column "url", but it is not possible to click on them. Code:
View Replies !
Export Resultset To Excel
I am trying to export the mysql resultset into Excel File by Using the following Mysql Query. INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C: esting.xls;','SELECT * FROM [Sheet1$]') SELECT * FROM table1 But, it gives 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 ''Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C: esting.xls;','SELECT * FROM [S' at line 1".
View Replies !
ResultSet.getArray Not Implemented
I'm using Mysql 4.1.10.a, Connector J 3.2.0 alpha and java 1.5. I'm trying to get all the result of a "SELECT" with a ResultSet.getArry. I've found the code on the net. rs = statement.executeQuery(sql); java.sql.Array array = rs.getArray(1); Object tableau = array.getArray(); String[] ids = (String[]) tableau; But it throws a SQL Exception : "Feature not implemented". I would like to know if this error means that the method is not implementd.
View Replies !
Avoid Couples In The Resultset
I use a query like this: select t1.id_topics, i1.id_indices , t2.id_topics from indices i1, indices i2, topics t1, topics t2 where (i1.ind ='test' and i1.id_urls=t1.id_urls and i2.id_urls=t2.id_urls and i1.ind=i2.ind and t1.id_topics<>t2.id_topics) it gives results like those: "id_topic1","id_indices","id_topic2" 36,682,34 37,682,36 36,682,37 37,682,34 I would like to eliminate the "inverted" mates of couples in the resultset. That means in the example "36,682,37" should be eliminated since "37,682,36" is already part of the resultset. Is it possoble to express this request in the query?
View Replies !
Duplicate Row Names In Resultset
SELECT * FROM threads t, users u WHERE t.uid = u.uid AND tgroup = true AND rootid = 0; This query will return among other things 2 rowns named uid. These are identical but the two tables have both a field called "desc", which returns two desc rows with different content. I know desc is a bad name to call a field, but its there so :) How do I specify what desc I want? in MS access the rows had the names t.desc and u.desc so that was easy. I would like to avoid constructing a long select that picks out the rows I want, plus aliases on the desc fields, so I'm wondering if there is another option?
View Replies !
Assign Resultset To A User Variable
I would like to know why am I getting an error when assigning the result of a query to a user variable? SET @newClientID = SELECT clientID FROM tblClients WHERE clientClave = @clientClave; The code above doesn't work on a Windows server running MySQL 4.0.12-nt. It does work on a workstation with Windows XP running MySQL 4.1.3b-beta-nt
View Replies !
Compare Previous String In Resultset With Next
The code below does not work. while(resultSet.next()) { if (!resultSet.getString.next().equals(resultSet.getString.previous()) { out.print("..."); } } Is it possible to compare previous string in a resultSet with the next string? If so, could you please show me an example.
View Replies !
Low Speed While Reading ResultSet In Java
I have a query to MySql table in my servlet. The table is really small, approximatly 20x20. Reading it through ResultSet takes several seconds(!). I traced the code and find out that getting every string takes almost a second(!!!), there could be no mistake! Similar things happens with other tables. The time could change from query to query. Running the same queries through MySql console takes no time at all. Connecting, quering are ordinary. Everything installed on the same machine, Windows2000. Resin v.2.1.6, Caucho's jdbc driver Mysql 2.1.0, JDK 1.4.
View Replies !
MySQL Doesn't Return Resultset, Or Errors?
- I have querries above and below this one that are working. - the table is in the db - The fields in the table are named properly - the query , when PRINTED to the screen can be executed in phpMyAdmin properly. - When I try to print out the mysql_errno or mysql_error, I get nothing. when I do "SELECT 1" I am returned 1 ( the connection to the db is working) the sql looks like this: SELECT field1 FROM table WHERE field2 = 151 AND field3 = 13; it's that simple but gives me nothing. if I replace field1 with "count(*)" it also gives me nothing. Another thing.. this is within a while loop with other querries being performed around it. but all the rest of them are working
View Replies !
Stored Procedure :: Can't Return Resultset In The Given Context
I'm facing a problem with stored procedures in MySQL 5.0.10. My application is in ASP.NET and I'm using Corelab's MySQL library to transact with the database. The problem is that all stored procs return an error message "Can't return resultset in the given context." Calling the stored procs directly through the DBTools client interface also gives the same error. I'm just not able to resolve this one. My client had given me the code and when I set it up on my local server I get this error and moreover, it is working fine at the client's site. Any idea why this is happening and a possible solution to this?
View Replies !
Select Hash Over Resultset For Cache Validation?
I have a table with a lot of values that rarely change and I would like to cache these values in a file at the client side. I would like to select a hash over the WHOLE resultset (NOT per row) - something like select SHA1(*) from some_table; This would enable me to check if an update is required without transmitting all the values.
View Replies !
|
TRACKER
