Fetch data from two rows using a single query

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

How can I fetch Username(s) from different rows of same table, given, UID of 1st person. ClientUID is the relation we have here to link both the rows.

The table is as follows.

---------------------------------------------------
UID............Username............ClientUID
1111...............Rick.....................2222
2222...............John....................3333

I want to fetch Usernames Rick and John using single SQL query and I have UserId = 1111 data with me. Currently I have broke them down to two queries, but looking for better solution. Please suggest if any.

Posted 18-Jul-15 20:44pm

Jones Aaron

Updated 18-Jul-15 20:59pm

PIEBALDconsult

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 19-Jul-15 2:56am

It would help to show us your current solution...

2 solutions

Solution 1

I'd likely use a Common Table Expression to fetch the first row, then a UNION to fetch second.

For Example

SQL

WITH cte AS
(
  SELECT * FROM table WHERE UID=@uid
)
SELECT * FROM cte
UNION ALL 
SELECT table.* FROM table INNER JOIN cte ON table.UID=cte.ClientUID

For something more complex, you might need to use a recursive CTE.

Posted 18-Jul-15 21:04pm

PIEBALDconsult

Comments

Jones Aaron 19-Jul-15 4:08am

Thanks for your suggestion.

Following this I have parameterized queries. How do I differentiate between the first row's Username and that of 2nd row when using reader.? Please find the below code modified as suggested.

cmdString = ("WITH cte AS (SELECT * FROM dbo.UserDetails WHERE UID = @uid) SELECT * FROM cte UNION ALL SELECT dbo.UserDetails.* FROM dbo.UserDetails INNER JOIN cte ON dbo.UserDetails.UID=cte.ClientUID");

SqlCommand cmd = new SqlCommand(cmdString, Conn);

cmd.Parameters.Add("uid", SqlDbType.NVarChar).Value = UID;

try
{
if (Conn.State.Equals(ConnectionState.Closed))
{
Conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Username.Text = reader["username"].ToString(); -> Rick
...
...
...
ClientUsername.Text = reader["?????"].ToString(); -> Rick
}
Conn.Close();
}

PIEBALDconsult 19-Jul-15 11:03am

You could compare the returned UID to the one you passed in.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-18T21:39:00

If I understand your question correctly, you want both of the users on the same row.
If this is the case you can use join. So something like

SQL

SELECT *
FROM user u1
     INNER JOIN user u2 on u1.clientuid = u2.uid
WHERE u1.uid = 1111;

On thing to consider is that if the ClientUID is not mandatory, you could use LEFT OUTER JOIN instead of INNER JOIN. Have a look at Using Outer Joins[^]

EDIT

Example how to give an alias name for a column in the result set:

SQL

SELECT u1.UID         AS UID1,
       u1.UserName    AS User1,
       u2.UID         AS UID2,
       u2.UserName    AS User2
FROM user u1
     INNER JOIN user u2 on u1.clientuid = u2.uid
WHERE u1.uid = 1111;

With the example above you can fetch the values from the reader with

reader["UID1"]
reader["User1"]
reader["UID2"]
reader["User2"]