June 11, 2012 at 11:46 pm
Hi All,
I want to retrieve two table based on the following condition
1) m.ids = '1800m380000ioet75tq0000000'and m2.portfolioname = 'CI Project'
2)M.ids = '1800m380000ioet75tq0000000'
3)m2.portfolioname = 'CI Project'
In the same query,User can pass both details to fetch the redord or he can use only one details in the single query
The query details Listed Below
Create Table MyTable ( Ids Int,Name Varchar(50),Varchar(50),OwnerId INt,baseLineDate datetime)
Create table Mytable2 ( ProcessId Int, PortfolioName Varchar(50),Ids Int, Entity Int)
Insert into MyTable ( '1800m380000ioet75tq0000000','FIFA','52698745896', Getdate())
Insert into MyTable ( '1800m380000ioet75tq0000001','MAX','458745896', Getdate())
Insert into MyTable ( '1800m380000ioet75tq0000002','JET','745896745896', Getdate())
Insert into MyTable ( '1800m380000ioet75tq0000003','Sungio','5260005896', Getdate())
Insert into Mytable2 ( '123','CI Project','1800m380000ioet75tq0000000','569')
Insert into Mytable2 ( '123','BI Project','1800m380000ioet75tq0000000','569')
Insert into Mytable2 ( '123','Gate Project','1800m380000ioet75tq0000002','569')
Insert into Mytable2 ( '123','Folder','1800m380000ioet75tq0000000','569')
Insert into Mytable2 ( '123','CI Project','1800m380000ioet75tq0000003','569')
Select m.ids,m.name,m.ownerId,m.baselinetime,m2.processId,m2.PortfolioName
From Mytabe M Join Mytable2 m2 On m.ids = m2.ids and m2.portfolioname = 'CI Project'
June 12, 2012 at 12:43 am
A few things:
1. Please test your DDL and Sample Data Scripts before you post it. Its not that hard to do it. Its as simple as CTRL C + CTRL V and F5.
2. Your DDL Script has Errors please check it and post a working script.
3. Why do you want to use a Case Statement when your query is getting you the results without any Conditional Logic?
I really don't see what the problem is in this post. Your JOIN Query seems to be working fine. Are you expecting a different Result-Set?
If so, then please post the Expected Result Set so that people can get to know what you are Expecting.
June 12, 2012 at 12:53 am
I think you are looking for a "Catch-All" query
Have a look at the link below which gives some ways to do it
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 12, 2012 at 12:58 am
Thanks For the Reply...
1) The User can pass only Ids Or User can pass only Portfolio name
2) When he pass portfolio name and dont pass Ids then ids take as Null and query results 0 Value But Still I need to retrieve the records matching with portfolio name when he don pass the Ids
3) When he pass both ids And Portfolio name then it retrieving the Records
Prob :
When user passes only the Ids Or PortfolioNAme it retrieving Zero record
June 12, 2012 at 1:01 am
I have to second vinu512's comments. We shouldn't be debugging your sample scripts.
That said, I think this is what you are trying to achieve:
/*
Drop Table Mytable
Drop Table Mytable2
go
*/
/*******************************************************************
I modified your column data types so the script will compile
*******************************************************************/
Create Table MyTable ( Ids Varchar(50), Name Varchar(50),OwnerId VARCHAR(30),baseLineDate datetime)
Create table Mytable2 ( ProcessId Int, PortfolioName Varchar(50),Ids Varchar(50), Entity Int)
go
/*******************************************************************
Questions to answer:
Is Ids in MyTable non nullable? A primary key? Unique?
Is Ids in MyTable2 non nullable? A foreign key to Mytable1 (Ids)?
*******************************************************************/
Insert into MyTable Values ( '1800m380000ioet75tq0000000','FIFA','52698745896', Getdate())
Insert into MyTable Values ( '1800m380000ioet75tq0000001','MAX','458745896', Getdate())
Insert into MyTable Values ( '1800m380000ioet75tq0000002','JET','745896745896', Getdate())
Insert into MyTable Values ( '1800m380000ioet75tq0000003','Sungio','5260005896', Getdate())
Insert into Mytable2 Values ( 123,'CI Project','1800m380000ioet75tq0000000',569)
Insert into Mytable2 Values ( 123,'BI Project','1800m380000ioet75tq0000000',569)
Insert into Mytable2 Values ( 123,'Gate Project','1800m380000ioet75tq0000002',569)
Insert into Mytable2 Values ( 123,'Folder','1800m380000ioet75tq0000000',569)
Insert into Mytable2 Values ( 123,'CI Project','1800m380000ioet75tq0000003',569)
Declare
@portfolio Varchar(50),
@ids Varchar(50)
/***********************************************************************
Experiment with commenting out the Set statements below
************************************************************************/
Set @portfolio = 'CI Project'
Set @ids = '1800m380000ioet75tq0000000'
Select
m.ids,
m.name,
m.ownerId,
m.baselineDate,
m2.processId,
m2.PortfolioName
From Mytable M
Join Mytable2 m2
On m.ids = m2.ids
/*************************************************************************
COALESCE returns the first value that is not null
If @portfolio exists, join portiofolio name in Mytable2 to @portfolio
Else join the portiofolio name against itself (like a WHERE 1=1)
If @ids exists, join ids in Mytable1 to @ids
Else join the ids value against itself
**************************************************************************/
Where m2.portfolioname = COALESCE(@portfolio, m2.portfolioname)
And m.Ids = COALESCE(@ids, m.Ids)
June 12, 2012 at 1:08 am
Yeah Something Like the
This is First Option:
Select m.ids,m.name,m.ownerId,m.baselinetime,m2.processId,m2.PortfolioName
From Mytabe M Join Mytable2 m2 On m.ids = '1800m380000ioet75tq0000000'
and m2.portfolioname = 'CI Project'
This is second Option:
When User Donts Pass m.ids,it becomes Null in the Same above Query , Query Result NoRecords
So i want to retrieve the records Based on the m2.portfolioName
M.ids Should not Conside in the Query
June 12, 2012 at 1:30 am
Kingston Dhasian (6/12/2012)
I think you are looking for a "Catch-All" queryHave a look at the link below which gives some ways to do it
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
+1.
After listening to all your requirements even I think that the link mentioned by Kingston Dhasian would have everything you need. You can supply all the conditions using a Corelation of ANDs and ORs.
Please checkout the above quoted link. It will help you.
June 12, 2012 at 1:34 am
It is in the Stored Procedure Way But i want the result in the Query based In Where Condition
June 12, 2012 at 3:24 am
Creating a script out of a stored procedure is not that difficult
You can replace the parameters in the stored procedures with variables and assign these variables some values as below
DECLARE@ids INT, @portfolioname VARCHAR(50)
SET@ids = NULL
SET@portfolioname = 'CI Project'
DECLARE @sql NVARCHAR(2000), @Where NVARCHAR(1000) = ''
SET @sql= ' SELECTm.ids, m.name, m.ownerId, m.baselinetime, m2.processId, m2.PortfolioName '
+ ' FROMMytabe m '
+ ' INNER JOINMytable2 m2 '
+ ' ONm.ids = m2.ids '
IF @ids is not null
SET @Where = @Where + 'AND m.ids = @ids '
IF @OrderID is not null
SET @Where = @Where + 'AND m2.PortfolioName = @portfolioname '
IF LEN(@Where) > 0
SET @sql = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
EXEC sp_executesql @sSQL,
N'@_ids INT, @_portfolioname VARCHAR(50)',
@_ids = @ids, @_portfolioname = @portfolioname
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 12, 2012 at 3:49 am
Thanks
Its Working fine Now...
I used below syntax
Declare @portfolioName Varchar(8000),
@ParentWorkids Varchar(50)
Set @portfolioName = 'CI Projects'
set @ParentWorkids = '1801a2g0000ih0vl2abg000000'
(vp.portfolio_name = @portfolioName Or @portfolioName Is nuLL)
And (vh.parent_work_id = @ParentWorkids Or @ParentWorkids Is Null)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply