Query To Display Duplicates?
I've looked online but haven't been able to find what I'm looking for. I just need a simple MySQL query to display all the records from a database if one of their fields (products_model) is being used by more than one record.
Something like...
ID products_name products_model
===========================
12 Product A 1345
237 Product G 1345
83 Product L 211
901 Product X 211
68 Product S 211
View Complete Forum Thread with Replies
Related Forum Messages:
Display Amount/No Duplicates
How do you select only the first #(10, 20 ,30... just some set number) rows in a table. Then if you want to continue, how can you select the next ten. I already know how to set them to display in desc order... but I don't know if adding this code will change it. In case you want to know, I am trying to make a news table and have it display on my web page, but I only want 10 headlines to display at a time, then click a link and the next ten will display. The other question is: How do you test to see if someone has already entered the data you are about to enter. An example would be, if someone entered the data(aa,bb,cc,dd) then tried to enter that exact same data again, it would not allow it.
View Replies !
Indexes To Speed Up A Duplicates Query.
OK, say you're trying to find all the non-exact duplicates in a table, and especially do it with a single query, so that the check can be part of a user interface. Then naturally you're going to have a slow query, because it has to compare every row with every other row. You ought to be able to cut out exactly half of the records in the second table, by just saying: SELECT * FROM TABLE LEFT JOIN users dups ON users.created<dups.created But I can't get MySQL to use the index I have on 'created' (same results for integer-primary key, & a string field). My best guess why not is that (according to the manual) "MySQL will not use an index when ... the use of the index would require MySQL to access more than 30% of the rows in the table" - is this the reason? Another option: say that I thought that my duplicates will always have the same letter in their surname, then I could make an index on the first letter of the surname, and make the join just on *that*. This should make the number of rows needed up to 26 times less, right? But exactly how is this done? MySQL won't use my index on the following query: SELECT * FROM TABLE LEFT JOIN users dups ON SUBSTRING(users.name,0,1)=SUBSTRING(dups.name,0,1)
View Replies !
Finding Dupes/duplicates In MySQL 5 Query
The query I have so far displays only the duplicates and only one each of the dupes. What I would like displayed is only the dupes and each of the dupes. For example, my query now shows only one listing for John Doe when there are really 3 records with John Doe as the firsname and lastname. I would like to show all three.(plus all the other dupes) Here is my query:
View Replies !
MySQL Query Display
Have anybody ever see this display problem, my "SELECT * FROM..." is blur, why is that? The chipset I'm using is Intel 855GME with Integrated 3D AGP featuring Intel Extreme 2
View Replies !
Display Result From Query In More Then 1 Column
I want to make a query to a database, and display it on my php page either through php code or with 2 queries to my mysql database. This is how i want it displayed: Data 1 Data 2 Data 3 Data 4 Data 5 Data 6 Data 7 Data 8 ... .... Data X Data X This is my current code which i use and get it displayed in 1 row:
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 !
Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark. Now i want the result as Name Raj Rina Tina Marks 80 90 70 I tried the --vertical option of mysql and G option in the query. for example when i tried select *from studentsG it displayed *********** 1. row *********** name: Raj marks : 80 ********** 2. row ************ name: Rina marks : 90 ********** 3. row ************ name: Tina marks : 70 but i want to display Name Raj Rina Tina Marks 80 90 70
View Replies !
Counting Duplicates With Duplicates
I have a table of votes for several categories. When a user votes in one category it overwrites any old votes, if they exist. I basically want to see how many categories users vote for on average. So I want to run a query that tells me how many users have their usernames appear only once, how many usernames appear exactly twice, three times, and so on.
View Replies !
Duplicates
Let's say I have a table of users, and each user has a list of categories. I could store each user's categories as TEXT with delimeters like "cat1|cat2|cat3" But then I need to be able to get a full list of everyone's categories, without duplicates. Retrieving all the categories, exploding them, and then removing the duplicates is a bit slow. Is there a better method?
View Replies !
Different Duplicates
Table with records, some are similar but not duplicates. I want to find all the duplicates that have a single field different (no keys involved). This gets me duplicates... select NAME from table group by NAME having count(*) > 1 order by 1 asc; How do I find a subset of those that have another field say DESC that aren't equal ? >
View Replies !
Get All Duplicates
I just wrote this query that I thought should return all the duplicate rows but it just runs and never returns a result and I ahev to restart apache. SELECT * FROM `usertable` WHERE `email` IN (SELECT email FROM `usertable` WHERE user_group=4 GROUP BY email HAVING count(*) >1) AND user_group=4 The piece of code "SELECT email FROM `usertable` WHERE user_group=4 GROUP BY email HAVING count(*) >1" returns all emails that are duplicates. My goal is to export a csv of all rows that are duplicates, not just the single emails that are duplicates. If there are 3 rows of the same email I want to return all 3 rows.
View Replies !
Finding Out Duplicates
I have a table with 3 columns 'sid', 'aid', and 'call'. What i want to do is to create a query that shows me all records where sid matches and aid matches but call differs. For example, it would give me this result: sid aid call abc def gh abc def ij and not: sid aid call abc def gh abc def gh Currently i have a query that gives me all matches of sid's and aid's but gives me call's that match and don't match.
View Replies !
Deleting Duplicates
i have a table that looks like this: article_id | article_title so there's a bunch of article titles with ids: 1 - google launches search 2 - local headlines 3 - how to cook got it? there are thousands of rows in this table. some of the articles have the same title(it's a duplicate). i need to write a script to remove the duplicate. ie. when there's two or more titles that are the same delete all of them except for one. so i need a script that deletes duplicated rows basically.
View Replies !
Duplicates And Appending
I have a MYSQL table with 6 columns. I want to be able to set up the primary key on the first column, which contains the ID number. My problem is this table has been created over time and as such there exists multiples of the same ID in column 1. I need to keep every instance of the ID and it's row but would like to combine them into 1 row in my table. Is there a way to do this in MYSQl?
View Replies !
Preventing Duplicates
I have a table that contains rows of department data, where each department has a deptcode, a status and some other arbitrary data, there can be a maximum of three rows with the same deptcode but each must have a different status i.e. live, pending or old. I have written the a sql update that changes the status from pending to live whilst simultaneously changing the status of the live version to old: UPDATE RECMGR_EL_DEPT_Query SET STATUS = case STATUS when 'live' then 'old' when 'pending' then 'live' else STATUS end Where DEPTCODE = <dtml-sqlvar DEPTCODE type="string">; However if I already have a department record with a status of old (from a previous update) when I run this sql I get another row with the same deptcode also having the status of old. So my query is, is there a way I can test for the existance of a row with the required deptcode and the status of old and delete it prior to running this sql update?
View Replies !
Insert Without Duplicates
I have a table with aroun 9-10 columns and I need to insert thousands of records without any duplicate entry for a couple of fields. Is there a tool which can produce such sql script ?
View Replies !
Finding Duplicates
I have a database that lists words with their part of speech. The textfile that I uploaded it from was a combined list, so naturally it had duplicates (ex: 'moo' is featured twice as a noun). My question is, which queries would select only those rows, and then subsequently delete one of them? I'm new to MySQL and I can't seem to figure out one that works.
View Replies !
Exclude Duplicates . . .
I have a table that stores ipnumbers of visitors who have triggered a "trap" and are thus banned from the site. It also contains those who have restored themselves by following a certain procedure. When restored, they are not removed from the table, so it has duplicates, since visitors may be banned and restored multiple times. I would like a query that returns only ipnumbers that are truly still banned - so I need to only return one ipnumber for each visitor and I do not want any ipnumber returned if the visitor has been unbanned. I tried: SELECT `ip` , `action` FROM `trap` WHERE `action` = 'banned' AND `action` = 'unbanned' ORDER BY 'ip` ASC But it returns 0 records.
View Replies !
Only Show Duplicates Once
is there any way of showing duplicates only once ? -- let's say, i have a database of cars, and they search by year... so, i create a select form that contains the years, but, the years can't show twice or more in that select form..
View Replies !
Searching For Duplicates
I have a table named "article" with a mere 5,000 rows. I would like to count the duplicate titles. The following query just hangs (or at least takes longer than 10 minutes as I killed it at that point): SELECT count(*) AS article_count FROM article AS a1 LEFT JOIN article AS a2 ON a1.title=a2.title Titles could be as long as 150 characters. The column named "title" is full text indexed. What am I doing wrong? I would also like to check the article bodies for duplicates. The column named "body" could be as long as 10,000 characters and is full text indexed. Whatever solution there is for title duplicates, I would like it to also work for body duplicates. Finally, the article table is going to get a lot bigger. It needs to work with 100,000 rows or more.
View Replies !
Remove Duplicates
I have 3 fields: id, rating, and ip sample format: id | rating | ip 1 | 10 | 1.2.3.4 1 | 10 | 1.2.3.4 1 | 10 | 1.2.3.4 1 | 10 | 1.2.3.4 1 | 10 | 1.3.2.4 1 | 10 | 1.1.2.2 I need a query to remove all duplicate ips for rating 10 for id 1 so the above sample will end up like this: id | rating | ip 1 | 10 | 1.2.3.4 1 | 10 | 1.3.2.4 1 | 10 | 1.1.2.2 please note that id ranges from 1-1000 and rating ranges anywhere from 1 to 10 so I need a query to remove dupe ip's from ratings 1-10 for each id
View Replies !
Search For Duplicates
I have a table w/ id, first name, last name, score. I want to do a search of all people who have are in the table more than once. ie. select * from table_1 where (first_name+last_name comes up more than twice.)
View Replies !
Getting COUNT Without Duplicates
I was just reading about mysql_calc_rows, which I cannot rely on since I am stuck on MySQL 3.23. I have a query that grabs products from a category and all of its subcategories. The category tree is maintained as a preorder tree with each node having a left and right value (makes it easy to grab all products in all subcategories). Here is the relevant setup: create table categories_list( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, lval INT NOT NULL, rval INT NOT NULL ); create table product_categories( prod_id INT NOT NULL, cat_id INT NOT NULL, PRIMARY KEY(prod_id, cat_id) ); create table products( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, [product fields] ); Products can belong to more than one category. Here is the query I use to get all products contained within the current category tree. SELECT DISTINCT $_PRODUCT_FIELDS FROM product_categories as pc LEFT JOIN products as p ON p.id=pc.prod_ID INNER JOIN categories_list as cl ON cl.id=pc.cat_id WHERE cl.lval >= $parent_left AND cl.rval <= $parent_right LIMIT $start_row, $rows_per_page If I didn't have that LIMIT clause in there, I would be happy to just use the PHP function for counting num rows returned. I want to show something like "Displaying rows 11-20 of 512". So I need to know how many rows the query would return without the LIMIT clause. I suppose I could run the query twice, once without the LIMIT, and then count all the rows, but with 20,000 products in the database I don't really want to shuffle all that data between PHP and MySQL. I would do something like: SELECT COUNT(*) FROM product_categories as pc LEFT JOIN products as p ON p.id=pc.prod_ID INNER JOIN categories_list as cl ON cl.id=pc.cat_id WHERE cl.lval >= $parent_left AND cl.rval <= $parent_right That will give me a different number though, because duplicate rows will be counted -- if a product is in more than one category, it will be counted twice. I may have made some typos in this post (please point out if something looks broken) but the query is working fine. I just want a way to count the rows without doing the query all over..
View Replies !
Deleting Duplicates Of Rows
i have made up a table which contains a bunch of results from a search and what i want to do is to delete any duplicate rows in my database. I not actually sure how to go about achieve this, i thought perphaps performing a search combined with some check method and a delete statement. Is there perphaps a msql function that can achieve this? Also note that when i say duplicate copies, it does not necesarilly mean that all the colums two rows while be the same but will have a enough in common to be able to tell if duplicate.
View Replies !
Remove Duplicates From DB Tables
[ID] (an autonumber) [Date] (YYYY-MM-DD HH:MM:SS) [Title] (255 character text) [xcount] (Numeric) What I want to do is delete the duplicate posts. They are duplicate ONLY if [title] is the same, and the [Date] is within 3 days of each other. I want to delete the lower numbered posts, or those with the lowest [xcount].
View Replies !
Duplicates On Multiple Fields
The table has fields: home_id, owner, street_number, direction_one, street, street_type, city I have a problem where the same address information was entered in but with different owners. How do I find all the duplicates? I tried this query: SELECT home_id, street_number, street, city FROM home_data GROUP BY street HAVING ( COUNT(street) > 1 ) but that only works for duplicates on street, at the very least it should check for dups on street_number and street and city. When I ran the above query it took forever. My table has over a million records in it. Basically my computer froze up but kept running and I kept getting virtual memory errors.
View Replies !
Importing A Csv File Without Duplicates
I have a csv file I would like to import into a table. The csv file has many duplicate rows that I do not need. How can I import this into a table without any duplicates? I still need the rows just not duplicates. Also the schema is not yet created for the table.
View Replies !
Creating A View For Duplicates?
I have a bit of a large database (2 million rows -- going to be around 10-12 million later), and I was wondering how to create a view that does the following: turning THIS: id | attribute | value ---|-----------|------ 1 name joe 2 name bob 3 name paul 4 name joe 5 name paul 6 name jones 7 age 12 into THIS: id | attribute | value | occur ---|-----------|-------|------- 1 name joe 2 2 name bob 1 3 name paul 2 6 name jones 1 7 age 12 1 the id field's values don't really matter to me except that they remain unique (the original table's id field is the primary key & auto_increments).
View Replies !
Special Remove Duplicates
I have a query that returns a result set that is composed of events with a start datetime and an end datetime. Some of these events occur during another event. For example: User AAA Event 1 Start Datetime 2006-11-01 12:00:00 End Datetime 2006-11-07 18:43:00 User AAA Event 2 Start Datetime 2006-11-03 08:21:00 End Datetime 2006-11-05 19:32:00 I am trying to get a result set that does not include those events that occur during another event. Or for the example above I want Event 1, but not Event 2. I am looking for a little direction.
View Replies !
Count Function :: How Many Duplicates
I have a database which I need to query to find out how many duplicate rows have been entered and then to use that query to count how many times the same person has inputed the data twice. eg - It counts the duplicate data Then counts the amount of times that the name of the person appears in the results I can do the counting the duplicate data but how would I go about counting the reults returned from that query This is the query I am using to count the duplicates: SELECT COUNT(*) AS `Rows`, `Initials` FROM `input data` GROUP BY `Date`, `Time`, `Name of caller`, `Animal`, `Details`, `Telephone number`, `Method`, `Passed to`, `Initials`, `DDI` HAVING COUNT(*) > 1; How could I count the amount of times the same initials appear.
View Replies !
DISTINCT Outputs Duplicates
I have an attribute "src" in one of my MySQL database tables which contains values such as node/83 and node/84. My table contains 2 same values: node/83 and node/83 I'm using SELECT DISTINCT(src) to remove all duplicates from that attribute but as I just said, those 2 same values still show up in the results. How come those duplicates were ignored when using DISTINCT? It it because of the / in the value?
View Replies !
Deleting Duplicates With Unique Id
CREATE TABLE test2 SELECT max(id), title, descrpt FROM test GROUP BY title, descrpt; Basically, I need to check for duplicates entries in every field but the first one (the primary key, auto_increment int.), and then delete any extras that match. Leaving only one copy. The id doesn't matter, for the most part, so it doesn't matter which of the duplicates it keeps as the new unique record.
View Replies !
Removing Duplicates With Group By
If I use Group by on one field that I don't want duplicated in my resultset, I get the desired results but I am not confident that this is a bona fide way of doing things. I've tried using DISTINCT and DISTINCTROW but they don't work, at least not the way I have been using them. I want to remove duplicates of a particular Course Reg Number (CRN). MY query looks something like SELECT * FROM table1 WHERE ... GROUP BY crn like I said I get the desired results just not sure.
View Replies !
Subquery For Duplicates Removal
I have the following query. It produces some details for a specific dat ($scandat) and for a specific location ($_GET["dep"]) Select scanarch.*, substr(scanid,6)+0 as sort, c.senddep, c.deliverdep, c.docnum from scanarch s,consignments c where (s.scantype = 'O') and s.scandate = '$scandat' and s.depotscan<>0 and left(s.identifier,22)=c.identifier and c.senddep = ".$_GET["dep"]. " and s.identifier <> '' order by sort"; It's a report of scans (barcodes) which match the criteria of the where clauses. Anyway, an item can be scanned twice which makes it look like there are more records than there really are. So i want to select only 1 unique record per day. If the identifier and date matches, i only want 1 of the records. if the identifier matches but the date is different, i would want 1 unique per each day. Hope this makes sense. I tried using a subquery, selecting distinct identifier/date before doing the above query (on the already "filtered" list of uniques) but it means a bunch of my needed output fields aren't available to the main query. And i can't include them in the subquery, because as you know the distint applies to every field, and including the "time" as well as the date, will make every single item unique.
View Replies !
Selecting From Two Tables, But No Duplicates
I want to select from two tables. Each table has a column called "course_id". I want to select from one table if the "course_id" doesn't exist in the other table. How would I do this in one or multiple queries? I was looking at unions or joins but I don't know if that is the most efficient or easiest solution. Any help is appreciated. Edit: I'm only really selecting from the first table, but making sure that the "course_id" doesn't exist in the second table.
View Replies !
Count(*) To Avoid Duplicates
This is for (2) seperate sites that share the same login table (same username/passwords will access both sites). Users may register at either site, if they choose, then they should login at the other site to "complete" the last phase of registration at the other site to insert remaining needed values. Problem is with duplicates. When some users won't login, as they should, "complete" the process at the other site. They may bypass and register anew like anyone else at the site for the first time. (Even though there is a pre-registration page at both sites -- asking if users have already registered at the other site prior to arriving at the second -- directing a login to complete the process.) Reason this is a concern is that one site has a (6) page registration process, inserting to (8) tables. My idea (aside from aleady checking for unique inserts on usernames/passwords/emails) is to check on the complete phone number. To be used on a conditional show region on the second page of registration -- if the latest registrant entered the same phone number asmay aleady exist in the table, (previously enterered while registering on the other site and forgot about it) -- this 2nd registration page will not show due to the: count(*) Starting with 'Area' code, is this practical/how to combine all three? Anyone have better suggestions or experience on handling two site duiplicates? Would there be any benefit in having ALL USERS (both new and prior from the "other" site) fill out the the 1st registration form (on the (6) page registration site) where they are then directed to login to complete the process? 'AREA' 'PRE' 'PHONE' SELECT * FROM `Members` WHERE `Area` IN ( SELECT `Area` FROM 'Members` GROUP BY `Area` HAVING count(*)>1 ) note: some registrants have same company names fir satellite/regional offices
View Replies !
Easy Way To Delete Duplicates?
I had 2 membership db's. One had all the members (65K) db2 had all the members that have registered on the website. I am wanting to consolidate the two. Basically i took both, and merged them together into their own db. now the membership list is about 80k. 20k or so of that are duplicates. My idea was to export the thing as .xls and remove the duplicates via Microsoft Excel. However, Excel has a limit of 65Kish rows. so i can't really go about that.
View Replies !
Removing Duplicates With Unique IDs?
I searched around and found a few threads similar to my problem, but I wasn't able to find a very clear answer to this question. Basically I have a huge database (8.2 m records) full of destinations, things like restaurants, hotels, etc, and there are a lot of duplicates. The duplicates are in the database from the source, and we cannot change that. Some duplicates actually have up to 3 columns that are different. The ID is unique for each row, and the category number can be different as well. I've even seen a couple instances where the zip code does not have the leading zero. Here are some example rows: ID_____NAME_____ADDRESS_____CITY_____STATE_____ZIP_____CAT 544 WAL-MART 1234 MAIN ST DALLAS TX 05424 1234 545 WAL-MART 1234 MAIN ST DALLAS TX 5424 4321 546 WAL-MART 1234 MAIN ST DALLAS TX 05424 1234 The reason why these duplicates exist (probably) is because one WAL-MART could fit into multiple categories, because it's a grocery store, a department store, a photo center, etc. I don't want all that junk, I just want 1 record for 1 Wal-Mart How could I go about deleting these duplicate records? and if possible, I'd like to be able to choose which record is kept, based on the category ID.
View Replies !
Copy And Remove Duplicates
I have a table that I need to add a lot of new data into. But there are a couple of problems with that..... First, the new data is not completely compatible with the old so I need to copy data from one column to another, if the second column is blank. I also need to make sure that there are no duplicate entries. I am using MySQL version 5 and I am not real sure how to proceed.
View Replies !
Delete Duplicates From 2 Tables
MySQL CREATE TABLE `subscribers` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `email` VARCHAR(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I have an accompanying table that houses additional subscriber data, with the column sd_sub_id being the subscribers table FK MySQL CREATE TABLE `subscribers_data` ( `sd_sub_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `sd_ip_address` VARCHAR(60) NOT NULL DEFAULT '', PRIMARY KEY (`sd_sub_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Chances are that there won't be duplicate rows, but! I don't want to take that chance so I'm cooking up a 'remove duplicates' function. I have it set up to remove the duplicates from the subscribers table, but how do I get the corresponding records from the subscribers_data table and delete them as well? MySQL CREATE TEMPORARY TABLE subscribers_temp(id VARCHAR(10), email VARCHAR(60)) TYPE=HEAP; INSERT INTO subscribers_temp(id,email) SELECT DISTINCT email FROM subscribers; DELETE FROM subscribers; INSERT INTO subscribers(id,email) SELECT id,email FROM subscribers_temp;
View Replies !
|
TRACKER
