Help with a SQL query

I am selecting search results from the following view. The problem I have is, t5 (a junction) is returning multiple rows, which makes each search result show up several times. I just need one uniqe row from t5 ... (it has to be one row from just t5, not one row from the whole query). Can someone show me how I would accomplish this?

If anymore information is needed, let me know and I will post here. Thanks!

SELECT t1.*, t2.doc_type_description AS doc_type_description, t3.file_id AS file_id, t3.file_path AS file_path, t3.active AS active,
t5.soq_id AS soq_id, t5.soq_name AS soq_name
FROM dbo.tblSOQDocuments t1 INNER JOIN
dbo.tblSOQDocTypes t2 ON t1.doc_type_id = t2.doc_type_id INNER JOIN
dbo.tblSOQFiles t3 ON t1.doc_id = t3.doc_id INNER JOIN
dbo.jctSOQDocument t4 ON t1.doc_id = t4.doc_id INNER JOIN
dbo.tblSOQs t5 ON t4.soq_id = t5.soq_id
ORDER BY t2.doc_type_description, t5.soq_name, t1.doc_nameUse sub-queries.

instead of doing:

INNER JOIN dbo.tblSOQs AS t5 ON t4.soq_id = t5.soq_id

do:

INNER JOIN (SELECT soq_id, MIN(soq_name) FROM dbo.tblSOQs GROUP BY soq_id) AS t5 ON t4.soq_id = t5.soq_id|||For a unique row from t5, use:

INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id|||For a unique row from t5, use:

INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id

That will potentially return multiple rows which is what the original poster wants to avoid|||"I just need one uniqe row from t5 ..."

He does not specify "One unique soq_id", which is why I asked for clarification. "MIN(soq_name)" seems kind of arbitrary to me. Why includ soq_name in the result set if it is going to exclude some values?

...but perhaps that is what he wants.|||When I select from this view, it will return several rows. Potentially 2 rows for each result (each of these two rows carries different file information). One result from this query can potentially be associated with several soq_id's, but I just want the first one for each result.

Clear as mud?|||Then do an EXISTS...no?

Do you need data from both tables or just the one?|||I need data from all the tables that I have joined here ... the relationship will always exist.|||Well either SELECT DISTINCT which would eliminate duplicates (I know...too easy) or you want 1 row, WHERE the keys are the same, but you will have different values for those keys.

YOU have to decided what to do with those values. They exists.

Either USE MAX or MIN, ect, or s series of temp tables.

Either way, it has to be handled.

And YOU have to make the decision.sql

Help w/syntax select in a while loop

I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:

'AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop'

Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:

Also I'm trying really hard not to use temp tables in this example

Result from select statement below

sh_serial
----
53565
53597

sh_serial
----
53565
53597

sh_serial
----

sh_serial
----
53588
53597

Desired results:

sh_serial
----
53588
53597
53565

Syntax:

declare @.start int
select @.start = 580
declare @.stop int
select @.stop = 900

while @.start <= @.stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in

(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))

select @.start = @.start + 1
end

I'd appreciate any help. Thanks! :oInsert into a table variable and select distinct from that when you leave the loop?|||cause of business logic I cannot use temp tables or tables...

is there a way that I can put this into some sort of a derived table and then do the select distinct?|||I was gonna rewrite it...but I got scared...

SQL 2000?

DECLARE @.start int, @.stop int
SELECT @.start = 580, @.stop = 900

DECLARE @.x TABLE(shSerial int)

WHILE @.start <= @.stop
BEGIN

INSERT INTO @.x(shSerial)
SELECT sh_serial
FROM casemas, schilin
WHERE schi_shser = sh_serial
AND schi_itemno = '004852'
AND sh_serial <> 600000
AND sh_serial in (53565,53588,53597)
AND sh_serial in ( SELECT DISTINCT sh_serial
FROM casemas, schilin
WHERE (schi_shser = sh_serial)
AND (schi_itemno = '004852')
AND sh_serial in (53565,53588,53597)
AND @.start NOT IN ( SELECT sh_istart
FROM casemas
WHERE sh_istart in (SELECT sh_istop
FROM casemas
WHERE sh_serial in (53565,53588,53597)))
AND @.start between sh_istart and sh_istop
GROUP BY sh_serial
HAVING (sum(schi_qty) + 1 < 4 ))

SELECT @.start = @.start + 1
END

SELECT DISTINCT shSerial FROM @.x|||Thanks Brett,

and by all means feel free to change anything...I do hate the fact that I have to use this statement all the time

'AND sh_serial in (53565,53588,53597)'

