Hello again, I have two basic questions:
1. How should I write the SQL sentence in order to get all the elements that match a particular interval? For example: Let Es = start position of an element, Ee = end position of an element, Qs = start position of my query interval, Qe = end position of my query interval. If I use chromStart and chromEnd, in order to find all the elements that match a particular interval I would have to use 4 different parameters in the WHERE clause a. Es1 > Qs AND Ee1 > Qe b. Es2 > Qs AND Ee2 < Qe c. Es3 < Qs AND Ee3 < Qe d. Es4 < Qs AND Ee4 > Qe However, I think there should be an easier way to obtain the same result... 2. I'm trying to run the next nested query: SELECT * FROM (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'hg19') WHERE chrom=1 AND chromStart >= 1 AND chromEnd <= 10; ... but I get and error-> Error Code: 1248. Every derived table must have its own alias. What I want with this query is to get all the elements and their related information for a particular interval. In VBA I would use a recordset, a loop and a query to solve this, but inside MySQL I don't know how to solve it. Any comments are much appreciated. Regards, Diego -----Original Message----- From: Hiram Clawson [mailto:[email protected]] Sent: Monday, August 22, 2011 1:48 PM To: Diego F Pereira Cc: 'Jeff Murray'; [email protected] Subject: Re: [Genome] Retrieving data from UCSC Good Morning Diego: You do not need special software interfaces to fetch data from the UCSC MySQL database. As mentioned in the FAQ: http://genome-test.cse.ucsc.edu/FAQ/FAQdownloads.html#download29 the simple command line operation mysql: mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -e "select count(*) from snp131;" hg19 can talk to the database and fetch whatever you want from any table. To view the description of any table's contents: mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -e "desc snp131;" hg19 You can feed the output of these commands into any processing software, or embed the commands into your system. Yes, you will need to decide what it is you want to fetch from any particular table and how you would like to relate it to another table. The UCSC Table Browser can also perform these functions, and it can display the relationships of the tables with each other. Your experiment design will clarify what relationships you desire to highlight and extract. When you work within the table browser, you can also view your results in the genome browser. You can also use the Galaxy interface: http://galaxy.psu.edu/ to organize the workflow of your experiments. The Galaxy system cooperates with the UCSC genome browser and many other bioinformatic tools to provide an integrated experiment work-flow design procedure. --Hiram Diego F Pereira wrote: > Hello Vanessa, > Is there a way in which I can connect a MSAccess database to your > MySQL database? > Or > Should I use PHP and MySQL on my computer to pull the data I need from > your MySQL database? > I have some experience with MSAccess, but by checking PHP that seems > several times easier to work with than VBA. > I had avoided to learn MySQL for long time, but if you think this is > the best way I'll definitely learn it. > Of course if you know a better/easier approach please let me know. > Many thanks in advance, > Diego > > PS: > I believe this is going to be very interesting for all the people who > is doing in-depth analyzes of the data displayed by the UCSC browser. > Therefore, even if I'm not a computer scientist I think it worth the effort. > If this works I'll be happy of sharing it with you and all the people. _______________________________________________ Genome maillist - [email protected] https://lists.soe.ucsc.edu/mailman/listinfo/genome
