SQL Select Duplicate and another fields

Creating tables and queries

SQL Select Duplicate and another fields

Postby newtonln » Thu Jan 28, 2016 3:48 pm

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
newtonln
 
Posts: 6
Joined: Thu Jan 28, 2016 3:35 pm
Location: Curitiba PR Brazil

Re: SQL Select Duplicate and another fields

Postby UnklDonald418 » Fri Jan 29, 2016 7:52 pm

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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1326
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL Select Duplicate and another fields

Postby newtonln » Sat Jan 30, 2016 12:03 am

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
newtonln
 
Posts: 6
Joined: Thu Jan 28, 2016 3:35 pm
Location: Curitiba PR Brazil

Re: SQL Select Duplicate and another fields

Postby F3K Total » Sat Jan 30, 2016 11:36 am

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   Expand viewCollapse view
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
F3K Total
Volunteer
 
Posts: 974
Joined: Fri Dec 16, 2011 8:20 pm

Re: SQL Select Duplicate and another fields

Postby charlie.it » Sat Jan 30, 2016 4:27 pm

Ciao, try:

Code: Select all   Expand viewCollapse view
SELECT "FIELD1", "FIELD2" , "FIELD3" COUNT( * ) "Total" FROM "TABLE1" GROUP BY "FIELD1" ", "FIELD2" , "FIELD3" HAVING COUNT( * ) > 1 ORDER BY COUNT( * ) DESC
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: SQL Select Duplicate and another fields

Postby eremmel » Fri Feb 05, 2016 2:34 pm

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

@newtonln:
Try
Code: Select all   Expand viewCollapse view
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
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests