having problem in building sql query

Question

Hi there,

I am just confused in building a SQL query. I tried to run it in pieces successfully but just not able to build it as a whole.

This is the case: In table 'userseaches', user Rushi has been searched three times with different searchID's(primary key in 'searchtable') 10, 11, 12 . When searched with searchID 10 got 110 results, with 11 got 112 and with 12 got 115 results.

Now what I have to do is, get searches of ID's 11 and 12(latest two searches) i.e 112 and 115 and get the usernames of those users who are newly added in last search(112) i.e three users.

I tried this with small pieces but hard to make it in whole. please feel free to ask for details. a

Thank you in advance.

ok below are the 3 tables: table mastersearches having fields :

ID  Name   srchtime

  1  aron   22:10:10

  2  rushi  12:12:14

table usersearches having fields :

ID  masterID  Name  nooffriends
1      2      rushi  110   
2      2      rushi  112
3      2      rushi  115

table searchtable having fields :

ID  searchID Name   friends
1     2       mike    25
2     2       paine   51 
.
112   2       kumar   87
113   3       bandy   23
 .
227   3       ganua    15   

ok.

Now I want to just fetch three newly added users for the searchID 3.

Now I tried with this : got two ID's with searches with this query:

select
    Name 
from
    usersearches
where
    searchID in (11, 12)

gave all the followers:

But not able to fetch that newly added friends.

Answer 1

Your question is confusing. Are "Friends" and "Followers" the same in your context? If so then try this

Select TOP 2
    Name 
from
    usersearches
where
    searchID in (11, 12) ORDER BY ID DESC

This would work only if the ID is sequential.

Also, you provided 2 tables "usersearches" and "searchtable"...how are those tables related?

Answer 2

My first suggestion, is to redesign your table structure. You should not have the name in both the mastersearches and in the usersearches. This results in you having to change the name in both places. The name when queried should come from one location not both. I am not seeing a purpose for the table usersearches. Why does it exist? It contains information that can be derived from master and searchtable, I believe.

Secondly, you should not have a count friends that you are entering manually in searchtable. This will often result in extra work in tallying this. Either factor it programmaticly or set it as a formula such as:

SELECT m.*, f.Name as 'Friends_Name', count(s.searchID) as 'Friends'
FROM searchtable f
left join searchtable s
on f.ID = f.Id
left join mastersearches m
on s.searchID = m.ID
group by f.ID

Now, we need to add a field to searchtable to store when the friend was entered into the table, I chose lastmodified as my field.

SELECT m.ID, m.Name, f.Name as 'Friends_Name', count(s.searchID) as 'Friends'
FROM searchtable f
left join searchtable s
on f.ID = f.Id
left join mastersearches m
on s.searchID = m.ID
where m.srchtime < s.lastmodified
group by f.ID

If you need any further assistance, please let me know. You should take note that the count 'Friends' is shown on each row but is reflective of the master name and not the searchtable name.

Here is the code to create the tables I created using his model.

DROP TABLE IF EXISTS `stack overflow`.`mastersearches`;
CREATE TABLE  `stack overflow`.`mastersearches` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` text NOT NULL,
  `srchtime` time NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `stack overflow`.`searchtable`;
CREATE TABLE  `stack overflow`.`searchtable` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `searchID` text NOT NULL,
  `Name` text NOT NULL,
  `friends` int(10) unsigned NOT NULL,
  `lastModified` time NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `stack overflow`.`usersearches`;
CREATE TABLE  `stack overflow`.`usersearches` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `masterID` int(10) unsigned NOT NULL,
  `Name` text NOT NULL,
  `nooffriends` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Answer 3

Try:

select Name 
from usersearches
where searchID in (11, 12)
group by name
having min(searchID) = 12