Solved

Help Filtering Join

Posted on 2013-12-23
13
239 Views
Last Modified: 2014-01-22
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
Comment
Question by:andyw27
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737493
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
 

Author Comment

by:andyw27
ID: 39737497
Nearly, but if it does'nt find OA then I want to display the TA records
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737515
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:andyw27
ID: 39737517
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737544
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39739337
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 39739794
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
 

Author Comment

by:andyw27
ID: 39758773
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 39758906
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39760178
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
 

Author Comment

by:andyw27
ID: 39762632
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39762792
Hi Andy

Can you please give us some examples with data, that helps us in understanding the problem better
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39762911
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question