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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records. The query is quite simple SELECT COUNT(*) FROM TblName WHERE Something = 1 I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?
View Replies !
What Do I Need To Do To Make This Query Work?
I must've been working too long hours this week, as my flow of ideas has come to a complete stop. Its really quite simple: 2 tables - bookings , invoices These two counts gives me precisely what I want PHP $unpaid_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE invoice_id = 0 AND client_id = $id AND status != Ɔ' AND finish <= '$time'"),0);$total_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings WHERE client_id = $id AND status != Ɔ' AND finish <= '$time'"),0); so I present this in a ratio, $unpaid_bookings / $total_bookings... awesome now, when I try and flip the coin and show unpaid invoices over total invoices, it all falls apart once I have more than 1 booking per invoice. First i'll explain the table structure. Each booking has a column `invoice_id`, which is assigned to the primary `id` of the invoice table. So one invoice might be associated with x bookings. PHP $unpaid_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id AND i.is_paid = Ɔ'"),0);$total_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b WHERE i.id = b.invoice_id AND b.client_id = $id"),0); Again so it works fine when 1 invoice is associated to 1 booking, but when 1 invoice is associated with say 2 bookings, the invoice count will return two instead of one. The worst part is, I know exactly why (because there are two bookings for the invoice), I just can't get around it! I've tried all sorts of joins, group by's, and i'm not having any luck. I'm trying not to be weak and add the client_id into the invoice table,
View Replies !
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
View Replies !
Query To Make One Column Into Multiple Columns
The table is as such CREATE TABLE `hardware_table` ( `timestamp` int(11) NOT NULL default '0', `node_ref` int(11) NOT NULL default '0', `var_ref` int(11) NOT NULL default '0', `value` varchar(255) default NULL, `t_date` date NOT NULL, `t_time` time NOT NULL, KEY `timestamp` (`timestamp`), KEY `node_ref` (`node_ref`), KEY `var_ref` (`var_ref`), KEY `t_date` (`t_date`), KEY `t_time` (`t_time`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; node_ref is a link to the hostname var_ref is a link to the variable etc. I get a column for each varname instead of all of them under the varname column. Getting close with the following.....
View Replies !
How Do I Make Rows From Another Table Appear As Columns In Query?
I have a join query (which r937 kindly helped me out with) which joins a table of tracks with a table of albums in a many-to-many relationship. I also have a table called links with a column of URLs called links. There's also a column of link categories called linkTypes which is of type enum with three possibilities - artist, album and track. What I'd really like to do is to add three columns to the result set (if that's the correct term) of the join from above i.e. I'd like to add trackLinks, artistLinks, albumLinks. So I guess my question is: How can I make columns on the fly by using field values from another table? In answer to the first thquestion - why don't you just add the columns to those two tables? The answer is because in the future I will need an undetermined number of each of these columns, e.g. one artistLink column per affiliate - artistLinkItunesUS, artistLinkItunesUK etc. So is this something I can feasibly do without restructuring the database or should I use a couple of queries and try combine the data through PHP? Or perhaps I'm looking at the relationships incorrectly? Having to add columns like that does seem like a one-to-many relationship which I haven't taken care of.
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 !
Howto Insert Into The Middle Of A Table
I've been trying to insert at new row into a certian point into a table without look. what I'm looking for is actually the opposite of "SELECT something FROM mytable WHERE id=some_number" .... I wanna do it the other way around so I can search a table for a certion number in the id-field and insert a new rowbeneath that... the id-numbers stay the same, no change is needed there.. any know how to do this? a short a method as possible as I'm trying to make script as fast as possible.
View Replies !
Howto Display Timestamp Like This 20081223015612
I use mysql client C api get data from mysql server. I want the get the timestamp data like this 20081223015612, not like this 2008-12-23 01:56:12. When I use mysql 4.0.12, the server will give me timestamp like 20081223015612, and my client (C program) deal this data. But now mysql 5.0 will give me timestamp like 2008-12-23 01:56:12, and my client will display timestamp wrong. Did I modify mysql 5.0 setting to let server give me timestamp just like mysql 4.0.12?
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 !
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 !
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 On Result Set
I have a result set from a query that was joined. I need the sum of one of the columns. What is the best way, have results go into a mysqlorary table? Have a sub query?
View Replies !
First Query Result Used For Second?
Is this possible / Practical? Function1 I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB. Function2 takes valueA & valueB and queries the database again using these values within the query. Im still learning mysql so the code is a real mess and not working. Could someone give a simplified example of how this could / should be done?
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 !
Output Query Result
Can I output a mysql query result direct to an Access database? I know (probably) I could use outfile to csv and read that through ODBC but then I've got the problem of not being able to overwrite it. I'm thinking that if I can output direct into an mdb it would get around that limitation.
View Replies !
Query Result Percentage
how I might be able to get each record's query match percentage? What I mean is, say your query contains multiple criteria, of which, a variable number of fields per row will match. You see this often with dating services, e.g. name, height, weight, hobby, etc. Not all fields will match so a percentage is provided, then you can sort by percentage. Is there a mySql function that can do this or must this be done the tedious way - like using PHP to test each value against each field's data per row, then updating, then sort
View Replies !
Strange Query Result
I have a column (tinyint) that has either 1s or 0s in it. When I run this query counting either 1s or 0s it counts all of the rows, not just the ones with 1s or 0s as stated in the query: SELECT COUNT( cb_paiddues = '0' ) from jos_comprofiler or Code: SELECT COUNT( cb_paiddues = '1' ) from jos_comprofile
View Replies !
How To Repeat A Result Set From A Query?
I need to repeat any times a result from an query, like this: select name, price, code from `acme`.`table` results: chave de fenda 1pol 10.50 048929 chave de fenda 2pol 10.90 048927 Instead of this, I need 7 times the first product and 5 times the second one, like that: chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 1pol 10.50 048929 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 chave de fenda 2pol 10.90 048927 Any idea?
View Replies !
Unexpected Query Result Using IN()
SELECT post_id, post_user, post_subject, post_category, post_date FROM sb_blogposts WHERE '10' IN(post_category) The above query is only returning results if the 10 is the first ( or only ) category number in the post_category field. post_category has been defined as a varchar(255) and contains a listing of categories that each post belongs in. All of the posts in this database have their categories set properly, and some posts are in more than one category. The query example should be returning with every post that has "10" in the list. but for some reason if the category is less than 10, it won't show. It's a bit strange and I don't understand why it's doing that.
View Replies !
Query In Storing The Result Set
SELECT ProductId FROM product WHERE prod_category_id IN (1,2) INTO OUTFILE '/usr/local/mysql/data/test/test.xml'; But what is my doubt is? I want to save the output of the file in the remote server, is that possible? http://mysql-api.blogspot.com/
View Replies !
Echo A Query Result
If I have a query: $sql= mysql_query("SELECT a.id, b.id FROM table1 a, table2 b WHERE a.id=b.id"); $get= mysql_fetch_query($sql); Normally, if the query is selecting just from one table, then I just use: echo $get[id] But how do you output both a's id and b's id? I tried echo $get[a.id] but it doesnt work.
View Replies !
Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with my results.....
View Replies !
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
View Replies !
Copy The Result Of A Query Into Another Table.
I am trying to make a select with a sum in it and group by account and copy the results in another table. As the site has the 4.0.25 MySQL version i guess i cant make subconsults. I thought of storying the results in vectors, but up to now it didnt work ...
View Replies !
Create Table With Result Of A Query?
Is there a way to create a table with the results of a query? i.e. I want to create a table of the with the users who registered on my site on January, Feb and so on ... All this records are stored in a single table: users, and there is a field called date_join, so i want to create a table with only the users who registered on january and name it users_jan for example ..
View Replies !
|
TRACKER
