Use a Union? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Use a Union?

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-07-24 : 05:52:13

I have a table with 3 columns

var MyID int

ID int identity
From_ID int
To_ID int

I would like to display the colums as such with the following conditions:

If the from column contains a value = to MyID then it will retrieve that record and display the ID field in the FID column and the TO_ID field in the From column. No big deal this is a basic query.

Here is the tricky part. If there is a record that has the Field To_ID = MyID and the same From_ID value as the one we just returned I want that record ID to go into the TID Column and the From_ID to go into the To column.

Now even trickier. If there is no record "opposite record" I need it to return a null so that I leave a blank in the adjacent column and move on to the next record.

The records need to line up with each other in that there may be one in the left pair of columns and one in the right, or none in the left and one in the right, or one in the right and none in the left. I need to display them all. Ultimately the From_ID and To_ID are joined to another table where names are associated. Below is the results i am looking for.

FID | From | TID | To
1 | Fred | 24 | Fred this shows me that Fred is in mine and I am in his
2 | Tom | --- | ---- This shows me that Tom is in mine but i am not in his
--- | ---- | 14 | Harry and this shows that I am in Harry's but he is not in mine.

Logically I have to return all records in which either the From_ID or To_ID = MyID, but then I have to split them up so they are in the right columns (from or to) and keep them paired and preserve the blank space.

Any ideas?

here is a copy of the query used to return the left 2 columns and one to return the right 2 if they were used in separate queries and some how magically matched up.

"SELECT *
FROM dbo.UF2_rt_friends
LEFT JOIN dbo.UF2_rt_Users ON dbo.UF2_rt_friends.TO_ID = dbo.UF2_rt_Users.User_ID
WHERE ((User_Active = 1) AND (from_id = " + Replace(MyID, "'", "''") + "))
ORDER BY User_First_Name, User_Last_Name ASC"

"SELECT *
FROM (dbo.UF2_rt_friends
LEFT JOIN dbo.UF2_rt_Users ON dbo.UF2_rt_friends.From_ID = dbo.UF2_rt_Users.User_ID)
WHERE ((User_Active = 1) AND (to_id = " + Replace(MyID, "'", "''") + "))
ORDER BY User_First_Name, User_Last_Name ASC"

Phil

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-24 : 17:11:05

This would be easier to analyze if you post the columns in rt_Users and rt_Friends.

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-07-24 : 18:20:03

rt_Friends
ID int identity
From_ID int
To_ID int

rt_Users
User_ID int identity
User_First_Name nvarchar 50
User_Last_Name nvarchar 50

Those are the effected columns

Here is a picture of what I am trying to accomplish:
[url]http://www.pwcphoto.com/friends.gif[/url]

The "You Added" column is where (From_ID = MyID) these are friends that I added. The "Who Added You" Column is to show who add me to their friends list (To_ID = MyID). Notice how if we have each other in our friend list the name is on both sides, however if only i have them as a friend it is only on the left and the right side is blank, and if i dont have them as a friend but they have me then the name is only in the right column as them having added me.

It is no problem returning a recordset for each column, that is basic and is shown in the actual queries above. The problem is getting them to match up and insert the blanks where they are required, not just fill it in with the next name in the recordset.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-24 : 20:28:40

[code]
WHERE ((User_Active = 1) AND (from_id = " + Replace(MyID, "'", "''") + "))
[/code]

What's User_Active, why is MyID not @MyID, and since MyID and from_id are both INT, why are you using REPLACE which operates on VARCHAR, not INT?

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-07-24 : 21:29:59

Sam, those where copies from the .asp page, here is basically the same thing that runs in the query analyzer. it returns 2 records sets, one for my friends, the first query, and one for people that have me listed as their friends, second query. The number 82 being MyID.

SELECT ID AS FM_ID,
user_ID AS FM_user_ID,
User_First_Name AS FM_User_First_Name,
User_Last_Name AS FM_User_Last_Name
FROM (dbo.UF2_rt_friends
LEFT JOIN dbo.UF2_rt_Users AS FM ON dbo.UF2_rt_friends.From_ID = FM.User_ID)
WHERE ((User_Active = 1) AND (To_ID = 82))
ORDER BY FM_User_First_Name, FM_User_Last_Name ASC

(SELECT ID AS MF_ID,
user_ID AS MF_user_ID,
User_First_Name AS MF_User_First_Name,
User_Last_Name AS MF_User_Last_Name
FROM (dbo.UF2_rt_friends
LEFT JOIN dbo.UF2_rt_Users AS MF ON dbo.UF2_rt_friends.TO_ID = MF.User_ID)
WHERE ((User_Active = 1) AND (from_ID = 82)))
ORDER BY MF_User_First_Name, MF_User_Last_Name ASC

I don't have a problem with the query returning records, the problem is how to get them to merge with each other so that I can display them as shown in the picture above.

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 08:36:37

You just need to 2 left joins...


SELECT 
	ID AS FM_ID,
	user_ID AS FM_user_ID, 
	User_First_Name AS FM_User_First_Name, 
	User_Last_Name AS FM_User_Last_Name,
	ID AS MF_ID, 
	user_ID AS MF_user_ID, 
	User_First_Name AS MF_User_First_Name, 
	User_Last_Name AS MF_User_Last_Name 
FROM dbo.UF2_rt_friends 
LEFT JOIN dbo.UF2_rt_Users AS FM 
ON dbo.UF2_rt_friends.From_ID = FM.User_ID
LEFT JOIN dbo.UF2_rt_Users AS MF 
ON dbo.UF2_rt_friends.From_ID = MF.User_ID
WHERE User_Active = 1 AND 82 in (To_ID,from_ID) 
ORDER BY isnull(FM_User_First_Name,MF_User_First_Name), isnull(FM_User_Last_Name,MF_User_Last_Name) ASC

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources