SQL Select Duplicate and another fields

Creating tables and queries
Post Reply
newtonln
Posts: 6
Joined: Thu Jan 28, 2016 3:35 pm
Location: Curitiba PR Brazil

SQL Select Duplicate and another fields

Post by newtonln »

Dear Friends :
I need to list (SELECT) FIELD1.TABLE1, FIELD2.TABLE1, FIELD3.TABLE1, FIELD1.TABLE2, FIELD1.TABLE3
Only the FIELD1.TABLE1 is duplicated !
I did a simple query, using group and count for FIELD1.TABLE1 but, I need to list the another fields too and, I need help!
Sincerely,
Newton
Curitiba - PR - Brazil
LibreOffice on Windows 7/8/ 10 / Mageia 6
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL Select Duplicate and another fields

Post by UnklDonald418 »

Yes, the query you show in your question will return a large number of lines.
For each record in Table1 you will get (number of records in Table2) X (number of records in Table3) lines of output.
If you are expecting something less you need to define a relationship between the tables and probably some conditions that will allow you to reduce the number of matches.
It is not at all clear from your question what you are trying to achieve.
You might get better help here if you upload a sample database and a good description of what results you are expecting.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
newtonln
Posts: 6
Joined: Thu Jan 28, 2016 3:35 pm
Location: Curitiba PR Brazil

Re: SQL Select Duplicate and another fields

Post by newtonln »

Dear Donald :
At my database (almost 70,000), I found more than one register for the same number and, I would like to know, how to display this information, only for the duplicates , for instance :
FIELD1.TABLE1, FIELD2.TABLE1, FIELD3.TABLE1, FIELD1.TABLE2, FIELD1.TABLE3
212134,JOHN,PR,CURITIBA,X45
212134,MARY,SC,PINHAO,X43
212134,TONY,SP,APUC,Z98
378975,CHIC,DF,MAIS,T32
378975,MARK,ES,MORE,U23
and so on
I tried this query successfully , but only for FIELD1
SELECT "FIELD1", COUNT( * ) "Total" FROM "TABLE1" GROUP BY "FIELD1" HAVING COUNT( * ) > 1 ORDER BY COUNT( * ) DESC
I would like that the another fields will be listed, only when FIELD1 was the same !
LibreOffice on Windows 7/8/ 10 / Mageia 6
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: SQL Select Duplicate and another fields

Post by F3K Total »

Hello,
what do you think?
We rebuild your database to find a running query for you?
UnklDonald418 wrote:You might get better help here if you upload a sample database and a good description of what results you are expecting.
Take a copy of your db, delete everything not necessary for the query, keep only some records, replace if necessary confidential informations, and execute once via Tools/SQL...

Code: Select all

CHECKPOINT DEFRAG
to compress the db before upload.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
charlie.it
Volunteer
Posts: 417
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: SQL Select Duplicate and another fields

Post by charlie.it »

Ciao, try:

Code: Select all

SELECT "FIELD1", "FIELD2" , "FIELD3" COUNT( * ) "Total" FROM "TABLE1" GROUP BY "FIELD1" ", "FIELD2" , "FIELD3" HAVING COUNT( * ) > 1 ORDER BY COUNT( * ) DESC
charlie
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1

http://www.charlieopenoffice.altervista.org
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL Select Duplicate and another fields

Post by eremmel »

@charlie.it: Your query will not return results according example data.

@newtonln:
Try

Code: Select all

 SELECT * 
FROM "TABLE1" 
WHERE "TABLE1"."FIELD1" IN ( SELECT "FIELD1" FROM "TABLE1" GROUP BY "FIELD1" HAVING COUNT( * ) > 1 )
ORDER BY "FIELD1", "FIELD2"
You might enable 'direct SQL' mode for this query (see tools menu).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply