MYSQL :: If Condition In View

Where Condition
I want to query my database like so...

$query = "SELECT * FROM wines WHERE restid =1 AND site_id_sask =1 ORDER BY name";

The only hitch is that in the restid field I would like to hold more than one entry. Meaning for some entries I would like to have the values 1, 2, 5, 9 for example. In another entry I may want to have 1, 5, 8.

Can I search this the restid field?

LIKE Condition
I have a field in a table called Title. I have a record whose Title field contains a value of Ford Mustang. When I run a LIKE on my table such as LIKE 'Mustang' my record does not shop up. I am assuming LIKE is for an exact match. What if I want any record that has EITHER Ford or Mustang in the Title.

WHERE Condition
I've had a bit of experience with MySQL and pgSQL databases and have never seen this type of thing before:

Code:

SELECT * FROM users WHERE id

This is just an example, but the point is what happens when the condition in the WHERE clause does not equate to anything? ie, shouldn't it be 'WHERE id = X' or something like that?
The query in question doesn't produce any errors, so is there something I've missed??

Update On A Condition?
I was wondering if its possible to do a different update based on a value in one table - specifically if I have:

table1
-T1_ID
-T1_Image
-T1_AdditionalImages (Bool)

and

table2
-T1_ID*
-T2_Image

obviously a v.stripped down version of my data structure, where the underlined T1_ID is a primary key, and the * is a foreigh key.

The logic behind it is that an item can have multiple (0..*) images - T1_AdditionalImages indicates whether there could be more images for this item in table2 (it is set to true when the item has 1 image added).

Now what I'd like to do is check whether T1_AdditionalImages is true, if it is true, then INSERT a new entry to table2 with the relevant details, if it is false then UPDATE table1.T1_Image to be the image details, and table1.T1_AdditionalImages to true.

Update And Sum Condition
I wonder if it's possible to update a field depending of a condition containing sum().
Example of what I want to do (strongly simplified, but easier to understand what I wanna do):

update customer, sales
set customer.field1 = "true"
where sales.custID = customer.ID
AND sum(sales.totalprice) > 1000
group by sales.custID

Without group by : "invalid use of group function"
With group by : error in SQL syntax.

Previous To Last With A Certain WHERE Condition
How do i fetch the previous row record with the same WHERE when the last record has only a lately date.

for example

field CConsorcio CRubro ImporteA FechaA

1 105 1000 2006-05-10
2 110 30 2006-05-12
5 105 300 2006-05-13
4 104 600 2006-05-14
2 103 800 2006-05-15

the WHERE CConsorcio=2
the last record: 2,103,800,2006-05-15
and i want to select the row: 2,110,30,2006-05-12

SORT BY With Condition?
is there a way to do sorting depence of some condtion directly in the MySQL query?
for example, I have this query:

SELECT cat_id, cat_name, cat_date, cat_parent
FROM categories
ORDER BY cat_name ASC
but I'd like change sorting if cat_parent = 23 for example.
so, is there a way use something like:
SELECT cat_id, cat_name, cat_date
FROM categories
ORDER BY ((IF cat_parent = 23) THEN cat_date ELSE cat_name) ASC

I dont know MySQL at all, so this is just an example of condition idea that I'd like to use.

More Than One Condition In A HAVING Clause
I have tried to run a query where the reuslts are restricted by a
HAVING clause with 2 conditions, but the second condition seems to be
ignored.

for example

SELECT userid, AVG(position) FROM `table1` WHERE (status='finished')
GROUP BY userid HAVING (COUNT(*) >= 4 AND AVG(position) < 5.5)

returns exactly the same as

SELECT userid, AVG(position) FROM `table1` WHERE (status='finished')
GROUP BY userid HAVING (COUNT(*) >= 4)

even though there are values for AVG(position) returned that are > 6.

Is it the case that I can't have more than one condition in a HAVING
clause? have I got the syntax wrong? Is the behavior different in a
later version?

I am using "mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu
(i386)"

Using OR In Join Condition
I Have this query which just takes too long and kills the server. I have 2 left joins and want to do an inner join matching values in either of the left joins. It seems to be the use of an OR in this inner join which is the bottleneck.

select * from
categories c
    INNER JOIN cat_subCat csc ON c.catId = csc.relCatId
    INNER JOIN subCategories s ON csc.relSubCatId = s.subCatId

    LEFT JOIN
    (
    SELECT sb_th.relSubCatId, thunderCatName, keywords AS th_keywords, relProdId as th_prod
    FROM subcat_thunderCat sb_th
    INNER JOIN thunderCategories th ON sb_th.relThunderId = th.thunderCatId
    INNER JOIN prod_thunderCat p_th ON th.thunderCatId = p_th.relThunderCatId
    ) as th_join
    ON s.subCatId = th_join.relSubCatId

    LEFT JOIN
    prod_subCats psc ON s.subCatId = psc.relSubCatId

    INNER JOIN tProducts p ON th_prod= p.productID OR psc.relProdId = p.productID

