PureBasic Forum
https://www.purebasic.fr/english/

Get all contact data for all records in one query
https://www.purebasic.fr/english/viewtopic.php?f=7&t=73244
Page 1 of 1

Author:  Fangbeast [ Mon Jul 22, 2019 5:35 am ]
Post subject:  Get all contact data for all records in one query

If I have a query to return all data from the db_addresses such as below:

Code:
SELECT * FROM db_addresses


Is there any way to select all the contact data for each address from the db_addressescontacts table for each one in the same query?

Something like:

Code:
SELECT * FROM db_addresses (SELECT FROM db_addressescontacts WHERE db_addresses.recordid = db_addressescontacts.recordid)


Tried this, didn't work but there must be a way to tie these together.

Data return would look something like: (Benny only has 2 items of contact data, others, more)

Quote:
Benny Benassi
20204849
forkle@dingo.com
Robert Pyjama
callan@festering.com
http://grr.poop.bum.nz
id:20174394739
skypid:flappit


IF I use this:

Code:
SELECT * 
FROM db_addresses 
AS m 
LEFT JOIN db_addressescontacts 
AS n 
ON m.recordid = n.recordid


I get multiple duplicated records for each contact item which isn't what I need.

Author:  Bisonte [ Mon Jul 22, 2019 7:25 am ]
Post subject:  Re: Get all contact data for all records in one query

MayBe INNER JOIN will help you

http://www.sqlitetutorial.net/sqlite-inner-join/

Author:  Fangbeast [ Mon Jul 22, 2019 12:50 pm ]
Post subject:  Re: Get all contact data for all records in one query

Bisonte wrote:


Don't understand it, that's the thing. Am playing with a few query variations and will keep reading tomorrow but for now, when I do:

Code:
SELECT
 titlename,
 firstname,
 middlename,
 lastname
FROM
 db_addresses
INNER JOIN db_addressescontacts ON db_addressescontacts.recordid = db_addresses.recordid;


Which returns multiple names for each contact data but no contact data (obviously) and then I reversed the query to see what would happen. Interesting but now what I need in the previous post.

Oh well, not that I was doing anything useful yet:):)

Author:  spikey [ Tue Jul 23, 2019 12:52 pm ]
Post subject:  Re: Get all contact data for all records in one query

Difficult to answer this sort of question without knowing what the schema contains - SELECT * doesn't tell me much, other than the obvious!

Post the schema for both tables - and an INSERT ... VALUES type SQL for some demo data wouldn't go amiss either... Saves time trying to guess what the schema might be...

I can tell you that a JOIN isn't going to deliver the results you want though. It expects to make an aggregate of the one for one correlation between two tables - possibly inserting nulls when one or other of the tables doesn't fully correlate. That's not what you are describing - it was what you were getting though :D

Author:  swan [ Tue Jul 23, 2019 11:52 pm ]
Post subject:  Re: Get all contact data for all records in one query

If your getting duplicate records with Inner Join (as long as their the right records) start with Select Distinct * (& so on). It will return only one of each record.

Author:  Fangbeast [ Wed Jul 24, 2019 12:29 pm ]
Post subject:  Re: Get all contact data for all records in one query

spikey wrote:
Difficult to answer this sort of question without knowing what the schema contains - SELECT * doesn't tell me much, other than the obvious!

Post the schema for both tables - and an INSERT ... VALUES type SQL for some demo data wouldn't go amiss either... Saves time trying to guess what the schema might be...

I can tell you that a JOIN isn't going to deliver the results you want though. It expects to make an aggregate of the one for one correlation between two tables - possibly inserting nulls when one or other of the tables doesn't fully correlate. That's not what you are describing - it was what you were getting though :D


It's 5 degrees here and I can barely move my fingers.

Just got the schema for now, more later when I can unfreeze myself.

Code:
Table db_addresses
        titlename TEXT
        firstname TEXT
        middlename TEXT
        lastname TEXT
        medical TEXT
        business TEXT
        nickname TEXT
        customname TEXT
        amateurname TEXT
        pobox TEXT
        street TEXT
        suburb TEXT
        city TEXT
        state TEXT
        postcode TEXT
        country TEXT
        displayformat TEXT
        displayas TEXT
        ownername TEXT
        category TEXT
        comment TEXT
        picture BLOB
        label TEXT
        archived TEXT
        favourite TEXT
        locked TEXT
        deleted TEXT
        updated TEXT
        recordid  INTEGER PRIMARY KEY AUTOINCREMENT
        UNIQUE(titlename,firstname,middlename,lastname,business,nickname,customname)

Table db_addressescontacts
        contactid INTEGER
        contacttype TEXT
        contactdetails TEXT
        recordid  INTEGER PRIMARY KEY AUTOINCREMENT
         FOREIGN KEYcontactid REFERENCES db_addresses(recordid)) ON DELETE CASCADE

Author:  spikey [ Thu Jul 25, 2019 5:30 pm ]
Post subject:  Re: Get all contact data for all records in one query

Code:
SELECT recordid, '1' AS seq, 'Name' as contacttype, titlename, firstname, middlename, lastname
FROM db_addresses
UNION
SELECT contactid, '2' AS seq, contacttype, contactdetails, NULL AS firstname, NULL AS middlename, NULL AS lastname
FROM db_addressescontacts
ORDER BY recordid, seq ASC;


There are a few important notes:-

1) This is a UNION. Unions will only work if the two sets contain the same number of columns, so I've had to pad out the 'db_addressescontacts' output with three NULLs to balance. If you alter the output you must alter it symmetrically or it will break the query.
2) Columns are named from the first SELECT statement executed.
3) The "# as seq" bits are mandatory - it won't work without properly without them, so don't try to tidy them out. Just exclude them when you display the results.

Author:  Fangbeast [ Fri Jul 26, 2019 1:00 am ]
Post subject:  Re: Get all contact data for all records in one query

Eek!! Never understand that code in a million years (Join that to never understand women at all)

It's interesting but not outputting the contacts for each person after each person's details from db_addresses.

Thanks for the help Spikey, when the weather warms I can actually think a little when the neurons start to flow.

This is what I will do below as I understand it. Just going to flesh the statements out a bit with some conditionals that come from text boxes: (Obviously lots of custom stuff in there but this has worked befored. Not elegant but works)

Code:
Procedure ShowCategoryAddresses()
  ;
  GetCurrentCategory()
  ;
  ; Debug "The current line is:::           " + Str(CurrentCategoryLine.i)
  ; Debug "The current category name is:::  " + CurrentCategory.s
  ; Debug "The current table name is:::     " + TableName.s
  ; Debug "The current procedure is:::      " + #PB_Compiler_Procedure
  ; Debug "-----------------------------------------------------------------------------------------"
  ; Remove the columns for this database
  RemoveGadgetColumn(#Gadget_MyInfo_Titles, #PB_All)
  ; Set the columns for this database
  AddGadgetColumn(#Gadget_MyInfo_Titles,  1,  "Item title",     440)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  2,  "Home phone",     100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  3,  "Work phone",     100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  4,  "Mobile",         100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  5,  "Fax number",     100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  6,  "Email address",  160)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  7,  "Callsign",        80)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  8,  "Archived",         0)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  9,  "Favourite",        0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 10,  "Locked",           0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 11,  "Deleted",          0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 12,  "Updated",          0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 13,  "Recordid",         0)
  ;
  Queue\CurrentCategory.s = Category\Category.s
  Queue\CategoryItems.s   = Category\Hasitems.s
  ;
  Program\LastCategory.s = Queue\CurrentCategory.s
  ;
  ;Debug "We got a current category selection:::  " + Queue\CurrentCategory.s
  ; Keep track of the current line to feed to the string display routine
  CurrentLine.i = #False
  ; Create a new list of record numbers we will use to fetch data with for linked tables
  NewList RecordNumbers.s()
  ; Fetch all recordid numbers from the selected table
  If DatabaseQuery(Program\DatabaseHandle, "SELECT recordid FROM db_addresses ORDER BY recordid") <>  #DatabaseQueryFail
    While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
      CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
      If CurrentRecordid.s  <>  #Empty$
        AddElement(RecordNumbers.s())
        RecordNumbers.s() = CurrentRecordid.s
      Else
        ; There was no current record id. Should never happen
      EndIf
      CurrentRecordid.s = #Empty$
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle)
  Else
    SetInfoBarArea("Headings",  "Error",  "The database query has failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
  EndIf
  ; Contact new list for temporary contact data to feed to display routine
  NewList TempContacts.ContactData()
  ; Iterate through the linked list and fetch each record along with the associated contact data
  ForEach RecordNumbers.s()
    ; Fetch all address data from the db_addresses table for each recordid in the list
    SetDatabaseString(Program\DatabaseHandle, 0, RecordNumbers.s())
    If DatabaseQuery(Program\DatabaseHandle, "SELECT * FROM db_addresses WHERE recordid  = ?") <>  #DatabaseQueryFail
      While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
        CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
        CurrentRecordid.s = #Empty$
      Wend
      FinishDatabaseQuery(Program\DatabaseHandle)
    Else
      SetInfoBarArea("Headings",  "Error",  "The database query has failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
    EndIf
    ; Fetch all contact data for each recordid in the list that has linked contact data
    SetDatabaseString(Program\DatabaseHandle, 0, RecordNumbers.s())
    QueryString.s = "SELECT contacttype, contactdata FROM db_addressescontacts WHERE recordid  = ?"
    If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <>  #DatabaseQueryFail
      While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
        CurrentContactType.s = GetDatabaseString(Program\DatabaseHandle, 0)
        CurrentContactData.s = GetDatabaseString(Program\DatabaseHandle, 1)
        If CurrentContactData.s  <>  #Empty$
          AddElement(TempContacts.s())
          TempContacts.s()\contacttype.s  = CurrentContactType.s
          TempContacts.s()\contactdata.s  = CurrentContactData.s
        Else
          ; There was no current record id. Should never happen
        EndIf
        CurrentRecordid.s = #Empty$
      Wend
      FinishDatabaseQuery(Program\DatabaseHandle)
    Else
      SetInfoBarArea("Headings",  "Error",  "The database query has failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
    EndIf
    ; Display the new string of address data, parse the list for each line
    DisplayAddressTitleString(CurrentLine.i, List ShowContacts.TempContactsData())
    ;
    CurrentLine.i + 1
    ;
  Next
EndProcedure

Page 1 of 1 All times are UTC + 1 hour
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/