Table join question - comp.databases.mysql

Hi, I'm just learning mysql and have a question about joining tables. I have two tables, and they both have a field called 'rating'. However, these two fields do not correspond to the same thing. If I join the two tables, how do I specify each particular 'rating' in the result? So if I have a table 'file' with these fields: FILEID FILENAME USERID RATING And a table 'ratings' like this: RATINGID FILEID (corresponds to fileid above) USERID RATING Then if I want to get a list of all the files along with each files rating from a particular user 'thisuser', I would use something like this? SELECT files.fileid, files.filename, files.rating, ratings.rating FROM files,ratings ON files.userid = thisuser and ratings.userid = thisuser and ratings.fileid=files.id But then in my results, I would have two fields called 'rating'? The one from table files and the one from table ratings. How do you deal with that in mysql? I could change the name of the field in one table, so that there is no overlap. But I was just wondering if there was another solution. Thanks Bob

Bob Sabiston escribi�: > I'm just learning mysql and have a question about joining tables. I have > two tables, and they both have a field called 'rating'. However, these two > fields do not correspond to the same thing. If I join the two tables, how > do I specify each particular 'rating' in the result? > > So if I have a table 'file' with these fields: > FILEID > FILENAME > USERID > RATING > > And a table 'ratings' like this: > RATINGID > FILEID (corresponds to fileid above) > USERID > RATING > > > Then if I want to get a list of all the files along with each files rating > from a particular user 'thisuser', I would use something like this? > > SELECT files.fileid, files.filename, files.rating, ratings.rating > FROM files,ratings > ON files.userid = thisuser and ratings.userid = thisuser and > ratings.fileid=files.id Have you actually tried? > But then in my results, I would have two fields called 'rating'? The one > from table files and the one from table ratings. How do you deal with that > in mysql? I presume your problem is in the client language you are using to retrieve data (PHP?). If the API allows to fetch columns by position, there's no problem at all. However, if the API fetches columns by name, you have to write queries with no duplicate names, e.g.: SELECT files.fileid, files.filename, files.rating AS files_rating, ratings.rating AS ratings_rating FROM files,ratings ON files.userid = thisuser and ratings.userid = thisuser and ratings.fileid=files.id BTW, I suggest you learn to use the JOIN operand. Joining tables the way you do is very hard to maintain. -- -- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programaci�n web: http://borrame.com -- Mi web de humor satinado: http://www.demogracia.com --

On Wed, 25 Nov 2009 10:33:15 -0600, Bob Sabiston <bob@flatblackfilms.com> wrote: >in my results, I would have two fields called 'rating'? The one >from table files and the one from table ratings. How do you deal with that >in mysql? Check out the 'AS' clause in the manual. -- Dennis

> > BTW, I suggest you learn to use the JOIN operand. Joining tables the way > you do is very hard to maintain. > What do you mean that it is hard to maintain? In what way? Do you recommend a book or source for learning this?

On Nov 25, 11:03=A0am, Dennis <nob...@nowhere.invalid> wrote: > On Wed, 25 Nov 2009 10:33:15 -0600, Bob Sabiston > > <b...@flatblackfilms.com> wrote: > >in my results, I would have two fields called 'rating'? =A0The one > >from table files and the one from table ratings. =A0How do you deal with= that > >in mysql? > > Check out the 'AS' clause in the manual. > > -- > > Dennis Which manual? I am working out of a big PHP and MySql development book, but it does not go into enough detail for me on table joining. Thanks Bob

Bob S escribi�: >> BTW, I suggest you learn to use the JOIN operand. Joining tables the way >> you do is very hard to maintain. >> > > What do you mean that it is hard to maintain? In what way? Do you > recommend a book or source for learning this? Using explicit joins makes it more visual. Also, the join conditions are kept separated from the row conditions. Compare: SELECT * FROM customers c INNER JOIN orders o ON c.customer_id=o.customer_id LEFT JOIN coupons ON o.coupon_id=c.coupon_id WHERE c.customer_id=31416 with SELECT * FROM customers c, orders o, coupons c WHERE c.customer_id=o.customer_id AND (o.coupon_id IS NULL OR o.coupon_id=c.coupon_id) AND c.customer_id=31416 -- -- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programaci�n web: http://borrame.com -- Mi web de humor satinado: http://www.demogracia.com --

Bob S escribi�: > On Nov 25, 11:03 am, Dennis <nob...@nowhere.invalid> wrote: >> On Wed, 25 Nov 2009 10:33:15 -0600, Bob Sabiston >> >> <b...@flatblackfilms.com> wrote: >>> in my results, I would have two fields called 'rating'? The one >> >from table files and the one from table ratings. How do you deal with that >>> in mysql? >> Check out the 'AS' clause in the manual. >> >> -- >> >> Dennis > > Which manual? I am working out of a big PHP and MySql development > book, but it does not go into enough detail for me on table joining. Load http://www.mysql.com and find your MySQL server version in the "Documentation" tab. The manual can be downloaded or read online. SQL is covered basically in the "SQL Statement Syntax" chapter ("Data Definition Statements" and "Data Manipulation Statements"). If you have a book, find whether it explains the basics of relational databases (like normalization or primary keys). -- -- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programaci�n web: http://borrame.com -- Mi web de humor satinado: http://www.demogracia.com --

On Wed, 25 Nov 2009 09:09:07 -0800 (PST), Bob S wrote: > >> >> BTW, I suggest you learn to use the JOIN operand. Joining tables the way >> you do is very hard to maintain. >> > > What do you mean that it is hard to maintain? In what way? Do you > recommend a book or source for learning this? Any good book on SQL should cover joins. I like Jan Harrington's "SQL Clearly Explained" because it's *very* careful to avoid the specific quirks of particular RDBMSs, so one doesn't end engraining habits from mysql that don't port to DB2, or Oracle features that MSSQL doesn't have. As for why it's bad and hard, the basic reason is two-fold: 1) Comma-joins like you're using separate the tables from the relationship between them. That doesn't seem like a big deal for a 3-table example query, but for really big things[1], it can become prohibatively difficult to keep that all in mind and hard to map back out later. 2) Comma joins also don't allow some of the flexibility of being able to specify HOW the join works. Do you want records only where there's corresponding elements in both tables? What if you want a list of all files, whether rated or not, but showing the ratings if there are some? -- 7. When I've captured my adversary and he says, "Look, before you kill me, will you at least tell me what this is all about?" I'll say, "No." and shoot him. On second thought I'll shoot him then say "No." --Peter Anspach's list of things to do as an Evil Overlord

Bob S wrote: > On Nov 25, 11:03 am, Dennis <nob...@nowhere.invalid> wrote: >> On Wed, 25 Nov 2009 10:33:15 -0600, Bob Sabiston >> >> <b...@flatblackfilms.com> wrote: >>> in my results, I would have two fields called 'rating'? The one >> >from table files and the one from table ratings. How do you deal with that >>> in mysql? >> Check out the 'AS' clause in the manual. >> >> -- >> >> Dennis > > Which manual? I am working out of a big PHP and MySql development > book, but it does not go into enough detail for me on table joining. > Thanks > Bob > Try the doc at http://www.mysql.com - it's very complete. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================

> > Which manual? =A0I am working out of a big PHP and MySql development > book, but it does not go into enough detail for me on table joining. > Thanks > Bob See my paper join types here http://www.gplivna.eu/papers/sql_join_types.htm Gints Plivna http://www.gplivna.eu