MYSQL :: Using The NOT Operator To Exclude Fields

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 ! View Related

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 ! View Related

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 ! View Related

Exclude Table(s) Using Mysqldump
I am sure if it's possible to do that, but I want to use mysqldump to backup
my databases. However, there is only one large table I want to exclude. Can
this be easily done?

View Replies ! View Related

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 ! View Related

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 ! View Related

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 ! View Related

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 ! View Related

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 ! View Related

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 ! View Related

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 ! View Related

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 Replies ! View Related

Regular Expression :: Exclude Text Within Brackets
I need to select a field from a table, but excluding any text that is enclosed within brackets.

For example, we have this data:

'(1) row 1'
'row 2'
'(3)row 3 (yes, that's 3)'

I need to return:
'row 1'
'row 2'
'row 3'

View Replies ! View Related

Using A Query To Exclude Records From One Table, If The Field Value Exists In Another
I'm very new to SQL, and wondered if someone could help me out:

I am building a query which contains two tables. One table has details of everything everyone owns.

The other table is a list of things which people own, but that I don't want to see.

Is it possible to design the query so that I see all of what everyone owns, except the items listed in the other table?

View Replies ! View Related

LIKE % Operator
I want to perform a simple search for a query such as:

SELECT *
FROM SOME_TABLE
WHERE SOME_TABLE.SOME_COLUMN LIKE '%SOME_KEYWORD%'

The same statement if implemented in a stored procedure would look similar like this:

CREATE PROCEDURE sp_FindKeyword (IN SOME_KEYWORD AS VARCHAAR(55))
BEGIN
SELECT *
FROM SOME_TABLE
WHERE SOME_TABLE.SOME_COLUMN LIKE '%' + SOME_KEYWORD + '%' ;
END

But this does't work .... I know I could manipulate the '%' before passing the parameter using the interface language such as php, asp, ..etc. but is there a way this can be done using MySql 5 itself?

View Replies ! View Related

OR Operator
Is the below correct. Trying to select items with typeid of 1 or 5.
WHERE ooer.shipdate >='$from' AND ooeer.shipdate <='$to'
AND equipment.typeid='1' OR equipment.typeid='5'

View Replies ! View Related

% Operator
I have a big database, and I need to change some of the items in a table, the stuff I need to change is in the middle of the sentance. EX: images/apparel/aprons/toppers_9324.jpg I want to just change the toppers to top, only problem is I need to chage it about about 40 columns and about 12 rows. If I do it by hand thats about 2hours wasted so I was wondering if there was a way to do it with some sort of query.
And this question is a little out there but I thought %toppers% would let me select anything that had toppers anywhere in it but Im getting this error and here is what I put in:
Query: SELECT * FROM `color` WHERE color1 = %toppers%
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 '% toppers % LIMIT 0, 30' at line

View Replies ! View Related

LIKE Operator
This is my query:
$result = mysql_query("SELECT * FROM `index` WHERE firstname LIKE '$s_firstname'");

But LIKE doesn't want to work; it's functioning as an = (equals), instead.

So, for example, if $s_firstname is "max" or "Max", and if 'firstname' in the database is "Max", then it displays the record info.

However, I need it to also display the record info when $s_firstname is typed as "Ma" or "Maxus". It's not doing that right now; how do I make it do this?

View Replies ! View Related

BETWEEN Operator
I'm having some trouble understanding how to use the BETWEEN Operator. As far as I know, the folloving statement works:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

But, it is possible to assign variable values to "value1" and "value2".
For instance, I need to match the previous and following records of a given result:

my_table
ID -- test
1 -- a
2 -- b
3 -- c
4 -- d
5 -- e
6 -- f
7 -- c
8 -- h
9 -- i

thus, at first I match my pattern, "c" for instance, and only later I match the range with the following query:
SELECT ID, test FROM my_table WHERE ID BETWEEN 2 AND 4 or ID BETWEEN 6 AND 8

Is there any smarter solution to obtain the same result? and moreover, how many OR operator can join the query?

View Replies ! View Related

The ^ Operator
I just wondered if anyone out there knows what the ^ operator does in a mathematical / logical context, NOT as a regular expression. The only reference I can find to it in the manual is in the page '12.1.1. Operator Precedence'

View Replies ! View Related

!= Operator In Queries
I have a query with the following syntax to find students that haven't voted:

SELECT * FROM Students
WHERE Students.StudentID != Voted.StudentID

The thing is, the results I'm getting are basically the same as if has just used 'SELECT * FROM Students' alone. The StudentID list of 'Voted' is shorter than that of 'Students', as some students haven't voted yet. Anyone know what I'm wording wrong here?

View Replies ! View Related

+= Assignment Operator
Does += assignment operator exist in mysql?

View Replies ! View Related

Truncate-operator (*)
I want to be able to search for *load* and want my boolean search to return stuff like downloads, shareloads, bogload etc.

The truncate-operator works only at the end of the word though.

I can only think of, when the searchterm is submitted, using php to strip the *term* out of the rest, and then adding OR|AND|AND NOT (depending on *term, +*term*, -*term*) tablefield LIKE ''%term%'

Is there a better way, without the use of LIKE?

View Replies ! View Related

IN Operator Value Handling
I have a small question on IN operator.
What is the maximum count of values an IN operator can handle properly in a query.

Code:

SELECT * FROM table WHERE id IN (1,2,3,5.....)

The values that i get are close to a million and i want to backup them up using mysqldump using the IN operator.

View Replies ! View Related

Operator Question
Is it possible to tell mysql to use '*' instead of '%' while using the like operator?

to:
select count(*) from customers where name like 'Mart*';

View Replies ! View Related

Operator Enquiry
Can anyone tell me why I keep getting an error for the below code..?

select content_id,weight from mapping
where node_id in
(select node_id from concept
where parent_node_id='n001')

The inner loop works fine and results in a set of 2 values.

View Replies ! View Related

SELECT With LIKE Operator
I'm using the following query to search my database after matching records. But whenever the search contains a space, the records doesn't show up. I'm using a GET method from the search page, btw.

$query9="SELECT b_id,b_name,b_lan,b_description,b_category FROM bands WHERE b_name LIKE '%$search_b_name%' OR b_description LIKE '%$search_b_name%'";

Maybe I have to split up the search keywords and do multiple searches for each one?

View Replies ! View Related

Subqueries And The IN Operator
hey all,

i am having serious problems with getting my head around why this will not work. i am trying to return a number of games played with a count of the number of people in it. the table structures are as follows:

games

gameref | gamepot | gamedate | gamelength |

..1...... | .... 50.... | 12.12.04 | ... 03.00.00 |

winnings

gref | pref | amountwon | stake |
1 . .| 1 .. | ....20 ...... | 10...|

i have used the dots to try and keep the table in some sort of form. i have only entered one row of data for each just to show the format of it, if this is not enough please ask for more.

the games table holds each game that has been played, the total pot available to be won the date played and the duration of the game.

the winnings table is teh winnings of each player in each game. the gref is the game it refers to, the pref is teh player it refers to, (there is a player table but is not used in this query at all). the amount that player won and the stake they entered into the pot.

if ya didnt gues its a poker database. my problem is as follows.

Quote: $sql = ("SELECT games.*, COUNT(winnings.gref) as playercount, winnings.gref
FROM games, winnings
WHERE gameref = gref
group by gameref
order by gamedate");

this query works a treat. it counts the amount of players in each game and outputs it all nicely.. but i wanted to edit the query to be able to show just all the games a single player had been in.

Quote: $sql = ("select games.*, COUNT(winnings.gref) as playercount, winnings.gref
from games, winnings
where gameref in (select gref from winnings where pref ='$pid')
group by gameref
order by gamedate");

$pid is passed into the page by

Quote: pt_register('POST','pid');

but this throws an error

Quote: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ghoxmfjz/public_html/database/poker/allgames.php on line 32
Query failed!

line 32 is:

Quote: $nrows = mysql_num_rows($result);

i can get it to work if i substitute the subquery for (1,2,3) being the first 3 games played. this would assume a certain player would have been in these 3 games.

the problem i get is that the COUNT function just counts every player in everygame not the individuals. if i group it by gref it counts them (sort of.. it doubles the count) but then does not return any of the other information for the query.

i am very confused any and all suggestions of things to look at or consider or a straight ur doing XXX wrong would be grand.. thx all

Jon

oh btw if you wish to look at the output if that helps the URL is:

http://www.happygolucky.me.uk/database/poker/allgames.php

with the variable $pid as the reference to try the player.

View Replies ! View Related

Logical Operator Hit
I have a rather complex query (about 17'000 characters long) being glued together from a php-script.

It is basically a select over a few tables using joins with some "where"-clauses and alot of "OR" clauses.

Example: and t6.fir="480" or t6.sod1a=8329 and t6.fir="480") or (t6.soc1a=11071 and t6.fir="490" or t6.son1a=11071 and t6.fir="490" or t6.sod1a=11071 and t6.fir="490") or (t6.soc1a=5009 and t6.fir="666" or t6.son1a=5009 and t6.fir="666" or t6.sod1a=5009 and t6.fir="666") or (t6.soc1a=4677 and t6.fir="450" or t6.son1a=4677 and t6.fir="450" or t6.sod1a=4677 and t6.fir="450") or ...

basically the problem can be reduced to this sample query

Select A from table where B=1 and C=2 or D=4 or E=5 or F=6

Now as it already takes quite some time to run the query, I would like to know which of the hundreds of OR-clauses did trigger. Is there any way to find out which field (if for the above example the values from the DB were B=1,C=2,D=9,E=5 and F=6) triggered the success? (so that it would be returning A and some hint that B,C,E and F were positive).

The alternative would be to loop through all the or-clauses for the result found which would take ages to complete the query.

View Replies ! View Related

Assignment Operator
What exactly does the operator := do/mean?
It is used frequently in MySql documentation, but no information exists about it except in the order of precedence section, where it is listed at the top, but not explained.

View Replies ! View Related

Using REGEX With AND Operator
I've already viewed the Reference manual but didn't quite see an example that suits my case.

This is what I have so far:

$in_list = "'".join("','",$cen_chiefs)."'"; //$cen_chiefs is an array

$query_cen_chiefs = "SELECT * FROM central WHERE CONCAT(strName,' ',strCity,' ',strState) IN({$in_list})";

How would I go about adding an AND clause to this query to return all rows where strName column values are in $in_list array

Should I be using strName as a pattern, because strName would be a partial value of $in_list array values? Something like:

REGEXP '^.....$'

"SELECT * FROM central WHERE CONCAT(strName,' ',strCity,' ',strState) IN({$in_list}) AND (REGEXP '^strName$') IN({$in_list}) ORDER BY conName"; //This query doesnâ€™t work though

View Replies ! View Related

Select != Operator
select servername from my_srv where id!=1;

or select servername from my_srv where id not equal '1';

View Replies ! View Related

<= Operator, MySQL 4.1.12
I have a float 13,2 column "balance' in my MyIsam table. One of the values in that column is 122.41. The query "select balance from master where balance >= 122.41" returns all the rows with values greater than or equal to 122.41, including 122.41.

However, the query "select balance from master where balance <= 122.41 does NOT include 122.41 in the resultset. Nor can I establish equality on 122.41. "select balance from master where balance = 122.41" returns an empty result set. What am I missing here?

View Replies ! View Related

How To Do Operator AND In Regexp?
I am trying to use operator AND in Regexp in mysql, but I can't find a way to do it.

For examples:

SELECT id FROM mytable WHERE row REGEXP "a & b";

so the data in row can be "a c e b" or "b e a c", etc.

I can't do SELECT id FROM mytable WHERE row REGEXP "a" AND row REGEXP "b"; because I am storing "a & b" as a single string somewhere else in the DB.

How do I do it?

View Replies ! View Related

What Operator For 'between' Search
I'm not quite sure how to approach this, I need to select numbers from

BETWEEN '10' AND '20' --but also-- Less than 1

is this possible? Can I combine two between operators?...

View Replies ! View Related

Between Operator For Two Columns?
I'm trying to get the query working. I have one value that i want to search two column with.

I have two columns: ProductYear_From, ProductYear_To. Both are int.

I want to search 2003. What is the best way to search 2003?

Does between operator take two columns?

View Replies ! View Related

Multiple LIKE Comparison Using IN Operator
Is there anyway to compare a field with multiple string values using
LIKE?

Here's the statement I have now:

SELECT * FROM list
WHERE email LIKE CONVERT( _utf8 'hotmail@hotmail.com' USING latin1 )
OR email LIKE CONVERT( _utf8 'john@hotmail.com' USING latin1 )";

I know how to do it using the IN operator,
SELECT * FROM list
WHERE email IN ('hotmail@hotmail.com', 'john@hotmail.com')

but I need to be able to use the LIKE operator with the CONVERT
function. The reason I am asking is because I am trying to match 50+
different email addresses.

View Replies ! View Related

Simulating MINUS Operator
Here is the short example of what I want to accomplish. I wish to
have the difference between two different select queries.
So if one query pulls records 1,2,3 and 4 and the second pulls records
1 and 4 I wish to have only the records 2 and 3. How can I accomplish
this easily.
In case that doesn't make sense here is the long version.
I have two tables that are keyed together through an id field. However
this is not a one to one relationship, it is a one to many
relationship. The following is an example of the table

Table 1 Table 2
------------------ ----------------
ID relid rid vid

Table 1 and table two are linked through the columns id and rid. There
can be many links between id and rid so the link is further refined
through a vid field. What I need is all records in table 1 that will
not link to table 2 such that relid=rid and vid=46

View Replies ! View Related

MySQL 'IN' Operator Syntax
I'm learning about MySQL but i'm having problems with the 'IN'
operator. For example:

SELECT * FROM TABLE_NAME WHERE ID = IN ('9','19','3','10');

....gives me a syntax error. I've also tried it with (9,19,3,10) but it
gives me the same problem.

View Replies ! View Related

Can't Execute Delete With The IN Operator
Lately I have been started using MySQL. I managed to create my
database and tables.

When I wanted to execute the following query:
delete
from adminpages
where parentid IN ( select DISTINCT A.id from adminpages AS A where
A.name='galeries' );

I have received the following error message:
Error Code : 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 'select DISTINCT A.id from adminpages AS A where
A.name='galeries'
(0 ms taken).

View Replies ! View Related

Error To IN Operator With Sub Query
I have a problem to use IN Operator in MySQL. IN operator do not work with sub query. My syntax was:

Expand|Select|Wrap|Line Numbers

View Replies ! View Related

FULLTEXT Default Operator
I am implementing a FULLTEXT search in my website. The thing is... if you use "ipod cable" on the search engine, it will return rows that have "ipod" or "cable" but no necessarily both.

What I want, is MySQL to use "AND" as a default operator. So, if I search "ipod cable" it will actually look for "+ipod +cable", throwing results that contain both words. So far, I haven't been able to find a solution on the MySQL side, it seems like I will have to develop a parser in PHP for these strings.

I am running the FULLTEXT search IN BOOLEAN MODE.

View Replies ! View Related

MySQL Index And LIKE Operator
I have a table with two fields education with BLOB field type and work_experience with BLOB type. I have created index for both.
If I run
select * from table where education like '%xxxx%' or work_experience like '%xxxxxx%'.

Then index is not working since the LIKE '%XXX%'.

But I have around 500,000 records in the table and I want to find a fastest way to retrieve data since this index support for '%%'.

I dont like to avoid %XXX% since I am running a keyword search from both fields.
Please help me to sort out this or are there any other way to achieve the records faster if index not support.

View Replies ! View Related

Syntax Error With OR Operator
I'm getting an SQL syntax error when trying to retrieve from my database. Below is the code.

I am passing a variable from a form to a page with a record set, I want to check if the variable exists in two of my database columns. The code works when checking a single column but fails when I add the additional OR operator.

This works:
SELECT column1, column2, column3, column4, column5, column6, column7, column8 FROM database.table WHERE column3 LIKE %MMColParam% ORDER BY column5 ASC

This fails:
SELECT column1, column2, column3, column4, column5, column6, column7, column8 FROM database.table WHERE column3 LIKE %MMColParam% OR column7 LIKE %MMColParam% ORDER BY column5 ASC

View Replies ! View Related