|
OK, so I have a table of businesses. Then I have a table of contacts. So, I have many contacts for the one business. I want to do a search, and use a contact name as a possible search. However, I don't ever want to return the same business more than once. So, I want to return each business that matches at least one contact, with any contact that matches. I've tried putting in a subquery so that I select the top 1 contact for a business, but I can't find a way to write SQL that works.
select
c.IDClient, c.Company, con.LastName + ', ' + con.FirstName as Contact, c.Phone, c.Fax
from tbl_client c
inner join tbl_client_contacts cc on cc.IDClient = c.IDClient
inner join tbl_contact con on con.IDContact = cc.IDContact
where con.IDContact = (select top 1 IDContact from tbl_client_contacts ccc where ccc.IDClient = c.IDClient)
Christian Graus
No longer a Microsoft MVP, but still happy to answer your questions.
|
|
|
|
|
SQL query seems ok.Your query don't return data or what?
Have you try to specify table name for column IDContact inside subquery?
<br />
select<br />
c.IDClient, c.Company, con.LastName + ', ' + con.FirstName as Contact, c.Phone, c.Fax<br />
from tbl_client c<br />
inner join tbl_client_contacts cc on cc.IDClient = c.IDClient<br />
inner join tbl_contact con on con.IDContact = cc.IDContact<br />
where con.IDContact = (select top 1 ccc.IDContact from tbl_client_contacts ccc where ccc.IDClient = c.IDClient)
Hope it will help.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Brilliant, that was the issue ( my old code would not even compile ). Thanks for the help, I appreciate it.
Christian Graus
No longer a Microsoft MVP, but still happy to answer your questions.
|
|
|
|
|
Never mind dude,I am glad that helped you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
I am using sql server 2005 and ASP .Net 2.0, VS 2005.
When I increased a parameter in Stored Procedure and in application, and again running the application it throws an error that, Parameters does not match with the stored procedure parameters.
All i have to do is restart the machine and again run the application. Then it does not throw the error and it works fine with the extra parameter in application as well as in storedproc.
I am unable to understand what is the exact problem, that i have to restart the machine and then it accepts the changes
|
|
|
|
|
I think you'll find it has to do with the way you are running the ASP for development, sound like ASP is cacheing the footprint and rebooting clears out the chache.
Try asking in the ASP forum, they may be more helpful.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I saved a data import task to a SSIS package in MSDB. SQL Server 2005.
Now how can I access that again??? I want to make an automatic task to upload files from a folder to tables??
Dont we have something easy and useful like DTS in SQL server 2005???
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
I can understand your angst, I knew DTS, it worked well and it was fairly simple. But they (MS) have moved on and inflicted us with SSIS, there is no going back.
Alternatively you can write your own ETL solution. This is what we did rather than use SSIS.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You wrote your own ETL Solution????? 
could you give me a headstart on that..not sure if i could have thought of that ever...
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
hi,
i made some procedures that ends in a edit process(edit,add,delete) or nothing, like this:
if not exists(select * from mytable where id = @whatever)
begin
...edit something
end
--else
--begin
--... do nothing here
--end
So, i trigger this stuff in c#(whatever event doesn´t matter) and i have to display info msg in label, like this:
OK: sale added
ERROR: can´t delete, client in sales table
OK, sometimes i don´t know where it enters/falls, i think on counting rows in datagridview to check a delete or add for exemple, on a edit i use a compare(C# datagridview)
i´m thinking on creating a trigger or an extra table to feed the info label, i don´t know really
(no need to place code, just ideas)
Thanks 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Could you break this question to pieces, since there is mices t-sql and user interface components. I take it you want to achieve some kind of error handling, but I'm not sure 
Mika
|
|
|
|
|
|
Ok I see,
Could you use output parameters in sp?
Assign the output parameter to something during the execution, based on what has happened inside the sp. Then after executing the sp from c# you should have a value in that parameter. Extract it and show it in the label.
I typically use enum types (mainly for localization reasons) in c# in similar situations (I just use the same numbers in sp than in c#) but if you just want to return text, there's no need to use numbers.
Mika
The need to optimize rises from a bad design
|
|
|
|
|
I'm not much of an SQL coder, but don't know where else to go. I have a small script that I would like to have modified. Right now it is a 'hard coded' date, meaning that I have to manually go in each time and make changes to it in order to pull the date range I want. I want to be able to have it run automatically (using additional software) for the previous month. Example: On Oct 1, 2008, I would produce a report, using this script, for the previous month, Sept 1 2008 - Sept 30 2008. Can it be done? I have copied the script below:
DECLARE @DateFrom VARCHAR(15)
DECLARE @DateTo VARCHAR(15)
--Date Selection
SET @DateFrom = '01/04/2008' --MM/DD/YYYY
SET @DateTo = '01/04/2008' --MM/DD/YYYY
select
Primary_InsPlanName AS [Plan Name],
DoctorName AS [Doctor Name],
CPTCode AS [CPT Code],
Modifier AS Modifier,
sum (ChargeCount) AS [Total Units],
SUM (ChargeAmount) AS [Total Billed Amount]
from vwIR_OBJ_stdCharges
--Insurance Plan Selection
where primary_insPlanNum IN ('300','303','449','455','473','497','504','530','2181','2303','10018','13422','13483','13671')
AND cast(CONVERT(VARCHAR(10),PostDate,101) as dateTime) >= cast(CONVERT(VARCHAR(10), @DateFrom, 101) as dateTime)
AND cast(CONVERT(VARCHAR(10),PostDate,101) as dateTime) <= cast(CONVERT(VARCHAR(10), @DateTo, 101) as dateTime)
group by
Primary_InsPlanName,
DoctorName,
CPTCode,
Modifier
Order by
Primary_InsPlanName,
DoctorName,
CPTCode,
Modifier
Any advice is appreciated. Thanks!!
|
|
|
|
|
hi,
forget the code, what do you want to do???
listen, don´t set date, use getdate(), datediff() or dateadd() function often
Shane Leach wrote: SET @DateFrom = '01/04/2008' --MM/DD/YYYY
SET @DateTo = '01/04/2008' --MM/DD/YYYY
i never do it. tell more maybe we could help you 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
It's a report that right now I have to manually go in and set the dates. It's always run on the first day of the month and the data we are looking for is the previous month's data (i.e., October 1st, I will run it for 9/1/08-9/30/08).
|
|
|
|
|
You can calculate the From and To days in your sp.
First day of previous month:
SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
Last day of previous month:
SELECT DATEADD(s, -1 ,DATEADD(DAY, -DATEPART(DAY, getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
Ryan
|
|
|
|
|
Thanks for the info. My question would be where in the original script would your statement go? Sorry, I'm a SQL idiot. . .
|
|
|
|
|
DECLARE @DateFrom VARCHAR(15)
DECLARE @DateTo VARCHAR(15)
SELECT @DateFrom = DATEADD(MONTH, -1, DATEADD(DAY, - DAY(getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
SELECT @DateTo = DATEADD(s, -1 ,DATEADD(DAY, -DATEPART(DAY, getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
|
|
|
|
|
|
what do you want to do???
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
I have a stored procedure as follows, which runs OK on SQL Server 2005.
CREATE PROCEDURE spGetChildAccounts
@ParentId Char(12)
AS
BEGIN
SET NOCOUNT ON;
WITH Subsidaries (ACCOUNTID,ParentID)
AS
(
SELECT ACCOUNTID, ParentID FROM Accounts WHERE ParentID = @parentID
UNION ALL
SELECT Accounts.ACCOUNTID, Accounts.ParentID FROM Accounts
INNER JOIN Subsidaries ON Accounts.ParentID = Subsidaries.ACCOUNTID
)
SELECT * FROM Accounts INNER JOIN Subsidaries ON Accounts.ACCOUNTID = Subsidaries.ACCOUNTID
END
The problem is that it has to run on SQL Server 2000 sp3 and when I try to create the procedure on the 2000 server it get an error "Incorrect syntax near the keyword WITH".
I am assuming the WITH statement is not supported in 2000 and would appreciate any help converting the above stored procedure to a vaild SQL Server 2000 format.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Try to generate compact-able script for your stored procedure from SQL2005 for SQL2000.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Blue_Boy wrote: Try to generate compact-able script
Thanks for the advice, unfortunately you are assuming I know what I am doing. Truth is I am currently on a very steep learning curve and I am only just getting to grips with T-SQL. That said I have no idea what you mean by a compact-able script or even how to go about generating one as you suggest.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I beleive the with structure you are seeing is not available in 2000 (sorry to state the obvious). What it is doing is allowing you to use a sub select. So the select in () is named Subsidaries and is then used in the join in the second select statement.
Work around would be a view ot temp table to isolate the first select so you can use it in the second select (or completely redesign the query)
OR
You could upgrade SQL Server 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
