4 replies to this topic

[sqlnewbie123]

Hello everyone!

I am new to SQL so I apolgize for what is probably a stupid question.

I have a very simple movie database. My main table "movies" holds each movie and basic information about that movie: title, rating, genre, plot, etc. I would like to have a few other tables to hold the option information. For example I created a table genre. This table has all the possible genres linked to a primary key index.

So far, I have set up the relationships using a foreign key. I have the primary key in the genre table connected to a genreid field in the movie table. I think all of this is set up correctly since it did not give me any errors.

My question is, how can I link these two tables so that when I go to Table > View Data and open up my movie table, I see a drop-down box under Genre holding all of my genre options that are stored in the genre table? I tried using SQL queries, but I don't see how these affect the data entry section.

I have the foreign key link, but how does it know that I want the genre table to populate a genre field in the movie table? Sorry if this sounds confusing. Please let me know if you need me to clarify what I am asking.

Thanks!

Attached Files

•  db.jpg   13.47K   5 downloads

[Mahesh]

Hi,

I can Understand that you have two tables Movie and Genre both are having Foreign Key relationship on a genreid key.

You want to see Movie -- Genre column as dropdown so, that it will show fields of Genre table's columns.


But it is not feasible to show Table data like this.
Table -- View Data will show only data of the same table.

[peterlaursen]

We have a releated request recorded here:
http://code.google.c...s/detail?id=350

But to display data from more that one table you will need to execute a JOIN query and display will be in RESULT tab.

[PerFredlund]

peterlaursen, on Mar 25 2009, 12:50 PM, said:

We have a releated request recorded here:
http://code.google.c...s/detail?id=350

But to display data from more that one table you will need to execute a JOIN query and display will be in RESULT tab.

Ok, this is probably the best way using sqlyog.

One could achieve much the same thing with a WHERE clause. Trivial example:
SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column3=table2.column3.
Actually, SELECT DISTINCT would be handy here ...
This works fine even without defining primary and foreign key(s)

The many JOIN versions can be very confusing. LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN ...

There is always more than one way to skin a cat!!

Per

[peterlaursen]

@Per .. what you are writing is also a JOIN even if it does not use the keyword JOIN explicitly! There are 3 JOIN constructions in MySQL

* JOIN in WHERE-clause (Per's example?
* JOIN somecolumn ON someothercolumn (what SQLyog Query Builder does)
* JOIN .. USING .. (requires identically named columns in joined tables)

In the situations where all 3 are possible the server will execute exactly the same for them all (what also EXPLAIN EXTENDED will reveal).