Index With Condition
Will a table index run faster depending on the order of the columns in it? My table has 500k rows and 600 columns. The search and sort queries run on subgroups: order by ssn where ssn like %555% and group = 2;

But these queries are _very slow. Do I create an index on group, ssn and lname, group? Or add more memory allocation to MYSQL?

Order By If Condition
I'm trying to return a list of products, where the top of the list will be one of 2 products, the choice being randomly selected. I tried a rand() condition in the ORDER BY clause, which partly works, but of course the rand() function is evaluated for every row, so it doesn't give the consistent results i need.

SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering
FROM tProducts p
JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1
JOIN taxCodes tax ON p.taxCode=tax.taxCode
WHERE p.featuredProduct=1
AND p.stocklevel>-3
GROUP BY p.productID
ORDER BY productID=if(rand()<0.5,117029,117035 ) desc, rand()
LIMIT 10 .

Double Condition
I have a relatively straightforward SELECT statement but am running into problems:

SELECT sons.id
FROM sons, fathers
WHERE
(sons.father_id = fathers.id AND fathers.name = 'Bill')
OR (sons.father_id = -1);

So essentially I want to select all sons with a father called 'Bill' or with no father i.e. father_id is -1.

This works fine without the last line, but when I add the last line in, it outputs duplicate rows of each son for every entry in the fathers table. As I understand it, this is because I'm referencing the 'fathers' table in the FROM statement but if the father_id is -1 then there's nothing to join the two tables on.

How do I get around this? I'm at a loss.

Multiline Condition
there's something that I don't manage to do with mysql

exemple :
I've a values list like (22,3,12)
I've a table like :

idx | idy
----------
6 | 22
6 | 3
5 | 22
5 | 1
6 | 12

So, from the values list, and with only one request, I'd like to get the value "6", in this case, the value that is recored with each values of the list.

Search Condition
I like to call all teachers except math teachers.

I mean that every registered members who entered his job as teachers, but exclude(minus) math teachers.

It would be something like below

WHERE job like '% teacher%' and job like '% ! math%'.

Join Condition
i have 2 tables

users
relations

in the table relations i record
relations.user_id and
relations.connection

those two fields refer both to the same id in the users table.

Now to the problem:

I want to do a query for one user, and find his friends,

therefore i have to search both fields in the relations table, and then depending on where his id was found select the counterpart. (if his id was found in the relations.user_id i need the relations.connection and the other way round...)

now i do not only want the id of the friend but i would like to pull his details from the users table....

to be honest i tried a few things but i need a hint whether this is possible or whether i need two queries... or maybe a subquery.

Insert If Condition
I am trying to optimise my application. One of the parts of it is inserting a value into a table if the number of rows in that table with the same `date` field as the row to be inserted is less than 24
So currently I do two statements, one to check the number of rows and another to insert.
Is there any way to INSERT something if condition. I have looked as CASE but it does not seem to do what i want. So a mock up would be:

Code:

SELECT COUNT(*) FROM `table` WHERE `date`='2006-01-01'
If the value is < 24
INSERT INTO `table` VALUES (...)

Condition Preferred
Imagine a table of company addresses. A single company can add many addresses and edit them. A company can mark one and only one particular address as Primary. I want to do a select query as follows:
if the company has a marked Primary address then select it and no other address, else select all addresses (perhaps with some additional conditions) for that company
The query would give just one result for some companies and a number of results for others.

How To Avoid Race Condition?
How do I lock a table for one of my insert (followed by a read) queries on a table such that other simultaneous insert/read queries on that table are put off until the first one is complete? I am trying to avoid a potential race condition.

Putting A Condition On A Join
I have two tables: users and events. events has id, event_id, name, and date.

I need to select all users, but check if each has an event today or later, and if so select the date of the next event. But if there is no such event the user should still be selected.

Of course I'm using a left join, but the problem that is killing the query is the stipulation with the date being today or in the future. I'm trying something like:

select users.name, events.date from users left join events on users.id = events.user_id having events.date >= curdate();
But it returns no result if there is no such event for that user.

Multiple Condition Select
I have an issue with a select that I needed a little help with. I have the following table structure with some sample data:

user_id, group_id, type
11, 1, 1
11, 2, 1
11, 3, 2
11, 4, 3
11, 6, 4
12, 1, 1
13, 3, 2
14, 1, 5
15, 3, 6

What I need to do is select all of the user_id's that have a group_id record of 1 AND 6. I tried a couple of things but I want to make sure I am doing this correctly.

Also, if this is not the correct forum, can someone point me to the correct one? I could not find anything for SQL syntax.

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?

Condition Using 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'

Get A Null Row Where Condition Isn't Respected
My query need 2 tables on my database. The first one is named Production and the second one is named Weight.

Production table contains information about a production. Each production has a valid start date and a end Date operation.

Here is an exemple of the production table :
p_ID p_StartDate p_EndDate Other info not useful...
6 2008-03-03 2008-04-02 ...
24 2007-12-16 2008-01-15 ...
35 2008-03-11 2008-04-23 ...

Secondly the Weigh table contains a list of weight inserting each day for a production.

Here is an exemple of the weight table :
w_ProductionID w_EntryDate w_Weight
6 2008-03-03 200
6 2008-03-04 201
6 2008-03-05 202
6 2008-03-11 202
26 2007-12-16 200

First of all I want to get all production that having EndDate most recent then now. So here is what I done for :

SELECT Productions.*
FROM Productions
WHERE (DATEDIFF( p_EndDate, NOW() ) > 0)
GROUP BY p_ID

And for each production resulting from my first query I want to get the row in Weight table having the w_EntryDate = NOW - 1 day. If there is no row equal to this date then I want to get a null row. Code:

Insert Into Table With Condition
I want insert into the field(city) of table with a condition,
like this:

