Unica query su due tabelle

[Rubber2]

Ciao a tutti!
Ho un piccolo problema ad eseguire un unica query su due tabelle che hanno in comune alcuni campi.
Le tabelle in questione sono le seguenti:

CREATE TABLE categorie (
cat_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
cat_parent_id INTEGER(10) NOT NULL DEFAULT 0,
cat_nome TINYTEXT NOT NULL,

PRIMARY KEY (cat_id)

);

CREATE TABLE files (
file_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
cat_id INTEGER(10) NOT NULL DEFAULT 0,
file_nome TINYTEXT NOT NULL,
PRIMARY KEY (file_id)
);

Nella prima tebella vengono inserite categorie e sotto categorie (Tutto funziona regolarmente)
Invece nella seconda vengono inseriti files associandoli ad una sottocategoria.
Fin qui tutto ok, i vari INSERT nel database vengono eseguiti correttamente.

Io vorrei una volta selezionata la categoria visualizzare le sottocategoria associate con i relativi files.
(Per ricavare la sottocategoria passo come variabile l'id della categoria principale)

Esempio selezionando la categoria"CPU" vorrei visualizzare le sue sotto categorie e files in questo modo:

CPU
|
|-----> INTEL
| |
| |----> File numero 1
| |----> File numero 2
| |----> .....
|
|------> AMD
| |
| |----> File numero 1
| |----> File numero 2
| |----> .....

Ho provato ad eseguire questa query ma non ottengo un ottimo risultato, le categorie con + files me le ritrovo doppie

mysql_query("SELECT * FROM categorie AS c LEFT JOIN files AS f ON  c.cat_parent_id = '".$_GET['cat_id']."' WHERE  f.cat_id = c.cat_id "); 

(questa query è una delle tante provate)
ho provato anche vari modi ma non ho trovato una soluzione.....

ecco come mi ritrovo una sottocategoria con pù files:

CPU
|
|-----> INTEL
| |
| |----> File numero 1
| |----> File numero 2
| |----> .....
|
|------> AMD
| |
| |----> File numero 1
| |
| |
|------> AMD
| |
| |----> File numero 2
| |
| |
|------> AMD
| |
| |----> File numero 3
| |
| |

Spero di non aver creato confusione ed esser stato chiaro.
Grazie per l'attenzione!

[MacApp]

Definisci le opportune constraint di univocità e di relazione.

Ad esempio intuisco che:

• categorie.cat_parent_id si riferisca a categorie.id;
• files.cat_id a categorie.id;
• categorie.cat_parent_id e categorie.cat_nome sia univoco per la tabella categorie;
• e più ne trovi e meglio è

dal mio punto di vista, senza relazioni, un database è una collezione di dati sparsi.

[dottwatson]

prova con

query="SELECT  c.cat_name AS nome,
                c.cat_id AS id,
                c.cat_parent_id AS parent,
                f.name AS filename
        FROM    categorie AS c
                LEFT JOIN files AS f
                ON        f.cat_id=c.cat_id
       ORDER BY c.cat_parent_id";

$list=mysql_fetch_assoc($query);

$tree=array();

while ($res=mysql_fetch_assoc($list))
  {
  extract($res);
  if ($parent =="")
    {
    $tree[$id]=array(); 
    }
  else
    {
    if ($parent != "" && $filename != "") $tree[$parent][$id]=array();
    else                                  $tree[$parent][$id][]=$filename;
    } 
  }
print_r($res); 

non assicuro niente visto l'orario

[Rubber2]

@dottwatson
Grazie ho provato ma non ho avuto alcun risultato

@MacApp
Potresti farmi capire meglio....


Non so più cosa provare....
Se qualcuno ha qualche altro consiglio anche su come strutturare le tabelle o altro....

[Ugly Mau]

Magari aggiungi a quella di dottwatson un "GROUP BY"

query="SELECT  c.cat_name AS nome,
                c.cat_id AS id,
                c.cat_parent_id AS parent,
                f.name AS filename
        FROM    categorie AS c
                LEFT JOIN files AS f
                ON        f.cat_id=c.cat_id
        GROUP BY f.cat_id ORDER BY c.cat_parent_id"; 

[Rubber2]

Ho provato i due esempi ma non capisco perchè mi doppia i nomi delle sottocategorie che contengono più files.
Esempio:

Sottocategorie Windows quando stampa a video:

Windows
Patch 10.1
Windows
Service pack 3

io vorrei che sia cosi invece

Windows
Patch 10.1
Service pack 3

[dottwatson]

mi fai il dumping delle 2 tabelle per are una prova perfavore?

in phpmyadmin esporta la tabella e incolla qui

[Rubber2]

Originariamente inviato da dottwatson
mi fai il dumping delle 2 tabelle per are una prova perfavore?

in phpmyadmin esporta la tabella e incolla qui

Si certo, fai attenzione ad alcuni nome nella tua query di esempio che sono diversi tipo c.cat_name (c.cat_nome) e cosi via...
Fai una prova, può darsi che sbaglio qualcosa io

CREATE TABLE IF NOT EXISTS `categorie` (
  `cat_id` int(10) unsigned NOT NULL auto_increment,
  `cat_parent_id` int(10) NOT NULL default '0',
  `cat_nome` tinytext NOT NULL,
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dump dei dati per la tabella `categorie`
--

INSERT INTO `categorie` (`cat_id`, `cat_parent_id`, `cat_nome`) VALUES
(1, 0, 'CAT 1'),
(7, 1, 'Mac os X'),
(5, 6, 'Windows'),
(6, 0, 'CAT 2'),
(8, 6, 'Linux');

CREATE TABLE IF NOT EXISTS `files` (
  `file_id` int(10) unsigned NOT NULL auto_increment,
  `cat_id` int(10) NOT NULL default '0',
  `file_nome` tinytext NOT NULL,
  PRIMARY KEY  (`file_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dump dei dati per la tabella `files`
--

INSERT INTO `tbl_files` (`file_id`, `cat_id`, `file_nome`) VALUES
(3, 5, 'Patch 10.1'),
(4, 5, 'Service pack 3'),
(6, 8, 'aMule'); 

[MacApp]

strambo come export, la tabella "tbl_files" non esiste.

[Rubber2]

Originariamente inviato da MacApp
strambo come export, la tabella "tbl_files" non esiste.

Si un mio errore, siccome avevo fatto delle modifiche al db volevo postarlo con le stesse caratteristiche iniziali del post

INSERT INTO `files` (`file_id`, `cat_id`, `file_nome`) VALUES
(3, 5, 'Patch 10.1'),
(4, 5, 'Service pack 3'),
(6, 8, 'aMule');