Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Help Filtering Join

I have the following join:

LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = 'TA' OR ‘OA’

How can this be further enhanced so that if it finds records with TA and OA then it only displays the OA record ?
0
andyw27
Asked:
andyw27
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Pratima PharandeCommented:
if it finds records with TA and OA then it only displays the OA record ?

do you mean you want to display only OA records ? then Remove TA
then
LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = ‘OA’

Or

 LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = 'TA' OR ‘OA’

where org_persons.role = ‘OA’
0
 
andyw27Author Commented:
Nearly, but if it does'nt find OA then I want to display the TA records
0
 
Pratima PharandeCommented:
IF EXISTS ( Select * from tablename
LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = ‘OA’
)

 Select * from tablename
LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = ‘OA’

ELSE

    Select * from tablename
LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = ‘TA’
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
andyw27Author Commented:
thanks looks good, can you elaborate how this can be inegrated into my existing statement please:

ALTER PROCEDURE [dbo].[Test_SP]
(
@person_id INT
)
AS
BEGIN
SELECT
items.item_id
FROM
item_manuf_refs
      INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
      LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
      LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
      LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
      LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
      LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
      LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = 'TA'
      LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
WHERE persons.person_id = @person_id
GROUP BY
items.item_id,
items.item_number

END

Open in new window

0
 
Pratima PharandeCommented:
ALTER PROCEDURE [dbo].[Test_SP]
(
@person_id INT
)
AS
BEGIN




IF EXISTS ( SELECT
items.item_id
FROM
item_manuf_refs
      INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
      LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
      LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
      LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
      LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
      LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
      LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = 'OA'
      LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
WHERE persons.person_id = @person_id
)

SELECT
items.item_id
FROM
item_manuf_refs
      INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
      LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
      LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
      LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
      LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
      LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
      LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = 'OA'
      LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
WHERE persons.person_id = @person_id
GROUP BY
items.item_id,
items.item_number
ELSE

SELECT
items.item_id
FROM
item_manuf_refs
      INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
      LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
      LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
      LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
      LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
      LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
      LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role = 'TA'
      LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
WHERE persons.person_id = @person_id
GROUP BY
items.item_id,
items.item_number



END
0
 
Surendra NathTechnology LeadCommented:
You can write it in an better way as below

ALTER PROCEDURE [dbo].[Test_SP]
(
@person_id INT
)
AS
BEGIN
;WITH CTE AS
(
SELECT
items.item_id,ROW_NUMBER() OVER(partition by items.item_id order by CASE org_persons.role WHEN ' OA' THEN 1 WHEN 'TA' THEN 2 ELSE 3 END ) RN  
FROM
item_manuf_refs
      INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
      LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
      LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
      LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
      LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
      LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
      LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role IN ('TA','OA')
      LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
WHERE persons.person_id = @person_id
GROUP BY
items.item_id,
items.item_number
)
SELECT item_id 
FROM CTE  
WHERE RN = 1
END

Open in new window

0
 
QlemoC++ DeveloperCommented:
Re: http:#a39739337:
In this special case, instead of using an ORDER BY CASE you could also just ORDER BY org_persons.role in the partition by clause, because 'OA' is always coming before 'TA'.
And you don't need a CTE, but that is only a nuance and a matter of taste. Putting both together:
ALTER PROCEDURE [dbo].[Test_SP]
(
@person_id INT
)
AS
BEGIN
SELECT item_id 
FROM (
  SELECT
  items.item_id,ROW_NUMBER() OVER(partition by items.item_id order by   org_persons.role) RN  
  FROM
  item_manuf_refs
        INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
        LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
        LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
        LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
        LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
        LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
        LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role IN ('TA','OA')
        LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
  WHERE persons.person_id = @person_id
  GROUP BY
    items.item_id,
    items.item_number
) data
WHERE RN = 1
END

Open in new window

0
 
andyw27Author Commented:
Hello - Thanks everyone for your suggestions:

Qlemo - I've tried you suggestions however that produces the following two errors:

Column 'org_persons.role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'org_persons.role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Surendra, your suggestion gives these errors?

Column 'org_persons.role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'org_persons.role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'org_persons.role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'org_persons.role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
QlemoC++ DeveloperCommented:
Didn't see that earlier:
Your initial query is "wrong", and we just extended on that. You select items.item_id, but group by items.item_id and items.item_number, leading to the same item id appearing more than once if there are more than one item numbers for it. That doesn't make sense. Maybe you omitted something in http:#a39737517 when posting on EE ...
On the other hand, using GROUP BY together with ROW_NUMBER() OVER is nonsense in most cases, too.
ALTER PROCEDURE [dbo].[Test_SP]
(
@person_id INT
)
AS
BEGIN
SELECT item_id 
FROM (
  SELECT
  items.item_id,ROW_NUMBER() OVER(partition by items.item_id order by   org_persons.role) RN  
  FROM
  item_manuf_refs
        INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
        LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
        LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
        LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
        LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
        LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
        LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role IN ('TA','OA')
        LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
  WHERE persons.person_id = @person_id
) data
WHERE RN = 1
END

Open in new window

0
 
Surendra NathTechnology LeadCommented:
try this out -- corrected

ALTER PROCEDURE [dbo].[Test_SP]
(
@person_id INT
)
AS
BEGIN
;WITH CTE AS
(
SELECT
items.item_id,ROW_NUMBER() OVER(partition by items.item_id order by CASE org_persons.role WHEN ' OA' THEN 1 WHEN 'TA' THEN 2 ELSE 3 END ) RN  
FROM
item_manuf_refs
      INNER JOIN items on item_manuf_refs.item_id = items.item_id and items.config_item = 'N'
      LEFT OUTER JOIN organisations on item_manuf_refs.organisation_id = organisations.organisation_id
      LEFT OUTER JOIN product_structures on items.item_id = product_structures.component_id
      LEFT OUTER JOIN items as pitems on product_structures.parent_id = pitems.item_id
      LEFT OUTER JOIN responsibilities on pitems.item_id = responsibilities.object_id and responsibilities.object_type = '1'
      LEFT OUTER JOIN organisations as pforganisations on responsibilities.organisation_id = pforganisations.organisation_id
      LEFT OUTER JOIN org_persons ON pforganisations.organisation_id = org_persons.organisation_id and org_persons.role IN ('TA','OA')
      LEFT OUTER JOIN persons on org_persons.person_id = persons.person_id
WHERE persons.person_id = @person_id
GROUP BY
items.item_id,
items.item_number,org_persons.role 
)
SELECT item_id 
FROM CTE  
WHERE RN = 1
END

Open in new window

0
 
andyw27Author Commented:
Hi Surendra - Thanks for the updated code, it now compiles fine.

I think its almost there, a couple of things, firstly I've now been asked to include a third variable (SO)
The rule there would be all records would have a TA, however each record could have EITHER  a OA or SO record, in which case it should display which ever?

Secondly, this code works fine assuming that the TA and OA have the same person_id, although it'll not work when the TA = person_id(5) and the OA = person_id(8) for example - where the TA or OA / SO roles are assigned to different people
0
 
Surendra NathTechnology LeadCommented:
Hi Andy

Can you please give us some examples with data, that helps us in understanding the problem better
0
 
QlemoC++ DeveloperCommented:
Surendra Ganti's code cannot work exactly, as there is a typo in the case statement, reversing the sort order. And as I have told, that is superfluous anyway, as is the GROUP BY.

Further, nothing should change with my code. If you include the new role to check for in the IN clause of the second last join, it should work, with a fixed preference of OA / SO / TA. The person_id is not used anywhere but by the join, and so isn't a grouping restriction.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now