Hente ut to resultater til forskjellige columns i én spørring

[Jonhoo]

Heisann folkens =)

Holder på med et PHP script som skal hente ut en del brukere og informasjon om dem fra en MySQL database. Brukerlisten ligger i tabellen phorum_users (slik som brukerid og e-mail), gruppeinformasjon ligger i tabellen phorum_user_group_xref, mens informasjonen ligger i tabellen phorum_user_custom_fields.

 

I phorum_user_custom_fields ligger informasjonen om brukerene i følgende kolonner:

user_id, type og data. Type 6 tilsvarer posisjon og type 7 tilsvarer en individuell sikkerhetskode for hver bruker

 

I phorum_user_group_xref er de to kolonnene user_id og group_id, og den viser bare hvilke brukere som hører til i hvilke grupper.

 

Det jeg ønsker å gjøre er som følger:

Jeg vil hente ut alle brukerene (user_id, username og real_name fra phorum_users) fra én gruppe (group_id i phorum_user_group_xref), og samtidig hente ut posisjon (type = 6 fra phorum_user_custom_fields) og sikkerhetskode (type = 7) som kolonnene posisjon og sikkerhetskode i samme rad.

 

Jeg har skrevet en query nå som fungerer, men den returner to rader for hver bruker, en for type=6 og en for type=7:

SELECT
`phorum_users`.user_id,
`phorum_users`.username,
`phorum_users`.real_name,
`phorum_users`.email,
`phorum_user_custom_fields`.`data` AS `position`,
`phorum_users`.date_added AS `date`
FROM
`phorum_users`,
`phorum_user_custom_fields`,
`phorum_user_group_xref`
WHERE
`phorum_user_group_xref`.user_id = `internat_phorum`.`phorum_users`.user_id
AND
`phorum_user_group_xref`.user_id = `internat_phorum`.`phorum_user_custom_fields`.user_id
AND (
	`phorum_user_custom_fields`.`type` = 7
OR
	`phorum_user_custom_fields`.`type` = 6
) AND
	`phorum_user_group_xref`.group_id = 27;

 

Noen som har noen gode ideer på hvordan jeg kan få gjort dette sånn at resultatene kommer inn i én row per bruker så ville jeg blitt veldig takknemlig =)

Jeg har dessverre ikke mulighet til å endre på databseoppsettet, bare så det er sagt..

 

Jon

[luxus]

Jeg tror jeg hadde skrevet det ala noe slikt som det her:

SELECT u.*, pos.data AS position, code.data AS code
 FROM phorum_user_group_xref g
   INNER JOIN phorum_users u ON(g.user_id = u.user_id)
     LEFT OUTER JOIN phorum_user_custom_fields pos ON(u.user_id = pos_user_id AND pos.type = 6)
     LEFT OUTER JOIN phorum_user_custom_fields code ON(u.user_id = pos_user_id AND pos.type = 7)
WHERE g.group_id = 27

Endret 9. april 2008 av luxus

[Jonhoo]

Takk for så kjapt svar =)

 

Er ikke så erfaren med joins (dessverre), men tror jeg skjønte hva du mente.. Prøvde å copy-paste inn det du skrev, men fikk feilmeldingen:

Column 'user_id' in from clause is ambiguous

 

Hvordan kan user_id ha flere meninger?

Endret 9. april 2008 av Jonhoo

[luxus]

Prøv med SQL'en som står der nå. Jeg endret i fra USING(user_id) til ON(...) i første JOIN. Når SQL klager ambiguous er det at kolonnen er nevnt flere ganger i samme rad og den klarer ikke velge hvilken den skal bruke.

 

Du kan forresten også bytte ut LEFT OUTER JOIN til INNER JOIN, om du er sikker på at det er korresponderende rader på gitt user_id i alle tabellene.. Altså at samme user_id alltid er å finne i alle tabellene.

Endret 9. april 2008 av luxus

[Jonhoo]

Hmm, nei det er jeg ikke sikker på.. Det vil variere.. =)

Querien fungerte ihvertfall, tusen takk!

(Måtte endre pos_user_id til pos.user_id, men det fikk jeg til på egen hånd )

 

Kan du kjapt forklare meg join tror du? Virker som en veldig hendig funksjon å kunne =)

[luxus]

Oj, ja hadde visst truffet _ der, istedenfor . hehe.

 

Vel, JOIN gjør det det høres ut som hehe..

 

Har du f.eks tabellene:

postnummer:
postnr | poststed
-------------
1000 | Oslo
2000 | Bergen

 

adresse:
gate | postnr
------------
Storgata | 1000
Lillegata | 2000
Mellomgata | 3000

 

Og skriver:

SELECT *
 FROM adresse a
LEFT OUTER JOIN postnummer p USING(postnr)

 

Vil du få ut

gate | postnr | poststed
------------
Storgata | 1000 | Oslo
Lillegata | 2000 | Bergen
Mellomgata | 3000 | null

 

Den kobler altså tabeller sammen.. USING(kolonnenavn) kan brukes når kolonnenavnene heter det samme i begge tabeller. ON(a.postnr = p.postnr [AND osv]) brukes når kolonnenavn ikke heter det samme, eller du vil ha inn flere ledd.. Akkurat som i WHERE.

 

LEFT OUTER JOIN tillater at de høyre tabell har NULL på join-kolonnen.. 3000 finnes jo ikke i postnr tabellen. Hadde vi bruker INNER JOIN må postnr være i begge tabeller. Siden 3000 bare er representert i adresse ville ikke den raden kommet ut med en INNER JOIN.

 

Man har også RIGHT OUTER JOIN, FULL JOIN (dog ikke i MySql) osv som er forskjellige varianter å joine tabeller mot hverandre på.

[Manfred]

http://www.tizag.com/mysqlTutorial/mysqljoins.php

 

http://php.about.com/od/learnmysql/ss/mysql_joins.htm

 

http://www.wellho.net/mouth/158_MySQL-LEFT...OUTER-JOIN.html

 

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

osv....