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 Complete Forum Thread with Replies
Related Forum Messages:
How To Get Data Based On The Month And Year??
"select month(str_to_date(fmonth,'%M')) from company where id=2;" (Query 1) The fmonth in company has values like 'March,October etc,...' this would give result as 4 for april (fmonth values in company table). i have another table which has values based on the date.(Transaction table) i want to fetch the values from the transaction table based on the date which is greater than the month of selected value fetched by the first query and of the year of current year.... i tried the query , "select *from transaction where date_format(transdate,'%Y %m') >= date_format(curdate(),'%Y %m');" This query returned the data which has a transdate >= 2006-10 i know that i have to combine the two query,but couldn't get those things.
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 !
Master List Of Select Rows
I am creating a database as part of a service record for items. When an item is serviced, an entry is added to the database with the item serial number and some other info. That all works great. I also want to create a master list of all items. If I use the old "SELECT * FROM database WHERE 1" query, it will display all the rows in the database. Is there any way to retrieve the last row for each item number, instead of displaying all rows for all items? It seems I have worked myself into a corner with this table setup. If it can't be accomplished the way I have it set up, is there a better way to structure the table? I thought about creating another field "type" and have it be either "parent" or "child" and then only display the "parent" rows in the master list. That would involve two queries when updating an item's status (adding a new row and updating the "parent" row for that item), and seems to be less convenient. This seems like a common application so there has to be another way.
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 !
List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme: TABLE Prerequisites PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column) UpgradeID (FK to Upgrades table as well) TABLE BuildingUpgrades BuildingID (FK to Buildings table) UpgradeID (FK to Upgrades table; states what Upgrade Building already has) So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U. What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way. The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me: SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID) FROM BuildingUpgrades BUPG JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID GROUP BY BUPG.BuildingID, BUPG.UpgradeID HAVING COUNT(PRE.PrerequisiteID) = ( SELECT COUNT(PrerequisiteID) FROM Prerequisites WHERE UpgradeID = PRE.UpgradeID GROUP BY UpgradeID ) What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.
View Replies !
Last Month Of Data
I am trying to limit my query to the last month of data. Below is the limiter that I have used. The query is accepted by the server, and returns data, but does not limit the data. Any suggestions as to how to correct this? where issueddate > date_sub(current_date,interval 1 month)
View Replies !
Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work. My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?
View Replies !
Backing Up A Circular Master-master Replication Setup
I have a running circular master - master replication running. I want to run a mysqldump on one of the servers but am worried what the impact of running a mysqldump will be on the replication. Will the transactions generated by the mysqldump get put into the binary log file? I see the option --delete-master-logs for mysqldump and am assuming that it deletes the previously inserted log items from the binary log file. If there is someone that can please just give me a little more clarification on how the --delete-master-logs option works.
View Replies !
Need To Find 1 Occurance In A Table
I have a database storing photos. I want to display the first picture from each album on one page. The table in question has, say, 10 pictures, all with the same "album" id. I want to pull the first picture from each album. Any ideas?
View Replies !
LOAD DATA INFILE :: Master & Slave
After recently replicating using the stop databases and copy innodb and mysql files method, things were fine...Until the slave thread stopped complaining about duplicate rows after one of our developers did a LOAD DATA INFILE operation on the master. Unfortunately the insert's are into a table using AUTO_INCREMENT as well and this may be compounding the problem.I have read the docs, but it's sort of unclear, does LOAD DATA INFILE work in a replicated 3.23.56-1 setup, only under certain circumstances, or not at all?
View Replies !
LOAD DATA :: Master & Slave Server
I was doing replication using MYISAM table types. When I set up my database server on the Slave, I used to LOAD DATA FROM MASTER to get the first snapshot of my running master. This worked great. Now we moved to using InnoDB table types to get rollback/commit features and also to enforce Foreign key checks. I read that current version of InnoDB does not support LOAD DATA FROM MASTER command. I would like to be able to setup my slave without taking down the server. Is there a way to do so without buying the InnoDBHotBackup.
View Replies !
Pulling All Data From Specific Month
I want to pull out all the data entries I've made for a specific month by using a $_GET method (archived.php?month=october). My field time rows look like "1161065143". How can I do a mysql_query for this? Here's what I have so far: mysql_query("SELECT * FROM entrynote WHERE time = '$date' SORTED BY time DESC
View Replies !
Birthday Query Per Month
I want to output all the birthday celebrants for every month. YYYY-MM-DD is the format is my birthday. Not just the present month birthday celebrant but, per month. I try to use Select * from tblSample where birthday between '' and ''; But no success for i don't have the start date and end date of every month.
View Replies !
Selecting First Occurance In A Given Period Of Record
I have a table to log sessions and user (connect_id) id's, start time etc - see below +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | connect_id | varchar(12) | | | | | | session_id | varchar(32) | | | | | | start_time | int(11) | | | 0 | | | client_ip | varchar(16) | | | | | +---------------+-------------+------+-----+---------+-------+ I need to create a report that will tell me, for instance, how many sessions were started today, that's easy I know but I also need another report that will tell me how many of those sessions were first-time visitors.
View Replies !
Getting Statistic From Detail Table Into Query On Master
I have two tables: Students, and Attendance_Dates. Students: Student_ID [PK] Last_Name First_Name Attendance_Dates: ID [PK] Student_ID Attendance_Date Attendance_Dates is a detail table containing all of the dates each student was in class. I want to write a SELECT statement that gives me a list of ALL students by Last_Name, First_Name PLUS an additional column that contains the LAST date they were in class. I tried this: SELECT Last_Name, First_Name, MAX(SELECT Attendance_Date FROM Attendance_Dates WHERE Students.Student_ID = Attendance_Dates.Student_ID) AS Latest_Date; Didn't work. Synatax error.
View Replies !
Mysql Is Giving The Previous Month, Not This Month, Strange!
PHP Code: $myquery = @mysql_query("select year(sendon) as yr, month(sendon) as mth, count(*) as hits from ebook_user group by yr, mth"); while ($myrow = mysql_fetch_array($myquery)) { $mymonth = $myrow['mth']; $myyear = $myrow['yr']; $myhits = $myrow['hits']; print '<td bgcolor="#E7E7E5">'.date("M", mktime(0, 0, 0, $mymonth, 0, $myyear)).' '.$myyear.' </td><td bgcolor="#E7E7E5"> '.$myhits.'</td>' } The above code is printing the previous month instead of current month ... can any one help?
View Replies !
Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that? SELECT date FROM clicks WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]' AND link_id = '{$row['ad_linkid']}'
View Replies !
Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month. For example Employee--Start Date--End Date Charles-----2008-6-3---2009-2-1 Vicky-------2009-1-1---Present So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
View Replies !
Master / Master Asynchronous Replication
Currently we have a web based application that is mostly reads (4:1 r/w). It is using a single MySQL database server. Is there any way to have two database servers in a master/master configuration such that writes to either database server are replicated to eachother. Basically even though we have a 4:1 ration of read/write the writes happen often enought that when the database goes down the app stops working. I know how to get this working in Oracle (insert big laugh here) but Oracle is cost prohibitive. Any pointers?
View Replies !
Master/Master Replication Problems
I was wondering whether someone could help me. I have inherited set of replicated servers from a guy I used to work with, basic set up is: Master1/Slave2 - Server ID 1 Master2/Slave1 - Server ID 2 When I looked at the logs it looks as though Server ID 2 is well out of step. Having looked at the slave status it appears that that Slave_SQL_Running state is set to No and a particular piece of SQL is causing the problem. Having ran the SQL on the particular server it has now problem at all. Now my understanding of this type of set up is that Server ID 1 writes directly to Server ID 2, then Server ID 1 is in turn replicated to. But for the life in me, I cannot understand why this piece of SQL is causing the problem. Can anyone offer me any ideas on how I can best get to the bottom of this, I'm really struggling with this. There was me thinking replication just worked! Is there an ability to get MySQL to log all queries it is running, so I can get to the bottom of all queries currently being executed?
View Replies !
Retreaving Data Based
I have tables called room_status and room_info room_status= room_id : date : status room_info= room_id : type : rate What I want to do is only make an entry in room_status when a room is booked, as it is obviously available otherwise(duh) but I don't know how to format a SELECT statement to bring back rooms available on a specific date by only returning results from the room_information table which do not have an entry in the room_status table for a specific date, which is the case if I don't formally record that a room is available.
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 !
Month By Month Count Of Records
I need to generate record counts for each month in a year, just one year at a time, but a full year at a glance. I am thinking 12 hits to the DB is perhaps not the most idea way to do it, is there a way to rip this out in one go?
View Replies !
Collecting Month By Month Results
does anyone have thoughts on how to do queries that produce date bins? I do things like DATE_FORMAT( created_time , '%M %Y' ) AS date and then group by date thing is that when there is nothing in a particular month, no row is returned ... So a query like this: Code:
View Replies !
Schedule Data Extractions Based On Requirements
I'd like to SCHEDULE a task to run automatically once per week that would EXTRACT all records from PHPBB_USERS which have either been newly created that week or were edited by the client within the same period. The data would extract to a COMMA-DELIMITED file which would then be automatically emailed using the PHP mail command to an intended recipient so that she can MERGE the datafeed into a larger and more complex offline database. Not entirely sure how this would be done, but I'm sure CRON would somehow get involved with the task.
View Replies !
Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city. What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'. So is it possible to make those 2 query in one so I do not need to do a php loop: SELECT city, COUNT(city) FROM profile GROUP BY city; then UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city
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 !
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 !
How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image. The file with the new data is a .txt file, a sample of which looks like this: v_products_modelv_products_imageEOREOR 5361453614.jpgEOREOR 5361553615.jpgEOREOR 5372453724.jpgEOREOR The beginning part of the table looks like this: +----------------------------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------+---------------+------+-----+---------------------+----------------+ | products_id | int(11) | NO | PRI | NULL auto_increment | | products_type | int(11) | NO | | 1 | | | products_quantity | float | NO | | 0 | | | products_model | varchar(32) | YES | MUL | NULL | | | products_image | varchar(64) | YES | | NULL | | | products_price | decimal(15,4) | NO | | 0.0000 | | | products_virtual | tinyint(1) | NO | | 0 | | From what I've read over the past few days...I think I need to: (a) delete the EOREOR column from the text file; (b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.
View Replies !
Storing List Data
How would a set of related values be stored together in a MySQL db? For example, if I wanted to store a user's buddy list, then the way I can think of would be something like this: user | friend John | Jim John | Bob John | Amy Bob | Jim Bob | Amy So John's friendlist would include Jim, Bob, and Amy, and Bob's friendlist would include Jim and Amy. But it seems really inefficient to create a new row every time a new entry in the list is needed I want to be able to store values like this: user | buddylist John | Jim,Bob,Amy Bob | Jim,Amy What would be the most efficient way to do this?
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 !
Top List Query
Currently im trying to query a data which will only display the top rows. For example: to display top 10 rows from the recent entered data.
View Replies !
Inserting Data With A Partial List.
If I have an auto increment field in a table and I were to insert data into other rows that were in the table but left the auto incremented one out of the list of columns to insert would it cause an error?
View Replies !
Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that start with the letter "A", "B", "C" .... and so on. I am pretty poor at SQL so I am sure some brains out there can do better than I have here. What I have is working, I just want to make sure that it is optomized. So let's assume I have some query "$query" that I want to run and get an A..Z list based on column "$column". Let's further assume that '$query" produces the following results, and that $column is equal to "last_name". last_name --------------- Anderson Bitmore brown Bogus My AZlist query would look like this: select * from (SELECT count(alist.$column) as a from ($query) as alist where alist.$column like 'a%' or alist.$column like 'A%' ) as a_result, (SELECT count(blist.$column) as b from ($query) as blist where blist.$column like 'b%' or blist.$column like 'B%' ) as b_result, .... (SELECT count(zlist.$column) as z from ($query) as zlist where zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result; And this retuns the following result: a | b |...| z -------------- 1 | 3 |...| 0 Meaning that $query has 1 result where the first letter in $column is "A" or "a", 3 results where the first letter is "B" or "b" and 0 results where the first letter is "Z" or "z". What I am afraid of here is that "$query" is being executed 26 times (once for each letter of the alphabet) . Is there a way to refine this, or is MySQL (4.x and 5.x) smart enough to optomize this on its own?
View Replies !
Top 10 List Query Question?
I need a little help with my top 10 best rated query. Right now I have: $sql = "SELECT * FROM tblPix, tblVoting WHERE tblVoting.picID = tblPix.picID ORDER BY picRating DESC LIMIT 10"; This one works but not as I want to. I want to take into account the num of votes. The tblVoting has a field called numOfVotes. So a rating of 5 with 10 votes will be above a rating of 5 with 6 votes.
View Replies !
Query To Get List Of Cocktails Given Ingredients
I'm trying to write a query to get a list of drink recipes which have one or more of the selected ingredients.... so, in a search form I want to be able to choose 1 or more ingredients and have sql return all drinks with that combination of ingredients. I have 3 tables (only relevant fields shown here): tDrinks -drinkid -drinkname -drinkdesc tIngredients (not needed in the query but used elsewhere) -ingredientid -ingredientname tDrinkIngredients -drinkid -ingredientid -qty so, I can get a list of drinks given one ingredient: Expand|Select|Wrap|Line Numbers
View Replies !
Getting Rows Multiple By List Query
I have a list of values (constants) that I use to find matching rows in a single table as follows: "select id, name from articles where id in(1,2,3,2,3,2,2)" seems very simple, but my problem is, the query delivers only 3 lines, of course, because the last four numbers are redundant. But my need is, to get a row for each number in the list, wheather it is doubled or not.
View Replies !
|
TRACKER
