Hallo zusammen,
ich möchte diese 3 Tabellen miteinander verknüpfen. Als Resultat der Abfrage soll
sich eine Liste mit Jobs der gewünschten Category und Location bilden.
Ich habe mich eine ganze Weile daran versucht, ich komme einfach nicht weiter.
Vielen Dank für euere Hilfe!

Gruss Tom

Hier das Script:

Tabellen


-- JOB ADS
CREATE TABLE IF NOT EXISTS `job_ads` (
`job_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`subtitle` varchar(50) NOT NULL,
`workspace` varchar(50) NOT NULL,
`description` varchar(8000) NOT NULL,
`category` varchar(30) NOT NULL,
`location` varchar(30) NOT NULL,
`created` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`job_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


-- CATEGORY
CREATE TABLE `job_cat` (
`cat_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`category` varchar(50) NOT NULL default '',
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


-- LOCATION
CREATE TABLE `job_loc` (
`loc_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`location` varchar(50) NOT NULL default '',
PRIMARY KEY (`loc_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


<?php

if (isset($_POST['search'])) {

# SEARCH VIEW
"SELECT job_id, title, subtitle, workspace,
category, location, created FROM ".job_ads."
LEFT JOIN job_cat ON job_ads.category = job_cat.cat_id
LEFT JOIN job_loc ON job_ads.location = job_loc.loc_id DESC";
$result = mysql_query($select);
$count = mysql_num_rows($result);

}

else {

# JOBLIST VIEW
$select = "SELECT * FROM ".job_ads." ORDER BY job_id DESC";
$result = mysql_query($select);
$count = mysql_num_rows($result);

}

?>

Code

-- JOB ADS 
CREATE TABLE IF NOT EXISTS `job_ads` ( 
`job_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
`title` varchar(50) NOT NULL, 
`subtitle` varchar(50) NOT NULL, 
`workspace` varchar(50) NOT NULL, 
`description` varchar(8000) NOT NULL, 
`cat_id` INT UNSIGNED NOT NULL, 
`loc_id` INT UNSIGNED NOT NULL, 
`created` timestamp NOT NULL default CURRENT_TIMESTAMP, 
PRIMARY KEY (`job_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
 
-- CATEGORY 
CREATE TABLE `job_cat` ( 
`cat_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
`category` varchar(50) NOT NULL default '', 
PRIMARY KEY (`cat_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
 
-- LOCATION 
CREATE TABLE `job_loc` ( 
`loc_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
`location` varchar(50) NOT NULL default '', 
PRIMARY KEY (`loc_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
 
 
SELECT job_id, title, subtitle, workspace, category, location, created 
FROM job_ads 
LEFT JOIN job_cat ON job_ads.cat_id = job_cat.cat_id 
LEFT JOIN job_loc ON job_ads.loc_id = job_loc.loc_id  
ORDER BY job_id DESC