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 Complete Forum Thread with Replies
Related Forum Messages:
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.
View Replies !
Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.
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 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 !
Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57 # User@Host: counter[counter] @ localhost [] # Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647 SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7; mysql> select count(*) from _1_log; +----------+ | count(*) | +----------+ | 111824 | +----------+ 1 row in set (0.00 sec)
View Replies !
What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this: SELECT COUNT(*) AS exists FROM mytable WHERE ... Sometimes I just select the first row and check later in php how many rows have been returned: SELECT some_col FROM mytable WHERE ... LIMIT 1 But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example: SELECT id, name, (SELECT COUNT(*) FROM mytable WHERE ...) AS exists FROM othertable WHERE surname='xxx' Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.
View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Sub Query - Aggregate Fields Based On Min N Max
In the Users table below there are duplicate users by email address +---------------------+------------------------------+-----------------+-----------+ | ts | email | field1 | field2 | +---------------------+------------------------------+-----------------+-----------+ | 2009-01-31 06:51:14 | user1@rediffmail.com | 05 | 03 | 2009-01-31 16:07:39 | user2@yahoo.com | 02 | 02 | 2009-01-31 16:15:02 | user2@yahoo.com | 09 | 04 | 2009-01-31 16:16:00 | user2@yahoo.com | 06 | 08 | 2009-01-31 16:19:52 | user2@yahoo.com | 01 | 09 | 2009-01-31 02:04:36 | user3@rediffmail.com | null | 01 | 2009-01-31 02:12:34 | user3@rediffmail.com | 01 | 03 | 2009-01-31 02:20:31 | user3@rediffmail.com | 08 | null +---------------------+-----------------------------+--------------+-----------+ I want to fetch one record per user ‘user1,field1,field2’ For user 1 select field1 where min(ts) select field2 where max(ts) the final output should be user1,05,03 user2, 02, 09 user3, 01,03 (max of ‘field2’ is null so it should pick the field value which matches the next min ‘ts’ val)
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example: time_start,time_end,num_a,num_b,price etc. How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X. So,the query is about finding the values of `price` between 2 dates in differnt tables.
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result. tbl_A fields: ID and Date tbl_B fields: ID and FileName Code: Select ID FROM tbl_A where Date>=CURDATE() this 1st_query_result will be any ID with current date from tbl_A. Then I need this 1st_query_result to query the filename which store at tbl_B. something like this: Code: select FileName from tbl_B where ID = 1st_query_result
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
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 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 !
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 !
|
TRACKER
