Hi, I am trying to pull multiple columns from 3 joined tables, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key in the data entry table). The issue i have is that there could be two data entries, numbered 1 and 2, belonging to a pet - the query has to just pick the data entry...
Hi, the first part of this problem was solved by some good help on here yesterday, but I've been struggling today to complete the query I need. I am trying to pull multiple columns from 5 joined tables based on some conditions, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key...
I have a products table, and a pictures table as such: Products id name model Pictures id pid url order The picture for each pid with the highest order is the cover picture. There can be more than 1 picture per product. I want to join this result when I query the products, so it would show these r...
I'm sure this is a common request but I wouldn't know how to ask for it formally. I encountered this a long time ago when I was in the Army. A soldier has multiple physical fitness tests but the primary test that counts in the most recent. The soldier also has multiple marksmanship qualifications but only the most recent qualification...
Hi, I'm doing some refactoring work on PHP/MySQL code and stumbled across an interesting problem, and as of right now, I can't find a proper solution for it. I have this table: CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, userID INT UNSIGNED NOT NULL, timestamp INT UNSIGNED NOT N...
I am writing a small tool for managing the people registered to an association. I have a table USERS and a table CARDS. Each user can have more than one card (because cards expire). With one query I would like to extract ALL user information plus ALL card information only for the most recent card (field: ISSUEDATE). Here is a very bas...
I have a correlated subquery that will return a list of quantities, but I need the highest quantity, and only the highest. So I tried to introduce an order by and a LIMIT of 1 to achieve this, but MySQL throws an error stating it doesn't yet support limits in subqueries. Any thoughts on how to work around this? SELECT Product.Name, Prod...
I am having so much domain name in my DB, for example www.yahoo.com/duniya.html www.yahoo.com/hero.html www.123musiq.com/ www.123musiq.com/home.html www.123musiq.com/horo.html www.123musiq.com/yuva.html www.sensongs.com/ www.sensongs.com/hindi.html www.sensongs.com/yuva.html www.sensongs.com/duniya.html www.sensongs.pk/duniya1.html i ...
SELECT id,region FROM ads RIGHT JOIN (SELECT * FROM ads a2 WHERE a2.region=ads.region LIMIT 4) AS regions ON regions.id=ads.id Says "unknown column ads.region". How to access main table column region? I need to fetch 4 rows for each region ...
SELECT student_id FROM `students` AS s1 WHERE student_id IN (SELECT s2.student_id FROM `students` AS s2 WHERE s1.year_of_birth = s2.year_of_birth LIMIT 10) Can't process this query on my server. It drops errors, that says that this version of mysql doesn't support limit inside subqueries etc(ERROR 1235). Is there any solu...
I am trying to retrieve a list of properties and to do this I have to tie together 4 tables. The tables are: property, property_type, state, and photo. The problem is getting ONLY the default image for a property. The photo with the lowest rank number should be the default. So if there are 2 photos for property 10, than the one with t...
Hi, I have a table (Counts) with many entries like this: ID | userid | count | entrydate where ID is an autoinc PK, userid refers to my Users table, count is a random integer, and entrydate is a unix timestamp. I also have a table called Listmembers: listid | userid where both fields together are PK, and listid refers to some lis...
How can I get a List all the JobPositionNames having the lowest jobPositionId when ContactId = 1 Tablel : | JobPositionId | JobPositionName | JobDescriptionId | JobCategoryId | ContactId --------------------------------------------------------------------------------- 1 | Audio Cables | 1 | 1 ...
Hello! I am trying to develop a SQL Server 2005 query but I'm being unsuccessful at the moment. I trying every different approach that I know, like derived tables, sub-queries, CTE's, etc, but I couldn't solve the problem. I won't post the queries I tried here because they involve many other columns and tables, but I will try to explain ...
I'm trying to figure out how to query my database so that it will essentially first ORDER my results and then GROUP them... This question seems to be slightly common and I have found examples but I still don't quite grasp the 'how' to do this and use the examples in my own situation.... So all help is definitely appreciated. Here are my...
I have my data split across two tables. One is the entity table where it describes the basics of the entity, and the revision table where the data of an entity resides. If I know the entity I can query for the most recent version: SELECT * FROM `entities` JOIN (SELECT * FROM `revisions` WHERE `entity_fk` = ? ...
Hi - I have a pretty simple table in SQLite, with the following schema: CREATE TABLE IF NOT EXISTS Palettes (id INTEGER PRIMARY KEY AUTOINCREMENT, class TEXT, count INTEGER, name TEXT); These represent color palettes, and several palettes can have the same name, but different counts (i.e. sizes). What I wa...
I have a query: SELECT reply.id, reply.message, reply.userid, reply.date, medal.id, medal.url, medal.name, user.id, user.name AS username FROM posts AS reply LEFT JOIN users AS user ON reply.userid = user.id LEFT JOIN medals AS medal ON medal.userid ...
I have a mysql table with entries like this: id name value date 1 results1 1000000 2010-06-02 01:31:12 2 results2 600000 2010-09-03 05:42:54 1 results1 1200000 2010-09-06 02:14:36 How can I SELECT all and filter multiple rows that have the same id, selecting only the one with latest date? The "date" column d...
Hello, I Have a table with the following columns: patient_id, visit_id, and visit_date. How can I write a query to get the max(visit_id) on the most recent visit date for each patient? (several visit_id could occur on the same date for the same patient) basically, I want to end up with NO duplicate patient ID's. Thanks. ...