Getting data from a join tables tu sub categories in access with SQL

Question

I created this relationship with Access database but when I am creating a query to return the values I dont get the all the data. enter image description here How can I get all the data from tube and tmc tables by ProjectType or ProjectId ?

So I should get something like this query but the query generated by access is not working

    SELECT Project.ProjectName, 
           ProjectSubTypesId.ProjectSubTypesId, 
           Tube.TubeName, Tube.Duration, 
           Tmc.TmcName
   FROM Tmc 
INNER JOIN 
(Tube INNER JOIN 
  (Project INNER JOIN ProjectSubTypesId ON Project.ProjectId = ProjectSubTypesId.ProjectId) 
ON Tube.TubeId = ProjectSubTypesId.TubeId) ON Tmc.TmcId = ProjectSubTypesId.TmcId;

Please edit your question with sample data and desired results. A SQL Fiddle is also helpful, although you will have to use a different database from MS Access. — Gordon Linoff, Jul 18 '15 at 01:34
The issues is with Access in a regular database cascade inner join probably work. The result is just if a project has two subtype of project kind tube the result will be to row with the name of the project and the TubeName and Duration — Devsined, Jul 18 '15 at 02:32

score 1 · Answer 1 · answered Feb 18 '16 at 18:37

Try this:

SELECT Project.ProjectName, 
       ProjectSubTypesId.ProjectSubTypesId
    FROM (
       (ProjectSubTypes
           INNER JOIN Project USING (ProjectId)
       )
           INNER JOIN Tube USING(TubeId)
       )
           INNER JOIN Tmc USING(TmcId)
GROUP BY ProjectSubTypes.ProjectId, ProjectSubTypes.TubeId, ProjectSubTypes.TmcId
ORDER BY ProjectSubTypes.ProjectId;

dnapierata · Answer 2 · 2015-07-18T03:01:05.050

0

Something like this work?

SELECT Project.ProjectName, 
       ProjectSubTypesId.ProjectSubTypesId, 
       Tube.TubeName, Tube.Duration, 
       Tmc.TmcName
FROM ((ProjectSubTypes
           INNER JOIN Project ON Project.ProjectId = ProjectSubTypes.ProjectId)
       INNER JOIN Tube ON Tube.TubeId = ProjectSubTypes.TubeId)
INNER JOIN Tmc ON Tmc.TmcId = ProjectSubTypes.TmcId
ORDER BY ProjectSubTypes.ProjectId;

This should give you a list of all sub-types along with their corresponding Tube, Tmc, and Project data.

If it is possible to have multiple ProjectSubTypes rows with the same TubeId and TmcId then consider adding this line before the ORDER BY line:

GROUP BY ProjectSubTypes.ProjectId, ProjectSubTypes.TubeId, ProjectSubTypes.TmcId

edited Jul 18 '15 at 03:01

answered Jul 18 '15 at 01:08

dnapierata

1,153
1
16
28

Yes this probably work in regular SQL but when I tried it on Access and I am having a syntax missing operator, I don't know if it is because access but it looks good syntax to me – Devsined Jul 18 '15 at 02:24
1

I've updated my answer because in Access you need to nest each additional join in brackets. http://stackoverflow.com/questions/7854969/sql-multiple-join-statement – dnapierata Jul 18 '15 at 03:03
That did not work that is I think the parenthesis are the issue with access – Devsined Jul 18 '15 at 12:02
I checked that post and was trying moving the parenthesis around but the TSQL engine dont like any combination here is a small sample of the db: "https://dl.dropboxusercontent.com/u/23000869/Database1.zip" so maybe it work with you I am using Office 2013 – Devsined Jul 18 '15 at 12:16

Getting data from a join tables tu sub categories in access with SQL

2 Answers2