-3

I am trying to do a SELECT to get the offers a certain user have earned. However, I cannot figure how to do it.

I have three tables

user: id | name
offer: id | name
user_offer: id | user_id | offer_id

How do I select all offer the user 1 has?

halfer
  • 19,824
  • 17
  • 99
  • 186
Pixark
  • 385
  • 1
  • 4
  • 13
  • I think You should try a SQL tutorial first. For example this one: http://beginner-sql-tutorial.com/ . Trust me, after a little learning You will do this simple select for yourself. –  Mar 11 '14 at 16:29

1 Answers1

2

This should work:

SELECT offer.name 
FROM offer INNER JOIN user_offer 
ON user_offer.offer_id=offer.id 
WHERE user_offer.user_id='1'
CodeBird
  • 3,883
  • 2
  • 20
  • 35
  • Yup, although `user_id` is likely to be an integer (and so probably does not need quoting). – halfer Mar 11 '14 at 17:37
  • @halfer I just prefer quoting all passed variables – CodeBird Mar 11 '14 at 17:59
  • I'd say the prevailing advice would be to [quote only if it is a string](http://stackoverflow.com/questions/2078942/should-i-quote-numbers-in-sql). – halfer Mar 11 '14 at 21:56
  • @halfer don't quote if it is a number, here user_id is a code not a number, you don't sum the user_id's or get their average so basically quoting here is good – CodeBird Mar 11 '14 at 22:06
  • We don't know the type, but we can guess that it is an integer, since it appears to be a foreign key and it is numeric with no string component. I am wondering whether using quotes might force the SQL parser to re-cast the integer part on the left-hand side into a string, incurring a (minor) time cost that is unnecessary? You are right in the sense that it will work, however. – halfer Mar 11 '14 at 22:10
  • @halfer from the question you linked to: `If you store product ID's in the database as strings, but in reality they are only made up of digits, then they're codes, not numbers. Codes should be quoted, numbers should not.` Here user_id is an integer for sure, but not a number in the meaning that you don't count it it is a code. imagine you have a mask for your ids 20141 20142 etc. mask is obviously 2014 so this is no longer consider a number but a code. – CodeBird Mar 11 '14 at 22:14
  • Thanks for the reply. Your quoted text refers to strings that are made up of digits but are stored as strings (e.g. product codes) and we agree that these should be quoted (you have no choice, in fact - not quoting these would be rejected by the parser). But that has no bearing on actual integers which are stored as integers, even though we are not interested in their mathematical properties. As I say, I suspect that quoting will cause the SQL engine to unnecessarily re-cast them, which is why I advise against it. We may to have to agree to disagree, of course. – halfer Mar 11 '14 at 22:20
  • 1
    @halfer I am currently doing some search if that would have minor time cost and will surely change my practice if that is true, so thanks for the advice. – CodeBird Mar 11 '14 at 22:33