sqlite joins to two tables returning only one row of data

[jalz]

Hi Guys,

I've got the following query below which works fine. The problem is it is searching through one table called project and I want some of the keys, such as customerID and project_statusID to return the actual values from their relative tables.

I've been reading up on the w3schools sql reference which is making some sense (didn't realise their were so many joins….). It seems like I need to do an inner join, however the examples seem to return all the values related in a table, whereas I want to only display values related to one projectID. Perhaps I'm over thinking this…

With my code below, anyone advise how I can link to the following tables

1) customer table using customerID returning column customer
2) project_status using project_statusID returning column project_status
Thanks

Code: Select all

   local tTheSQLQuery, tTheData
   
   if (gConnID > 0) then
      put "SELECT projectID,project_title,project_manager,start_date,end_date,project_statusID,project_brief," &\
      "customerID,customer_contact,active FROM project WHERE projectID=" & pProjectID into tTheSQLQuery
      put revDataFromQuery(tab, cr, gConnID, tTheSQLQuery) into tTheData
      
      if tTheData begins with "revdberr," then
         delete item 1 of tTheData
      else
         set ItemDel to tab
         put item 1 of tTheData into field "fld_project_id"
         put item 2 of tTheData into field "fld_project_title"
         put item 3 of tTheData into field "fld_project_manager"
         put item 4 of tTheData into field "fld_start_date"
         put item 5 of tTheData into field "fld_end_date"
         put item 6 of tTheData into field "fld_project_status"
         put item 7 of tTheData into field "fld_project_brief"
         put item 9 of tTheData into field "fld_customer_contact"
         if ((item 10 of tTheData)  = true) then 
            set the hilite of button "chk_active" to true 
         else 
            set the hilite of button "chk_active" to false
         end if
      end if
   end if

[jalz]

Hi Guys,

I finally figured it out.

SELECT project.projectID,project.project_title,project.project_manager,
project.start_date,project.end_date,project.project_statusID,project_status.project_status,project.project_brief,
project.customerID,customer.customer,project.customer_contact,project.active
FROM project
INNER JOIN Customer
ON Project.CustomerID=Customer.CustomerID
INNER JOIN Project_status
ON Project.Project_statusID=Project_Status.Project_StatusID
WHERE project.projectID=1