Solved

Controlling JOIN Hierarchy in SQL2000 without CTE

Posted on 2008-06-23
6
609 Views
Last Modified: 2010-10-05
I am joining on a set of data in SQL2000, and would like to control the hierarchy of which JOIN is used when given a choice of matches.

I can assign a numeric value to the data set, so I only need to JOIN on the lowest/highest number available.

So for example on the below if joining on the middle column with B, I would want to return 250, not another value.

1     A    150
1     B     250
2     A     330
2     B     558
3     A     554
3     B     447
3     C     557
0
Comment
Question by:rickchild
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
possibly just use MIN or MAX in your select?
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 50 total points
Comment Utility
SELECT P1 FROM T1 INNER JOIN (SELECT ColB, MIN(ColC) FROM Table2 GROUP BY ColB) T2 ON T1.ColB = T2.ColB
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 200 total points
Comment Utility
i used TOP instead of MIN/MAX.  i'm not sure if this is what you're looking for:

create table test (one int, two char(1), three int)
insert test
select  1,'A',150
union
select 1,'B',250
union
select 2,'A',330
union
select 2,'B',558
union
select 3,'A',554
union
select 3,'B',447
union
select 3,'C',557

select * from test

select TOP 1 a.one,a.two,a.three from test a inner join test b
on a.one = b.one
where a.two = 'b'
group by a.one,a.two,a.three
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 13

Author Comment

by:rickchild
Comment Utility
lludden: I will try youtr solution now

dbaSQL: Your solutions is fairly similar to mine, but the problem I am having is that I am trying to use this query to join onto another table, and I seem unable to pass in the 'b' from the first table.

I either need to pass in the Code to the WHERE, or be able to work on the hierarchy when I join it to the main table


LEFT JOIN

(

    SELECT TOP 1 * FROM

    (

	SELECT * FROM

	(

		SELECT 1 as Orders, Entity, Code, Description from revenuereporting..tbl_DIMProduct

		WHERE Entity = 'ASL' and Code = L.[Product Name]

		UNION

		SELECT 2 as Orders, Entity, Code, Description from revenuereporting..tbl_DIMProduct

		WHERE Entity = 'Inc' and Code =  L.[Product Name]

	) AS FFFF

    ) as FF

) as F

ON L.[Product Name] = F.Code

Open in new window

0
 
LVL 13

Author Comment

by:rickchild
Comment Utility
PS.  This will work if I pass in the Code as a variable, but I am in SSRS so struggling to get that to work.
0
 
LVL 13

Author Comment

by:rickchild
Comment Utility
Think I may have sussed this now using a subquery generated column, rather than joining on a subquery.

The hierarchy just depends on the order of the UNION, which is ok for this case.
select a.*, (

		select top 1 Description FROM

		(

		SELECT Code, Description

		FROM revenuereporting..tbl_DIMProduct

		WHERE Entity = 'ASL' and Code = a.Code

		UNION ALL 

		SELECT Code, Description

		FROM revenuereporting..tbl_DIMProduct

		WHERE Entity = b.Entity and Code = a.Code

		) as Description

	) as Description

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

15 Experts available now in Live!

Get 1:1 Help Now