Solved

Help Filtering Join

Posted on 2013-12-23
13
233 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 68

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 68

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 68

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now