I've been having some trouble with an SQL statement I'm trying to create and I figured that someone here would probably be able to help me out.  I've got two tables: Matches and Users. The Matches table consists of User_id and Partner_id that represents a user and their respective match (partner). Each time a user clicks a button, a new match will be found and a row will be created in the Matches table.  Now, when a user clicks the button to find a match, I need to - randomly - find a user in the Users table that is not the user himself (obviously) and does not already have a relation to the current user in the Matches table. - This is where I get stuck.  How can I pick a user based on the criteria that they do not already have a relation to the currently logged in user, through the Matches table?  All answers are much appreciated. Thank you.  Edit: Having read both  and  (as well as others)  only had me more confused afterwards. - I simply didn't quite understand the answers that were given well enough to convert them to what would have been useful in my case.
3504
I've got the following two tables (in MySQL): Phone_book +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1  | John | 111111111111 | +----+------+--------------+ | 2  | Jane | 222222222222 | +----+------+--------------+  Call +----+------+--------------+ | id | date | phone_number | +----+------+--------------+ | 1  | 0945 | 111111111111 | +----+------+--------------+ | 2  | 0950 | 222222222222 | +----+------+--------------+ | 3  | 1045 | 333333333333 | +----+------+--------------+  How do I find out which calls were made by people whose phone_number is not in the Phone_book? The desired output would be: Call +----+------+--------------+ | id | date | phone_number | +----+------+--------------+ | 3  | 1045 | 333333333333 | +----+------+--------------+
96046
I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.  I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.  What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?
115476
I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for these entries, such as email and firstname.   I first used group by to get every user where those two values are duplicates with this query:  SELECT *  FROM jasenet  GROUP BY hetu, sukunimi  HAVING count(*) &gt;= 2  ORDER BY sukunimi;   This returns all duplicate entries. After some research I tried to use IN operator in order to find out each individual duplicate person with this query:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   This works somewhat because I get each individual with duplicate surname, but in addition to this I need to be able to have the query use the social security number as well (hetu).  I tried this query, but it didn't work:  SELECT *  FROM jasenet  WHERE sukunimi  IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) AND hetu IN (SELECT sukunimi      FROM jasenet      GROUP BY hetu, sukunimi      HAVING count(*) &gt;= 2      ORDER BY sukunimi) ORDER BY sukunimi;   How can I use two columns returned from a query in another query?
44610
I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id. I know the syntax to get the row data, but I have never tried comparing 2 rows from the same table before. How is it done? Would I need to use variables to store the data from each? Or some other way? (Using SQL Server 2008)
95649
I have been taught in my MSSQL classes, this is how to join two tables   select * from FirstTable A JOIN SecondTable B on A.ID= B.ID    Now in my professional life, I came across JOIN queries like this   select * from FirstTable A, SecondTable B where A.ID=B.ID    I know the second option was once norm but perhaps now abandoned.  I find that in complex queries where I join 6+ tables + have a number of sub queries , the second form is a lot easier to understand and is short and pretty.  Questions   Which one should I use? Is there an advantage of one over the other?
24654
Is there any performance difference between these two queries?  select name from Account where id = :myId  select name from Account where id = :myId limit 1   Or, I guess more general: does salesforce stop searching after first hit when there's a unique value as a condition?
21469
I am studying the new database layer, and I am glad it is changed to an OOP layer. I need to implement a query with db_select(). I found I can add a WHERE statement with $query-&gt;condition(), but by default the conditions are AND'ed together.  How can I OR the conditions together?  $query=db_select('users','u')-&gt;fields('u',array('uid','title','created','uid')); $query-&gt;join('flag_content','fc' , 'u.uid = fc.content_id'); $query-&gt;condition('fc.fid', '5' , '='); $query-&gt;condition('fc.uid', $uid , '='); //…
65216
I am dissecting some code and came across this,   $sql = 'SELECT page.*, author.name AS author, updator.name AS updator '      . 'FROM '.TABLE_PREFIX.'page AS page '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS author ON author.id = page.created_by_id '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS updator ON updator.id = page.updated_by_id '      . 'WHERE slug = ? AND parent_id = ? AND (status_id='.Page::STATUS_REVIEWED.' OR status_id='.Page::STATUS_PUBLISHED.' OR status_id='.Page::STATUS_HIDDEN.')';   I am wondering what the "?" does in the WHERE statement.  Is it some sort of parameter holder?
19178
We have a user table, every user has an unique email and username. We try to do this within our code but we want to be sure users are never inserted (or updated) in the database with the same username of email. I've added a BEFORE INSERT Trigger which prevents the insertion of duplicate users.  CREATE TRIGGER [dbo].[BeforeUpdateUser]    ON  [dbo].[Users]    INSTEAD OF INSERT AS  BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      DECLARE @Email nvarchar(MAX)     DECLARE @UserName nvarchar(MAX)     DECLARE @UserId int     DECLARE @DoInsert bit      SET @DoInsert = 1      SELECT @Email = Email, @UserName = UserName FROM INSERTED      SELECT @UserId = UserId FROM Users WHERE Email = @Email      IF (@UserId IS NOT NULL)         BEGIN             SET @DoInsert = 0         END      SELECT @UserId = UserId FROM Users WHERE UserName = @UserName      IF (@UserId IS NOT NULL)         BEGIN             SET @DoInsert = 0         END      IF (@DoInsert = 1)         BEGIN             INSERT INTO Users             SELECT                             FirstName,                             LastName,                             Email,                             Password,                             UserName,                             LanguageId,                            Data,                             IsDeleted                         FROM INSERTED         END     ELSE         BEGIN             DECLARE @ErrorMessage nvarchar(MAX)             SET @ErrorMessage =                           'The username and emailadress of a user must be unique!'             RAISERROR 50001 @ErrorMessage         END  END   But for the Update trigger I have no Idea how to do this. I've found this example with google:  But I don't know if it applies when you update multiple columns at once.  EDIT:  I've tried to add a unique constraint on these columns but it doesn't work:  Msg 1919, Level 16, State 1, Line 1 Column 'Email' in table 'Users' is of a type  that is invalid for use as a key column in an index.
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
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?
107229
Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the , I have been using this join:  ... FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID  LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1 LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1 LEFT JOIN _bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1   My results show the same email send, with the same JobID, sent exactly at the same time, to the same subscriber, but with different batch numbers. On top of that, only the first record has an open date:    According to documentation, BatchID   is "The batch ID number for any batches used in the send" - but what does it mean, really? And how should the  BatchID be treated in queries?   Thanks!
89827
In my system, I want each user to have an own id sequence for his articles.   So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the outline of such table:       +------+----------+---------+     | id   | article  | user_id |     +------+----------+---------+     | 1    |some notes| 1       |     +------+----------+---------+     | 2    |some notes| 1       |     +------+----------+---------+     | 3    |some notes| 2       |     +------+----------+---------+      | 4    |some notes| 2       |     +------+----------+---------+     | 5    |some notes| 3       |     +------+----------+---------+   But I want to generate the ids in this table in the following way:       +------+----------+---------+     | id   | article  | user_id |     +------+----------+---------+     | 1    |some notes| 1       |     +------+----------+---------+     | 2    |some notes| 1       |     +------+----------+---------+     | 1    |some notes| 2       |      +------+----------+---------+     | 2    |some notes| 2       |     +------+----------+---------+     | 3    |some notes| 2       |     +------+----------+---------+     | 1    |some notes| 3       |     +------+----------+---------+   So, when a new user is being created in the associated user table, an id sequence for his articles will start from 1.   Is there any generally accepted approach to solve this problem?
112394
Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 and Table2, and Table2 needs to have the inserted id (PK) from Table1...  If I were to do this...  INSERT INTO Table1 SELECT A, B, C FROM MyTable INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable   I'd get the ID of the last inserted record into Table1.  Is a CURSOR or WHILE loop the only ways to do this?
29474
I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change.  As an minimal example, suppose I want to list the leaders of various countries in a database that tracks many politicians, including those in positions other than 'leader'. I can create a table of politicians, and a table of countries, like so:  CREATE TABLE politician (   id integer unsigned NOT NULL,   name varchar(45) NOT NULL,   PRIMARY KEY (id) );  CREATE TABLE country (   id integer unsigned NOT NULL,   name varchar(45) NOT NULL,   PRIMARY KEY (id) );   Now, if every country had only a single leader forever, I could just link these with a table such as:  CREATE TABLE country_leader (   countryid integer unsigned NOT NULL,   leaderid integer unsigned NOT NULL,   PRIMARY KEY (countryid, leaderid),   KEY fk_countryleader_country_idx (countryid),   KEY fk_countryleader_politician_idx (leaderid),   CONSTRAINT fk_countryleader_country FOREIGN KEY (countryid) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT fk_countryleader_politician FOREIGN KEY (leaderid) REFERENCES politician (id) ON DELETE NO ACTION ON UPDATE NO ACTION );   However, this situation obviously isn't the case, because existing leaders are sometimes re-elected (possibly even with some time between two different terms in office), and when they aren't then new leaders take over. So I think that a better link table would look something like this:  CREATE TABLE country_leader (   countryid integer unsigned NOT NULL,   leaderid integer unsigned NOT NULL,   termstart date NOT NULL,   termend date NOT NULL,   PRIMARY KEY (???),   CONSTRAINT ???,   .... );   So my question is, what is the best way to fill in the missing PK and constraint information in the junction table country_leader? Or should I be using a different approach to solve this problem?
69005
Need help if anyone can offer! I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about writing a query that could show me what has CC numbers have changed. If something has changed, it will send a report to the listed people. If nothing, then it does nothing. It only actions once something has happened. We have an inhouse system that will do the automated part... I just need a select statement that will return what has changed. If I can get some help on this, it would be amazing! Thank you!
81760
Do any queries exist that require RIGHT JOIN, or can they always be re-written with LEFT JOIN?  And more specifically, how do you re-write this one without the right join (and I guess implicitly without any subqueries or other fanciness):   SELECT * FROM t1 LEFT JOIN t2 ON t1.k2 = t2.k2 RIGHT JOIN t3 ON t3.k3 = t2.k3