September 10, 2013 at 2:24 pm
hello all,
Here's what I got:
Table 1 structure:
Id int,
controlIDvarchar(13)
data:
1,1000
2,1001
3,1002
4,1003
5,1004
And so on
table 2:
idint,
controlstartvarchar(13),
controlendvarchar(13),
boxint
data:
1,1000,1001,1
2,1002,1003,2
3,1004,1005,3
and so on
How would I write a query to join the 2 tables that would display info like this:
1000,1
1001,1
1002,2
1003,2
1004,3
1005,3
Thanks for all the help!
September 10, 2013 at 2:44 pm
Do you even need table1?
I have this:
Sample data (table1 included):
USE tempdb
GO
IF OBJECT_ID('tempdb..table1') IS NOT NULL DROP TABLE table1;
IF OBJECT_ID('tempdb..table2') IS NOT NULL DROP TABLE table2;
CREATE TABLE table1
(id int primary key,
controlID varchar(13) not null);
CREATE TABLE table2
(id int primary key,
controlstart varchar(13) not null,
controlend varchar(13) not null,
box int not null);
GO
INSERT table1
SELECT 1,1000 UNION
SELECT 2,1001 UNION
SELECT 3,1002 UNION
SELECT 4,1003 UNION
SELECT 5,1004;
INSERT table2
SELECT 1,1000,1001,1 UNION
SELECT 2,1002,1003,2 UNION
SELECT 3,1004,1005,3
Query:
SELECT controlstart, box
FROM table2
UNION ALL
SELECT controlend, box
FROM table2
order by controlstart
-- Itzik Ben-Gan 2001
September 10, 2013 at 4:07 pm
thanks Alan,
what if the controlstart and controlend range is larger, ex: 1000 to 1025? Then I can't use the union all correct?
September 10, 2013 at 4:40 pm
You should still be able to use UNION ALL in that scenario. Below is some code to create sample data similar to what I think you are dealing with. The I changed the ORDER BY clause to sort by box; this will show you that the query is working with a larger range.
-- (1) CREATE SAMPLE DATA
USE tempdb
GO
IF OBJECT_ID('tempdb..table2') IS NOT NULL DROP TABLE table2;
CREATE TABLE table2
(id int primary key,
controlstart varchar(13) not null,
controlend varchar(13) not null,
box int not null);
WITH tally(n) AS
(SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns),
prep(id,controlstart,box) AS
(SELECT n,n+1000,n
FROM tally
WHERE n<20)
INSERT INTO table2
SELECTid,
controlstart,
controlstart+(1.0+floor(25*RAND(convert(varbinary,newid())))) controlend,
box
FROM prep;
-- (2) LOOK AT THE SOLUTION:
--your sample data:
SELECT * FROM table2
--the UNION solution:
SELECT controlstart, box
FROM table2
UNION ALL
SELECT controlend, box
FROM table2
order by box
-- Itzik Ben-Gan 2001
September 10, 2013 at 6:21 pm
You might want to consider using the CROSS APPLY VALUES approach to UNPIVOT (see my signature links) and do it this way (works on SQL 2008 and uses Alan's set up data):
SELECT b.[control], b.box
FROM table2 a
CROSS APPLY (VALUES (controlstart, box),(controlend, box)) b([control], box);
This results in a single clustered index scan, whereas Alan's method uses 2.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply