|
I don't understand the LEFT JOIN between A.bID and C.clientID. You infer that the second query will always get the answer you are looking for so, an INNER JOIN is appropriate. With further examiniation, only B.Detail may be NULL if you have a valid aID. It is usually easier to let the client handle the NULL column than two types of data structures.
The query below will always have data in Client. Detail may or may not be NULL. This is assuming that aID is valid.
SELECT
C.name AS Client,
B.Detail AS Detail
FROM
tableC C
INNER JOIN
tableA A
ON (C.clientID = A.bID)
LEFT JOIN
tableB B
ON (C.clientID = B.clientID)
WHERE
aID = '100'
|
|
|
|
|
You can use the Case Statement for this:
Declare a variable @Client Varchar(100) = NULL, now put
Select C.name as Client , @Client = C.name,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
Now check in Case like this::
CASE WHEN @Client IS NULL THEN
... Put Your Another Select Query Here ...
END
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Hi There
You can do a rowcount and if a count on the no of rows is zero, then Select .....
For example
IF EXISTS (SELECT COUNT(*) FROM tablename)
BEGIN
SELECT ..... FROM TABLENAME
END
-- if no data then do... that is if row count is zero
ELSE
SELECT ..... FROM TABLENAME
|
|
|
|
|
Another method is to use a variable to hold the number of records and based on the number of records, decide which sql statement to execute.
For example
--PUR ROW COUNT IN A VARIABLE
DECLARE @rowcount INT
SELECT @rowcount = COUNT(*) FROM tablename
IF @rowcount >= 27 --SPECIFY the number expected
BEGIN
PRINT 'TEST-1' --OR SELECT STATEMENT
END
ELSE
BEGIN
PRINT 'TEST-2' -- OR SELECT STATEMENT
END
hope this helps.
Cheers
|
|
|
|
|
TRY THIS
Declare @rowcount int
select @rowcount = (Select count(*) from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= 100)
if @rowcount > 1
begin
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= 100
else
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= 100
end
Hope this will help 
|
|
|
|
|
I have one SQL Table with 2 columns as below
Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL
Now There are 2 Records in Table
1-1-2012, 5
1-3-2012, 7
Output of Result should be as below if i give date range StartDate as 1-1-2012 and EndDate as 1-15-2012
1-1-2012 5
1-2-2012 0
1-3-2012 7
1-4-2012 0
1-5-2012 0
1-6-2012 0
.
.
.
1-15-2012 0
Means Query should return all the dates of given range with Quantity and if no entry in Table then 0 for Quantity.
How to Do it? Please suggest with Query
|
|
|
|
|
You could accomplish this with a script rather than a single query. I have not run this and if you are allergic to table vars then stop reading. Presuming you have a begin date that is less than or equal to the end date you can proceed like this...
declare @loopDate as datetime
declare @dateRange as table ( aDate datetime )
set @loopDate = @beginDate
insert into @dateRange (aDate) values (@loopDate )
while @loopDate < @endDate
begin
Dateadd(day, @loopDate, 1)
insert into @dateRange (aDate) values (@loopDate )
end
select aDate, isnull(quantity, 0)
from
yourTable a
left outer join @dateRange b on
b.aDate = a.Productiondate
I have not tested this. There might be a syntax flaw. Flame away. - Bill O'Brien
|
|
|
|
|
Usually someone will say use a generated (permanent) table, however your solution seems logical.
P.S. I have not tested it either. I hope it will work for rahulpatel112.
|
|
|
|
|
It is amazing what you can find in these forums if you look. Bob has got it right and if you had even looked just a little further you would have found this answer[^] 4 threads down!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you need it to be a query and not a Stored Procedure you can try this:
WITH DateRange(Date) AS
(
SELECT @StartDate Date
UNION ALL
SELECT DATEADD(day, 1, Date) Date
FROM DateRange
WHERE Date < @EndDate
)
SELECT dr.date
,case mt.quantity when null then 0 else mt.quantity end quantity
FROM DateRange dr
LEFT OUTER JOIN MyTable mt
ON dr.date = mt.productiondate I haven't tested it, but you'll get the idea.
|
|
|
|
|
DECLARE @TargetDate SMALLDATETIME
DECLARE @InitialDate SMALLDATETIME
SET @InitialDate = '06-19-2012'
SET @TargetDate = '06-25-2012'
WHILE @InitialDate <= @TargetDate
BEGIN
PRINT @InitialDate
SELECT @InitialDate = DATEADD( D, 1, @InitialDate )
END
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
i need to make a duplicate copy of an existing database with a new name in sql server and this has to be done using QUERY ONLY,
i tried to take a backup and restore but that restores with the same name...
|
|
|
|
|
|
thank you that worked for an extent, but still finding a bit of problem when trying to restore the database from sql query using c# in asp .net
|
|
|
|
|
when i try to restore it says the mdf file location is in correct but when i try to run the same query in sql it works perfectly, any idea on what iam doing wrong....
also how do i know the logical name of the db, since i will be restoring it in the runtime i cant afford it to come randomly, when i tested sometimes it comes as master and mastlog and sometimes it comes as the db name i have backed up...
|
|
|
|
|
nvarunkumar wrote: QUERY ONLY
Why? That is a rather inefficient approach...
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
because i have backup and restore at runtime in asp .net, so if i get the query i can put it in sqlcommand in asp...
|
|
|
|
|
I am attempting to put a database into the Data-Tier application model, and the wizards (being MS Wizards) are painful and lack error reporting.
So, I'm trying to export my database as scripts, and import the scripts into the DAC model.
Here's the problem: over the years, our tables have undergone modifications. What I don't get is why SQL Server seems to have to regenerate a table from script in sequence (here's a sample)
CREATE TABLE [dbo].[SampleUserMaster](
[SampleUserMasterKey] [dbo].[udt_keyfield] IDENTITY(1,1) NOT NULL,
[UserKey] [dbo].[udt_keyfield] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NULL,
[SampleKey] [dbo].[udt_keyfield] NOT NULL,
[UserMasterKey] [dbo].[udt_keyfield] NULL,
[AddressRefusedUnknownFlag] [dbo].[udt_logical] NULL,
[SampleCompletedFlag] [dbo].[udt_logical] NULL,
[SampleCompletedDate] [datetime] NULL,
[IncomingSampleActionDate] [datetime] NULL,
[IncomingSampleActionCodesKey] [dbo].[udt_keyfield] NULL,
[IncomingSampleActionCode] [dbo].[udt_sharedcode] NULL,
[IncomingSampleActionDescription] [dbo].[udt_sharedcodedescription] NULL,
[LockSession] [uniqueidentifier] NULL,
[LockTime] [datetime] NULL,
[LockVersion] [timestamp] NOT NULL,
[WWClearanceCodeKey] [dbo].[udt_keyfield] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCode] [dbo].[udt_sharedcode] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceDescription] [dbo].[udt_sharedcodedescription] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCodeDateTime] [datetime] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [WWClearanceCodeAuthorityInvolvedKey] [dbo].[udt_keyfield] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [IsElligibleWorkRelease] [dbo].[udt_logical] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [DepartmentKey] [dbo].[udt_keyfield] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [CreatedDateTime] [datetime] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [ModifiedDateTime] [datetime] NOT NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [VisitorListLastChangedDateTime] [datetime] NULL
ALTER TABLE [dbo].[SampleUserMaster] ADD [IsSealed] [bit] NULL
CONSTRAINT [PK_SampleUserMasterkey] PRIMARY KEY CLUSTERED
(
[SampleUserMasterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Note the alter table statements for each column added over time - why is this???!?!?!
I have 20,000+ tables, so obviously I can't manually edit these to move the added columns into the definition itself - is there a way to make it generate a table as a table without all the alter table commands for added columns? Any ideas?
This fails Data-Tier Application importing; it will not run any of the alter table statements, thus these columns never make the model, thus the stored procs and views fail whereas they reference missing columns.
Is there a way to generate a CREATE TABLE statement with ALL Columns in is? I don't get the way it does it now... Thanks in advance.
I've exported the DB, and restored it and it still keeps the full history of alters to add columns, and generates them as individual statements....
Think of how stupid the average person is, and realize half of them are stupider than that. - George Carlin
|
|
|
|
|
Try using the SqlPubWiz to generate the scripts.
P.S. Oh, and there's an article on here that generates scripts.
|
|
|
|
|
Thanks for the tip; trying it now. (SQL Pub Wiz)
Think of how stupid the average person is, and realize half of them are stupider than that. - George Carlin
|
|
|
|
|
I'm curious, never having used UDTs, what requires you to use so many and what is in the types.
Do you think the UDTs affect performance, normally I would not even ask but you seem to have a hoarde of them!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below
CREATE TABLE IF NOT EXISTS `history` (
`history_id` int(5) NOT NULL AUTO_INCREMENT,
`upload_id` int(5) NOT NULL,
`user_id` int(5) NOT NULL,
`datetime` date NOT NULL,
`location` varchar(200) NOT NULL,
PRIMARY KEY (`history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `history`
--
INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES
(1, 1, 5, '2012-01-03', 'home'),
(2, 2, 5, '2012-06-20', 'Office'),
(3, 3, 5, '2012-03-06', 'office'),
(4, 4, 5, '2012-06-05', 'Home');
-- --------------------------------------------------------
--
-- Table structure for table `uploads`
--
CREATE TABLE IF NOT EXISTS `uploads` (
`upload_id` int(5) NOT NULL AUTO_INCREMENT,
`user_id` int(5) NOT NULL,
`file_path` varchar(200) NOT NULL,
`picture_path` varchar(200) NOT NULL,
PRIMARY KEY (`upload_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `uploads`
--
INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES
(1, 5, 'hope', 'star'),
(2, 5, 'sky', 'mountain'),
(3, 5, 'smoking', 'habbits'),
(4, 5, 'sleeping', 'walking');
-- --------------------------------------------------------
--
-- Table structure for table `user`
--
CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
`address` varchar(200) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `user`
--
INSERT INTO `user` (`user_id`, `name`, `address`) VALUES
(5, 'Asif', 'Bannu'),
(6, 'Safeer', 'Bannu'),
(7, 'Farhad', 'Peshawar'),
(8, 'Rashed', 'Peshawar'),
(9, 'Yasir', 'Quetta'),
(10, 'Muhib', 'Islamabad');
i m executing the query
SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_id
but i m getting multiple rows with same data.
plz help me out in this situation. thanks a lot
|
|
|
|
|
U can create view from the tables and then create a join query using that view.
I dont know exactly how ur answer should be, so correct me if i am wrong!!!!
karthi
|
|
|
|
|
so what type of joining i can use?
|
|
|
|
|
How your output should be...? Based on your output only we can write views which can use left, inner or right join....
If u can post how your answer looks like based on your datas i might try from my side
karthi
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
