Enforcing Relationships In Tables

[skaterdav85]

whats the difference (any advantages or disadvantages) of enforcing table relationships through the WHERE clause or through JOINS? Heres a simple example.

SELECT title, genre FROM titles, genres WHERE genres.genre_id = titles.genre_id

OR through an inner join

SELECT title, genreFROM titlesINNER JOIN genresON genres.genre_id = titles.genre_id

[justsomeguy]

I'm not quite clear on the specifics, but I believe that the first example will create a temporary table in memory that consists of all possible combinations from each of the joined tables, or the cartesian product. So, if each table has 10 rows, it will create a temporary table with 100 rows. Then it will apply the where condition to the temporary table and return the results that match. I believe the second example will create a temporary table only with the rows that match and return that, so the second one should be more efficient to use. But like I said, I'm not clear on the specifics.

[skaterdav85]

oh ok. well how would you convert this sql statement into using joins? All the inner join examples i've seen only combine 2 tables.

SELECT title, genre, rating FROM dvd_titles, genre, rating WHERE genre.genre_ID = dvd_titles.genre_ID AND rating.rating_ID = dvd_titles.rating_ID AND title LIKE 'A*'

heres the schema

Edited July 21, 2009 by big dave

[justsomeguy]

You just use two joins. You join two tables on whatever columns, then join that with the other table.

SELECT title, genre, rating FROM dvd_titles INNER JOIN genre ON genre.genre_ID = dvd_titles.genre_IDINNER JOIN rating ON rating.rating_ID = dvd_titles.rating_IDWHERE title LIKE 'A*'

[djp1988]

The two SQL's are the same, the only difference is the way the SQL engine will execute your query, one will be slower then the other, if you're interested in this side of SQL, lookup SQL optimization

[skaterdav85]

You just use two joins. You join two tables on whatever columns, then join that with the other table.

SELECT title, genre, rating FROM dvd_titles INNER JOIN genre ON genre.genre_ID = dvd_titles.genre_IDINNER JOIN rating ON rating.rating_ID = dvd_titles.rating_IDWHERE title LIKE 'A*'

ok i see. What if the rating table had a relationship to the genre table and had no relationship to the dvd_titles table. And say you decide to use the USING keyword like this:

SELECT title, genre, rating FROM dvd_titles INNER JOIN genre USING genre_IDINNER JOIN rating USING rating_IDWHERE title LIKE 'A*'

How does the sql interpreter know if rating_ID is joined to the dvd_titles table or if its joined to the genre table? Or would the query be different?

[justsomeguy]

When you do a join, you're creating a new temporary table. The third join isn't joining to one of the two other tables, it's joining to the new temporary table.

[skaterdav85]

When you do a join, you're creating a new temporary table. The third join isn't joining to one of the two other tables, it's joining to the new temporary table.

oh ok that makes alot more sense. In that new temporary table, does it contain all the columns from both tables, or just the ones you chose in the SELECT part of the SQL statement?just curious, but i see you are focusing on php and javascript. do you just keep reading different books on these languages? are u doing javascript or a js library? Edited July 22, 2009 by big dave

[justsomeguy]

I'm actually not sure about that, I believe it only creates columns for the ones you specify.I haven't been reading much lately. If I were to pick up another book about programming, it would be about SQL. There are so many intricacies with SQL, and the only formal training I've had was really one class in college. I have a good textbook about database theory, but at the time I only read it because I had to and not really because I was interested in what it had to say. If I can find the time to dedicate to it, I might re-read that one, or pick up some books about optimizing SQL queries and database theory in general. It's pretty easy to set up a database to do what you need, but it's really easy to set up an inefficient database just because you don't know any better. So SQL would probably be top on my list of things to study. It's pretty amazing about the performance gains you can get from just a few small optimizations.Lately almost all of my Javascript development has been using the ExtJS framework. The just released a new version of ExtJS that includes a lot of cool new things, and they've also got something called Ext Core that doesn't include any of the user interface stuff, it's just the DOMquery/ajax/data management stuff. At the day job I've been designing and developing a pretty big application for more than a year. It's a learning management system, or LMS, and right now we have 5 of our clients using it. We've got another 20 or 30 clients using previous versions that we'll eventually move to the new system. The largest client we have using it has about 70,000 registered users, with between 1,000 and 4,000 new users added per month. Just in numbers, that uses about 527KB of PHP code to do the backend work, and a total of 1.4MB of Javascript code for all of the interface stuff. That's just the code I've written, not including the framework stuff. A lot of that code is largely duplicated with small changes for the various user types. The main admin Javascript code alone is about 611KB, there are 2 other admin types that have subsets of that code, and then the student users who only need 134KB of code to get the job done. When I minify the code for delivery, the main admin code only takes up 311KB, and the student code only 74KB. The HTML and CSS is negligible compared to the Javascript and PHP. I'm the only one who designs and develops that application, so that takes up most of my time at work. I've written all of the non-framework code for it, and do all of the maintenance and new features. There's still a lot of work to do for it. The LMS isn't the core business of the company, but once this version is generally considered finished we'll start to market and sell it.At home, I still use ExtJS for various projects I do for my friends. I built this site for a friend:http://corriezazzera.com/site/I'm still working on that, still need to set up the photo gallery (watch your speaker volume if you click the Music tab unless you want to rupture an eardrum). I've got this site for a friend that runs a barbershop:http://dylansbarbershop.comHe's still setting up the content for it, and I'm not entirely satisfied with the slightly fecal interface. Still some work to do there.Other than the small stuff like that, I'm working on a ticketing/support system for another friend that runs an IT business that we'll eventually market and sell also. The barbershop site and the ticket system are using an actually-modular setup I've got working for ExtJS, the details are fairly complex but it's a really slick system. If you have Firebug open when you look at the barbershop site, you can see that clicking on the various links will send out a request for a new Javascript file, those are the modules. If you check the source there, you'll see that the modules just add new methods to the main site object, so loading the module will include the new Javascript file, add all of the new methods to the core code, and then you've got access to new stuff. It only loads it when you click on it, so it doesn't need to download a bunch of code you may not use. Eventually I might redo the LMS to take advantage of that, so I can split up all of the admin tasks into modules and then I don't need to have duplicate code for the various admin types. The word "modular" is thrown around a lot in software development, but that's really the first bonified modular system I've come up with. The barbershop site is pretty cool in that it exposes all of its content to Google also. Even though the user interface is done entirely in Javascript, I set it up specifically so that Google can find all of the content in the regular HTML, including links to other pages and stuff. The Javascript transforms a lot of the HTML into the nicer interface, and changes the navigation links and things to load modules instead of just linking to another page.So that's what I'm doing lately. Not a lot of study, mostly actual design and development. ExtJS has added a lot of capability to my work, it's pretty amazing what it can do, especially version 3. I've got several PHP classes I'm using for most things, but I'm not learning much new about PHP. Once version 6 comes out and includes support for namespaces and things like that I'll probably have more to play with.

[skaterdav85]

wow, sounds like you do quite a bit of development outside of work. What made you choose ExtJS over something like jquery? do you work for a web development firm or a company on the web development team?

[justsomeguy]

aspnetguy recommended using ExtJS for something I was trying to do, I looked over it and saw a lot of things I could do with it. I've never used jquery, it might be able to do everything that Ext can do. The company I work for makes online training programs in Flash, so we get hired to create training courses that launch in a browser. The LMS is the system that launches and tracks those courses. We've got several artists and writers who create the courses, and myself and one other programmer. We've got a library of code that the course production staff use to create the courses, they aren't programmers themselves but they're pretty good at being able to copy and paste things we've written for them in the library to set up the courses to work like they need them to, but we're available to debug anything that isn't working right. The other programmer helps more with course production code and some of the smaller projects, and I'm focused on the LMS and helping the boss write up quotes and estimates for the larger projects. Just did one today for a 650+ hour project. I've put around 2500-3000 hours into the current version of the LMS though, so that's a big one. It takes the writers and artists about 320 hours to produce a single run-hour of training. So if you have a course that takes the student an hour to go through, it takes us about 320 hours to produce the entire thing. I believe we charge $28,800 per run hour. The largest courses we've done are in the 15-16 hour range, mostly for the military or some of the larger corporate clients. We've made 3 or 4 courses for an international tax firm where each course is 12+ hours. If you're having problems getting to sleep, take a 12 hour course on how to do personal taxes.

