Joining Tables

[flixer]

Hi there,

Ive just finished setting up my first mysql database using this excellent sqlyog.

How do i go about querying data from two tables at once.

i.e) i'd like to match the “Ratings” data from one table with the “DVDTitle” data from another table

The SQL join command looks quite complicated, is there an easier way with SQLyog?

[peterlaursen]

No there isn't really any easier way. We plan a GUI SQL-builder but most likely won't be this year.

In most uncomplicated cases you can omit the JOIN keyword itself like.

Code:

SELECT table1.DVDTitle, table2.Ratings FROM table1, table2 WHERE ……

Example WHERE with a typical FK-setup (no matter if you use it as CONSTRAINT with InnoDB or not)

Code:

WHERE table1.parent_field = table2.child_field

.. I assume that you have SOME identical info in both tables that identifies each DVD (whether a number or something else!). If you don't you are in trouble …

[flixer]

peterlaursen wrote on Sep 10 2006, 10:58 PM:

No there isn't really any easier way. We plan a GUI SQL-builder but most likely won't be this year.

In most uncomplicated cases you can omit the JOIN keyword itself like.

Code:

SELECT table1.DVDTitle, table2.Ratings FROM table1, table2 WHERE ……

Example WHERE with a typical FK-setup (no matter if you use it as CONSTRAINT with InnoDB or not)

Code:

WHERE table1.parent_field = table2.child_field

.. I assume that you have SOME identical info in both tables that identifies each DVD (whether a number or something else!). If you don't you are in trouble …

Thanks for the feedback. Yes that query statement works and i getting matching results. The only problem i now have is im getting more than one matched result for certain titles. The ratings table may have 2 or more entries for “Batman” for example one entry for Batman, the movie which came out in 1989 and another entry for Batman the Tv series which aired in 1992, both with unique ratings. So even though my UK DVD titles list may only have one entry for Batman, i get several results.

Obviously this is more a problem with the raw data than my database or sqlyog.

I possibly may be able to get around it by adding another table of titles and release dates so atleast then in the results i will know how to differentiate between the various duplicate results.

Any other feedback/suggestions appreciated.

[peterlaursen]

“m getting more than one matched result for certain titles”

use GROUP BY

[flixer]

peterlaursen wrote on Sep 12 2006, 08:07 PM:

“m getting more than one matched result for certain titles”

use GROUP BY

That worked great thankyou!. The query took approximately 25 seconds to execute, and produced 1010 results, is this a typical time

to process SQL querys across 2 tables?

I'm guessing the “Views” subtree is where i can save certain search results for future reference?

Could you briefly explain the “Stored Procs” and “Functions” subtrees also available in the database tree, im guessing one of these is for saving SQL queries?

Sorry to ask so many questions. But im very keen to learn SQL for personal use and also to improve my job prospects.

[peterlaursen]

I do not think there is nothing like 'typical time' It depends on teh server configuration, the networks connection (if a lot of data need to be transferred) and more.

“Views” “Stored Procs” and “Functions” (and “Triggers”) are MySQL objects introduced in MySQl version 5.

Almost every Database Server support those (though implementation often is a littele different).

Refer to the MySQL docs:

Views: http://dev.mysql.com/doc/refman/5.0/en/views.html

SP's and SF's http://dev.mysql.com/doc/refman/5.0/en/sto…procedures.html

When these features were introduced with MySQL there were som pretty good articles published. Find them in this archive:

http://dev.mysql.com/tech-resources/articles/

[Author]

Posts