Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how we put this query together

Posted on 2013-01-29
5
Medium Priority
?
301 Views
Last Modified: 2013-02-22
Hi guys, i have a user table,

id    user    
1      bob
2      ted
3      robert

 and this funcion dbo].[Fn_Get] that returns one row of 3 collumns, like this based on the userid.
so if i call the funcion like this : dbo].Fn_Get(1) should return

idl     idb     idc
3        4        5

i want the select to return the users like this.

id    user          idl     idb      idc
1      bob           3        4         5
2      ted            6         3         8
3      robert        4        6        5

so the select query should look like?

select t1.id, t1. from tbuser as t1
0
Comment
Question by:rafaelrgl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 38832494
Not real sure of your question. Are you looking for something like this -
select id, user, dbo.Fn_Get(id) from users
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38832571
I think you need { cross | outer } apply, but not 100% sure.


SELECT
    ut.id, ut.user, fg.idl, fg.idb, fg.idc
FROM dbo.userTable ut
CROSS APPLY dbo.Fn_Get(ut.id) AS fg --Fn_Get is a table-valued function


On SQL 2008, you have some additional options using VALUES() also.
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 38832666
yes, it's cross aplly, but can we change the function to a procedure. because i can't use newid() on funcion. So can we make the same query you show me but instead of using a function it will be an procedure? can we?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38832720
No, you can't use a procedure that way.

Why would you need NEWID() in a function?
0
 
LVL 10

Assisted Solution

by:deviprasadg
deviprasadg earned 500 total points
ID: 38834317
In that case add NEWID to the select statement.(select statement with usertable and crossapply on function)
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question