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

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

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 ''cb.CurrentBalance'' is invalid in the HAVING clause ...

Not sure why I am getting this error below. It has someting to do with my CurrentBalance calculation portion in my INNER JOIN area:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM,

SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(SELECT RMSFILENUM, (SELECT (rb.RMSCHGAMT - rb.RMSRCVPCPL)

+(rb.RMSASSCCST - rb.RMSRCVDCST)

+(rb.RMSACRDINT - rb.RMSRCVDINT)

+(rb.UDCCOSTS1 - rb.UDCRECCS1)

+(rb.UDCCOSTS2 - rb.UDCRECCS2)

+(rb.RMSCOST1 - rb.RMSCOST1R)

+(rb.RMSCOST2 - rb.RMSCOST2R)

+(rb.RMSCOST3 - rb.RMSCOST3R)

+(rb.RMSCOST4 - rb.RMSCOST4R)

+(rb.RMSCOST5 - rb.RMSCOST5R)

+(rb.RMSCOST6 - rb.RMSCOST6R)

+(rb.RMSCOST7 - rb.RMSCOST7R)

+(rb.RMSCOST8 - rb.RMSCOST8R)

+(rb.RMSCOST9 - rb.RMSCOST9R)

+(rb.RMSCOST10 - rb.RMSCOST10R)

- rb.RMSXCSRCVS

FROM RPRDBAL rb) as CurrentBalance

FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

WHERE rm.rmsacctnum = '4313030999894992'

GROUP BY rm.rmsacctnum, rf10.rmstranamt10

HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)

AND cb.CurrentBalance <> 0.00

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM,

SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)

+(RMSASSCCST - RMSRCVDCST)

+(RMSACRDINT - RMSRCVDINT)

+(UDCCOSTS1 - UDCRECCS1)

+(UDCCOSTS2 - UDCRECCS2)

+(RMSCOST1 - RMSCOST1R)

+(RMSCOST2 - RMSCOST2R)

+(RMSCOST3 - RMSCOST3R)

+(RMSCOST4 - RMSCOST4R)

+(RMSCOST5 - RMSCOST5R)

+(RMSCOST6 - RMSCOST6R)

+(RMSCOST7 - RMSCOST7R)

+(RMSCOST8 - RMSCOST8R)

+(RMSCOST9 - RMSCOST9R)

+(RMSCOST10 - RMSCOST10R)

- RMSXCSRCVS

) as CurrentBalance

FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

--WHERE rm.rmsacctnum = '4313030999894992'

GROUP BY rm.rmsacctnum, cb.CurrentBalance

HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)

AND cb.CurrentBalance <> 0.00

column alias in where clause

Hi all,
I have the following query that uses a subquery for one of the columns.
However if I try to use this subquery column in the where clause with the
alias I've given it, it tells me it is an invalid column. Here's the query:
select a.*,(select companyID from contacts where contactID = a.contactID) as
compID
from appointment a
where compID in(select companyID from tsClientUsers where UserID=5) or
compID is null
It keeps giving me the error that compID is an invalid column name. why? And
how can I get around this? I cannot do an inner join because not all
appointments have a related contact.
Any help would be much appreciated!
--
SaraSara, try this:
select * from
(
select a.*,(select top 1 /*just in case when subquery returns more than one
value*/companyID from contacts where contactID = a.contactID) as
compID
from appointment a )p
where compID in(select companyID from tsClientUsers where UserID=5) or
compID is null
Regards,
Tomislav Kralj
"Sara" <s@.mail.com> wrote in message
news:E5241E9D-47B0-40F5-AD0D-CA8B06347755@.microsoft.com...
> Hi all,
> I have the following query that uses a subquery for one of the columns.
> However if I try to use this subquery column in the where clause with the
> alias I've given it, it tells me it is an invalid column. Here's the
> query:
> select a.*,(select companyID from contacts where contactID = a.contactID)
> as
> compID
> from appointment a
> where compID in(select companyID from tsClientUsers where UserID=5) or
> compID is null
> It keeps giving me the error that compID is an invalid column name. why?
> And
> how can I get around this? I cannot do an inner join because not all
> appointments have a related contact.
> Any help would be much appreciated!
> --
> Sara|||The WHERE clause has no idea about the alias, because it is processed long
before the expressions in your SELECT list. So, in most cases, you will
either have to repeat the expression in the WHERE clause, or use a subquery
like Tomislav suggests.
However in this case I believe it is possible to avoid all those subqueries
and use a LEFT OUTER JOIN:
DECLARE @.userID INT
SET @.userID = 5
SELECT a.*, c.companyID
FROM appointment a
LEFT OUTER JOIN Contacts c
ON a.contactID = c.contactID
LEFT OUTER JOIN tsClientUsers t
ON a.compID = t.compID
WHERE t.userID = @.userID
-- you may also try AND instead of WHERE here
If you provide DDL, sample data and desired results (see
http://www.aspfaq.com/5006), I might even be able to verify that my above
solution works in your case.
On 3/15/05 3:15 AM, in article
E5241E9D-47B0-40F5-AD0D-CA8B06347755@.microsoft.com, "Sara" <s@.mail.com>
wrote:

> Hi all,
> I have the following query that uses a subquery for one of the columns.
> However if I try to use this subquery column in the where clause with the
> alias I've given it, it tells me it is an invalid column. Here's the query
:
> select a.*,(select companyID from contacts where contactID = a.contactID)
as
> compID
> from appointment a
> where compID in(select companyID from tsClientUsers where UserID=5) or
> compID is null
> It keeps giving me the error that compID is an invalid column name. why? A
nd
> how can I get around this? I cannot do an inner join because not all
> appointments have a related contact.
> Any help would be much appreciated!|||On Tue, 15 Mar 2005 00:15:03 -0800, Sara wrote:
(snip)
>It keeps giving me the error that compID is an invalid column name. why? And[/color
]
Hi Sara,
That's a result of the defined order of evaluation for SELECT
statements. In theory, the order is:
1. Build temp result set from FROM clause (including all joined tables).
2. Remove rows not satisfying the WHERE clause
3. Create groups, according to GROUP BY clause
4. Remove all rows from groups not satisfing the HAVING clause
5. Build final result set, as specified in SELECT clause, based on data
in temp result set.
(6. Reorder the result set, based on ORDER BY - technically, this is a
cursor operation and not part of the SELECT statement).
(In practice, most products will change the order of evaluation for
optimization, as long as the results don't change)
As you can see, the building of the SELECT clause is the final step;
that's the reason that aliasses can't be used in the rest of the query
(though they can be used in the ORDER BY).
>how can I get around this? I cannot do an inner join because not all
>appointments have a related contact.
You could use a derived table (as suggested by Tomislav), but in this
case I believe there's a better solution:
SELECT a.Col1, a.Col2, ..., a.ColN, -- Never use SELECT * !!
c.CompanyID AS compID
FROM Appointments AS a
LEFT OUTER JOIN Contacts AS c
ON c.ContactID = a.ContactID
WHERE c.CompanyID IN (SELECT CompanyID
FROM tsClientUsers
WHERE UserID = 5)
OR c.CompanyID IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)