xcity='chichago"
insert into table cust(id,city) values("4567",if(city="xcity",city=xcity,city='')

if xcity is "chicago" insert field city with chicago, if city<>chichago, insert field city with blank..
How can I do

If Condition In Update Statement
I have a table with three fields - stockLevel, backOorder and allocated (there are other fields but these are the inportant ones here).

Is it possible to create a SQL update query that will deduct a qty from the stockLevel if there is sufficient stock and if not deduct as much as possible and put the this amount on allocated and the remainder on backOrder?

Mysql WHERE Condition Execution
I have 2 columns and I search rows with two conditions.

SELECT * FROM table WHERE A = '$a' AND B = '$b'

How can I make mysql search columns with A condition first and after
that within these condition B.

I would like to know, how mysql search works.
How can I tell mysql how to search?

Do Update With IF Condition... In MySQL
Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Give On Condition Higher Priority
I have a system where my pages are built of a bunch of boxes. Each box can have multiple contents, which is displayed att different times. So a box can have a box contents with "begins" = NULL, and "ends" = NULL. At the same time, it can have a box contents with "begins" = 2007-10-01, and "ends" = 2007-11-10.

When I select it all, I group on box id, because I just want one box contents. But the problem is that I need the contents which doesn't have begins/ends as NULL to get a higher priority than the once that do. So a user can have one box contents to be displayed all the time, except from 2007-10-20 to 2007-10-21.

My current query (it's stripped, I don't use SELECT * ) looks like:

SQL
SELECT
*
FROM
pages AS p
LEFT JOIN
pages_boxes AS pb
ON pb.page_id = p.page_id
LEFT JOIN
boxes AS b
ON pb.box_id = b.box_id
LEFT JOIN
boxcontents AS bc
ON b.box_id = bc.box_id
WHERE
( bc.box_ends > CURRENT_TIMESTAMP OR bc.box_ends IS NULL )
AND
( bc.box_begins < CURRENT_TIMESTAMP OR bc.box_begins IS NULL )
GROUP BY
b.box_id

But it always just selects the box contents which was inserted first.

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.

Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' that contains a field representing the topic's author. I then have another table called 'ignorelist' which has two fields; one represents another user and the other a reference to the aforementioned author. There can be many ignored authors for any one user.

For any given user, I'd like all the rows from the forumtopics table where there is no match for (user, author) held in the ignorelist table. Obviously it's easy to check for existence, but can the opposite be done in a single query?

I run MySQL 4.0.17.

How Do I Update A Column Value Depending On A Condition...
I want to update the value in a column only if it is less than a supplied value.

for example if I had a table called autionItems containing a column called offers, I have got a new offer and need to update the autionItems table only if the new offer is greater than the existing value.

Automatic Operation When Some Condition Apply
I have a table with a big number of rows, on which I do simple operation
(basically I need the sums of some columns).

As the table gets bigger and bigger, I would like to know if there is a way such than when it reaches a given number of rows (let's say 100000), it stores the results on a new table and use this results for future query (I mean I want to avoid of summing all the rows each time, but I would prefer to use the results I already have obtained).

If I have understood well, this could be done with triggers and procedures,
but I have no idea about how.

Inserting Data Into Record With Condition?
i would like to insert data with "where" condition is this correct codes
below?

$query = "INSERT INTO photos (comments) VALUES ('$comment_tmp') where ID = '91';

I want to insert the $comment_tmp into comments only on ID = 91.
Is this correct or wrong?

Database Access Race Condition
I am working on a website with two applications on the server - both accessing the MySql database. PHP applications are reading and writing to the database and a Java servlet (running under Tomcat) is also accessing the database.

Currently the software in both applications is under development so there is very little in the way of actual database accesses, however, someone has suggested that we will have problems in the operational system because both Java and PHP applications will be attempting to access the same database.

Joining Tables Based On Condition
I want to have a field determine what table the field should be joint on. For example, I have a field that is one of eight different values (contacts, leads, accounts, ect...). I want to be able to join on the specific table specified by the field because this field determines what type the id field corresponds to.

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.

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?

Is This Possible With A View?
I'm trying to cut down on the size of my database so I've changed it so that rather than implicitly setting every value in one table, I've split the data into two tables: one table (iData) contains data for the start of evey week:
iData
-----------------------------
2000-01-01 Volume: 20000000
2000-01-06 Volume: 30000000
2000-01-11 Volume: 40000000
Another table (jData) contains the delta (difference) from the previous iData entry:
jData
-----------------------------
2000-01-02 Volume: 1
2000-01-03 Volume: -2
2000-01-04 Volume: 1
2000-01-05 Volume: 3
2000-01-07 Volume: 2
2000-01-08 Volume: -5
2000-01-09 Volume: 6
2000-01-10 Volume: 1
2000-01-12 Volume: 2
2000-01-13 Volume: -4
2000-01-14 Volume: 4
2000-01-15 Volume: 3

Obviously this will save a lot of space as the delta is much smaller than storing the exact value (standard compression technique).

So what I would like to do is create a view to automatically display all of the data as if it were all stored in one table:

All Data:
---------
2000-01-01 Volume: 20000000
2000-01-02 Volume: 20000001
2000-01-03 Volume: 19999999
2000-01-04 Volume: 20000000
2000-01-05 Volume: 20000003
2000-01-06 Volume: 30000000
2000-01-07 Volume: 30000002
2000-01-08 Volume: 29999997
2000-01-09 Volume: 30000003
2000-01-10 Volume: 30000004
2000-01-11 Volume: 40000000
2000-01-12 Volume: 40000002
2000-01-13 Volume: 39999998
2000-01-14 Volume: 40000002
2000-01-15 Volume: 40000005

I know I can do this programatically, but is it possible to do this with a view? Any pointers would be much appreciated.

View SQL Files
I am about to by the content of a site, but it is in sql files. I need to view this content and extract to make a new site from it in html format.

How do I view SQL files and extract it content? Is there any program that does it?

Can I View .frm, .MYD And .MYI Files?
At my current host, the server crashed and all data seemed to be lost. Lucky for us they had had a backup of all data which they put back on the server.

But now I get errors with some tables in phpMyAdmin where they show up as "in use" and when I view them I get the following error:

MySQL
#1033 - Incorrect information in file: *.frm
or

MySQL
#1016 - Can't open file: '*.InnoDB'. (errno: 1)

Because of the issues, they send me the *.frm, *.MYD and *.MYI files.
But am I able to view these files to see the structure or data?
And can I verify if structure or data is corrupt?

View Implementation
Anybody has implemented View for MySQL?
I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

VIEW Problem
I create the next view:

CREATE VIEW VideoDB.FindCustomer AS
SELECT id, last_name&' '&name&' '&initial AS cname, picture, birthday, birthday_already, account_type, rents, exp_date, deposit, exp_deposit, auto1, kinship1, auto2, kinship2,
auto3, kinship3, auto4, kinship4, auto5, kinship5, auto6, kinship6
FROM VideoDB.customers;

When I execute the next statement the result is 0:
select cname from videodb.findcustomer;

How can I concat the whole name.
last_name&' '&name&' '&initial AS cname

Create A View
I have a master table and five detail tables. Now I want to have an overwiew on all detail tables that are related to the master table. The problem is that there are to many repeated records.

My wish is that to have each record of detail table in a single row with master table information in the view table. I tried to reach this with joins (left or right). No chance.. Code:

Mysqldump View
I try to dump the view definition and records using the following command.

mysqldump -uroot -psecret! --databases test --tables proxy_view > mytest.txt

What all I get is the view definition without records. What is the correct method of dumping a view?

Create View
I currently have a script that I have developed on a MS SQL server. As part of it I use a VIEW to do most of my lookups. I want to transfer it to a MYSql DB, but when I try and CREATE VIEW I get the following message....

mysql> create table t (qty INT, price INT);
mysal> insert into t values (3, 50);
mysql> create view v as select qty from t;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'view testv a
s select qty from t' at line 1
mysql>

I am using MYSQL for Netware. I am using an example that I have found to demostrate it, but it just does not work.