Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.
Maybe a better explanation is given by example. Think of having a table of employees, with one of the columns being their bosses email address. Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)
I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Selecting Records Younger Than 24 Hours Using SQL Query?
I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like: SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY) for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.
Help With Selecting Unique Values
I have a table called 'pictures', this holds information for every picture in an art exhibition. Its structure is (catno, title, artistfirstname, artistsurname, size, medium,price,dimensions) sample data would be something like. 1, birds, James, Quinn, 23*23, watercolour, 355, unsold 2, snake, David, Blake, 43*43, pencil, 499, sold 3, lion, David, Blake, 25*53, pencil, 250, unsold 4, tiger, Adam, Healy, 63*73, watercolour, 235, sold 5, fish, John, Smith, 12*15, charcoal, 450, sold 6, eagle, Peter, Blake, 54*54 pencil, 950, unsold I need a page that lists all the exhibiting artists and was wanting to use the database to generate this rather than having to do it manually. Artists can have multiple pictures in the system. Also some artists will share surnames. I need a statement that pulls out all the unique artists, giving a result like: James Quinn David Blake Adam Healy John Smith Peter Blake
Selecting Unique Rows
I have a query that checks a list of users against a list of items and returns for example the following. SELECT users.username, ratings.recording_uid FROM users INNER JOIN ratings ON (users.uid = ratings.user_uid) WHERE ((users.username = 'a') OR (users.username = 'b')) which returns... username recording_uid a 1 b 1 a 2 b 2 b 3 a 4 b 4 b 5 How can I add to that query so that I know what user a has that user b doesn't and vice versa? eg. so that it returns username recording_uid a 3 b 5
Selecting Unique Values From A Table (NOT DISTINCT!)
Lets say we have a table that has the field photo_id and has values of it many times. For example 245 can exist any number of times (1, 5, 8, e.t.c.). I want to select the values that exist in the table ONLY ONCE. Please note that i dont want to select each value once (using DISTINCT), that would return all the values that exist in the table. For example lets say the table has the following values: 245 563 776 224 563 563 776 776 776 776 The query i want to make should return only the values 245 and 224 as 563 and 776 exist many times in that table.
Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email) Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact. I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column. I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates). I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?
Getting Unique Records
How do i get a list of ids from one table that do not have the same ids in another table?
Retrieving Unique Records
Hi the subject matter pretty much sums up what i need, a piece of sql code that queries 3 or more tables and selects only those records that have a date field that only occurs in one of the tables. So for example if one table has 20 records with a date of 12/12/04 and the other 2 tables do not have any records with a date of 12/12/04 then i only want to select those records with a date of 12/12/04.
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?
Generate A Unique Key For A Group Of Records
I am interacting with a database using jdbc and I have a particular storage need. I have data pairs I need to store with some association knowledge. Basically I get in a bulk of key/value pairs and I need to store them with a common 'group id' and other common info so I can retrieve them all at a later point. To do this, I have a table 'messages' with a bigint 'groupid' and 2 varchar 'key','value' columns. I also have a table 'groups' with a autoincrement 'groupid', a 'stamp' timestamp', and a varchar 'details'. How I envision (with little db knowledge) the flow to go is: 1. insert a new groups record, providing the new common info. 2. Get the autogenerated groupid. 3. store each piece of data in the messages table using the obtained groupid. My question: is there any way to easily get the generated groupid from step 1?
Selecting Top Records
I was posed a question in an interview that required me to select the top 3 records of table after doing a sort. I have been studying MySQL for the last 6 months or so and don't recall that capability in MySQL. I know it is available in Oracle and SQL Server. Is this possible in MySQL?
Selecting Many Different Records
I have table A_1 which has one of its fields called ID (a unique integer field). Now if I wanted to select let say the records with the ID matching these numbers 5, 7, 8, 9, 15, 22, 26, 29 how would I do this? I would imagine there is a better way to do this than using many AND statements to do this?
Selecting Known Records
Say I have a big list of IDs of the records I want to select. What is the best way to do this? 1. A select statement with multiple OR statements eg. SELECT * FROM table WHERE ID = 2 OR ID = 5 OR ID = 10 etc... 2. Multiple separate mysql queries each selecting an ID each. 3. A select statements like obove but all the queries sent in a single query using union. I dont know how important it is but with lots of queries a second it could make a difference. If there is other better way please tell.
SELECTing Records
I want to select records for several events in the future. Here's my problem - I need the next seven days that events occur on - but these days are not nessicarily consecutive. I had the idea to write a query like this: SELECT PROGRAMME.PROG_TITLE, COMPANY.CO_NAME, VENUE.VEN_CITY, PERFORMANCE.PERF_DATE_TIME, COUNT(DISTINCT DATE_FORMAT(PERFORMANCE.PERF_DATE_TIME,'%D-%m-%Y')) AS NUM_DAYS FROM PROGRAMME JOIN COMPANY ON COMPANY.CO_ID = PROGRAMME.CO_ID JOIN PERFORMANCE ON PERFORMANCE.PROG_ID = PROGRAMME.PROG_ID JOIN VENUE ON VENUE.VEN_ID = PERFORMANCE.VEN_ID WHERE NUM_DAYS <= 7 ORDER BY COMPANY.CO_NAME, PROG_DATE_TIME Only to realize that I can't use a column alias in the WHERE clause. I know that there must be an easy way to do this but I can't seem to figure out how.
Selecting All Records
How would I go about selecting all records in a table with a date field of "date" -- "0000-00-00", "yyyy-mm-dd" -- that are 3 days older then the current date?
Selecting Records
I have one table containing itmes each having its own id, and a another table containing some of these numbers in one of its columns.I need to to build a query that returns the itmes from the first tables whose numbers are not present in the second table. (SELECT t1.* FROM t1, t2 WHERE < the value in t1.item_id is nowhwere in t2.item_id >) How do I do that?
Selecting Records
This is a bit of a forlorn hope, but are there any timestamp values automatically associated with records eg can I select all records created after a certain date
Selecting Records
my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code. Code: SELECT DateOfStats FROM table1 WHERE (DateOfStats NOT IN (SELECT dateofstats FROM table2)) and (DateOfStats NOT IN (SELECT dateofstats FROM table3)
Selecting Non Matching Records
How do I go about selecting records from two tables that do not match? example: I have two tables (Table1 and Table2) linked by field1. Table1 will have records added right away where as Table2 will get its records written at a later date. Thus having it in some cases where Table2 will not have a matching record for Table1. What I want to do is generate a list of those items in Table1 that do not have a record written in Table2 as yet.
Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?
Selecting Records Only X Days Old
Using PERL and MySQL. I have the select statement working for all records, is it possible to select only records (in this case it will only return one) that is as old as say 30 days?
Selecting Date Records
What is the most efficient way to select records based on a field called NOW which contains a date or a datetimestamp I use: SELECT * FROM `TBL` WHERE DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( ); Is this the best way? TIA [color=blue] > Nicolaas[/color]
Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database: ID Channel Description 41 Africa News Latest news from africa 42 Technology News Latest Tech News 43 PC News Latest PC related news 44 Africa News Latest news from africa I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique. How can i return only one of the Africa entries in the results while still having the primary key value returned as well? Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?
Selecting Past 12 Weeks Records
Im trying to grab the past 12 weeks records in a table. here is my code: $query1 = "SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate"; if($result1 = mysql_query($query1)) { $amount = 0; while($row = mysql_fetch_array($result1)) { $compdate = $row['compdate']; $compdate = substr($row['compdate'],8,2); $compdate = ltrim ($compdate, "0"); $days_array[$compdate] = 0; } } else { echo mysql_errno().": ".mysql_error()."<br>".$query1; } And here is the error: 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 'WEEK) ORDER BY compdate' at line 1 SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate
Selecting Records Containing Multiple Values
I want to query a database for records with which the field 'network_letter' contains G,R,M, and Q and display those results back. Is there an easier way than doing the following: $query1 = mysql_query("SELECT * FROM global_pops WHERE network_letter='G'"); $query2 = mysql_query("SELECT * FROM global_pops WHERE network_letter='R'"); $query3 = mysql_query("SELECT * FROM global_pops WHERE network_letter='M'"); $query4 = mysql_query("SELECT * FROM global_pops WHERE network_letter='Q'"); and then merging those results into one variable and printing them?
Selecting The Correct Row In A Table Which Records Changes
I have an "employees" table which records which department each employee has worked in, and the date that each employee started working in their departments. Since it is possible for an employee to change departments, a single employee can have several records in this table. For example, consider the following history for Employee 42: 1. On Jan. 15, 2007, Employee 42 is hired to work in Department 21 2. On Feb. 1, 2007, Employee 42 changes departments to Department 5 3. On Feb. 14, 2007, Employee 42 changes departmets to Department 22 4. On Mar. 3, 2007, Employee 42 changes departments to Department 18 Then, Employee 42's records in the "employees" table would look like this: CODEselect * from employees where employee_id = 42;
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
Selecting All Records In A Parent/child Tree Structure
I have a tree menu and I am filtering it so that it shows only nodes in the tree that have products against them. I have two tables: 1. Tree table Name: 'shopCategory' Fields: cat_pk category_text parent_pk url 2. shop items Name: 'shopItems' many fields but key field of TDCategoryID links to cat_pk in the shopCategory table I have got as far as: SELECT * from shopCategory WHERE cat_pk in (Select TDCategoryID from shopItems); This gives me all the nodes that have items against it: +--------+--------------------------+-----------+------------------------------- | cat_pk | category_text | parent_pk | url +--------+--------------------------+-----------+------------------------------- | 89 | Food and drink | 1 | ***** | 93 | Vitamins and supplements | 89 | ***** | 94 | Wine | 89 | ***** | 100 | Other wine | 94 | ***** | 121 | Home and garden | 1 | ***** +--------+--------------------------+-----------+------------------------------- What I need to do now is follow the parent/child relationship and return all the records to the recordset until parent_pk is '0' i.e. the top of the tree. THe problem is the length of the tree varies so it may be folder|items or folder|folder|items is this possible?
Unique Query Help
I have two tables Table 1 has 3 unique URL (for e.g. hotmail.com) and Table 2 has 4 records of (hotmail.com)...The only way to display the combination for a particular URL is to use group by. Table 1 id url 1 hotmail.com 2 sitepoint.com 3 youtube.com Table 2 id userid (user who added) url notes 1 user1 hotmail.com good site 2. user2 hotmail.com email site 3. user3 hotmail.com great site 4. user4 hotmail.com cool site Now I want to JOIN table 1 and table 2 and display unique records for hotmail.com...Only way i can do is by using group by...I don't want to use that. And it doesn't really matter if it display record by any user, but as long as it is only one record for hotmail.com in Table 2 Can anyone please help me If I use inner join from Table 1 to table 2 ON URL, it still shows 4 records of that url, though i only want to show the first one if repeat record occur.
Unique Query
I don't know if this can be done but here goes I have a mysql table with the fields id, user_name, L1 and L2 the first query is Code: select L1 from tbl_name where id=some_value now I want it to check if L1 is NULL if it is not then check if L2 is null if it is not take the value of L1 as some value and redo the query and if both L1 and L2 are not NULL in this query go back to the original L2 and take that value as some_value and do the query and continue like this until a NULL value is found then do an update I am not very good at explaining so if this is not clear please ask
Unique/distinctrow Query ???
I have two tables each with two fields I want to retrieve: name and number. I only want to retrieve the rows from which a year=2007. This is my query: SELECT table1.name, table1.num, table2.name, table2.num FROM table1, table2 WHERE table1.year=2007 AND table2.year=2007 When I do this directly in MySQL it brings back the results: ANDREA 1 ALVIN 1 ANDREA 1 BARBARA 2 ANDREA 1 THREE-E 3 BARRY 2 ALVIN 1 BARRY 2 BARBARA 2 BARRY 2 THREE-E 3 where table1 is on the left and 2 is on the right. This data is only in the actual table once (in other words, for whatever reason it's returning each row twice. The problem is I'm trying to execute it in PHP and it only returns table 2's data. Is it something in my query that I am missing or do I need to focus on the PHP side? I've been studying the query altering it a little with DISTINCTROW and whatever else I can think of but can't quite get the desired results.
Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column. The reason is because I have a column that just contains just the YEAR of a given report. I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).
Query To Show Unique Entries
In the table word_list is a list of words. This field is not unique so you can get 3 of the same word (they have different attributes in other fields). How can I alter this query below so that I only get unique entries in the word field (so that the query does not return more than one of the same word).
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
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........
Selecting A Distinct Query
I want to: select name, address, city, state, zip from client; However I want to query a distinct address(some of the people work at the same address, and I only want one letter to go to each address.). Is it something like: select name, distinct[address], city, state, zip from client; I know this is not right, but does anyone know this? Thx.
SELECTing And UPDATEing The Same Table In One Query?
I have the following table (theres more to it, but this is all thats relevant): TABLE: Sequence +--------+-------------+ | Code | SequenceNum | +--------+-------------+ | 38L | 0 | +--------+-------------+ and what I need to do is increment the SequenceNum by 1 and update it in the table, *all* in SQL and all within *one* query...is this possible? For example, the logic of it would work something like this: UPDATE Sequence SET SequenceNum = (SELECT SequenceNum FROM Sequence WHERE Code = '38L') WHERE Code = '38L'; This, however, doesnt work, because the SELECT and the UPDATE are referencing the same table and MySQL doesnt seem to like that. It DOES work if I SELECT from one table and UPDATE another. For instance, if I duplicate the Sequence table and name it SequenceCopy: UPDATE SequenceCopy SET SequenceNum = (SELECT SequenceNum FROM Sequence WHERE Code = '38L') WHERE Code = '38L';
Deleting Records Upon Query... (!?!)
Sorry for the confusing thread title. Here's what I am trying to do... Read the last six added records for a particular criteria, and delete the rest. Now consider I have around 1000 unique "criteria" and this query is executed a few times a day for each unique... should I delete it on each read or put it in a cron job and schedule it for every night. Here's a sample MySQL syntax for my selectquery: SELECT * FROM table1 WHERE field1 = sometext ORDER BY some_time_field ASC LIMIT 0,6 So the questions, again, are:Can I join this SELECT query with a DELETE, and delete all field1s with "sometext" which were not selected by the query?If I can't, how would I do it with a seperate DELETE query? i.e. Leave the last six records for field1 = sometext and delete the rest?Do I really need to order by some_time_field? Or does MySQL have an internal schema to sort records by their "internal" creation date?
Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f). Now this works well, but what I want to do is say to my query is find all of these dates BUT - As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....
Count Records Within Query
I have the following information in table 'Test1': field1 field 2 000001 000000 000002 000001 000003 000000 000004 000000 I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id. output should look like field2 recno 000000 3 000001 1 can some one help with the trick to make this work?
Query Cannot Retrieves All The Records
I've got this problem, this query is used to create a combo wich display all months where at least a news is been submited (a kind of hostory menĂą), the fact is that it can display max the months wich correspond to the 107 record submited. The query: select date_format(data,"%b/%Y")as textMonth, month(data)as month,dayofmonth(data)as day,year(data)as year from archivio where category = "Rassegna Stampa" group by month Just to be shure I've also tried the query above on mysql browser, and the result is the same. Can you tell me if there's a kind of limit?
Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages. columns: textId, languageCode, textline The application adds records for specific languages as: textId 1 languageCode: EN textline: "Username" another record could be: textId 1 languageCode: DE textline "Anwendername" Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as: Select count(*) from texts where textline != '' and textId in (120,124,134); But what when the records are there in ENglish and German but not in French? Any ideas how to solve this?
Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name. Eg Total records for author Eddings = 10 So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like: Select sum(Eddings) $total From books
MySQL Query For All Records With NULL Value
I have recently been trying to extract a few products from a database (that is quite big), so that I can find products that have a specific NULL Value, what kind of a SQL statement would allow me to do this?
Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key. In normal conditions, the position of this records should be as follows: 1, 2, 3, 4, 5, 6, 7, 8, 9 . etc... One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows: SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record. At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this: UPDATE employe_records SET position = updatedValue FROM employe_records WHERE employe_id = ? ORDER BY position This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.
|
TRACKER
