Skip to main content

MySQL query to retrive only one record for a repeating column or rows of data

MySQL query to retrive only one record for a repeating column or duplicate rows of data

Suppose there is a table tbl_BOOKS which stores records of various books. some books comes under different categories. so there may be multiple records with same BookReferenceNumber. But when user searches for a particular reference number, the search result should display only one record eventhough there are multiple entries for same BookReferenceNumber.

Let us illustrate this with a sample table given below:
Table Name: tbl_BOOKS

BookId BookReferenceNumber BookName BookCategory BookDetails BookStatus
1 5546 Let us C C Language C tutorial book Available
2 2311 Java Complete reference Java Programming Java quick reference text Unavailable
3 5546 Let us C Programming C tutorial book for dummies Available
4 8900 PHP & PERL Web Programming Complete guide for PHP and Perl programming Available

In the above table, the book "Let us C" is repeating, this is to show the record under multiple categories. ie "C Language" and "Programming" , but user should view only one of this 2 records in the search results

Here is the sample query for accomplishing the above mentioned task.


SELECT
BookId, BookReferenceNumber,
BookName, BookCategory,
COUNT( BookReferenceNumber ) AS HowManyRecs,
BookDetails, BookStatus

FROM tbl_BOOKS

WHERE (
BookReferenceNumber LIKE '42324%'
OR BookName LIKE '%42324%'
)
AND (
BookStatus = 'Available'
)
GROUP BY BookReferenceNumber
HAVING (
HowManyRecs > 0
)



The result of the above query is given below. Please note there is only one record corresponding to book "Let us C"

Book
Id
BookReference
Number
Book
Name
Book
Category
HowMany
Recs
Book
Details
Book
Status
3 5546 Let us C Programming 2 C tutorial book
for dummies
Available
4 8900 PHP & PERL Web Programming 1 Complete guide for
PHP and Perl
programming
Available

Try it and post your valuable comments  in the comment section below :)



Sample query to find records having duplicates

SELECT BookReferenceNumber, COUNT(BookReferenceNumber) AS HowMany FROM tbl_BOOKS
GROUP BY BookReferenceNumber
HAVING ( COUNT(BookReferenceNumber) > 1 )


SELECT BookReferenceNumber, COUNT(BookReferenceNumber) AS HowMany FROM tbl_BOOKS
GROUP BY BookReferenceNumber
HAVING ( HowMany > 1 )



Read how to Delete duplicate records from a mysql table by keeping only one record with highest or lowest id value.


Related post:

Popular posts from this blog

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »
Member
Search This Blog