RIDDLE

I bet this is simple, but I need help!

OK CHECK IT

Table A

UserID Date DateID
===== ==== =====
1 2/2/07 100
2 3/12/07 101
3 4/1/07 102
2 5/10/07 103

Table B

DateID UserID
===== =====
2 100

I am user 2.
I need a query that will bring back everything from table A that has my ID (2) and everything from table A that is associated with my ID from table B.

(so I should get back 3 records)

Make sense?

Anyone have an idea?

No it does not make sense. Which Id are you talking about UserId or DateID? From the data you provided neither matches with what you are expecting the output to be.

|||

Hopefully it is obvious that I messed up.

Table B fields should be reversed... DateID = 100 and UserID=2

|||

cyberwin:

Hopefully it is obvious that I messed up.

Table B fields should be reversed... DateID = 100 and UserID=2

That would only bring in 2 records. You were expecting 3?

|||

Also, can you post the exact output you are expecting since the structure of the 2 tables are different. I am guessing you need the 2 rows from TableA and 1 row from TableB.

|||

What I need is 3 rows from table A.

The 2 that have my ID (2) and the the one that links to my ID according to table B.

|||

Declare @.userIdintSet @.userId = 2Select UserID, DateID, Datefrom TableAWhere UserId= @.userIdUNIONALLSelect DateID, UserID,NULLfrom TableBWhere DateId= @.userId

|||

What you're saying really doesn't make sense. Why don't you put in the data you expect to get back as you would expect to see it

|||

Table A

UserID Date DateID
===== ==== =====
1 2/2/07 100
2 3/12/07 101
3 4/1/07 102
2 5/10/07 103

Table B

UserID DateID
===== =====
2 100

What I expect to get back is (assuming I am passing in UserID=2)

UserID Date DateID
===== ===== =====
2 3/12/07 101
2 5/10/07 103
1 2/2/07 100

I don't think it can be done with a single query. I think I will have to write a recursive function that checks the link table (table B) for the UserID that is passed in and goes back to table A for the record that matches...

|||

You can do it via the UNION ALL method I posted in my previous reply. give it a try and if you cant get it to work post back and we can help you out. Its pretty simple.

|||

Hey ndinakar, that sure did work!

I need to tweak it a bit to get exactky what I need... but that is certainly the right direction!

Thanks a million for the help. I was playing with that for an hour before I posted it!

thanks again