Query Help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-18 : 11:34:33

[code]

I need a query to pull the data from the tables.
Thanks for your help in advance

TableA
-------
AID ACD ADesc
-- ---- -----------
3 Prd1 03/31/2008
4 Prd2 06/30/2008
5 Prd3 09/30/2008
6 Prd4 12/31/2008

TableB
-------

BID BName VCD
-- ----- ---
8 CPrd 2
9 CPrdYr 2008

Results:

ACD ADesc
----- --------------
Prd2 06/30/2008

Since in the tableB BName=CPrd and VCD=2 and BName=CPrdYr and VCD=2008 then it should pull the prd2,06/30/2008..
If the value of BName=CPrd and VCD=3 and BName=CPrdYr and VCD=2008 then it should pull the Prd3, 09/30/2008

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 11:56:54

Will you be having only Prd & PrdYr items in TableB?Cant understand why your tables are designed like this.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-18 : 13:23:12

I have other items in tableb since i am concerned only with
Prd & PrdYr items..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:30:26

[code]SELECT *
FROM TableA a
WHERE a.ACD IN (SELECT REPLACE(b1.BName + CAST(VCD as varchar(5)),'C','') FROM TableB WHERE BName='CPrd')b1
AND YEAR(a.ADesc) IN (SELECT VCD FROM TableB WHERE BName='CPrdYr')
[/code]

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-18 : 14:27:42

Thanks visakh !!
But i got below error!!

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'B1'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 14:32:09

copy paste error

SELECT *
FROM TableA a
WHERE a.ACD IN  (SELECT REPLACE(b1.BName + CAST(VCD as varchar(5)),'C','') FROM TableB WHERE BName='CPrd')b1
AND YEAR(a.ADesc) IN (SELECT VCD FROM TableB WHERE BName='CPrdYr')

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-09-18 : 15:23:12

Thanks it works!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources