SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Is this Possible In One Query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cosmarchy
Starting Member

14 Posts

Posted - 03/11/2014 :  09:41:25  Show Profile  Reply with Quote
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



Edited by - cosmarchy on 03/11/2014 10:34:01

stepson
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 03/11/2014 :  09:57:20  Show Profile  Reply with Quote

 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')





sabinWeb MCP
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 03/11/2014 :  11:05:18  Show Profile  Reply with Quote

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



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

cosmarchy
Starting Member

14 Posts

Posted - 03/11/2014 :  11:50:38  Show Profile  Reply with Quote
		  
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

	
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

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.

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)


I therefore end up with

OB_2	Q
2514	10
2820	20	
4404	30
5190	40

This isn't much good to me as I really would like to know what each widget is called. So really I want this:

Desc		Q
I am a widget	10
So am I		20
Hey, me too	30
And me		40

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:

Desc			Q
Widget container	10
Widget container	20
Widget container 	30
Widget container  	40
Go to Top of Page

Ifor
Aged Yak Warrior

587 Posts

Posted - 03/11/2014 :  12:14:23  Show Profile  Reply with Quote
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;
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
330 Posts

Posted - 03/11/2014 :  12:18:59  Show Profile  Reply with Quote
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
Go to Top of Page

cosmarchy
Starting Member

14 Posts

Posted - 03/11/2014 :  12:29:30  Show Profile  Reply with Quote
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
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 03/11/2014 :  12:55:42  Show Profile  Reply with Quote
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.

Edited by - sqlsaga on 03/11/2014 12:58:12
Go to Top of Page

cosmarchy
Starting Member

14 Posts

Posted - 03/12/2014 :  05:11:25  Show Profile  Reply with Quote
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
Go to Top of Page

Muj9
Starting Member

United Kingdom
49 Posts

Posted - 03/12/2014 :  11:30:54  Show Profile  Reply with Quote
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
Go to Top of Page

cosmarchy
Starting Member

14 Posts

Posted - 03/12/2014 :  12:25:37  Show Profile  Reply with Quote

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


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)


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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000