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
SQL Select Duplicate and another fields
SQL Select Duplicate and another fields
LibreOffice on Windows 7/8/ 10 / Mageia 6
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: SQL Select Duplicate and another fields
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.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: SQL Select Duplicate and another fields
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 !
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
Re: SQL Select Duplicate and another fields
Hello,
what do you think?
We rebuild your database to find a running query for you? to compress the db before upload.
R
what do you think?
We rebuild your database to find a running query for you?
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...UnklDonald418 wrote:You might get better help here if you upload a sample database and a good description of what results you are expecting.
Code: Select all
CHECKPOINT DEFRAG
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
- charlie.it
- Volunteer
- Posts: 417
- Joined: Wed Aug 21, 2013 2:12 pm
- Location: Italy
Re: SQL Select Duplicate and another fields
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
Italian AOO Admin
macOS 14 Sonoma M1: Open Office 4.1.15 - LibreOffice 7.5.7.1
http://www.charlieopenoffice.altervista.org
Re: SQL Select Duplicate and another fields
@charlie.it: Your query will not return results according example data.
@newtonln:
Try
You might enable 'direct SQL' mode for this query (see tools menu).
@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"
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)