0

I'm working on an existing .net system and I have a SQL table with "offers" being made between two users. The "offers" go two-ways (giggidy) from usertype1 to usertype2 and vice versa. To make it "simple" there is an "offertype" field that defines which way the offer is going in order for the correct user to "accept" the offer. Simple enough when you want to query it, you can execute queries quite easily if you want to get data on a single offer. Here's my problem:

I'm trying to build a view that shows a list of all offers for user1. In this view it should show all offers of "offertype1" where user1 is the "offerloginuser" and all offers of "offertype2" where user1 is the "offerrecipientuser"

offerid offerloginuserid    offerrecipientuserid    offertype
1       1                   2                       1
2       2                   1                       2
3       1                   3                       1
4       3                   1                       2
5       3                   4                       2

I need to be able to see offerid's: 1;2;3;4 but not 5 since user1 isn't involved. At an initial glance it looks simple enough, you have user IDs, the thing is, the designer of the system chose to use separate tables for user IDs based on usertype so I may have two users with the same ID.

I have two select statements:

select * from tbl_offers where offertype = 1 and offerloginuserid = 1

and

select * from tbl_offers where offertype = 2 and offerrecipientid = 1

Each executed separately give me what I need, is it possible to join them and show all results or do I have to do a join? I'm binding these results to a DevExpress grid for display to the user when logged in.

1
  • UNION ALL may help. Commented Mar 28, 2017 at 9:55

2 Answers 2

4

This could help you?

select * from tbl_offers where (offertype = 1 and offerloginuserid = 1) OR (offertype = 2 and offerrecipientid = 1)
Sign up to request clarification or add additional context in comments.

Comments

1

Please try this:-

select * from tbl_offers  
where offertype in (1,2) and offerrecipientid = 1

1 Comment

Thanks Anup but you're forgetting that it's offerrecipientid for one type of offer and offerloginuserid for another type. @etsa has answered the question, I'm just waiting so that I can accept it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.