[skaterdav85]

haha wow. thats alot of web development work. you must be an expert at this!so one more question about the example above. How would i do an insert using joins? In this example, would the SQL look for PG-13 in the ratings table, find that ID, and insert that ID into the dvd_title? Or is this completely wrong?

INSERT INTO dvd_titles (title, rating_ID)VALUES ('Transformers', 'PG-13')INNER JOIN ratingON dvd_titles.rating_ID = rating.rating_ID

[justsomeguy]

Joins are only for SELECT queries, they won't work with INSERT queries. You can use SELECT statements with INSERTs though.http://dev.mysql.com/doc/refman/5.1/en/insert.html

[skaterdav85]

How would I Join the values of one table to multiple columns in another table?For example, I have a table called "Semesters", which has 2 columns: 'ID', 'semester'I have another table called "Reviews", which has 4 columns: 'ID', 'review', 'start', 'end'Both the 'start' and 'end' columns in the Reviews table use values from the Semester table. I tried this, but apparently you cant use the same table twice in different joins.

SELECT * FROM ReviewsINNER JOIN SemestersON Reviews.start = Semesters.IDINNER JOIN SemestersON Reviews.end = Semesters.ID

How do i solve this?

[justsomeguy]

SELECT * FROM ReviewsINNER JOIN Semesters AS s1ON Reviews.start = s1.IDINNER JOIN Semesters AS s2ON Reviews.end = s2.ID

[skaterdav85]

SELECT * FROM ReviewsINNER JOIN Semesters AS s1ON Reviews.start = s1.IDINNER JOIN Semesters AS s2ON Reviews.end = s2.ID

how would i reference each value for 'start' and 'end' in my php loop?i did something like:while ($row = mysqli_fetch_array($result)) {echo $row['start']; //this gives me the number used to join the tablesecho $row['semester']; //this gives me the respective value for the startecho $row['end']; this gives me the number used to join that same table to the end column in Reviews}//how do i get that value for 'end'?

[justsomeguy]

Instead of selecting everything, it will probably be better to list which columns you need and use aliases if any of them have the same names.

Edited July 30, 2009 by justsomeguy

[skaterdav85]

Instead of selecting everything, it will probably be better to list which columns you need and use aliases if any of them have the same names.

do you mean something like this? By referencing the alias name such as 's1.semester' using the 'table_name.column_name' syntax, does 's1' refer to the results of the join or is it just referring to an alias for the table named 'Semesters'?

SELECT Reviews.review, s1.semester AS start_semester, s2.semester AS end_semester FROM ReviewsINNER JOIN Semesters AS s1ON Reviews.start = s1.IDINNER JOIN Semesters AS s2ON Reviews.end = s2.ID

[skaterdav85]

Instead of selecting everything, it will probably be better to list which columns you need and use aliases if any of them have the same names.

do you mean something like this? By referencing the alias name such as 's1.semester' using the 'table_name.column_name' syntax, does 's1' refer to the results of the join or is it just referring to an alias for the table named 'Semesters'?

SELECT Reviews.review, s1.semester AS start_semester, s2.semester AS end_semester FROM ReviewsINNER JOIN Semesters AS s1ON Reviews.start = s1.IDINNER JOIN Semesters AS s2ON Reviews.end = s2.ID

[justsomeguy]

Yeah that will work. It's referring to the table, since you're using the same table twice you just need to use aliases so it knows which to join with which.