Go Premium for a chance to win a PS4. Enter to Win

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

Controlling JOIN Hierarchy in SQL2000 without CTE

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
rickchild
Asked:
rickchild
  • 3
  • 2
2 Solutions
 
dbaSQLCommented:
possibly just use MIN or MAX in your select?
0
 
lluddenCommented:
SELECT P1 FROM T1 INNER JOIN (SELECT ColB, MIN(ColC) FROM Table2 GROUP BY ColB) T2 ON T1.ColB = T2.ColB
0
 
dbaSQLCommented:
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
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.

 
rickchildAuthor Commented:
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
 
rickchildAuthor Commented:
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
 
rickchildAuthor Commented:
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

Independent Software Vendors: 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!

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