MySQL query Help.

Question

0.00/5 (No votes)

See more:

Well I have a table like this:

SQL

CREATE TABLE IF NOT EXISTS `rb_media` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Category` int(11) NOT NULL,
`FilePath` varchar(255) NOT NULL,
`SongArtist` varchar(100) NOT NULL,
`SongName` varchar(100) NOT NULL,
`SongLengh` bigint(20) NOT NULL,
`LastPlay` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

and

SQL

CREATE TABLE IF NOT EXISTS `rb_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) NOT NULL,
`PreCategoryID` varchar(255) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2

I don't want to do a query like:
SELECT * FROM 'rb_media' WHERE id=432;

And then:
SELECT name FROM 'rb_categories' WHERE id=Category

Any Solution to only do one query?

[Added appropriate code formatting]

Posted 23-Feb-10 7:19am

ReymonARG

Updated 23-Feb-10 8:07am

Toli Cuturicu

v2

Add a Solution

3 solutions

Solution 2

Thanks, Wich is the other option? That you say that I am using MyISAM? I always use that engine. I have to read about others like immoDB. :-O

Posted 23-Feb-10 8:44am

ReymonARG

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Alaric Dailey · Accepted Answer · 2010-02-23T07:34:00

your choice to use myisam means that you won't be able to enforce good data (your media table can contain category ids that don't exist in your category table).

but the query you are looking for is

SQL

select c.`name` from `rb_media` m inner join `rb_categories` c
on m.Category = c.id
where m.id = 432

Alaric Dailey · Accepted Answer · 2010-02-23T09:02:00

To make sure the "Category" to exists in your other table you need to a foreign key. Which means InnoDB is your only option.

Foreign keys in MySQL only actually do the expected enforcement of data on InnoDB. Other engines provide foreign keys "for compatibility only and have no effect on data" (I can't find that quote right now. )

Though on this page I found this to support my assertion.

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well.

MySQL query Help.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0