and if anyone else has any ideas on how i can tweak this horrid statement...I'd appreciate it! Thanks! :)|||Store those values in a temp table (@. or #) and do WHERE EXISTS (...).

Help talking to stored proc

Can someone please lend a hand.
I am a total noob at this .NET/ASP stuff. I simply need help passing and returning values to a stored procedure.
Here’s my sp.

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here @.sessionUservarchar(25)OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

So when the user logs into to the default page (windows authentication) I capture their domain\login in a session variable (sessionUser). On the next page, when the page loads I need to pass this to my stored proc and get the idUser in return.
Here is what I have for the page load so far.

try{ SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output; cmdReturnID.Connection.Open(); cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = cmdReturnID.Parameters["idUser"].Value;}catch (Exception ex){ lblStatus.Text = ex.Message;}

Of course this fails cause I don’t know what I’m doing. My error label shows the following: "An SqlParameter with ParameterName 'idUser' is not contained by this SqlParameterCollection."

Can someone point me to what I’m doing wrong?

Hmm.. You mixed up every thing here..

First you need to a pass an input parameter and expect an out put paremeter. So, your stored procedure should have two paramets defined in it.. You have only one

And also, in your c# code, you need to assign the value of session variable to the input parameter, which you have not.

Anyway, try this..

ALTER PROCEDURE [dbo].[returnIdUser]
-- Add the parameters for the stored procedure here

 @.sessionUservarchar(25),

 @.idOUT varchar(25) out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set idOUT = (SELECT idUserFROM tblUsersWHERE domainUser = @.sessionUser)

return @.idOUT
END

and your code to

cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Input;

command.Parameters["@.sessionUser"].Value =  Session["UserName"]

 cmdReturnID.Parameters.Add("@.idOUT", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;

SqlDataReader reader = cmdReturnID.ExecuteReader();

 reader.close();

 Session["sessionUserID"] = cmdReturnID.Parameters["idOUT"].Value;

Even though, the code looks real, its not... I typed by hand, so beware of small errors... 

|||

Hi , in your example I guess you want to pass a session user into the stored procedure and return idUser. In this case your stored procedure should be :

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here (@.sessionUservarchar(25))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

And here is the modified code:

try { SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25); cmdReturnID.Connection.Open(); SqlDataReader sqldr = cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = sqldr.GetString(0); }catch (Exception ex) { lblStatus.Text = ex.Message; }

Here I use sessionUser as an input value and read idUser with SqlDataReader.Hope this helps.|||it took some alterations, but finally got it to work. Thanks to both of you!

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking You
Hi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table us
ing * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table i
s having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
--
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

HELP Retrieving remote XML and returning ROWSET

Within a stored procedure, I need to retrieve XML over HTTP from an external
URL, then load some stuff into a recordset and return as such.
Like this, sort-of:
-- *******************************
create procudure queryremote (@.param)
declare @.xmldoc varchar(2000)
set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
@.param)
sp_preparedocument (@.xmldoc etc.)
SELECT * FROM OPENXML(thexmlthing, "/xpath")
Return
-- *********************************
Just to be clear, this is exactly the opposite of what everyone is trying to
do; many articles on getting XML OUT of SQL server and putting XML data INTO
SQL server, over HTTP.
This requirement is to provide backward -compatibility to
SQL-recordset-aware-only applications. Making an HTTP XML query service
LOOK like an SQL recordset.
Email and post please, thanks a lot!
Richard Weerts
rweerts@.ndis.us
In SQL Server 2000 you have two options:
Do the get on the mid-tier and pass the XML as an argument to the stored
proc.
Write your own extended stored proc to GET the data.
In SQL Server 2005, you can write the function using CLR instead of writing
an extended stored proc.
HTH
Michael
"411XML Richard Weerts" <rweerts@.ndis.us> wrote in message
news:OLr5YmXfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Within a stored procedure, I need to retrieve XML over HTTP from an
> external
> URL, then load some stuff into a recordset and return as such.
> Like this, sort-of:
> -- *******************************
> create procudure queryremote (@.param)
> declare @.xmldoc varchar(2000)
> set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
> @.param)
> sp_preparedocument (@.xmldoc etc.)
> SELECT * FROM OPENXML(thexmlthing, "/xpath")
> Return
> -- *********************************
> Just to be clear, this is exactly the opposite of what everyone is trying
> to
> do; many articles on getting XML OUT of SQL server and putting XML data
> INTO
> SQL server, over HTTP.
> This requirement is to provide backward -compatibility to
> SQL-recordset-aware-only applications. Making an HTTP XML query service
> LOOK like an SQL recordset.
> Email and post please, thanks a lot!
> Richard Weerts
> rweerts@.ndis.us
>
>