VERY STRONG query

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

VERY STRONG query

Post by sal21 »

In the table ANAGRAFICA are patients
In the table PATOLOGIA are Pathologies

Mathcing IDPAT from ANAGRFICA and ID from PATOLOGIE possible to return the list related client with IDCLI 52?

In my case IDCLI 52 have:

14 IPERTENSIONE ARTERIOSA
7 DERMATITI

I hope I was clear

note:
SQL for ado connection
You do not have the required permissions to view the files attached to this post.

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: VERY STRONG query

Post by SpeakEasy »

Step 1 - normalisation. You need to normalise ANAGRAFICA to at least First Normal Form

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

Speak
Not understand, your suggestioni.

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: VERY STRONG query

Post by SpeakEasy »

I don't mean to be negative, but I think you need to be aware of some database basics before playing around with them.

Here's a starter:
  • A table is referred to as being in its First Normal Form if atomicity of the table is 1.
  • Here, atomicity states that a single cell cannot hold multiple values. It must hold only a single-valued attribute
  • The First normal form disallows the multi-valued attribute, composite attribute, and their combinations.
And if you look at the ID field in PATOLOGIA you will see that it breaks that rule.

Fix that, and your question becomes much easier to resolve

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

@SpeakEasy: Don't you mean the IDPAT field in ANAGRAFICA?

S2634.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

Code: Select all

    Dim lngCLI As Long
    Dim strSQL As String
    lngCLI = 52
    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
        lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & PATALOGIE.ID & '-*'"
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: VERY STRONG query

Post by SpeakEasy »

HansV wrote:
25 Nov 2024, 19:47
@SpeakEasy: Don't you mean the IDPAT field in ANAGRAFICA?


S2634.png
I do indeed! Trying to do too many things at the same time ...

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

I know the feeling... :smile:
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

HansV wrote:
25 Nov 2024, 19:54

Code: Select all

    Dim lngCLI As Long
    Dim strSQL As String
    lngCLI = 52
    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
        lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & PATALOGIE.ID & '-*'"
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

Try

Code: Select all

    Dim lngCLI As Long
    Dim strSQL As String
    lngCLI = 52
    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
        lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & ID & '-*'"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

HansV wrote:
26 Nov 2024, 08:50
Try

Code: Select all

    Dim lngCLI As Long
    Dim strSQL As String
    lngCLI = 52
    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
        lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & ID & '-*'"
HUMMMM...

The query work in Access ide, and in vb 6.0 sql return null recorset!
Mistery

Code: Select all


    Dim lngCLI As Long
    Dim strSQL As String

    lngCLI = 52

    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
             lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '*-' & ID & '-*'"
             
    Debug.Print strSQL

    Set RS = New ADODB.Recordset
    RS.CursorLocation = adUseClient
    RS.Open strSQL, CON, adOpenForwardOnly, adLockReadOnly

        Do While Not RS.EOF
            Debug.Print RS.Fields(0).Value & "-" & RS.Fields(1).Value
            RS.MoveNext
        Loop


User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

Try

Code: Select all

    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
             lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '%-' & PATOLOGIE.ID & '-%'"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

HansV wrote:
26 Nov 2024, 11:51
Try

Code: Select all

    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
             lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '%-' & PATOLOGIE.ID & '-%'"
WORK!
tKS BRO

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

Access uses * as wildcard by default, but ADO uses %
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

HansV wrote:
26 Nov 2024, 11:51
Try

Code: Select all

    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
             lngCLI & " AND '-' & ANAGRAFICA.IDPAT & '-' Like '%-' & PATOLOGIE.ID & '-%'"
BRO...
instead to use the filed ANAGRAFICA.IDPAT, possible to use the value in myvar, similar "-1-12-58"

note:
In this case not important to use the where for ANAGRAFICA.IDCLI
Last edited by sal21 on 01 Dec 2024, 17:09, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

Code: Select all

    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
             lngCLI & " AND '-' & " & myvar & " & '-' Like '%-' & PATOLOGIE.ID & '-%'"
But myvar should look like "1-12-58", not "-1-12-58"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

HansV wrote:
01 Dec 2024, 17:08

Code: Select all

    strSQL = "SELECT PATOLOGIE.ID, PATOLOGIE.PATOLOGIA FROM ANAGRAFICA, PATOLOGIE WHERE ANAGRAFICA.IDCLI=" & _
             lngCLI & " AND '-' & " & myvar & " & '-' Like '%-' & PATOLOGIE.ID & '-%'"
But myvar should look like "1-12-58", not "-1-12-58"
:cheers: :clapping:

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

MY SQL:

SELECT * FROM ANAGRAFICA, PATOLOGIE WHERE "-1-" Like '*-' & PATOLOGIE.ID & '-*'

in effect i need to extract * from ANAGRAFICA where in IDPAT contain 1

in my case:
54 DE RUGGIERO GIOVANNI
56 VERDI ALESSANDRO
57 GRANDE PICCOLA 9

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VERY STRONG query

Post by HansV »

Code: Select all

    strSQL = "SELECT * FROM ANAGRAFICA WHERE '-' & IDPAT & '-' Like '%-1-%'"
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4576
Joined: 26 Apr 2010, 17:36

Re: VERY STRONG query

Post by sal21 »

HansV wrote:
08 Dec 2024, 19:18

Code: Select all

    strSQL = "SELECT * FROM ANAGRAFICA WHERE '-' & IDPAT & '-' Like '%-1-%'"
Ops.,.
I Need also a where IDCLI from ANAGRAFICA.
Sorry.
For example: 59