November 13th, 2013, 09:15 AM
Mysql query using join
i am a frontend developer. i recently came across this query. can anyone help me understand this query.
[MYSQL]SELECT DISTINCT aln.strain FROM " . ALIGNMENT_TABLE . " aln JOIN " . SET_TABLE . " s ON s.id=aln.setid WHERE aln.ownerid='$ownerid' AND s.deleted=$deleted AND s.organismid=$organismid ORDER BY s.uploaded ASC, aln.strain[/MYSQL]
thanks in advance.
November 13th, 2013, 09:36 AM
First delete some syntactical string sugar and some reorganization to almost be able to see what the query would look like:
SELECT DISTINCT aln.strain
FROM ALIGNMENT_TABLE aln
JOIN SET_TABLE s ON s.id=aln.setid
1. join ALIGNMENT_TABLE with SET_TABLE
2. Filter away based on ownerid, deleted and organismid
3. sort on s.uploaded and aln.strain
4. return a bunch of rows of aln.strain values
But since we don't know what type for example the column uploaded is we can't tell you if you are going to get the oldest records (if it's a datetime) or if it's an int that counts how many times a file has been uploaded, etc).
But always start by removing the syntactic sugar of the frontend programming language so that you actually see the query that is sent to the database (or better yet, run the query and capture it in the general query log for MySQL that way you will truly see what the query looks like, many problems like non initialized variables in where condition etc is found this way).
November 13th, 2013, 09:38 AM
sr, how can you return only DISTINCT aln.strain and yet ORDER BY s.uploaded?
my brain's query-understanding-centre just exploded
November 13th, 2013, 09:52 AM
Well I didn't say that his query is correct! I just spun a small tale around it to try to explain in layman's eyes what we read directly in the SQL.
November 14th, 2013, 09:16 AM
thanks for the reply....
it seems my query is incorrect beciause i am not getting the desired result. can anyone tell me what am i doing wrong?
November 14th, 2013, 09:50 AM
we don't know what it's returning, nor why it's wrong, nor what you actually want it to return
Originally Posted by ap88
perhaps you can explain in more detail what you're trying to do?