SQL Inner Join Issue/Alternative Join

Question

I'm trying to correct this query where I need to join the table capcase first and then join table called capstatus second.There are different tables from capcase status and capcaseservicesprovidedstatus where foreign keys can be seen and I need to join the correct tables. I'm wondering if maybe I need to use a join other than an 'inner join' to accomplish this task or confirm what I'm doing wrong with my query as the last two lines are the ones I believe are issue. Any help is appreciated, thank you in advance.

SELECT family.Id as Family_ID,  person.firstName + ' ' + person.lastName as Name, capcaseServicesProvided.idperson as Person_ID,capcaseservicesProvided.idCase as Case_ID, capcaseServicesProvided.Id as Service_ID,capStatus.Status as Case_Status, capServices.Service, capStatusService.ServiceStatus as Service_Status, 
capcaseServicesProvided.Qty, capServicesUnit.Unit, capcaseServicesProvided.Date as Service_Date, capCase.DateApplied as Case_Date

FROM capcaseServicesProvided

INNER JOIN person on capcaseServicesProvided.idperson = person.Id
INNER JOIN capServices on capcaseServicesProvided.idService = capServices.Id
INNER JOIN capServicesUnit on capcaseServicesProvided.Unit = capServicesUnit.Id
INNER JOIN capStatusService on capcaseServicesProvided.Status = capStatusService.Id
INNER JOIN family on person.idFamily = family.Id
INNER JOIN capStatus on capcaseServicesProvided.Status= capStatus.Id
INNER JOIN capCase on capCaseServicesProvided.Date = capCase.DateApplied;

How can you join capstatus second when you are using capcaseServicesProvided.Status to join on? -- Also, you are joining capCase to capcaseServicesProvided only on date... Shouldn't there be more to it than that? — SqlZim
– SqlZim, Commented Oct 2, 2017 at 22:50
It's not clear (at least to me) what you're trying to accomplish here (or what is actually wrong with your current query). Are you getting different results than you expect? — ZLK
– ZLK, Commented Oct 2, 2017 at 22:51
It's hard to help without a better description of the table structures and what you're trying to get out of them. — codemonkey
– codemonkey, Commented Oct 2, 2017 at 23:13

Paul Maxwell · Accepted Answer · 2017-10-02 23:29:48Z

I would like to suggest an approach rather than attempt a specific solution. Note we know absolutely nothing about your tables or their design so asking us to understand either requires quite a bit of information, so ultimately it is better for you to learn about JOINS and/or how to solve such dilemmas yourself.

I suggest you reduce the size of the initial query to the ABSOLUTE MINIMUM which looks (I think) like this:

SELECT
        family.Id AS Family_ID
      , person.firstName + ' ' + person.lastName AS Name
      , capcaseServicesProvided.idperson AS Person_ID
      , capcaseservicesProvided.idCase AS Case_ID
      , capcaseServicesProvided.Id AS Service_ID
FROM capcaseServicesProvided
INNER JOIN person ON capcaseServicesProvided.idperson = person.Id
INNER JOIN family ON person.idFamily = family.Id
;

Now assuming the minimum query is correct, try joining ONE EXTRA TABLE and including the columns needed from it into the select clause. Start by adding the extra table using an INNER JOIN. If you notice that some rows you want disappear change that to a LEFT JOIN

e.g.

SELECT
        family.Id AS Family_ID
      , person.firstName + ' ' + person.lastName AS Name
      , capcaseServicesProvided.idperson AS Person_ID
      , capcaseservicesProvided.idCase AS Case_ID
      , capcaseServicesProvided.Id AS Service_ID
      , capServices.Service
FROM capcaseServicesProvided
INNER JOIN person ON capcaseServicesProvided.idperson = person.Id
INNER JOIN family ON person.idFamily = family.Id
INNER JOIN capServices ON capcaseServicesProvided.idService = capServices.Id
-- or if needed --LEFT JOIN capServices ON capcaseServicesProvided.idService = capServices.Id
;

Keep repeating this cycle until you have joined all the needed tables.

I suspect you just need one or more LEFT JOINS but without sample data from each table and DDL for each table it is really hard to tell you which tables need these.

Collectives™ on Stack Overflow

SQL Inner Join Issue/Alternative Join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related