[Løst]MySQL: Trenger hjelp til å formulere spørring

[Azidops]

MySQL v. 5.1.37

 

Hei.

 

Har følgende tabeller:

 

USER: (brukertabell)

- id_user (PK)

- id_firm (FK) <----- Oppdatert

- firstname

- lastname

 

FIRM: (firmatabell)

- id_firm (PK)

 

COUNTIES: (tabell med alle fylker i Norge)

- id_county (PK)

- county

 

USER_COUNTIES (oversikt over hvilke fylker hver bruker er tilknyttet. En bruker kan være tilknyttet flere fylker)

- id_county (FK)

- id_user (FK)

 

 

Jeg vil lage en spørring som henter ut alle ansatte (brukere) i et firma og liste ut alle fylkene denne ansatte er tilknyttet. Et resultat kan se slik ut:

Brukernavn. | Fylker

Donald Duck | Hordaland, Oslo, Akershus, Østfold

Mikke Mus.. | Østfold, Rogaland, Troms

 

Jeg har følgende spørring så lang som ikke er tilfredstillende:

SELECT CONCAT (`firstname`,' ' , `lastname`) as name, id_fylke
FROM `user` as u, firm as f, user_county as uc
WHERE u.id_firm = f.id_firm 
AND u.id_firm = 16
AND u.active = 'yes'  // betingelsen er at brukeren er aktiv i systemet
AND u.id_user = uc.id_user

Resultat:

Brukernavn. | Fylke
Donald Duck | Hordaland
Donald Duck | Oslo
Donald Duck | Akershus
Donald Duck | Østfold
Mikke Mus.. | Rogaland
Mikke Mus.. | Østfold
Mikke Mus.. | Troms

 

How to do, people? Flott om noen vet!

 

********* OPPDATERING **********

 

CREATE TABLE IF NOT EXISTS `user` (
 `id_user` int(8) NOT NULL AUTO_INCREMENT,
 `passw` varchar(100) NOT NULL,
 `e_mail` varchar(50) NOT NULL,
 `firstname` varchar(30) DEFAULT NULL,
 `lastname` varchar(30) DEFAULT NULL,
 `phone` bigint(12) DEFAULT NULL,
 `cell` bigint(12) DEFAULT NULL,
 `id_firm` int(4) DEFAULT NULL,
 `active` varchar(3) DEFAULT NULL,
 `userlevel` int(1) DEFAULT NULL,
 PRIMARY KEY (`id_user`),
 KEY `id_firm` (`id_firm`)
) ENGINE=InnoDB 

CREATE TABLE IF NOT EXISTS `firm` (
 `id_firm` int(4) NOT NULL AUTO_INCREMENT,
 `company_name` varchar(100) NOT NULL,
 `contact` varchar(60) NOT NULL,
 `phone` bigint(12) NOT NULL,
 `cell` bigint(12) NOT NULL,
 `address` varchar(30) NOT NULL,
 `zip` varchar(10) NOT NULL,
 `city` varchar(30) NOT NULL,
 `email` varchar(50) NOT NULL,
 PRIMARY KEY (`id_firm`)
) ENGINE=InnoDB

CREATE TABLE IF NOT EXISTS `counties` (
 `id_county` int(2) NOT NULL,
 `county` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`id_county`)
) ENGINE=InnoDB


// Ett fylke kan ha mange konsulenter (users) og en konsulent kan være aktiv i flere fylker.Derfor denne tabellen. En user kan ha andre roller enn konsulent

CREATE TABLE IF NOT EXISTS `user_county` (
 `id_county` int(2) NOT NULL,
 `id_user` int(8) NOT NULL,
 PRIMARY KEY (`id_county`,`id_user`),
 KEY `id_user` (`id_user`)
) ENGINE=InnoDB

 

Kommentarer på selve tabellene er også velkomne

Endret 22. november 2009 av Azidops

[BlueEAGLE]

Du mangler informasjon i tabellene som beskriver en relasjon mellom en bruker og et firma.

 

Jeg ville nå tro at firmaet var tilknyttet flere fylker og ikke brukerene med mindre det her er snakk om ansvarsområder.

 

Jeg ser isåfall for meg relasjonen slik.

 

Firma1 (1 til 1) Bruker1 (1 til fler) fylker[]

Firma1 (1 til 1) Bruker2 (1 til fler) fylker[]

Firma2 (1 til 1) Bruker3 (1 til fler) fylker[]

Firma2 (1 til 1) Bruker4 (1 til fler) fylker[]

 

Isåfall så trenger du et felt i brukertabellen som knytter personene mot firmatabellen.

[JohndoeMAKT]

Kan du gi oss SQL-en som oppretter tabellene?

Og hva er det som ikke er tilfredstillende med spørringen din?

[Azidops]

Du mangler informasjon i tabellene som beskriver en relasjon mellom en bruker og et firma.

Rettet på. Se første post.

Jeg ville nå tro at firmaet var tilknyttet flere fylker og ikke brukerene med mindre det her er snakk om ansvarsområder.

Riktig. Det er snakk om ansvarsområder.

 

Jeg har også en tabell med oversikt over hvilke fylker ett firma jobber i. Den er ikke relevant her. Den hjelper meg til å begrense brukerens(admin) utvalg av fylker når en ny konsulent blir opprettet. Firma A kan altså opperere i Hordaland og Rogaland, men man kan sette en konsulent i Firma A til kun å være tilgjengelig i Hordaland.

 

JohndoeMAKT:

Jeg ønsker at spørringen skal hente ut resultatet slikt det er vist i qouten i starttråden. Det jeg får til er det som står i første code-quote, men da må jeg benytte meg av programmeringsspråket til å sy sammen ønsket resultat ( = ugly kode).

[JohndoeMAKT]

JohndoeMAKT:

Jeg ønsker at spørringen skal hente ut resultatet slikt det er vist i qouten i starttråden. Det jeg får til er det som står i første code-quote, men da må jeg benytte meg av programmeringsspråket til å sy sammen ønsket resultat ( = ugly kode).

Gidder du poste SQL-en som oppretter tabellenen?

 

F.eks som dette:

SHOW CREATE TABLE users;

[Azidops]

Koden ligger allerede i førsteposten.

 

Uansett, jeg fikk det til med følgende kode:

SELECT CONCAT( `firstname` , ' ', `lastname` ) AS name, GROUP_CONCAT( DISTINCT CONVERT( id_fylke USING utf8 ) ORDER BY id_fylke SEPARATOR ', ' ) AS fylker
FROM `user` AS u, firm AS f, user_county AS uc
WHERE u.id_firm = f.id_firm
AND u.id_firm =16
AND u.active = 'yes'
AND u.id_user = uc.id_user
GROUP BY id_user

Endret 23. november 2009 av Azidops

[JohndoeMAKT]

Beklager, det fikk jeg ikke med meg. Bra at du fikk det til.

 

Min beste kommentar er bruk av UNSIGNED tall når du ikke trenger negative verdier.