Solved

Controlling JOIN Hierarchy in SQL2000 without CTE

Posted on 2008-06-23
6
646 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
ID: 21845402
possibly just use MIN or MAX in your select?
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 50 total points
ID: 21845562
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
ID: 21845573
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 13

Author Comment

by:rickchild
ID: 21863459
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
ID: 21863551
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
ID: 21863912
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 36
Sql Server group by 10 29
insert wont work in SQL 14 22
Need help constructing a conditional update query 16 47
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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