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 Complete Forum Thread with Replies
Related Forum Messages:
Multiple Subselect Vs Additional Query
I have an existing single row LIMIT 1 query. I also have another table that has 3 multi row columns that I need to count that has a relation to one column in the original table. I can accomplish this with 3 subselects in the original query or I can add another query. I guess my question would be are the 3 subselects going to add enough overhead that an additional query would be warranted? Or is it still more efficient to use a subselect than add a query? I can't change the original query and I am accessing it through hooks so my options are limited there.
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 !
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 Replies !
Parametrize Additional Column
I need to put current date into additional column title. I mean something like this SELECT Name_1, ' ' AS CURDATE() FROM Table_1 ... or, better SELECT Name_1, ' ' AS CONCAT('My column ', CURDATE()) FROM Table_1 ... But it doesn't work. AS seems to take only single strings. Is there any solution?
View Replies !
Parametrize Additional Column
I need to put current date into additional column title. I mean something like this SELECT Name_1, ' ' AS CURDATE() FROM Table_1 ... or, better SELECT Name_1, ' ' AS CONCAT('My column ', CURDATE()) FROM Table_1 ... But it doesn't work. AS seems to take only single strings. Is there any solution?
View Replies !
Open Additional Port
I need to access an external MySQL db through a firewall. I'd like to open up an additional port (leaving 3306 as is) to the same MySQL instance. For example, I have my mail server running on both ports 25 and 26 (ISP blocks outbound 25).
View Replies !
How To Parametrize Additional Column Title?
I need to put current date into additional column title. I mean something like this SELECT Name_1, ' ' AS CURDATE() FROM Table_1 ... or, better SELECT Name_1, ' ' AS CONCAT('My column ', CURDATE()) FROM Table_1 ... But it doesn't work. AS seems to take only single strings. Is there any solution?
View Replies !
SELECT DISTINCT Then Additional Fields
I am trying to do the equivalent of : Code: SELECT DISTINCT email, * FROM student_bookings I know this will never work but think it explains best what I am looking for - I want to select additional fields from each DISTINCT email. I know I could use a subquery but are there any other ways of doing this? (unfortunatly I can't use subqueries as we don't have the correct version of mySQL for that ).
View Replies !
Import Additional Field Data To Existing Table
I've got my data all set-up in a mysql table and all functioning well. We have decided that we need some additional bits of data for each record and we have that data in a csv file. We've created the fields in the mysql table. In the csv file, I have the userid (to match that in the current sql table) and the additional data with the column names in the csv file matching exactly the new fields in the sql table. The additional fields in the original sql table are empty, so we can just simply write-over these fields with the data from the csv file. But...we are having problems with the import using the MySql administration tool on our server. If I select a csv file upload, I get a number of fields is not same error If I use csv load data upload, it just overwrites the first few lines of current data and doesn't put the new data in the correct fields. Can anybody help, please? If csv load data is the correct format to use, what do I put in the import tool for these variables (given that I have a standard csv file that has been created using excel) Replace table data with file (yes/no default is no) Ignore duplicate rows (yes/no - default is no) Fields terminated by(default is Fields enclosed by(default is ") Fields escaped by(default is ) Lines terminated by(default is auto) Column names (default is blank) Use LOCAL keyword (yes/no - default is yes)
View Replies !
Nested Set: Problem: Get Branch With Leafs And 1:n Data Conected Via Additional Table
I am quite at the end of my wits, I have a nested set table which leaves are conected with 1:n to a other table. Is it possible to write a query for a nested set to get a branch with its leaves. and at least one of the Entrys for the leafs from the other table? The SQL for the Subtree is: SELECT group3. * , COUNT( group2.id ) -1 AS LEVEL FROM contenttree AS group1, contenttree AS group2, contenttree AS group3 WHERE group3.lft BETWEEN group2.lft AND group2.rgt AND group3.lft BETWEEN group1.lft AND group1.rgt AND group2.lft BETWEEN group1.lft AND group1.rgt AND group1.id =112 GROUP BY group3.lft ORDER BY group3.lft
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 !
Add Additional Data To Existing Data In A Blob Field
I have a Blob field called "category" that we use to store different categories of job types that our users set up so they receive emails based off of the ones that they have picked. What I need to do is to add 4 new values to the beginning of each users "category" field. I am not really sure how to do this except that I might need to use UPDATE to get it done. This is where it will get tricky, I need to check for the 4 values to see if they already have them and just add the oines that they don't have. Here are the 4 values that I need to check for and add. Allied Health Care, Nursing, Rehab/Therapy, and Other/Pharmacy/Physicians. Here is what a current list of values would look like in the users table and the category column: Code:
View Replies !
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?
View Replies !
Using IF Condition
I want to do a query that first checks whether 2 items are similar, and if they are, uses conditon A, and if it doesn't, uses condition B. For example: SELECT * FROM table IF(A = B, WHERE A > 10, WHERE B > 10) So if A = B, the query will be: SELECT * FROM table WHERE A > 10 and if A != B, the query will be: SELECT * FROM table WHERE B > 10
View Replies !
Like Condition
How would I select everything that starts with a and b from the name column in the persons table? I know how to get it for just people starting with a- SELECT * FROM persons WHERE name LIKE 'a%' But how do I do a and b?
View Replies !
If Condition Help
SET @RecordCount = 0, @PageCount = 0, @PageNumber = 0, @PageSize = 0; SET @PageSize = 10; IF(1 > 1.0) THEN SET @PageCount := (@RecordCount / @PageSize) + 1; END IF;
View Replies !
If...else Condition
I have a table - mytable and the columns in the table are as follows: CityName (varchar) updated_by (varchar) added_by (varchar) The first column is a city column, someone would add/update the city names and accordingly their name would be updated/added to the updated_by and added_by columns. Now, the condition of my query would be: - if the updated_by column is NOT empty and the added_by column is empty, then the value of the updated_by would show . - if the added_by column is NOT empty and the updated_by column is empty, then the value of the added_by column would show. - if both added_by and updated_by columns are NOT empty then the value of the updated_by would show.
View Replies !
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)"
View Replies !
Condition In CONCAT
i have three fields of name in database named as fnam,mname,lname. fname and lname is mandatory field and mname is optional. i want to make a search query on name Expand|Select|Wrap|Line Numbers
View Replies !
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 (...)
View Replies !
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.
View Replies !
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.
View Replies !
WHERE Clause 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??
View Replies !
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.
View Replies !
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?
View Replies !
If Condition In View
Basically i want to add put a where condition in a view if the user variable '@org_id' is > 0. Here is some pseudo code: CREATE VIEW view_students AS SELECT * FROM students If (@org_id > 0) THEN WHERE org_id = @org_id END IF I've tried lots of different approaches but i just cant figure out how to do it.
View Replies !
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.
View Replies !
Insert With Condition
would like to check if data exists in my database, if no then insert it to my database: insert into table (column1,column2 ,colum3) values (value1,value2,value3 ) where(column1<>value1);
View Replies !
Nested Condition?
I'm just getting trouble making a query that should be like this: SELECT * FROM table where [cond1...] AND [cond2...] AND ([cond3...] OR [cond4]); In fact I need to verify if certain value is in one column or another.
View Replies !
Condition Practices
is it better to have conditions for joining tables in the WHERE section of a query string rather than the ON part of the LEFT JOIN part when there are multiple conditions? The reason I ask is I have a query string with multiple (17 to be exact) joins and a few of them are farily big tables, and I had mysql-nt crash when I had 2 separate conditions on 1 of the left joins. I moved one of the conditions to the WHERE section and the crashes seem to have stopped. I pinpointed the crash to 1 LEFT JOIN that has 2 conditions tied to it so I made a snipped version of the string that still crashed it: SELECT * FROM tblinvoices inv LEFT JOIN tblinvoicedetails invd ON invd.invoiceid=inv.ID LEFT JOIN tblrelpatternfit pf ON pf.patid=invd.descid AND (pf.fitid=invd.fitid OR pf.fitid IS NULL) WHERE invd.invoiceid=$intid OR (invd.invoiceid IS NULL AND inv.ID=$intid); This string crashes it everytime (because of the tblrelpatternfit part). I checked the table with check table just to make sure it wasn't corrupted and it was OK. When I moved the 2nd condition on the tblrelpatternfit to the WHERE at the bottom it stopped crashing, but I wanted to see if this was a known problem (v5.1.11) or just me being a noob. Just as a side note, there are other LEFT JOINs in this query that work fine with 2 conditions, but the tables aren't as big as that one is.
View Replies !
Use A Result Set As A Condition
I have two different tables in my database. One is a relationship table defining parent/child relationships between my data items. Here's a simplified example: ID1......ID2 ..1.......101 ..1.......102 ..1.......103 ..2.......104 ..2.......105 --etc-- The other includes a field that has the the name of each item. Another simplified example: ID........Name ..1........Animal ..2........Plant ..101......Monkey ..102......Dolphin ..103......Eagle ..104......Grass ..105......Sunflower --etc-- My task is to find the IDs and Names of all the children of a given ID. Getting all the childrens' IDs is easy. Here's the query I'm using: SELECT ID2 FROM RelTable WHERE ID1 = [given ID] But to get the names of all the IDs returned in the result set, I have to make a query to the name table for each row returned. So if I get 300 children, that's 300 separate queries to the name database, which is slower than I'd like. Is there any way to do this with a constant number of queries?
View Replies !
Using <= Condition In Formula
I have a SQL formula as below. Manifest.Weight, GREATEST(Manifest.Weight, (((Manifest.U * Manifest.L * Manifest.B * Manifest.H) + (Manifest.U1 * Manifest.L1 * Manifest.B1 * Manifest.H1)) / (Manifest.Factor)) As `Greater of DIMMS & Weight` With the alias result I need to determine in which catergory it will be as below. Client.`0to20KG`, Client.`20to30KG`, Client.`30to40KG`, Client.`40to50KG`, Client.`50KGplus`
View Replies !
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
View Replies !
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.
View Replies !
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%'.
View Replies !
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 .
View Replies !
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.
View Replies !
Order By Condition?
The query below is a union of 3 SELECT statements. The only difference in each one is the value of 'submission_views.timestamp' in the WHERE clause. This query is very long and I want to shorten it. Would it be possible to combine all 3 statements by sorting the records by WHERE conditions? The records where the timestamp is within the last 7 days would come first, followed by the ones where the timestamp is older than the last 7 days, then finally the records where the timestamp is NULL.....
View Replies !
Wrapping A Condition
Would mysql interpret this query properly? ...WHERE type_id='$type_id' AND cat_id='$cat_id' OR maincat_id='$maincat_id' AND item_activated='1' I have a GET var that will get either cat_id or maincat_id but all other conditions apply,i want something like this: WHERE type_id='$type_id' AND (cat_id='$cat_id' OR maincat_id='$maincat_id' ) AND item_activated='1'
View Replies !
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?
View Replies !
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 )
View Replies !
Delete With Data Condition
I have a table with a ton of rows but I only want to delete all the rows after the following date. 2008-09-03 18:55:09 All the dates before that are in the year 2007 so how can I form a statement which deletes from the column "DataEntered" with any dates before 2008. Just out of curiosity how do I make the condition after the DELETE to ignore the seconds and such so that it only needs to read 2007-11-18 and ignores 16:27:41
View Replies !
LIKE Condition For An Exact Match?
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.
View Replies !
|
TRACKER
