Is this Possible In One Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is this Possible In One Query?

Author

Topic

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-11 : 09:41:25

Hi,

I have a question regarding whether it was possible to have a single query for the following. I have two tables in the following format:

TableA
==================
[cl_1] [cl_2] [ob_1] [ob_2] [q]
353 354 82154 Part1 1
353 354 82154 Part2 2
353 354 82154 Part3 3
353 354 82154 Part4 4
353 354 82160 Part1 1
353 354 82160 Part2 2
353 354 82160 Part3 3
353 354 82160 Part4 4

TableB
==================
[ob_1] [desc]
Part1 I am a widget
Part2 So am I
Part3 Hey, me too
Part4 And me

Currently I have managed to get all of the parts in TableA by using the following:


SELECT TableA.[ob_2], TableA.[q]
FROM   TableA INNER JOIN TableB 
ON     TableA.[ob_1] = TableB.[ob_1]
WHERE (TableA.[cl_1] = 353) AND 
      (TableB.[cl_2] = 354) AND 
      (TableB.[ob_1] = 82154)

which gives me
[ob_2] [q]
Part1 1
Part2 2
Part3 3
Part4 4

what I would like to know is whether it was possible to alter this query so that I can read [ob_2] from TableB into its [desc] so that it gives me this:
[desc] [q]
I am a widget 1
So am I 2
Hey, me too 3
And me 4

Hopefully this makes sense to someone?

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-11 : 09:57:20

[code]
SELECT [desc] [q]
FROM
TableA as A
INNER JOIN TableB as B
on A.[ob_2]=B.[ob_1]
WHERE ([cl_1] = 353) AND ([cl_2] = 354) AND ([ob_1] = '82154')
[/code]

sabinWeb MCP

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-11 : 11:05:18

[code]
DECLARE @TableA TABLE
(
[cl_1] INT,
[cl_2] INT,
[ob_1] INT,
[ob_2] VARCHAR(24),
[q] INT
)
INSERT INTO @TableA VALUES(353, 354, 82154, 'Part1', 1), (353, 354, 82154, 'Part2', 2), (353, 354, 82154, 'Part3', 3), (353, 354, 82154, 'Part4', 4),
(353, 354, 82160, 'Part1', 1), (353, 354, 82160, 'Part2', 2), (353, 354, 82160, 'Part3', 3), (353, 354, 82160, 'Part4', 4)

DECLARE @TableB TABLE
([ob_1] VARCHAR(16),
[desc] VARCHAR(24)
)
INSERT INTO @TableB VALUES('Part1', 'I am a widget'), ('Part2', 'So am I'), ('Part3', 'Hey, me too'), ('Part4', 'And me')

SELECT b.[desc], a.q
FROM @TableA a
INNER JOIN @TableB b ON a.ob_2 = b.ob_1
WHERE a.cl_1 = 353 AND a.cl_2 = 354 AND a.ob_1 = 82154
[/code]

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-11 : 11:50:38

[code]
TableA
OB_1 CL_1 OB_2 CL_2 Q
82154 353 2514 354 10
82154 353 2820 354 20
82154 353 4404 354 30
82154 353 5190 354 40
[/code]
[code]
TableB
OB_1 CL_1 Desc
2514 354 I am a widget
2820 354 So am I
4404 354 Hey, me too
5190 354 And me
82154 353 Widget container
[/code]
Thanks guys for your input. However when I tried your suggestions I came up with problems and whilst trying to describe the problem to you I was getting more and more confused myself.

I've therefore tried to simplify this question by re-wording hopefully in a simpler way and for me to get my head around!!

Looking at this again we have:

A widget container which contains four different widgets. The widget container is, an assembly if you like, full of parts. The widget container (assembly) and the flavours of widgets (parts) are in tableB. The quantities (Q) of each type of widget is in tableA.

So, this query will get all of the widgets and quantities contained in the widget container.
[code]
SELECT TableA.OB_2, TableA.Q
FROM TableA INNER JOIN TableB
ON TableA.OB_1 = TableB.OB_1
WHERE (TableA.CL_1 = 353) AND
(TableA.CL_2 = 354) AND
(TableB.OB_1 = 82154)
[/code]

