Need SQL query

35 pts.
SELECT statement
SQL Query
I have two tables DETAIL and USER. Detail table have two columns (projectid and projectdetail). Projectid is a primary key in DETAIL table. USER table have two columns (projectid and Userid). User table is like more than one userid can be under each projectid, one user can access more than one projectid. like the following DETAIL TABLE: projectid ProjectDetail 1 D1 2 D2 3 D3 USER TABLE: projectid userid 1 TT15 2 TT15 3 TT15 1 TT16 2 TT16 I need output like if i give Userid it should list the projectdetails of the projectids which are under his userid. With above table if i give userid as TT15. It should list the details of the Projectids(1,2,3). Please I need the query for this.

Answer Wiki

Thanks. We'll let you know when a new response is added.

You want a query like this then.

<pre>SELECT Detail.ProjectDetail
FROM Detail
JOIN User on Detail.ProjectId = User.ProjectId
AND User.UserId = TT15</pre>

As I understood, Minuvinu needs a comma-separated list.
Here the sketch of solution (MS SQL 2005):

<pre>select userid, (select cast(projectid as varchar)+’, ‘ as ‘data()’ from [user] u1 where u1.userid=u2.userid for xml path(”)) list
from [user] u2
group by userid</pre>

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • KVGK
    Hi, I think your query is correct..but my suggestion is in real time scenarios, it will be better to avoid the joins in queries as it will degrade the performance. I think, you can write the simple query as SELECT Detail.ProjectDetail FROM Detail, User WHERE User.UserId ='TT16' AND User.ProjectId = Detail.ProjectId
    25 pointsBadges:
  • Denny Cherry
    That is the exact same query as the one I wrote. The only different is that my query uses the newer standard of writting the INNER JOIN, when the query that you wrote is the older style of INNER JOIN. The SQL Engine will process both queries the same. It is recommend to not use the older syntax as it will eventually no longer be supported any more.
    69,075 pointsBadges:
  • Denny Cherry
    Creating an XML document for each row is a very expensive operation. It would be much more effective to return the data in a single recordset and have the UI roll up the data. Based on the initial requirements the op needs to be able to query this by user.
    69,075 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: