Hello, The queries for the Table browser can all be recreated in regular SQL, but the method used by the Table browser will not always be most desirable query for a user. However, the schema information provided by the Table browser will definately be necessary when constructing your own query. For our example, I am assuming that you are using our public mySQL server (not Oracle or Sybase which have their own syntax).
For mySQL syntax, try a google search. There are several good sites. To learn the table structure, using the Table browser, bring up the track of interest. The default table is the primary table. Click on the view schema button next to the table. This page has three components. Top = the table schema with examples and data definitions. Middle = Linked tables, the actual table name and the key (field) to link between in the "where" clause of your mySQL statement. Bottom = (For primary tables only) The track description. The middle tables are interactive, meaning that you can click on them to view their schema and related tables below. A general query would be something like this: select table1.field1, table3.field2 from table1, table3 where table1.field3 = table2.field4; The select and from clauses are required. The where clause is optional but is where the actual filtering or joining between data keys occurs. This can get complicated. Then there are all sorts of other options, "sort by" and such. There are rules for using quotes and rules for when a "=" should be used (for numbers) rather than an "equal to" should be used (for non-numbers). Wildcards are possible in some types of matches. And of course the whole thing can be wrapped up into a shell, perl, c or other script with the right libraries installed. Here is the link to our public server mySQL help page, that includes some help regarding set up: http://genome.ucsc.edu/FAQ/FAQdownloads#download29 If you find yourself stuck with a particular query, we can offer some help to fine tune or offer advice, Thanks, Jennifer ------------------------------------------------ Jennifer Jackson UCSC Genome Bioinformatics Group ----- "Justin Fincher" <[email protected]> wrote: > From: "Justin Fincher" <[email protected]> > To: [email protected] > Sent: Wednesday, September 2, 2009 11:55:49 AM GMT -08:00 US/Canada Pacific > Subject: [Genome] building MySQL queries (examples or techniques) > > Pardon if this is a remedial question, but I am just getting started > working with using MySQL queries to extract information from the > database. I was wondering if there was a resource with some example > queries or if there was a basic "formula" for understanding how to > convert what is done in the table browser to a database query. I > only > have a little experience with MySQL and am finding browsing through > the tables to be less beneficial than I would hope. Thank you for > your help. > > - Fincher > _______________________________________________ > Genome maillist - [email protected] > https://lists.soe.ucsc.edu/mailman/listinfo/genome _______________________________________________ Genome maillist - [email protected] https://lists.soe.ucsc.edu/mailman/listinfo/genome
