Request Help For Subquery
I have a table that records targets and the time it appears on a
display. What I would like to do is to report the time difference for
each individual target from the initial appearance to the subsequent
one, and the time difference from the subsequent one to the next, and
so on.
So how do I put these these all together to produce one query:
for each "select distinct target from display"
for number of rows -1 with target
"select timeX - timeY from (subquery for distinct target) where
(X,Y = subsequent, initial times)"
View Complete Forum Thread with Replies
Related Forum Messages:
Request Modelling
I would like to list all courses with have less than lets say 10 participants. Does anybody know how I would have to modify the code below to get this to work ? schedule_id is the course itself SELECT schedule_id,count(schedule_id) FROM attendance WHERE count(schedule_id)<10 GROUP BY schedule_id ; My problem is that I would need to use the count function actually within the WHERE (clause which is not allowed).
View Replies !
Enhancment Request
How does one go about submitting an official MySQL enhancement request? I am in the process of trying to certify our product (WebLogic Portal) for MySQL and have run into the following in several places ... database error code [1,093] SQL state [HY000] 2263.; nested exception is: java.sql.SQLException: You can't specify target table 'xxx' for update in FROM clause The fact that this SQL works just fine for FIVE other currently supported DATABASES but that we will have to change it to make it to work with MySQL is quite an annoyance. As best as I can tell, we won't even be able to use the same SQL for MySQL as we do for other databases so the code will have to look at what DB the app is configured for and choose the appropriate SQL.
View Replies !
Exclusiv Request
I'd like to select ID's from a table wich are not in another table: | TABLE1.ID | TABLE2.ID | |--------------------------------| | 1 | 1 | | 5 | 5 | | 7 | | | 8 | | So, the result of my SELECT should be: 7, 8.
View Replies !
Request | Username
I didn't found a good example to set the three % to single variable characters... My sql string = SELECT * FROM table WHERE username LIKE '".$_SESSION['user'] &&& $_SESSION['domain']."' ORDER BY username ASC LIMIT $current_page, $results_per_site");
View Replies !
Password Request
I just recently downloaded the community version of MySQL 5.0. I'm trying to set it up using the Config wizard. A while ago I asked how I go about getting or finding out what my root password is so I can proceed with the set up. I was told to set up my password using the command line.I was also told to log in as root and issue the command SET PASSWORD ('[new password]'); When I open Command line it automatically asks for my password. Since I don't have one yet I tried to ignore the request by typing the info. above and it won't let me in. Is there another way for me to set up my password using command line? I also don't understand how to log in as root.
View Replies !
Request Error
this is my mysql query: $requete1= " SELECT DISTINCT mariages.* FROM mariages, "; $requete1.= " articles_a_offrir AS A1 INNER JOIN articles_a_offrir AS A2 "; $requete1.= " ON (A1.id_mariage=A2.id_mariage) WHERE "; $requete1.= " ((mariages.id_mariage=A1.id_mariage) "; $requete1.= " AND (mariages.pren_epouse='$prenom_epouse') AND "; $requete1.= "(mariages.nom_epouse='$nom_epouse')) "; $requete1.= " OR ((mariages.nom_mari='$nom_mari') "; $requete1.= " AND (mariages.pren_mari='$prenom_mari')) "; $requete1.= " OR ((DAYOFMONTH(mariages.date_du_mariage)='$jour') "; $requete1.= " AND (MONTH(mariages.date_du_mariage)='$mois') "; $requete1.= " AND (YEAR(mariages.date_du_mariage)='$annee')); "; but i would like it to display only one wedding even if the family name of somebody exists for two differnet people with a different first name
View Replies !
Limitation On Request Per Second
when i preform performance tests i reached up to 30 req/sec can some one know what should i check/do to increase performance? HW Langley 2 1 G memory SW Linux As 3 update 8 MySql version 5.1
View Replies !
Slow Request
I want to run the following request: mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263); where fingerprints is the following table: ....
View Replies !
SUBQUERY Vs DEPENDENT SUBQUERY
I have a table called `accounts`, that contains columns: account_name VARCHAR (128) account_no VARCHAR (16) account_hash VARCHAR (32) balance DECIMAL(10,2) There is an INDEX on account_hash and there's currently 128 rows in the table. I have a table called `activity` that contains columns:....
View Replies !
Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query. First Query select ACCOUNT_ID, ACCOUNT_TYPE_C, PAT_ID from PAT_ACCT_CVG where ACCOUNT_TYPE_C in (120103,120104,120101) Second Query SELECT PAT_CVG_FILE_ORDER.PAT_ID, PAT_CVG_FILE_ORDER.LINE, COVERAGE.COVERAGE_ID, COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT FROMPAT_CVG_FILE_ORDER LEFT OUTER JOIN COVERAGE ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID Where coverage.payor_id = ?' Third Query select TRAN.ORIG_SERVICE_DATE TRAN.TRAN_TYPE, TRAN.INSURANCE_AMOUNT from Tran where TRAN.TRAN_TYPE = 1 and TRAN.INSURANCE_AMOUNT > 0 and TRAN.proc_ID in 1008,1009 (now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)
View Replies !
MySQL Crashes During Request
I'm working on MySQL 5.0 on Windows XP. I've tested a request on MSAccess and it's working. But if I launch it under MySQL, the PC crashes and I have to restart server. this is the request : SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 147 and Ve_Val ='NO_OUTIL' and Ve_Groupe IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 148 and Ve_Val ='FIB' and Ve_Groupe IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 149 and Ve_Val ='1.4732')); There are 3 requests. If I put 2 requests, it's running. But If I put more than 2 requests, it crashes. Have you an an idea about this problem ?
View Replies !
How To Get The Time Spent On A Request From PHP?
I would like to write the spent time for a mysql select, in the style of Google's searches: "x results found for xxxxxx in xx secs". How could i get the spent time from my PHP code? When you do a mysql statement on a terminal this is shown at the end of the results, but i can't find how to get this from PHP.
View Replies !
Request Returned With Sql_error
iam useing windows 2000 pro as server,i install mysql 5.0, can use window 2000 pro as mainserver for mysql 5.0? when i create dsn [MySQL][ODBC 3.51 Driver]Unknown MySQL server host 'ss' (11001) i cant connect mysql
View Replies !
Date Range Request
Dates are stored in unix format GMT. I want to find all records either by "day" or by A Date Range "week" i have to deal with users seeing data in their time zone so people in GMT +9 would see records posted accordingly to someone in say GMT -8? Code:
View Replies !
Multiple Tables Request
I would like to perform a query on two tables. I want only records that match the conditions in each of the base. First bas would be : where date>mktime() Second would be : where test is not empty When I performed with the following SELECT, I have too many records with strange joining. $now=mktime() SELECT * FROM table1, table2 WHERE table1.date>$now AND table2.test<>""
View Replies !
Is There A Way To Make 2 ORDER BY In The Same Request?
I'm using Mysql for my databsae but I don't know a lot about it. I made a request like this: $query = 'SELECT * FROM serie ORDER BY title ASC' But I also have a 'season' colunm in my database an I want the title to be order by their name and either the season. For example my db is filled like this: id:1, title:spongebob, season:2 id:1, title:alien, season:1 id:1, title:spongebob, season:1 My $request will echo: alien, season 1 - spongebob, season 2 - spongebob, season 1 It's ok for the title but not for the season...
View Replies !
How Database Handle Request?
I want to write a program that can access to multiple database based on the user ID, but I dont know how the database handle user request. Please help me... if user A execute program and redirect to database abc, and user B also accessing it. user A retrieve value from database but not yet update it, at the same time user B go get the value. How can I control it? can I code a program that control if A not yet finish, we cannot allowed user B to retrieve the value? I cannot use Java synchronization. if I use it, it will synchronize for all the request although the request is redirect to different database(I am using many databases but sharing 1 program). There is no point to do synchronization if another user accessing other database that is no user at that moment but still need to wait user at different database finish their tasks first.
View Replies !
Song Request System
I'm new to MySQL and have been racking my brains at this for hours, studying the MySQL documentation, etc, and have given up. All I want is some form of PHP/MySQL integration on my website that allows visitors to Reuqest Songs, and my DJ's be able to view all the reuqests in a page. It'd just involve adding values to a database (song name and song title) then getting a page to query them out and display them again. Would someone be so kind to write me a very very basic script that will do what I require?
View Replies !
Baffled By A Query Request
I got a task in my database design class which has baffled me. No matter what I'm trying to to, it's a no go. CREATE TABLE IF NOT EXISTS `country` ( `Code` char(3) NOT NULL default '', `Name` char(52) NOT NULL default '', `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia', `Region` char(26) NOT NULL default '', `SurfaceArea` float(10,2) NOT NULL default Ɔ.00', `IndepYear` smallint(6) default NULL, `Population` int(11) NOT NULL default Ɔ', `LifeExpectancy` float(3,1) default NULL, `GNP` float(10,2) default NULL, `GNPOld` float(10,2) default NULL, `LocalName` char(45) NOT NULL default '', `GovernmentForm` char(45) NOT NULL default '', `HeadOfState` char(60) default NULL, `Capital` int(11) default NULL, `Code2` char(2) NOT NULL default '', PRIMARY KEY (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I am asked to display the highest populated country on each populated continent. And it is supposed to be done in one query.
View Replies !
MySQL Execute An HTTP Request
Can the MySQL engine execute an HTTP request? Here is he scenario. I have a table that rarely changes but when it does I need to notify a web service. Is it possible to create a trigger that will make an HTTP call when fired and pass a couple of the field values as URL parameters.
View Replies !
Request With Large Results Seems Stuck
when running the following request: select fingerprint, count(*) from fingerprints group by fingerprint; where fingerprints contains 32M rows, I have no results -it seems stuck- and I can't even see mysqld task in 'top', while when running the same request with a 'where' statement to reduce the number of data: select fingerprint, count(*) from fingerprints where fingerprint<353535 group by fingerprint; (about half of the fingerprint have value<353535) I get a result in less than a minute and mysqld is the first in the 'top' list, taking 100% of the cpu (not much memory though).
View Replies !
Big INSERT Request :: Lost Connection
I send a big INSERT request to the mysql server and I very often get a 'Lost connection to MySQL server during query' error. Though the client and the server are on the same machine.... Is there any way to avoid this problem ?
View Replies !
2nd Request, Alpha & Numeric Queries
I am trying to pass through a string that I am exploding into individual values to use in a query. For example, I want to pull the following three values through a query: 99201 A0040 99211 When I run it through the following query (it's missing some html tages, just trying to do a dry run) i twill run the first value through (regardless if it's a alpha based or numeric based), but after that it hits the else phase of the if (mysql_num_rows($mcalookup) >= '1') { code. Any clues what's going on? BTW< if I strip off the ' ' around the variable, it dies when it hits the alpha based variable. Code:
View Replies !
Request Idea On Best Performance Setting Of Variables
Linux : Fedora 8 RAM : 1.8 GB Use for my algorithm are innodb table types. My current 'show variables' list the following: mysql> show variables; +---------------------------------+-------------------------------+ | Variable_name | Value | +---------------------------------+-------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | / | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | keep_files_on_create | OFF | | engine_condition_pushdown | OFF | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | YES | | have_bdb | NO | | have_blackhole_engine | YES | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_example_engine | NO | | have_federated_engine | YES | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_merge_engine | YES | | have_ndbcluster | DISABLED | | have_openssl | DISABLED | | have_ssl | DISABLED | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | hostname | bytedge1 | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_adaptive_hash_index | ON | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 268435456 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | ON | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | 1 | | long_query_time | 1 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294963200 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 2146435072 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 67108864 | | myisam_stats_method | nulls_unequal | | ndb_autoincrement_prefetch_sz | 1 | | ndb_force_send | ON | | ndb_use_exact_count | ON | | ndb_use_transactions | ON | | ndb_cache_check_time | 0 | | ndb_connectstring | | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 1024 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | pid_file | /var/lib/mysql/bytedge1.pid | | plugin_dir | | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 1048576 | | read_only | OFF | | read_rnd_buffer_size | 4194304 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 1 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_load_tmpdir | /tmp/ | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slow_launch_time | 2 | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 1048576 | | sql_big_selects | ON | | sql_mode | | | sql_notes | ON | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | IST | | table_cache | 256 | | table_lock_wait_timeout | 50 | | table_type | MyISAM | | thread_cache_size | 8 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 33554432 | | tmpdir | /tmp/ | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | updatable_views_with_limit | YES | | version | 5.0.67-community-log | | version_comment | MySQL Community Edition (GPL) | | version_compile_machine | i686 | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | +---------------------------------+-------------------------------+ 237 rows in set (0.00 sec)
View Replies !
Sum() And Subquery
SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2 FROM `NetworkTraffic` LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId WHERE NetworkTraffic.protId=2 AND date between "2005-10-11" AND "2005-10-18" GROUP BY domainTable.name I am using the left-join above to merge two tables (NetworkTraffic and domaintable) on domainId to find which domain to present. By doing this i summarize the download and upload from NetworkTraffic between 2005-10-11 and 2005-10-18, however i want to be able to summarize the traffic from one day, say 2005-10-12 in column 3 and 4 (Download2, Upload2). Is it possible to make a subquery in the sum-function or should i take another approach? I am using mysql server 4.0.18-max-debug
View Replies !
LIKE ANY + Subquery
I'm trying to get the following statement to work: SELECT * FROM discountItems di WHERE di.name LIKE ANY (SELECT lsw.word FROM ifDefinedSearchWords dsw join ifLinkedSearchWords lsw on lsw.fIFEntityID = dsw.fIFEntityID WHERE dsw.word like 'schoggi') It is supposed to find some words in a subquery as one row, and then search another table for records matching any of those words. I get the following error message: #1064 - 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 .....
View Replies !
Subquery Help
I am using mysql 4.0 which does not support subquerys.How can i rewrite the below query using joins for mysql 4.0 select * from t1 where t1.eid not in(select eid from t2)
View Replies !
Subquery Help.
I am using the following but then I rembered I am getting the readyPrinted_id from the select statement!! How can I fix it so it gets the readyPrinted_id and then performs the AND with that same ID? SELECT `sub_name` , `readyPrinted_name` , `readyPrinted_id` FROM sub s, readyPrinted r WHERE s.sub_id = $sub_id AND s.readyPrinted_id = r.readyPrinted_id AND r.readyPrinted_id = readyPrinted_id
View Replies !
How To Do A Subquery With MySQL 4.0 ?
I couldn;t upgrade to mysql 4.1 for some reason, so I am forced to use mysql 4.0. I try to change my database from pgSQl to mySQL. But the proble is how to make a query like : "SELECT field FROM table WHERE (select count(*) from table2)<5 " which is very easy under pgSQL but not allowed under mysql because the subqueries seem not to be allowed...
View Replies !
Subquery (not Exists)
I've got some sort of syntax problem that doesn't seem to make a lot of sense. I'm developing a Categories Theory application and because of that I need to make big, and by "big" I mean HORRIBLY HUGE queries. That one has 54 lines and 3 subqueries (only the first one is shown so that I won't scare people off :)). The thing is, I don't seem to be getting the hang of how to do subqueries. The syntax seems fine... But it'll still always say the same thing: 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 'exists ( select * from objeto c, morfismo f1, morfismo f2 (It's MySQL 4.0.11a-gamma) The only difference I see between the code below and the mysql.com documentation is that my subqueries aren't alone in their "where" clauses... Aside from that, they seem pretty much okay. Am I missing something?.....
View Replies !
Correlated Subquery
SELECT MAX(e1.score) AS high_score , e1.dept , (SELECT e2.emp_no FROM Employee_Evaluations AS e2 WHERE e2.dept = e1.dept AND e2.score = MAX(e1.score)) AS emp_no FROM Employee_Evaluations AS e1 GROUP BY e1.dept It doesn't work in MySQL (I've tried 4.1 and 5.0), but it seems like it should work and if I'm not mistaken, it's valid SQL99. Can anyone confirm this? I don't need help writing a compatible version, I've already done that - just curious if this little bugger is standards compliant.
View Replies !
Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished) select date from temps where date in (select distinct date from observations where camera like "a") The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?
View Replies !
Subquery As Join
I have the following query, which works perfectly on my developpement computer: UPDATE writings SET num_votes=(SELECT COUNT(vote) FROM votes WHERE writing_id=id), rating=(SELECT AVG(vote) FROM votes WHERE writing_id=id); Unfortunately, my host doesn't seem to allow subqueries. Is there a way to do this with joins? I've heard that joins are faster anyhow.
View Replies !
Subquery Issue
I have a query at sql.pastebin.com/d3398502e and it's a bit of a monster (sorry, it says I can't post the actual link because I'm a new user, I assume it's a spam precaution, so please copy and paste). It's a search query for a hotel site, like expedia, but it needs the ability to get different prices for children in a room based on their age. Each hotel can have age groups, so it needs to figure out how many children are in each age group for that hotel (which works, that's all the UNIONs), and then apply the price associated with that age group to the number of children for that hotel for that date. That's where I'm stuck since the price data for that date is not available in the subquery. The way I have it now it's selecting all dates that have child prices applied and adding those together, rather than just the one date.
View Replies !
Subquery To JOIN
SQL Code: Original - SQL Code SELECT COUNT(*) FROM `cmn_group` AS `g` INNER JOIN `cmn_company` AS `c` ON g.cmn_company_id = c.cmn_company_id WHERE g.cmn_group_id !=5 AND g.group_name = 'G4' AND g.cmn_company_id = ( SELECT cmn_company_id FROM cmn_group WHERE cmn_group_id =5 )  SELECT COUNT(*)FROM `cmn_group` AS `g`INNER JOIN `cmn_company` AS `c` ON g.cmn_company_id = c.cmn_company_idWHERE g.cmn_group_id !=5AND g.group_name = 'G4'AND g.cmn_company_id =( SELECT cmn_company_id FROM cmn_group WHERE cmn_group_id =5)
View Replies !
Using Limit In Subquery
I am trying to use Limit in a subquery but receive an error. Is there another way to archive the same end result without Limit? SELECT * FROM news WHERE news.id NOT IN (SELECT news.id FROM news ORDER BY date DESC LIMIT 15) Error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
View Replies !
Update With Subquery?
I have two tables and I want to update one with data from the other TableA id description date TableB id counter date I want to update counter in TableB with its current value minus a count from tableA UPDATE TableB set counter = counter - (SELECT count(id) WHERE description = 'blabla') But I only want to do this on the date columns are equal. For example date in TableB is 2007-03-13, and counter should be updated with its value minus the selection from TableA where date is the same. But I want to do this on all rows for all dates in one query. How do I do that? Add a where to the subquery ie "WHERE TableB.date = TableA.date"? Is that correct?
View Replies !
SubQuery To Add A Column
I have the following SQL which is the best I can do to illustrate what I am trying to accomplish: select column_name as 'name', column_default as 'default', column_type as 'type', (select column_name from maillists where listname="winserver") as 'data' from information_schema.columns where table_name='maillists'; Essentially, I am trying to get a "description" of my table along with an additional column of the current value for each field in the table. What I am getting instead is a repeat of the column name in the data column....
View Replies !
Subquery In COUNT()
I have table with names and ages of peoples. I need to select ALL names and number of younges peoples. name|age aaa |30 bbb |31 ccc |32 eee |32 result: aaa |0 bbb |1 ccc |2 eee |2 I try use: SELECT T1.name, COUNT(SELECT * FROM tablename AS T2 WHERE T2.age<T1.age) FROM tablename AS T1; But error in syntax :(
View Replies !
Error While AVG On A Subquery
I have a ticket_audit_log table that is a list of changes done on tickets in the ticket table. Every change is a timestamp, so i can select the min and max timestamp for each ticket change and calculate the difference that is the time the ticket has been worked on. The query I use is: ....
View Replies !
Using IN With An Empty SUBQUERY
UPDATE bb_topics SET topic_status = 1 WHERE topic_id IN (SELECT topic_id FROM bb_topics WHERE forum_id = 1 AND topic_start_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND topic_status = 0); If the subquery returns an empty dataset I get a syntax error. Is there any way to test if the subquery is null or exist. I have tried EXISTS and IFNULL with no success.
View Replies !
Subquery Using Limit
I am trying to use a limit 1 in a subquery which its returning a error saying its not supported. Basically I have a table for Locations where equipment has been and I need to run one query that returns only the last location that the equipment has been. The below query is returning any of the ID numbers where the current location = 232. This does return results but it also returns results where 232 is one of the previous locations. A simple fix would be to use LIMIT 1 in the subquery but since that isnt supported .....
View Replies !
Subquery As An Array?
Let us say I have two tables: - user: userid, username - post: postid, userid In a single query, given the "userid" value, I want to select "username" from the user table, and select all postid values from the post value where the userid="myuserid". Something like... SELECT username, (SELECT postid FROM post WHERE userid="myuserid") AS postids FROM user WHERE userid="myuserid" I'm sure you can spot the problem--the subquery returns more than one row of data. Is there any way I can make "postids" be an array, or a comma-separated list, or something like that?
View Replies !
|
TRACKER
