Combining subquery results into one field

Hello there

I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:

bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

thanks

I would look at returning two results to a dataset then creating a relationship between them. Displaying the results is pretty easy using nested repeaters:http://gridviewguy.com/ArticleDetails.aspx?articleID=185

Combining Queries/ Results

I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table.
However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results.
What would be the best method to combine searches?
At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries.
Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)
Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)
e.g.
1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))
2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
Combined using my function creates:
Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))

When I combine I'm drilling down, so if the first query returns a count of 400 (where thetitleORsubjectcontains 'run') and then the second query returns 600 records (where thetitleORsubjectcontains 'level') I need to combine so that I'm looking for records where thetitlecontains both keywords 'run' AND 'level' OR else thesubjectcontains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words.
I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?

You don't need to build it like you are. Just keep adding ANDswith the appropriate conditions. SQL will figure it out.
Example:
S1: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
S2: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
S3: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
AND (CONTAINS(title,'"blah"') OR CONTAINS(subject,'"blah"'))
...

Combine multiple results of subquery

Table users:
userid, name, added...

Table groups
groupid, groupname...

Table groupadmins:
userid, groupid

The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'

If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users

then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?

The best example of how to do this in this article by Aaron Bertrand:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

If you are using SQL Server 2005, the FOR XML PATTH solution is really nice.

|||

Thanks a bunch. I tried googling and searching these forums, but without the right keywords, useful results can be hard to come by.

aspfaq.com is now bookmarked for many future references.

sqlsql

Combine 2 tables

i have 2 tables in a database
one is recording all the fax-in to users, the another is recording all the
fax-out by users.
i wish to combine the tables (by JOIN?) so that the users can see its own
fax-in and fax-out.
After the join, then the new look is TableA.date, TableB.date in 2 column.
How can i make it in one column, say Table.date combining both tables dates.
thanks a lot.
tony> After the join, then the new look is TableA.date, TableB.date in 2 column.
> How can i make it in one column, say Table.date combining both tables
> dates.
What on earth does "combine" mean? Adding? Concatenating? Doing a
datediff? Max?
Please read the following article and come back with a better question:
http://www.aspfaq.com/5006|||"Tony WONG" <x34@.hknet.com> wrote in message
news:Opjt5F98FHA.1292@.tk2msftngp13.phx.gbl...
> i have 2 tables in a database
> one is recording all the fax-in to users, the another is recording
all the
> fax-out by users.
> i wish to combine the tables (by JOIN?) so that the users can see
its own
> fax-in and fax-out.
> After the join, then the new look is TableA.date, TableB.date in 2
column.
> How can i make it in one column, say Table.date combining both
tables dates.
> thanks a lot.
> tony
>
Tony WONG,
SELECT TableA.date
,TableA.[fax-in]
FROM TableA
UNION ALL
SELECT TableB.date
,TableB.[fax-out]
FROM TableB
Untested, but the idea is the right one so far as I can tell from
the available description.
Modify the above as needed.
Sincerely,
Chris O.|||Thanks a lot.
it is what i wish.
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> glsD:FIOdnXOHVqh8GBfenZ
2dnUVZ_sudnZ2d@.comcast.com...
> "Tony WONG" <x34@.hknet.com> wrote in message
> news:Opjt5F98FHA.1292@.tk2msftngp13.phx.gbl...
> all the
> its own
> column.
> tables dates.
> Tony WONG,
> SELECT TableA.date
> ,TableA.[fax-in]
> FROM TableA
> UNION ALL
> SELECT TableB.date
> ,TableB.[fax-out]
> FROM TableB
> Untested, but the idea is the right one so far as I can tell from
> the available description.
> Modify the above as needed.
>
> Sincerely,
> Chris O.
>

column management help

I have to read 25 usernames from a single users row

than display to that user the 25 profiles.

I assume this is possible with a subquery right?

Now do i have to make 25 columns 1 for each username or

could it read user1 user2 user3 ? and how please.. Thanks much

Can you explain a little more about how these usernames are currentlystored in your database, and why you need to make them columns? Sounds like they're better suited to being rows, instead ofcolumns.
|||

i have an infinite number of rows with primary key username

each column needs to store up to 25 usernames who sent a message

than im thinking a subquery can display the info for each username for that exact person.

info to be display is picture and some things about themself

How... im still trying to figure out.

i have this in one table

Username - primary key

user1, user2 user3 as columns - for each person that made an action on that users page

store them in a column

My question is: There a simplier way to read these users instead of making a bunch of columns for each user who made an action?

|||

Guys would making subtables cause trouble in the long run?

If i make a sub table for every user?

|||

ck1mark wrote:

My question is: There a simplier way to readthese users instead of making a bunch of columns for each user who madean action?

Hi,
Yep, there is. Any time you have an inclination of creating a tablewith numbered fields like that, it is a huge warning flag that thestructure is not normalized. Almost certainly, a better way is to havea Message table (I'm guessing here on what table names will make sense)that has a MessageID and whatever other fields you need to have tostore whatever information.
Then you have a child table with a structure something like this:
UserID -- Could be the user name or a artificial primary key
MessageID -- A foreign key into the Message table
This way storage is more efficient, you don't have to worry about theone message you'll occasionally get that has 26 users, and SQL isdesigned to handle related tables like this.
Make sense?
Don
|||

help me write this please im having trouble

i need to insert the select statement values

INSERT INTO table1
VALUES username, photo1

(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

|||You might want to check Books Online for the syntax. But it should besomething like this (untested, so may still need some tweaking):

INSERT INTO table1 (username, photo1)
(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

I'm not quite sure what the context is for this statement, so the quotes might need more tweaking as well.
Does that work? If not, what troubles are you having?
By the way, this is dangerous code because of SQL injection. Usinguser.identity.name may be safe, but only if you've made sure the namedoesn't have any bad stuff in it. It's always better to useparameterized queries.
Don

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@.UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.com>...
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?
> Thanks!

If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon|||Rolando Barberis (rolandobarberis@.hotmail.com) writes:
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?

This is a dead end. There is no way you can do this in SQL only. As Simon
says, you need to go client-side and deal with the the procedures there.

Not even client-side there is any good way to determine the number of
columns without running the procedure. In some contexts, ADO uses
SET FMTONLY ON which causes SQL Server to only sift through the statements
without executing them, but still return information about the result
sets. But there are several unexpected things that can happen with SET
FMTONLY ON, so in my opinion it's useless.

Then again, once you are client-side, it is not problem to run the
procedures. SQL Server will return information about the result sets,
and you will get recordsets, data tables or whatever depending on
which client library you use. (My personal preference is for ADO .Net.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@.UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.com>...
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?
> Thanks!

If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon