MYSQL :: WHERE Conditions

LIKE Conditions
Basically I have a table, with a column which holds a list of related tags separated by spaces. The user can search for a row using a key word that will search this column (and the column named header too) and return if the keyword appears in the tags (or heading)... I tried to achieve this by using the following SQL but it actually returns every entry in the database... I have never used "LIKE" before so if someone could give me a slap and say I am doing something stupid that would be awesome .

SELECT * FROM VIDEOS WHERE VIDEO_HEADER LIKE '%$seachText%' OR VIDEO_TAGS LIKE '%$searchText%'

2 Of 5 Conditions Match
I need my query to match at least 2 of 5 given conditions.

Example:
I have conditions
content REGEXP 'A'
content REGEXP 'B'
content REGEXP 'C'
content REGEXP 'D'
content REGEXP 'E'

I need database to return this entry only if at least 2 conditons are true.

Need Help With Multiple WHERE Conditions...
i'm wondering if anyone knows how to have multiple WHERE conditions...i'm using php/mysql as my programing languages...

example --> SELECT * FROM table WHERE rid=$ride && uid=$userid && accepted=Ɔ'

I need help selecting rows where all three of the conditions above are met...anyone knows how to do this?

MySQL 5.0 And OR Conditions
I have heard that version 5.0 of MySQL has performance gains for "OR" conditions. Can anyone confirm this? If so, how significant are the performance gains over 4.1?

For instance, I am referring to something like: SELECT * FROM some_table WHERE this_thing = 'something' OR that_thing = 'whatever';

Search On Several Conditions
I want to retreive data from a table on several conditions in order; for example;

All i am trying to do is select all records on the following condition;

User_id = variable AND (Profile = "variable" OR Profile = "Both") ORDER by hits in DESC

I came up with this statement but no matter what i do i cannot get it to produce the result above

"SELECT * FROM links WHERE user_id='$user_id' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY total_hits DESC"

Can anyone offer me any help please, would really appreciate this as its doing my head in.

BTW, the following statement seems to work so not sure whats going on ? Only thing i can think is that its screwing up if total_hits above is equal 0 ?

"SELECT * FROM links WHERE folder_id='$activefolder' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY title ASC"

Sums For Two Different Conditions
I want to combine the following 2 queries into 1 query where it would give me 2 results (proflad and proftot) but don't know how

select sum(return-inv)as proflad
from allbets where com="lad"

select sum(return-inv)as proftot
from allbets where com="tot"

Transactions And Race Conditions
In an application I'm developing, I'm at a point where there needs to be tight integration of application-level logic sprinkled in between several consecutive SQL statements. This negates the option to use stored procedures since subsequent SQL statements depend on application logic that cannot be transferred into the database. I need the ability to rollback on errors (i.e. I need to use a transaction), but I also need to avoid race conditions.

Reading the MySQL documentation, START TRANSACTION will implicitly issue an UNLOCK TABLES statement; conversely, LOCK TABLES will implicitly COMMIT any active transaction. At this point it appears that I have to choose between transactions and locks, but I need the functionality of both.

Two Alike Conditions In Query?
Example. I have a table with information about payments (id, amount, pay_date, customer_id). Can I select two sums for two different date ranges, grouped by 'customer_id' (for each customer) in one query?

Left Join Conditions
Two tables.

table1, table2

Table 1 contains all unique items.
Table 2 contains x references to table 1, unique by customerId

I want to do a join onto table2 where table1.id = table2.table1Id AND table2.customerId = the user's ID

I can't quite figure out how to work in the idea of an AND into my left join. Unless both those conditions are two, I don't want any data from table2.

Only solution I can think of so far is to just left join on the id's, but select the customerId from table2, and as I loop through, check to see if it's supplied and the right id, and keep track of duplicate items from multiple customers myself.

Suggestions?

Conditions On Left Join
I'm having trouble constructing this left join query. I want to retrieve all rows from tProducts, and relevant matches from pictures, however what I have got just jams up the server and I have to restart. Am I doing it wrong?

SELECT prodCode, prodName
FROM tProducts LEFT JOIN pictures
ON productID = relProdId
AND prodCode = 'SF2'

Select With Multiple Conditions
I've a table called tag_log.

In this table are two columns called tag_id and sub_id. This table is used to link tag names to stories.

I'm trying to write the query for a search. The search is looking for all the sub_id that matches the tag_id given.

Example of what I have currently:
select distinct(sub_id) from tag_log where tag_id in(101, 102, 103);

In the above example, it was performing a union instead of what I really want. It was returning results that have either one or all of the tags.

What's required is for the story to have ALL the three tags. It's should be like a typical search, where the more search terms you enter, the more focused the results will be.

Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1

data:
$min_time, $max_time, $time, $attempts

so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database.

"UPDATE mytable SET
avg=(avg*attempts+$time) DEV (attempts+1),
min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time??
**and similar with max_time .

Query Performance On Column Conditions
I have a question regarding MySQL performance on column conditions. How MySQL reacts where you add a condition to the same column?

For example:

SELECT * FROM users WHERE status<>4
and the same query with extended column check
SELECT * FROM users WHERE status<>4 AND status<>5 ...

I am specially interested in situation where the conditions checks a column value and whether the data is specified:

SELECT * FROM users WHERE status<>4 OR status IS NULL

What's with the MySQL's performance in such cases? Is the same column (status in the above example) checked twice or the optimizer takes care of it in some special way?

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

Combining UPDATE Statements With Different Fields And Conditions
I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query:

UPDATE table SET field1 = field1 - n WHERE field 1 > x
UPDATE table SET field2 = field2 - n WHERE field 2 > x

.......

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Granting User Access On Field With Restrictions Or Conditions
I am planning to develop a database that will have many users. Assume I have a table called Customer having these fields (id, name, user). Assume we have these Data:

ID Name User
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1
4 Cust4 user2
5 Cust5 user2

I want the users to have access on those records that are created by them only or those that have their name on in the (User Field).

For example, if (user1) accessed the database and called: "SELECT * FROM customer;" it should return these data only automatically (or throw an exception at least):

Result:
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1

(or throw an exception at least) that he cant call this statements without a where cluse like (USER=user1).

also, the user should have only the right to insert records having their name on in the (User) Field. For example: user1 can only call : "INSERT in CUSTOMER Values (6 , Cust6, user1), if he inserts user2 instead, the system should through an exception.

I hope I made clear examples of what I would like to build. How can I acheive this?

Specify Conditions For The Left Side Of A Multiple Left Join
I'm doing a left join that looks like this standard example:

SELECT t1.c1, t2.c2, SUM(t3.c3)
FROM t1
LEFT JOIN t2 ON t1.c1=t2.c1
LEFT JOIN t3 ON t1.c1=t3.c1
GROUP BY t1.c1

The problem is that I also want to specify a condition for selecting records from t1:

WHERE t1.c1='x'

so that only the records with that value in c1 will be returned on the left side of the join. I don't know where to put this in the SQL.

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's
the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift
and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order.

now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it
does work.
Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there
is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code: