 |
|
 |
hello there
I have question about retrieving data from two tables that have similar columns and the similar columns DATA will not be same.
i have two tables STUDENT(contains students data) and S_LESSENS(contains data about lessons that students selects in a semster)
WHAT I WANT IS :
select students which have not select any lessons in this term(termid), IN ANOTHER WORD select SIDs from STUDENT table that is not in S_LESSONS table with a specified TERMID.
Existence of SIDs in S_LESSONS table means that student have selected lesson in a semester(TERMID).
these are my tables:
CREATE TABLE IF NOT EXISTS `student` (
`sid` varchar(30) NOT NULL,
`fname` varchar(40) NOT NULL,
`lname` varchar(40) NOT NULL,
`gradeid` int(2) NOT NULL,
`termid` int(3) NOT NULL,
PRIMARY KEY (`sid`),
KEY `termid` (`termid`)
) ENGINE=MyISAM ;
CREATE TABLE IF NOT EXISTS `s_lessons` (
`lid` int(5) NOT NULL,
`sid` varchar(15) NOT NULL,
`termid` int(3) NOT NULL,
`gradeid` int(11) NOT NULL,
PRIMARY KEY (`lid`,`sid`,`termid`),
) ENGINE=MyISAM ;
i have tried this but it makes my computer crash in localhost
SELECT student.sid, student.fname, student.lname
FROM student
WHERE student.sid NOT IN (SELECT s_lessons.sid
FROM s_lessons
WHERE s_lessons.termid= 900 )
I appreciate any suggestion.
with prior thanks.
|
|
|
|
 |
|
 |
left join the tables on the two columns.
|
|
|
|
 |
|
 |
Hi All,
The following is a query I have which takes a long time to produce results mainly due to the large number of data being stored in 'aggregated_scans' table (around 60,000 records). Can anyone help me with improving the query performance ? perhaps by rewriting the query using left outer joins (though I not quite sure how) or some other means?
Query :
select hash, sex, age, count(*) as entrances from aggregated_scans where hash != '' and scan_ts >= date_sub(now(), interval 28 day) and hash not in (select hash from aggregated_scans where scan_ts < date_sub(now(), interval 28 day)) group by hash
|
|
|
|
 |
|
 |
The very first thing you need to do is remove the count(*) from the code and replace it with count(a field name). That should stop a large number of the table scans that are going on.
Secondly check all the indexing on the tables. Specifically make sure the is indexing not only on the primary key of the tables but on fields like hash and scan_ts and then see how that works.
Also have a look at the Explain function in MySQL this should help to pin point in more detail where things are going wrong.
|
|
|
|
 |
|
 |
Thanks Womle, it seems I did neglect to add index on the hash field. Now the query is much faster
|
|
|
|
 |
|
 |
Hi All,
I have an application which has its versions for different clients with local database(to make it work offline). and one centralized master database on server, to which the local databases should be synchronized at any point of time when clients are online.
Here I wanted to know what are the various approaches and mechanisms available for synchronization?
Regards,
Ashok
ashok
|
|
|
|
 |
|
 |
how we can connect sql server database in asp.net ?
|
|
|
|
 |
|
 |
Using a SqlConnection[^]-object.
This is the "MySQL" forum; you could get flamed here for posting questions on a competing product. May I suggest the "Database"-forum for further inquiries on Sql Server?
Bastard Programmer from Hell
|
|
|
|
 |
|
 |
Bernhard Hiller wrote: you should also configure the MySQL server to allow connections from other computers
You can do this in the config file by setting the bind address to the ip address of the server, rather than localhost.
|
|
|
|
 |
|
 |
I am not sure if you are familiar with php, if you are you can install phpmyadmin, outstanding tool for managing mysql
|
|
|
|
 |
|
 |
phpmyadmin is a good tool. Like many other services you would use for this purpose, it requires upkeep to remain secure. Make sure you know how to keep this product patched, and use strong passwords.
I only mention this because some trusted sites I belong to have been hacked through the phpmyadmin interface recently.
|
|
|
|
 |
|
 |
As a matter of fact,
The setting for phpmyadmin must have to be set for local only not global. which is a common mistake. But for the public site it is required so, make sure whether your entire page is SSL secure or not but phpmyadmin page must have to SSL Secure otherwise a simple monitoring on the network will reveal the password
|
|
|
|
 |