Join/Select Only Most Recent Record
I have two tables. The first is a list of customers and the second is a list of sales invoices. I want to link the 2 tables but only select the newest/most recent invoice. There can be many invoices for a single customer.
Customer Table
CustomerID integer (primary key);
CustomerName char
Sales Table
SaleID integer (primary key)
CustomerID integer (key from customer table)
SalesDate Date
SalesAmount integer
Here is what I have so far:
select customertable.customername, salestable.salesdate, salesamount from customertable
inner join salestable on customertable.customerid = salestable.customerid
How can I return only a single record for each customer (the most recent/newest sale)?
View Complete Forum Thread with Replies
Related Forum Messages:
Join 2 Tables And Select Only Most Recent Records
I have been wrestling with a way to select only the most recent records for a group of clients without using a subselect. I have two tables: cdp_clients and cdp_level_records related by 'admitid'. For each cdp_client there are multiple cdp_level_records, each with a different 'level_date'. I want to select only the most recent cdp_level_record for each client in cdp_clients.
View Replies !
SELECT JOIN Where A Record Doesn't Exist
I have two tables. A company table and a table with a list of modules that links to the company table. I have had think about this but not come up with any sort of solutions. How to I select all companys where a company module record doesn't exist. i.e. Company1 module1 module2 Company2 module1 module2 Company3 module1 module2 Company4 module1 module3 So how would I select only Company4 where there is no module2. Please note there is many to one relationship between company and module, i.e many module to 1 company. If I use something like SELECT * FROM company, modulelist WHERE modulelist.module!=module2 it doesn't appear to work.
View Replies !
Getting The Most Recent Record
Given a table that has the following fields: create table WAGE_RATES( EMPLOYEE_ID int unsigned references EMPLOYEES, DATE date, HOURLY float(5,2), WEEKLY float(5,2), OTHER float(5,2), index(DATE)
View Replies !
Recent Record
Basically what i want to do is create an SQL string that searches the table for a specific userID, THEN finds the most recent record of the userID (according to my datetime field which is called transdate). Im Using ASP to connect to a MySQL database. i've looked all over the net trying to find out how, also i dont really know which forum this should be in (either mysql or asp?).20
View Replies !
Trying To Retrieve Most Recent Record Per Date
I have the following query which is retrieving a set of data it is almost what I want but I can not manage to get the result I desire. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output WHERE output.id_modality = '1' AND output.id_linac='2' ORDER BY output.date DESC 'r1','r1_dev','r2','r2_dev','date','time' '37.500','0.334449476181','[NULL]','[NULL]','2006-07-27','15:00:00' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '40.812','0.8538683','0.000','0.0000000000','2006-05-12','08:50:00' '41.580','2.7517359','40.370','-0.2383940000','2006-05-12','08:45:00' '40.756','0.8155671','0.000','0.0000000000','2006-05-12','15:00:00' '37.952','-0.472445','0.000','0.0000000000','2006-05-12','14:53:18' '38.010','-0.3203424','0.000','0.0000000000','2006-05-12','14:52:33' '39.488','3.3474615','37.916','-0.7667557000','2006-05-12','08:35:43' '39.650','3.7714458','38.020','-0.4945683000','2006-05-12','08:22:38' '40.330','-0.2382025','0.000','0.0000000000','2006-05-12','15:00:00' '41.330','2.2354728','0.000','0.0000000000','2006-05-05','00:00:00' '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00:00:00' '40.696','0.8814325','0.000','0.0000000000','2006-04-28','15:56:00' '41.000','1.635019','0.000','0.0000000000','2006-04-28','15:55:00' '38.380','0.8642144','38.400','0.9167752000','2006 -04-28','15:56:10' '37.970','-0.2132824','38.510','1.2058597000','2006-04-28','15:49:23' From the set above I would like to retrieve the latest measurement per date but if I try to group by date (like below) I always get the first result instead of the latest result. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output WHERE output.id_modality = '1' AND output.id_linac='2' GROUP BY output.date ASC ORDER BY output.date DESC 'r1','r1_dev','r2','r2_dev','date','time' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '39.650','3.7714458','38.020','-0.4945683000','2006-05-12','08:22:38' '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00:00:00' '37.970','-0.2132824','38.510','1.2058597000','2006-04-28','15:49:23'
View Replies !
Return Most Recent Record For A Group Of IDs
How do I return only the 1 most recent record from one table given a list of customer IDs from another? We're unfortunately running just MySQL 4.0 Pseudo code: show a list of distinct customer IDs for those customers that have a currentbalance of > $0, who had an order marked canceled in the last 7 days. Given: orders - orders.customerID - orders.status - orders.updated (datetime) billing - billing.customerID - billing.currentbalance - billing.updated (datetime)
View Replies !
Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
Left Join To Get The 1 Most Recent
Firstly table structure - 1 user has multiple status records. When a new status record is created it gets the current datetime inserted into 'date_created'. user user_id site_id status status notes date_created I wish to display a list of all users and show beside them their current/most recent status record. Here is my code currently: SELECT user.*, status.date_created, status.status, status.notes, DATE_FORMAT(status.date_created,'%l:%i %p') as last_updated_time, DATE_FORMAT(status.date_created,'%e/%c') as last_updated_date FROM user LEFT JOIN status ON status.user_id = user.user_id WHERE user.site_id = 1 GROUP BY user.user_id ORDER BY CASE user.user_id WHEN 8 THEN 0 ELSE user.user_id END Currently via the LEFT JOIN and GROUP this does display a list of all users with 1 status record per user but it's simply the first status record in the table (ordering by id i guess), not the most recent. How can I get it to display the most recent instead, based on date_created. I've tried adding ordering to the left join but this returned an error.
View Replies !
Group/Join - Wish To View Most Recent...
I've been trying to sort this out for over 2 hours and I can't get it to work! What I want is for the code at the base of this post to list each of the forums with the most recent topic details. My problem is that it doesn't choose the most recent post, it chooses the first. I've tried: - changing 'forums_topics.topic_time' to 'MAX(forums_topics.topic_time)' [returns the MAX value, but not the corresponding topic_id/title - it still returns the first topic_id/title!] - adding 'forums_topics.topic_time DESC' to 'ORDER BY' [no effect] - adding 'forums_topics.topic_time' to 'GROUP BY' [this lists them successfully, and with this the ORDER BY has an effect, but I don't want to list all topics, just the most recent] - adding subqueries (couldn't get to work) - adding 'HAVING topic_time = MAX(forums_topics.topic_time)' after 'GROUP BY forums_forums.forum_id' [which returns only the forums with a single post] .....
View Replies !
Select Most Recent From A Group
Hello I am having troubles with a query I am trying to make. I have a table to keep notes on customers note_id - primary biz_id date_of_interest notes I want to grab the biz_id and notes of every record that has a date_of_interest before or on today's date. The problem I am having is if one customer has multiple notes such as: 3, 1233, 2006-05-02, note 8, 1233, 2006-06-28, note 13, 1233, 200-07-24, note he will get selected from my query since two of his previous notes have a date_of_interest before today but I really only want to care about the most recent of notes so he shouldn't get selected. Is there a way to do this in the query or should I add another field to the database to mark it as the most recent.
View Replies !
Select, Order By. Most Recent One Last
I have one more question which is best explained by example. My database table contains a (very large) table with books and for simplicity sake lets assume every record only contains 2 fields: Title and AuthorID ....
View Replies !
SELECT Categories, SORT BY Most Recent Entry In Said Category...
My blog has multiple categories in one table, and each category has multiple associated blog entries in another table. Long story short, I want to SELECT (and display) each category title, but sort the categories by the most recent article posted to that category. The query below works, but I think it could be optimized: .....
View Replies !
JOIN -> Limiting One Record Per User
I have a database table called 'users' and an associated table called 'profile_pics' that has records for uploaded pictures for any associated user that chooses to upload one. The thing is, one user can have many profile pictures, but on this particular page I am making, I want only the most recent profile picture to display for each user. So I want my select query to retrieve all profile pictures from all members who have a profile picture(s), but only display one profile picture for each member (eventhough they may have more than one). How can I go about doing that in my SELECT statement? Any ideas how I can work this out?
View Replies !
Select Last Record
I have this in my query: SELECT flash_data.flash_data, h_package_data.hpackage_data FROM flash_data, h_package_data What i want to do is add the values in two tables together? There are alos a load of other tables but i have only used two here. I want to be able to select the last entered record of each table, then add them together. I know i could just put a "+" but this will not work with all the rest of the tables and data i need to use.
View Replies !
SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity. Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ? How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?
View Replies !
SELECT Where There's No Record In Table
[products] ID name [users] ID fname lname [products_users_lookup] uID pID Ok, I need the following: SELECT all products FROM these 3 tables WHERE there's no record in the lookup table. So I need all products that are not attached to the user account.
View Replies !
Want To Select Unique Record Only?
i have a field and this field has many records. but some record have duplicates. now how can i select the uniqe record name only in the field? for example, user_name ----------- john john joey joe gina karen warren karen george warren i want to select them all but not including the duplicate so if i want to select the the name only not including the dupliate then the result is user_name ----------- john joey joe gina karen warren george
View Replies !
Position Of A Record In A Given SELECT Statement
I want to make an iteration (on www) through some dataset (let's say - address book). One record maight be: - third out of 1200 from city MyCity - 134th out of 102000 from district MyDistrict - etc... Any ideas how to get position in any of those subsets in some elegant way? Till now I was doing SELECT someID FROM address WHERE myCondition(ie. city=MyCity) ORDER BY neededOrder and then iterated one by one through result searching for my ID and therefore knew it was ie 10th out of 120. Everyone will certainly agree that it's not elegant and far from optimized.
View Replies !
Is SELECT Speed Dependent On Record Size?
My current Mysql database has a table with a Text field and a few blob fields. The average record can range from 500kb to 5mg, the bulk coming from the blob fields. I select from each record based on words in the Text field, but after about 3000 records, the search takes too long. If I setup another table that has just the text field and shares a key with a table containing the blobs, will it be quicker to search the Text field? In other words, does having larger records slow down my search in the Text field?
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 !
How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each........
View Replies !
(select Where) Join OR (select Join) Join
which one is better, (select where) join OR (select join) join ?! I can join two table with select and where, also i can do the same with join keyword. The result is same but which one is better? I know that joining with join keyword is better for explicit code but what about performance?
View Replies !
Select, Join
table 1: ID, name, etc. table 2: ID, other info table 3: ID, more info all indexed on ID Relative beginner trying to find the easiest way to select all records from table 1 where where ID is NOT found in table 2 AND ID is NOT found in table 3. Possible to do with one select?
View Replies !
Select With Join
I have two table that make up the posts in a forum. the Post table has the main post data (topic_id, post_id, title, post, date/time) then the post_reply data (post_id, reply_id, title, post, date/time) so that each post may or may not have a post_reply or may have multiple replies. Each topic is ordered by the date and time of the most recent post to keep active topics on top of the list. Right now I'm keeping a "last post" date/time stamp int he post record to order the list. What I'd like to do is use the most recent post_reply data for that post to order the list so I won't have to keep the post field updated and it will be more accurate. I can't figure out the select statement to get one record for each post in order by the post_reply date/time field, or if the post has no replies (sub-select returns null) to use the date/time field from the post record itself. Is there a way to do this? Or is keeping a last post field in the post table itself still the best way?
View Replies !
SELECT JOIN
I have two tables, articles and series. Not all articles are part of a serie, but some are. I want to have a query that displays all the articles, with latest on top, no matter if they are a serie or not, but here comes the problem. From a series I only want to display the oldest article that is openSome articles are closed and then they should no longer be displayed as part of that serie. Although the listing as a whole is date DESC I still want the selection of the one article matching the entry in the series table to be date ASC. Is there any way to do what I want to do? I thought about doing a UNION between two SELECT queries, one for only-articles and one for articles that are part of a serie. The problem is with the one for series. How do I do it?
View Replies !
SELECT / JOIN
I have two tables one with 100k rows and the other with 40k. All rows in the 40k exist in the 100k and can be joined on a single column. I want to find all the 40k so I can perform some action on them. I have tried using simple JOIN's and LEFT JOIN's but I either get no records returned or the server seems to disappear in to oblivion and never comes back (phpMyAdmin). The basic syntax I am using to proove the code and simply find the data is: SELECT t1.id, t2.id FROM t1, t2 WHERE t1.id = t2.id
View Replies !
Select MAX Within Join
SELECT tblPageItems.id, tblPageItems.item_name, tblPageItems.item_category, tblItemVersions.html FROM tblPageItems INNER JOIN tblItemVersions ON tblItemVersions.page_item_id = tblPageItems.id If there's more than one entry in tblItemVersions with a particular Item ID - as there will be, this is for a CMS with version control - then the query above will return a row for each version of each item found. I don't want that. I just want a row for each Item ID, containing the latest (ie: highest ID) corresponding html field from tblItemVersions. And I'm having a mental block, and can't figure it out.
View Replies !
SELECT And JOIN
I have two tables: a group table with columns id and name, and a profile table with columns uid and gid. I want a query to list all the groups which a user is not a member of. I thought something like SELECT * FROM group, profile WHERE uid = 123 AND id <> gid might work, but it doesn't.
View Replies !
JOIN And SELECT ?
My question is about joining 2 tables and getting values where repeating values are ignored First table is: imgimages and the second table is : imgcats I need to join these tables such that they are sorted by decreasing "cid" value. and I need to select the "thumb" values in imgimages table. BUT, since there are repeating values of cid in the imgimages table, i cannot get 5 different thumb values from 5 different cids. If you look at the image, you if you call for cid it wil give you values of 32,31,31,31,31. But I need the values like 32,31,30,29,28. Its really hard to explain it in words. Here is an image of what I would like to have: This is the code I wrote, but I cannot make it to act the way I want: // Get 5 recent albums $recentalbums = '' $query = $db->query("SELECT a.views, a.cid, a.name FROM $table_imgcats a WHERE $restrict a.cid = a.cid ORDER BY a.cid DESC LIMIT 0,5"); $image = $db->query("SELECT i.iid FROM $table_imgthumbnails i WHERE $restrict i.cid = i.cid ORDER BY i.cid DESC LIMIT 5"); while ($views = $db->fetch_array($query)) { $url = $db->fetch_array($image); $views_albums = stripslashes(censor($views['name'])); $recentalbums .= '<a href="gallery_view_album.php?cid='.$views['cid'].'"><img src="gallery.php?action=viewthumb&iid='.$url['iid'].'" alt="'.$views_albums.'" title="'.$views_albums.'" border="0" '.$resize.' /><br />'.$views_albums.'</a><br />' } $db->free_result($query); i need $image to change
View Replies !
Select Where Join Is NOT
Basically I need to get a list of tags out of a database, but only the ones that do not match another table, which has manufacturers names in it. A rough table outline would be: tags tag (the actual tag) qty (the number of times this tag has been used) manufacturers id (internal, auto-incrementing value) name (the actual manufacturers name)
View Replies !
HOW TO JOIN Value From HASHTABLE To SELECT
HOW TO JOIN value from hashtable/arraylist to SELECT? eq: hashtable has the following value: 3 4 7 mysql database table has the following value: ID: 1 2 3 4 5 6 7 RESULT is: 1 2 5 6 How do we issue a command for that? SELECT ID FROM XTABLE INNER JOIN ? Whats next?
View Replies !
SELECT / JOIN Two Tables
I've seen several ways to make a "join" of 2 tables, and I don't know which is best practice. I only have some small tables (at the moment) to use it on, so I can't tell by the speed ... but maybe I could programm it better... First method, which I normally use: SELECT * FROM tbl1 JOIN tbl2 on tbl1.ID = tbl2.ID Another way I've seen: SELECT * FROM tbl1, tbl2 WHERE tbl1.ID = tb2.ID What is the best, or is this depending on the purpose ?
View Replies !
Select Varchar Vs Join
(i'm considering minimizing the normalization, but i'm not sure how this will affect performance.) if i make a varchar column an index, how much slower would that be than an integer column?
View Replies !
Problems With SELECT And JOIN From 2 Dbs
I have 2 databases on the same server, each under a different username. I used to be able to (using php) select, join, etc. between the two without a problem. Everything was sunshine and apple pie. Now, all of a sudden I'm having a lot of problems doing this. For instance, I used to be able to: create db1.temporary table insert into db1.temporary select from db2.external table Now I have to: create db2.temporary table insert into db2.temporary select from db2.external table Of course, this isn't working for my design. I have no idea where to look to see what the trouble is. Persistent connections maybe? phpsuexec? I dunno... Any pointers or thoughts on how to even try to troubleshoot and see where I may be having a problem?
View Replies !
Many-to-many Left Join Select
I am making a document management system and have many-to-many relationships that I am storing with a 3rd table. so imagine each doc can be in more than one category: useful_files: ---------- file_id file_desc useful_files_category -------- cat_id cat_name file_cats ------- file_id cat_id I can search for docs by category by using this query: ....
View Replies !
SELECT Command - JOIN?
I have two tables, 'devices' and 'lads', that look like this: devices mysql> select * from `devices` where `team` = 'both' or `team` = 'sml'; ....
View Replies !
Select, Join, Order Assistance
I have 2 tables similar to T1 ------------------------------------------- ID | initial | T2 ------------------------------------------ initial | full_name | I want output based on the T1.ID row which has a given ID=' X ' (If ID = 7 then pretend initial = initial3, as example) The first or last row of output to be initial | full_name expanding the T1.initial from T2 where T1.ID=' X ' then all subsequent rows from T2 In a rough sense would need to look like T1.initial3 | T2.full_name3 where ID=' 7 ' T2.initial2 | T2.full_name2 T2.initial1 | T2.full_name1 T2.initial4 | T2.full_name4
View Replies !
Select With Join Query Question
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status !=3D 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = =3D s.job_id) where s.status_id !=3D 1 group by j.job_id Any help is greatly appreciated.
View Replies !
Select And Join For Multiple Tables
I have five tables in my database, there are actually NO common fields between them, not even a KEY or ID or anything like that, except for the "body" of a blob field. and that text is not identical, just a portion of that text is identical. each table has 5 fields, all different except the blob, which is called "message", so normally I use something like: select * from table1 where message like '%apple%'; to query this table, and the same goes for table 2, except the blob is different, table 2 normally is like this: select * from table2 where message like '%customerid=453%'; It's impossible to change the data in these fields (which would be the best option), but there is one common element between them in the message blob. What I want to do is something like this: select * from table1, table2 where message like '%order=100%'; however only one table will have that order, either table1 or table2, but never both, and theres no way to tell which of the tables will actually have the text. In other words, I want to search a bunch of tables for common text without having to actually submit the query five times, because the list of elements to search is about 25,000 items... I'd rather submit 25,000 queries than 125,000 queries.
View Replies !
Another Easy(for You, Hard For Me) Select/join
Table 1 indexed on ID. Table 2 is a chat log. Colums: From, To I'd like to find out who is NOT chatting. So, I'd like to select all ID in Table1 that are NOT found in either column From or To in Table2 So far I'm using two selects, but this doesn't seem right.
View Replies !
SELECT From Two Tables + LEFT JOIN
Since MySQL v5 was released our script stoped working due to mysql query errors. i.e: SELECT a.id FROM table1 a, table2 b LEFT JOIN table3 c ON (c.id = a.id) This will return error: Unknown column 'a.id' in 'on clause' To solve this we can do in two ways: 1) change tables order in FROM statement: SELECT a.id, b.id, c.id FROM table1 b, table2 a LEFT JOIN table3 c ON (c.id = a.id) Or 2) use brackets in FROM statement: SELECT a.id, b.id, c.id FROM (table1 a, table2 b) LEFT JOIN table3 c ON (c.id = a.id) Both ways works, but I have question to the experts: which way is the most optimized/better?
View Replies !
Select Unique Records From A Join?
I have two tables, both tables contain a matching JobID. Some records in the job table are not present in the joblocation table. I need to Select only those records. I thought I could just do a left join on the two tables with a not equal to where clause? SELECT job.JobLocation1, job.City, job.Province, job.SiteName1, job.Code, job.JobID, joblocation.JobID FROM soileng.joblocation joblocation LEFT OUTER JOIN soileng.job job ON joblocation.JobID = job.JobID WHERE job.JobID != joblocation.JobID limit 100 Any ideas on how to get this to work?
View Replies !
|
TRACKER
