|
Hi,
i have the following code:
SELECT visit_investigations.*,
price_list_items.*,
employee.employee_short_name as created_by
FROM visit_investigations
INNER JOIN price_list_items ON price_list_items.item_id = visit_investigations.item_id
INNER JOIN employee ON visit_investigations.created_user = employee.employee_number
WHERE visit_investigation_id = param_visit_investigation_id;
where created by is the employee created the record andit will never be blank.
I want to add the field visit_investigations.result_by for the user who submited the result. this will be a number representing the employee.employee_number and it might be blank for records without result yet.
How can I do this?
Technology News @ www.JassimRahma.com
|
|
|
|
|
I beleve you want to join to the employee table more than once.
It would be something like:
INNER JOIN employee e1 ON visit_investigations.created_user = e1.employee_number
INNER JOIN employee e2 ON <someother foreign="" key="" to="" the="" employee="" table=""> = e2.employee_number
Hope that helps. 
|
|
|
|
|
As David has suggested you need a 2nd join to the employee table but make it a LEFT join if there are missing records.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Issue occrue on select top 2 like this
when run the following query then show the following result
SELECT u.ElectionDtlID,u.PartyShortNameEng,u.ConstituencyName,u.VotesObtained
FROM tblElectionsDetail u where u.ConstituencyName='NA 108' and u.electionyear='2013'
and u.VotesObtained > 0
RESULT:
ID NameEng Constituency VotesObtained
10864 PPPP NA 108 29883
10865 PMLN NA 108 73789
10866 PTI NA 108 25406
10867 JI NA 108 18270
10868 Ind NA 108 85009
But now i want to get only top result of Maximum VotesObtained Candidate
like this :
Query:
select top 2 VotesObtained from tblElectionsDetail where ConstituencyName='NA 108';
and electionyear='2013'; order by VotesObtained desc
Then this issue the following Result
OutPut Table:
VotesObtained
85009
73789
this is working good and also result show correct but when i want to show max/top 2 recrod but order by asc then it's working correct like this
Query:
select top 2 VotesObtained from tblElectionsDetail where ConstituencyName='na 108'
and electionyear='2013' order by VotesObtained asc
the result of the query like this
OutPut Table:
VotesObtained
0
0
so i wan to get the result like this.
VotesObtained
73789
85009
any body can help me ?
-- modified 29-May-13 8:47am.
|
|
|
|
|
SELECT everything from your resultset in reverse order.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
select T.ElectionDtlID, T.PartyShortNameEng, T.ConstituencyName, T.VotesObtained
FROM (select * from tblElectionsDetail where ConstituencyName='NA 108' and electionyear='2013' order by VotesObtained desc limit 2) T order by T.VotesObtained asc;
--
Arman
|
|
|
|
|
hi experts,
can anyone guide me ,how to decrypt the encrypted user defined function in sql server 2008
|
|
|
|
|
Your question makes no sense: are you saying that the functions, itself, is encrypted and you need to decrypt it or the output from the function? In either case, speak to the original author of the function: there is no earthly way that anyone here could possibly guess at the encryption used.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Those who seek perfection will only find imperfection
nils illegitimus carborundum
me, me, me
me, in pictures
|
|
|
|
|
|
hi,
thanks for replying...by d way i am not hacking anyone's db...i tried my function...i succeeded in encrypting but couldn't succeed in decrypting it....ie i asked in this forum...once again thanks for replying...
|
|
|
|
|
Member 8701813 wrote: by d way i am not hacking anyone's db
I do not see any other reason to decrypt a procedure.
Member 8701813 wrote: i succeeded in encrypting but couldn't succeed in decrypting it
There are some tools and some sprocs that can decrypt an sproc/function on Sql2005. None of those will work on Sql2010, and that change "might" have been introduced in Sql2k8R2. In that case, you're out of luck.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Encryption / Decryption
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Cryptography]
(
@String varchar(50)
)
/*
Author: POONGUNRAN S
Create date: 29-May-2013
Description: Encrypt / Decrypt
Parameters : @String - String to be encrypted / decrypted
*/
RETURNS varchar(50)
AS
BEGIN
IF @String = ''
RETURN ''
SELECT @String = LTRIM(RTRIM(@String))
DECLARE @Key varchar(50), @Crypt varchar(50), @Len smallint, @n smallint
SELECT @n = 235
SELECT @Key = ''
WHILE @n <= 255
BEGIN
SELECT @Key = @Key + CHAR(@n)
SELECT @n = @n + 1
END
SELECT @Crypt = ''
SELECT @Len = LEN(@String)
SELECT @n = 1
WHILE @n <= @Len
BEGIN
SELECT @Crypt = @Crypt + CHAR(ASCII(SUBSTRING(@String,@n,1)) ^ ASCII(SUBSTRING(@Key,@n,1)))
SELECT @n = @n + 1
END
RETURN @Crypt
END
Input put:
Select dbo.fn_Cryptography('test')
o/p : Ÿ‰žš
select dbo.fn_Cryptography('Ÿ‰žš')
o/p : test
|
|
|
|
|
Hello everyone,
I created a table which has following columns Sl_no,name and employee_id.
Is that a possible when i select a table i should get a designation of an employee in place of his name??
Kindly please help me out.
|
|
|
|
|
What we are supposed to be telepathic!
What database server
What is your select query
There is no field called designation in your table!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
We are some kind of "gods" and we can read minds 
|
|
|
|
|
Shiv irfi wrote: I created a table which has following columns Sl_no,name and employee_id
SELECT Sl_no, name, employee_id
FROM [a table]
WHERE 1=1
Shiv irfi wrote: Is that a possible when i select a table i should get a designation of an employee in place of his name??
Yes.
SELECT Sl_no, designation, employee_id
FROM [a table]
WHERE 1=1
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
hi,
If you had a separate table for the designations for that employees then u can join both the tables and get what ever fields u have and make aliasing names for that columns... Otherwise it is not possible to get because u are not having the designation column in the Your table
|
|
|
|
|
Hi
First create Employee table
CREATE TABLE EMPLOYEE
(
Sl_no int identity,
Name varchar(40),
Employee_id int
)
insert the values
INSERT INTO EMPLOYEE(Name,Employee_id)
SELECT 'Anil',1001
UNION ALL
SELECT 'Mahesh',1002
UNION ALL
SELECT 'Raju',1003
then create designation table
CREATE TABLE DESIGNATION
(
Employee_id INT,
Designation varchar(30)
)
then insert the values
INSERT INTO DESIGNATION
SELECT 1001, 'Developer'
UNION ALL
SELECT 1002, 'Tester'
UNION ALL
SELECT 1003, 'Trainee'
then follow this query
SELECT E.*,D.Designation FROM EMPLOYEE E INNER JOIN DESIGNATION D ON E.Employee_id=D.Employee_id
Prakash.ch
|
|
|
|
|
Here's a problem I've come up against on a number of occasions and I have so far failed to come up with an elegant query to deal with it. I feel the GROUP BY clause might have to come into play but I don't really know how.
Let's say I have a table called Orders containing information on orders received from customers. The table has the following columns:
CustomerID (INT)<br />
OrderPlaced (DATETIME)<br />
ProductID (INT)<br />
AmountOrdered (INT)
So I want to write a query that will show me the information from this table on the first order for a specific product (let's say id 123) received by every customer.
The following query will get me almost there:
SELECT CustomerID, MIN(OrderPlaced)
FROM Orders
WHERE ProductID = 123
It only gets me almost there though because notice how the AmountOrdered field is missing from the query and unless it's part of an aggregate function I can't add it. I can add it with a subquery but that is where the query loses its elegance rapidly, especially considering that the real world tables I'm dealing with usually have more than just one or two extra columns.
Any ideas?
|
|
|
|
|
Almost all your solutions using an aggregate will require a sub query, there is nothing inelegant about using one!
You can also look into ROW_NUMBER and PARTITION.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks. I've been shying away from Over(), ROW_NUMBER and Partition By because they're rather Microsoft specific.
I agree, generally there's nothing inelegant about using a subquery but in this particular case it is anything but elegant.
Consider the table in my example and let's say there are a few more columns:
ContractID (INT)<br />
OrderSource (INT)<br />
AgentID (INT)<br />
ProvinceID (INT)<br />
DeliveryMethod (INT)
So, in order to get what I want, using GROUP BY and subqueries, it would end up looking something like this:
SELECT
CustomerID,
MIN(OrderPlaced),
FROM Orders
WHERE ProductID = 123
GROUP BY CustomerID
I'm sure you'll agree that the above query is pretty much the antithesis of elegance.
modified 27-May-13 4:18am.
|
|
|
|
|
No no no you want the ID of the record for each customers earliest order (I hope) so first create a query that gets you that order ID, probably add the orderID to the above query.
Make sure the IDs are the expected results before moving on to the next stage.
Now wrap that query in a
Select * from Orders
where orderID in (Your innerquery)
or if there are multiple fields in your inner query use a join
Select *
From Orders
inner join (Yourinnerquery) X on X.orderid = orders.orderid
You can make the inner query as complex as required but it should return the minimum required to join to your field table (Orders presumably)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
BINGO! You've hit the nail on the head. Thanks. Here is the query that does that:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT (
SELECT TOP 1 OrderID
FROM Orders O2
WHERE CustomerID = O1.CustomerID
ORDER BY Inserted
)
FROM Orders O1
GROUP BY CustomerID
)
Of course this query relies on the existence of OrderID, a primary key of the Orders table. I didn't define it in my original question but just about every table should have a primary key anyway and just about all my tables usually do 
The great thing about this query is that the outermost query can have a SELECT * and, as you say, could even join the Orders table with any other table that might contain linked information on the orders.
Thanks again. It might seem insignificant but you've just helped me solve a problem I've been grappling with for years and never really bothered to definitively put to bed.
|
|
|
|
|
You are definitely welcome, once you get your head around SQL it can be very rewarding making the dammed thing do what you want. Then the bastards introduce a new way of doing something (CTEs come to mind) and you have to start all over again!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<edit>low on caffeine => dysfunctional brain</edit>
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
modified 27-May-13 17:15pm.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
