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 Complete Forum Thread with Replies
Related Forum Messages:
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Retrive Record Based On Results
Is there anyway to create a loop in mysql so that I can use a variable to return a record, and then get variable from that record to return another record and so on until the variable from the last result set it 0
View Replies !
Grouping Results Based On Internal?
I have in a database which stores insurance quote requests. I want a query that counts the number of quotes by each referrer, grouping quotes made within a 10 minute period as 1 quote Here is an example of the table and the wanted results. TABLE ID referrer type time ip 1 XADW0124 MC 1161683434 82.7.164.59 2 XADW0124 MC 1161683464 82.7.164.59 3 XADW0124 MC 1161683484 82.7.164.59 4 XADW0124 PC 1161684425 62.173.110.62 5 XADW0139 MC 1161685322 195.137.84.105 RESULT ID referrer type time ip 3 XADW0124 MC 1161683484 82.7.164.59 4 XADW0124 PC 1161684425 62.173.110.62 5 XADW0139 MC 1161685322 195.137.84.105
View Replies !
Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this: SELECT A.id AS Id, A.nome AS Nome FROM main_table A JOIN joined_table JT ON JT.id = A.id_cat ORDER BY ("first whoever have a row in" another_table AT) another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first... I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)
View Replies !
Showing Results Based On A Date Range
I have a calendar application, which stores dates as characters in its own column, ( was designed externally and using a now defunct openforge project) So y = year m = month and d = day. So the result would be ....
View Replies !
Conditional SELECT
I have a problem which I am not sure or it is even possible to fix with sql statements. So here it goes. SITUATION --- I have a table with 3 columns (PID, LANGUAGE, TEXT). PID+LANGUAGE is PRIMARY KEY. In this way I can have unlimited languages next to each other in one table with one correspending ID. Depending on the language chosen, I want to retrieve the text. No so hard. SELECT TEXT FROM TABLE WHERE LANGUAGE = 'spanish'. PROBLEM --- NOT all languages are presented. So I want to fall back to English if Spanish (e.g.) is not presented in the table. Of course I could choose for SELECTING english AND the language I want, and determine with PHP or I have data. But I am talking about 10k+ rows, so I dont want to retrieve everything double. Just only when I really need to
View Replies !
What Is The Best Way To Select By Conditional Data
I have the following structure: dater1_id dater2_id dater1_rating_overall dater2_rating_overall Now, i need to select from the table the values where dater1 or dater2_id equals a certain id and check that the dater1 or dater2 rating (depending on weather the dater1 or dater2 id was matched) is higher than certain number. Maybe I set up my table structure wrong? Making 2 entries for each rating of one user to the othe seems highly ineffecient though.
View Replies !
Using The LIKE Conditional Twice In A Query
I can't get my head around this query and i would be greatful for any help thrown my way. Its part of my search feature on one of my sites. I have the variable $keyword which is a word or phrase the customer enters trying to find a product on the site. I need the query to search 2 fields that could contain the keyword (ie they could enter a product code or part of the products name/title). The following query works:
View Replies !
Conditional Select/Join Across Three Tables
transactions id, customer_id, customer_valid, amount, processed_date valid_customers id, first_name, last_name invalid_customers id, first_name, last_name in the transactions table, the customer_valid is a boolean value which indicates whether the customer record resides in valid_customers or invalid_customers and i have achieved the information i want via this method ...
View Replies !
SELECT Conditional... Matching NULL Rows
I am trying to use a SELECT query to retrieve records from a table that have a certain row set to NULL. However when I try a query like the one below... it is not returning any records, eventhough there are thousands of records that have NULL in the 'row1' row. Example query: PHP $query = "SELECT * FROM table WHERE row1=NULL ORDER BY date DESC";
View Replies !
Count Query With Conditional Exclusion By Value?
Below is the first bit of code which is fine (ignore the php syntax) SELECT COUNT(*) FROM $wpdb->posts p WHERE p.post_parent = '{$page->ID}' AND p.post_status = 'publish' AND p.post_type = 'page' This is in a Wordpress environment if it isn't obvious. The meta table just lets you assign user defined key/value pairs to posts. Now if you want to positively check against meta values the Wordpress website recommends something like this: SELECT COUNT(*) FROM $wpdb->posts p, $wpdb->postmeta m WHERE p.post_parent = '{$page->ID}' AND p.post_status = 'publish' AND p.ID = m.post_id AND p.post_type = 'page' AND m.meta_key = 'tag' AND m.meta_value = 'email' This is easy, just more ANDs as you're looking for posts with a specific key/value pair. But what we need in our case is to exclude the posts with the specific key value pair! The key/value pair is sparse and it isn't practical to require the opposite for every post. The odd thing is, just to get started...
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do: 1- I have Two tables: polls_created and votes 2- Table polls_created is like: poll_id owner poll_subject 3- Table votes has the votes issued for a given poll, like this: vote_id poll_id vote vote_date So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables. How do I then generate this result: MySQL Code: SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes "sorted by polls that have gotten most number of Votes" Of course "sorted by polls that have gotten most number of Votes" is not real MySQL
View Replies !
SELECT Based On 2 Tables
I'm writing some custom stuff to pull from a WordPress install I have one one of my servers. For one function I want to SELECT all the rows from one table based on criteria from that table and another table (they are related by the row ID of the first table). Here is the SQL I have now.... sql Code: Original - sql Code SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC It seems to be returning too many results. It's just returning all the rows from wp_posts that match the post_type and post_status criteria and ignoring the term_taxonomy_id (from wp_term_relationships) requirement.
View Replies !
Select Based On Rarity?
I have a table and one column is titled "rarity." Every once in a while, ONE random row from this table will need to be selected... I want to select the random row based on the rarity column. All of the rarities would equal 1. For example, a rarity of .38 would have .38 probability of being selected from the table.
View Replies !
SELECT Based On All Day Names And Their Hours
I have a table which has a DATETIME column which stores datatime for each record. I wanted to calculate the total/average of the records for each day and then for each hour of that day ALL TIMES like not only the current week or current month or year but for all available records. a sample output can be Monday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) Tuesday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) ... Sunday 00 (count value) 01 (count value) 02..... (count value) 23 (count value) I tried something like following SELECT count(id) as cnt,HOUR(hitdate) as hr, hitdate FROM hits GROUP BY DAYOFWEEK(hitdate),HOUR(hitdate) And I am getting the following results, (long result) cnt hr hitdate 1274 0 2008-07-27 00:00:50 1235 1 2008-07-27 01:00:07 921 2 2008-07-27 02:02:13 830 3 2008-07-27 03:00:03 674 4 2008-07-27 04:02:49 617 5 2008-07-27 05:00:37 606 6 2008-07-27 06:01:41 545 7 2008-07-27 07:01:52 534 8 2008-07-27 08:05:58 630 9 2008-07-27 09:04:01 680 10 2008-07-27 10:04:41 688 11 2008-07-27 11:01:26 881 12 2008-07-27 12:00:22 745 13 2008-07-27 13:00:09....
View Replies !
Select Columns Based On Subselect
Is it possible to do the following: select (select from factors where type='factor_1') from survey_results where last_name='some_name'; I get the error: "#1242 - Subquery returns more than 1 row" Can I convert the rows to a list? More info if needed: I have two tables, 'factors' and 'survey_results'. A survey_result row contains a list of factors (> 30). Factors can have different types. For a survey result I'd like to select only the columns related to a certain type of factor and analyze just those factors (e.g., find the min and max scores). I don't want to hard code lists of factors of certain types to use in my outside select statement. I'd like to do it dynamically based on the factors table.
View Replies !
Select Statement Based On Bytes
I have a table with an attribute named "message" with a varbinary type in which information is being stored in. It contains a total of 10 bytes and I want to select ONLY bytes 4-7.
View Replies !
Mysql Select Based On Datetime
I am having an issue returning a known record based on a date field that is of datetime format from mysql. the data looks like this in mysql: 2006-01-26 20:01:45 here is the eror I receive: SQL 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 ཐ:01:45' at line 1 this is the select statement I am using ("SELECT selections from your_show WHERE userid = $userid and date = ".rawurldecode($date)." "); $date is passed as 2006-01-26%2020:01:45
View Replies !
Select Record Based On 'change' Date
I currently have a database containing a table with the following fields: signid, sign_typ, changedate and time. The 'changedate' field is the date on which the star sign changes and becomes 'active' until the next 'changedate' in the table. I am trying to search this field by a date the user enters and display the 'sign_typ'.
View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
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 !
CREATE Tablename Based On Result Of SELECT Statement?
Is it possible to have an CREATE statement that contains a SELECT in the section of the CREATE that specifies the TABLE? I want to have a sql script that can execute SELECT YEAR(CURDATE()) and SELECT MONTH(CURDATE()) to give me the Year and Month and then use these to create the Table Name: tbl_MetaData_2008-03 Example: CREATE TABLE tbl_MetaData_X-Y ( ... Where X is the YEAR and Y is the MONTH
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 !
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 !
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 !
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 !
Results Of A SELECT SUM
I am trying to figure out how to use the results of a SELECT SUM using Java (which I know very little about) rs = databaseStatement.executeQuery ("Select sum (unitcost) as total from subcomponents") The query seems to run correctly so, I now say System.out.println (rs.getString("total")); or System.out.println (rs.getDouble("total")); and the result is java.SQL.SQLException: Before start of result set What am I missing.
View Replies !
SELECT Results After X Rows
I am trying to run a league creation script using php and mysql. I was thinking that i could do it by getting every user into one column and then pair them off by starting from the second result found and place that with the first user in the first fixture row. For the next fixtures i would place the 3rd result found in a row with the initial users. This way i make sure they dont play each other twice and each fixture created in unique. Is their a mysql function to do a select query but dont have it start outputting the results until it reaches a certain row from the results? I will also need it to go back to the first result found once it reaches the end of the results.
View Replies !
Limiting The Results In SELECT
I could not find where it is written, so I want to ask if MySQL can do this. In the event I want to not select a few columns is there a way I could say something like: SELECT * (but exclude col_08 and col_99) FROM table or would I have to list all 98 columns I wanted to exclude the two I don't want?
View Replies !
Unique Results :: Select Repeated Value Once
I have a column which has a variety of strings, some of which are repeated several times in the column. I'm trying to use a SELECT statement which will only select unique values from the column (only select repeated values once), according to further conditions in my WHERE clause. So, just for example, say I have names, and there's 20 Joe's and 15 Frank's, or whatever; I only want one Joe and one Frank to come from the SELECT statement
View Replies !
Can SELECT Return The Position Of The Results?
I have a table with values: Row: Name Row: ID John 4890 Mark 4922 Peter 4934 John 4980 Joe 5029 Lisa 5044 etc... What I need is a query that I would do for example on John (name='John') that would return the following: location 1 4 In fact the only thing I need is the location of the John in the resulting table. Apart from the Name column I do have other column of ID's. They are incremental but not step 1 if this can help in any way. Is something like that possible with one query?
View Replies !
No Results With SELECT Using INET_NTOA & INET_ATON
Why am I not getting any row when in fact there are several records in the db? // `user_ip` int(10) unsigned NOT NULL default '0', $query = query_db("INSERT INTO online_users (user_ip, time_stamp) VALUES (INET_ATON('".addslashes($user_ip)."'), '".addslashes($timeout)."')"); /* ... code code code ... */ $query = query_db("SELECT * FROM online_users WHERE (user_ip = INET_NTOA('".addslashes($user_ip)."'))"); echo mysql_num_rows($query); // always 0
View Replies !
Select From Table And Count Results In Another
I have to tables table one containing airports and table two containg bookings. What I need to be able to do is count how many rows are returned form the booking table that matches each airport in the airport table. So I might have in my airport table Newcastle Heathrow Standstead etc etc and in my bookings table I have Newcastle Newcastle Standstead Heathrow Heathrow And what I want returned is Newcastle(2) Standstead(1) Heathrow(3) How can I go about doing this.
View Replies !
Many To Many Select Using DISTINCT Or Not Return The Same Results
I was on MySQL website looking for an answer, but still need assistance please. I want to do the opposite of the following: SELECT * FROM myTable WHERE id IN(1,2,3,4,5) ; I want to use my array(1,2,3,4,5) to exclude items in the result set, not include them. So, I want to do something like this: SELECT * FROM myTable WHERE id NOT_IN(1,2,3,4,5) ;
View Replies !
Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables ex: table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... , , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... , , ... , , ...table3_Col1, table3_Col2, table3_Col3... , , ... , , ...table3_Col1, table3_Col2, table3_Col3... using the 4.0 sql so no subqueries supported.
View Replies !
|
TRACKER
