[SQL] Join

[whisher]

Ciao.
Posto la struttura

CREATE TABLE `tickets` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_category` int(10) unsigned NOT NULL default '0',
`id_user` int(10) unsigned NOT NULL default '0',
`subject` text NOT NULL,
`content` mediumtext NOT NULL,
`submitted` datetime NOT NULL default '0000-00-00 00:00:00',
`status` enum('0','1','2') NOT NULL default '0',
`blocked` enum('0','1') NOT NULL default '0',
PRIMARY KEY (`id`)
)
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_workgroup` smallint(5) unsigned NOT NULL default '0',
`name` varchar(50) NOT NULL default '0',
`surname` varchar(50) NOT NULL default '0',
`username` varchar(32) NOT NULL default '0',
`password` char(40) NOT NULL default '0',
`email` varchar(100) NOT NULL default '0',
`logged` timestamp NOT NULL default CURRENT_TIMESTAMP,
`uid` char(40) NOT NULL default '0',
`is_admin` enum('0','1') NOT NULL default '0',
`type` enum('0','1') NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username` (`username`),
KEY `uid` (`uid`)
)
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL auto_increment,
`position` varchar(255) NOT NULL default '0',
`id_workgroup` smallint(5) unsigned NOT NULL default '0',
`name` varchar(255) NOT NULL default '0',
`description` tinytext NOT NULL,
`icon` varchar(255) NOT NULL default '0',
PRIMARY KEY (`id`)
)
CREATE TABLE `workgroups` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)

e la mia query

SELECT t.id AS ticket_id, t.id_category AS category_id,
t.id_user AS user_id, t.subject, t.content,
t.submitted, t.status, c.name AS category_name 
FROM tickets AS t
INNER JOIN categories AS c ON t.id_category=c.id
INNER JOIN users AS u ON  u.id_workgroup=c.id_workgroup
WHERE u.id_workgroup='8' 

In pratica voglio recuperare tutti i tickets
che hanno in categories un dato id_workgroup
come faccio a recuperare i dati dell'user con
valore user_id ?

[dottwatson]

forse

SELECT  t.id_category           AS category_id,
        t.id_user               AS user_id,
        t.subject, t.content,
        t.submitted, 
        t.status, 
        c.name                  AS category_name

FROM    tickets                 AS t,
        categories              AS c,
        users                   AS u
WHERE   u.id_workgroup='8'

[whisher]

Forse

No non va

Tutto bene dottò

[whisher]

Ho provato così e sembra forse
funzionare ma vorrei qc input please !!!

SELECT t.id AS ticket_id, t.id_category AS category_id, t.id_user AS user_id, t.subject, t.content, u2.name, u2.surname, u2.email, t.submitted, t.status, c.name AS category_name
FROM tickets AS t
INNER JOIN categories AS c ON t.id_category = c.id
INNER JOIN users AS u ON u.id_workgroup = c.id_workgroup
INNER JOIN users AS u2 ON u2.id = t.id_user
WHERE u.id_workgroup = '8' 

[dottwatson]

cavolo

la domenica spengo il cervello

hola swish ti dovrò contattare poi


cmq tornando alla query, SI è coretta in quanto sia nella tua originale che nella mia (frutto di una dormita colossale) mancavano le condizioni della WHERE clausule per il recupero dei campi nelle altre tabelle