How to calculate the age of the user inside a SQL query. Be aware I splitted the database into the fields: birthday, birthmonth and birthyear.  I created a  to show what I mean.  CREATE TABLE IF NOT EXISTS `user_profile` (   `user_id` int(11) NOT NULL AUTO_INCREMENT,   `user_birthday` tinyint(4) NOT NULL,   `user_birthmonth` tinyint(4) NOT NULL,   `user_birthyear` smallint(6) NOT NULL,   PRIMARY KEY (`user_id`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  INSERT INTO `user_profile`  (user_birthday, user_birthmonth, user_birthyear) VALUES (31, 12, 1990)
9379
What would be the best way to calculate someone's age in years, months, and days in T-SQL (SQL Server 2000)?  The datediff function doesn't handle year boundaries well, plus getting the months and days separate will be a bear.  I know I can do it on the client side relatively easily, but I'd like to have it done in my .
73
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to , like in the following example:  $unsafe_variable = $_POST['user_input'];   mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");   That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:  INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')   What can be done to prevent this from happening?
45859
I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation: pointsup (contains up points) and pointsdown (contains down points)  select sum(pointsup - pointsdown) as points  from table1 t1 join table2 t2      on t2.Id = t1.id where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'  group by t1.id  order by points desc   Everything is fine, but the problem comes when I have to rank the users with same points. In this case, I have to consider the registration date: who registers first will come up first on the ranking. The registration date is in another table table3. I have tried to join this third table but the sum is behaving oddly, showing wrong sum calculation.  Can anyone help please?
45100
I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this:  +----------+----------------+---------------------+---------------------+ | server   | user           | start               | end                 | +----------+----------------+---------------------+---------------------+ | server01 | Alice,Bob      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 | | server02 | Carlos         | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 | | server03 | Dan,Erin,Frank | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 | +----------+----------------+---------------------+---------------------+   To check whether user Carlos has a booking for server02, I use the following SQL query:  SELECT 0, id FROM booking WHERE user = 'Carlos' AND (( start &lt; now() and end &gt; now() ) AND server = 'server02') ;   But this query does not work if there are multiple (comma-separated) users. How can I check if Alice has booking for server01 ?
109174
I was writing a query on SEDE, when I noticed there was no "Days Visited" value.    I have circled the available user metrics, alas there is no Days Visited  While there is a CreationDate, this can't be used as accuretly in queries as me (and other people) create accounts on sites but then rarely visit the site.  I'd use this to weight user "scores" but they are probably a lot more uses for this.
86193
Now that we have an awesome tool , won't its usefulness be multiplied manifolds if we could have a way of embedding queries onto a site that can fetch the results without the user having to actually go to the StackExchange website?    Something like this:    goes to    select top 10 u.Id as [User Link],   (select sum(len(Body)) from posts where owneruserid = u.id) posts,  (select sum(len(Text)) from comments where userid = u.id) comments,  (select sum(len(Body)) from posts where owneruserid = u.id) +  (select sum(len(Text)) from comments where userid = u.id) total from users u order by total desc   and produces the result  User Link        posts   comments total    ---------------- ------- -------- -------  Jon Skeet        6895871 1616661  8512532  Marc Gravell     4454304 800243   5254547  Alex Martelli    4018617 737938   4756555  VonC             3688197 355679   4043876  BalusC           3235636 740699   3976335  Pascal Thivent   3298339 427297   3725636  S.Lott           2473343 1177130  3650473  paxdiablo        2870220 745320   3615540  Jonathan Leffler 2663832 885100   3548932  tvanfosson       2972126 564040   3536166   (10 row(s) affected)     I am not an expert on web designing and might have not used the perfect language while describing this. So, if you find any technical faults with the question, please edit.   Can this be done already? If not, what do you think about its usefulness?
28370
I have a table similar to this:  CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer);   I can use the parentid field to arrange data into a tree structure.  Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?  UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.  BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
28397
I have these two CREATE TABLE statements:   CREATE TABLE GUEST (   id int(15) not null auto_increment PRIMARY KEY,   GuestName char(25) not null );  CREATE TABLE PAYMENT (   id int(15) not null auto_increment   Foreign Key(id) references GUEST(id),   BillNr int(15) not null );   What is the problem in the second statement? It did not create a new table.
123207
Can anyone help me to find a below given details for long running query. ProcessID, process name, database, host ,user ,process login time, query start time and query duration.   I am looking for a query or an SP which gives me this data.
13404
The  explains how to put a Datetime query in a SOQL query.    SELECT Id FROM Account WHERE CreatedDate &gt; 2005-10-08T01:02:03Z   I need to just put in a date for this query. Something like:   SELECT Id FROM Account WHERE CreatedDate &gt; 2005-10-08   But that throws an error. Any ideas?
22741
There is a restriction on setting your birthday:     Oops! There was a problem updating your profile:   - Birthday must be after 1920/01/01   I realize the number of 90 year old users is small, but shouldn't the restriction be a bit more relaxed, such as larger than any ?  Either 120 or 150 seems appropriate.  For example, my grandfather turns 90 next year (and just barely misses the current cutoff), but I can't imagine he's the only one around that age who is computer literate and still active enough to be interested in (the increasingly broad range of) SE sites.  I don't think he has contributed yet (mostly because of the comment restriction, I'd see him commenting before asking or answering), but I have emailed him with links that I know he's read.  Incidentally, the error message is wrong: birthday must be after 1919/12/31 and using the next day is accepted.
25957
I want to write a function that returns users which have entered a certain value to a custom user field.   Lets say there is a custom birthday field for every user. What would be the best way to get all users with a certain birthday?  I could create a DB query that searches the field_data_field_birthday table and returns the entity_id(s) (which in this case would be the users id(s)) for which the field_birthday_value matches a given value and then load the user(s).   This seems a little bit crude, are there generic functions for this use case available? In the entity API or the field API maybe?
9564
I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same column (meta_value).   The database structure is as follows:  id | user_id | meta_key | meta_value   sample data is as follows:   1 | 3434 | first_name | Brandon 2 | 3434 | last_name  | Johnson 3 | 3434 | street_add | 123 main 4 | 3434 | city       | ocean beach 5 | 3434 | state      | Texas   I am trying to return the first name, last name, street_add, city, and state in one sql statement.  I'd like the output to look like:  Brandon, Johnson, 123 Main, Ocean Beach, Texas
34791
This is the announcement post of the Stack Exchange Data Explorer. Its answers were meant to iron out the initial glitches 6 to 8 years ago.  If you have found a bug or need support  today with any of SEDEs features, please ask a new question and tag it with   What does it do? Allows you to run arbitrary SQL queries on the data dumps, and share those queries with your friends. Technical details It is hosted on the Stack Exchange infrastructure , the database is hosted on the same SQL Server version all other sites are hosted on. You can use most of the TSQL commands, even create temporary objects (but not User Defined Functions). The controls used are jQuery based, the grid is  . All query results are cached, so sharing a link to a query will not result in the server going in to a tailspin. The query edit control is a slightly modified  control, I also am considering bespin cause the inter iframe communication is nasty complicated. It gives up nice syntax highlighting for queries. Parameters There is support for parameterized queries, to get it to work name your params like ##this##. For example have a . Query name and description A query can have a name and description, to name it lead with a comment, to describe it continue commenting, for example: -- This is my query name  -- This is my description -- I can span multiple lines  SELECT 1  Anonymous users can initially name a query, but can not change the names of any existing queries. Tutorial There's a  which helps you getting started in writing your own SQL queries. Logging in You can use your openid provider to log in, at the moment once logged in we will track the list of queries you execute (giving you basic history support) Also if there are any params named UserId they will be populated automatically if your EmailHash matches the one on the site being queried. Magic columns If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site. Similarly if you alias an id column with as [User Link] it will display a link to the user page. select top 10 * from Posts will show you how tags are done. (Magic columns for images is planned) example (most controversial posts:  ) So what are you waiting for, check it out at  Any feedback would be greatly appreciated. I hope to have the login section working fairly soon and allow voting on the best queries and commenting. Featured Queries At the moment, admins have permission to feature interesting queries on this page , I am looking for better ways to manage the huge query list that is building up.
48895
I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this:  set session "myapp.user" = '12345'; ... SELECT current_setting('myapp.user');   and it seems to work - I thought that "myapp.user" should be declared in .conf file but it seems that I can create session variables on the fly (I didn't change .conf file at all).  Is there any disadvantage of doing like this?
9915
Query 1:  select count(UserID) from users where UserID IN (select UserID from otherTable)   Returns: 178  Query 2:: (simply changed IN to NOT IN)  select count(UserID) from users where UserID NOT IN (select UserID from otherTable)   Returns: 0  Query 3: (remove the IN-clause completely)  select count(UserID) from users   Returns: 1047123 (over a million)  What? I can't... Even...   The only explanation I have is my database in inconsistent/corrupted, I have to run DBCC CHECKDB or something.  PS. UserID is a primary key, so - no dupes.
71286
In MySQL table definitions is there a different between INT(5) and SMALLINT(5)?  Or do they both represent the same size?