I therefore end up with
[code]
OB_2 Q
2514 10
2820 20
4404 30
5190 40
[/code]
This isn't much good to me as I really would like to know what each widget is called. So really I want this:
[code]
Desc Q
I am a widget 10
So am I 20
Hey, me too 30
And me 40
[/code]
The problem here is that the Desc is contained in tableB. If I use the previous suggestions, I end up with results looking like this:
[code]
Desc Q
Widget container 10
Widget container 20
Widget container 30
Widget container 40
[/code]

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-03-11 : 12:14:23

The table design is poor.


-- *** Test Data in Consumable Format ***
-- You should provide this!
CREATE TABLE #TableA
(
	OB_1 int NOT NULL
	,CL_1 int NOT NULL
	,OB_2 int NOT NULL
	,CL_2 int NOT NULL
	,Q int NOT NULL
);
INSERT INTO #TableA
VALUES (82154, 353, 2514, 354, 10)
	,(82154, 353, 2820, 354, 20)
	,(82154, 353, 4404, 354, 30)
	,(82154, 353, 5190, 354, 40);

CREATE TABLE #TableB
(
	OB_1 int NOT NULL
	,CL_1 int NOT NULL
	,[Desc] varchar(20) NOT NULL
);
INSERT INTO #TableB
VALUES (2514, 354, 'I am a widget')
	,(2820, 354, 'So am I')
	,(4404, 354, 'Hey, me too')
	,(5190, 354, 'And me')
	,(82154, 353, 'Widget container');
-- *** End Test Data in Consumable Format ***

SELECT B.[Desc], A.Q
FROM #TableA A
	JOIN #TableB B
		ON A.OB_2 = B.OB_1
			AND A.CL_2 = B.CL_1
ORDER BY A.OB_2, A.CL_2;

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-03-11 : 12:18:59

I think there is a problem with your code as if you say TableB.OB_1 = 82154 as above that you will only get one DESC as writen. I believe your join should be on different columns.

djj

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-11 : 12:29:30

quote:
Originally posted by Ifor

The table design is poor.

I couldn't agree more, however I inherited this mess from a 'professional' PDM company and has been butchered by other supposed professionals in the mean time!!

All I'm trying to do is piece it back together

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-11 : 12:55:42

You are joining OB_1 with OB_1 but you are supposed to do it with OB_2 and OB_1... that's where the problem in your query lies..

try the code I gave you...

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-12 : 05:11:25

quote:
Originally posted by sqlsaga

You are joining OB_1 with OB_1 but you are supposed to do it with OB_2 and OB_1... that's where the problem in your query lies..

try the code I gave you...

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Ah yes, missed that. I clearly had a typo moment

Thanks very much for this

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-03-12 : 11:30:54

drop table TableA
create table TableA (
[cl_1] int,
[cl_2] int,
[ob_1] int,
[ob_2] varchar(10),
[q] smallint )

insert into TableA([cl_1],[cl_2],[ob_1],[ob_2],[q])
values
(353,354,82154,'Part1',1),
(353,354,82154,'Part2',2),
(353,354,82154,'Part3',3),
(353,354,82154,'Part4',4),
(353,354,82160,'Part1',1),
(353,354,82160,'Part2',2),
(353,354,82160,'Part3',3),
(353,354,82160,'Part4',4)

drop table TableB
create table TableB (
[ob_1] varchar(10),
[desc] varchar(8000) )

insert into TableB([ob_1],[desc])
values
('Part1','I am a widget'),
('Part2','So am I'),
('Part3','Hey, me too'),
('Part4','And me')

select * from TableA
select * from TableB

select b.[desc],a.q from TableA a
left join TableB b on a.ob_2 = b.ob_1

cosmarchy
Starting Member

14 Posts

Posted - 2014-03-12 : 12:25:37

[code]
TableB
OB_1 CL_1 Desc
2514 354 I am a widget
2820 354 So am I
4404 354 Hey, me too
5190 354 And me
82154 353 Widget container
[/code]
[code]
SELECT TableB.Desc, TableA.q
FROM TableA INNER JOIN
TableB ON TableA.ob_2 = TableB.ob_1
WHERE (TableA.cl_1 = 353) AND (TableA.ob_1 = 82154)
[/code]

Thinking about this and taking this a little further, would it be possible to change the where clause so that instead of (TableA.ob_1 = 82154) you use TableB.Desc?

In essence replacing ob_1 which is 82154 in both TableA & Table B with 'Widget Container' (the desc for OB_1 in table B) in the where clause?

It would be useful to have a meaningful name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources