Ako vhodne spojit tabulky?

tyr · Napísal **tyr**: 09.08.2010 9:58

Ahoj, mam taky problem.. Robim login system a mam tam 2 "druhy" uzivatelov... nazveme ich ucitel a ziak napriklad. Aj jeden aj druhy maju podobne nazvy stlpcov v mysql ucitel_id, ziak_id..... ucitel_email, ziak_email..... atd a teraz potrebujem podla zadnia emailu vybrat konkretny riadok z mysql. Email bude unikatny, id vsak nie (auto increment)... AKo na to? prisiel som na nieco take:

Kód:

SELECT ziak_id, ziak_email, ziak_password, ziak_account_id
FROM ziaci
WHERE ziak_email='$email'
UNION ALL
SELECT ucitel_id, ucitel_email, ucitel_password, ucitel_account_id
FROM ucitelia
WHERE ucitel_email='$email'

no a.... teraz by to aj vybralo povedzme jeden riadok.... unikatny ale je toto dobry sposob? myslim.... nedaju sa nejak "premenovat stlpce pri selecte" alebo nejako efektivnejsie to urobit?
Dakujem

coldak · Napísal **coldak**: 09.08.2010 10:22

samozrejme ze sa stlpce premenovat daju

Kód:

select user_id, email , password, account_id, source from (
SELECT ziak_id as user_id, ziak_email as email, ziak_password as password, ziak_account_id as account_id, 'ZIAK' as source
FROM ziaci 
UNION ALL 
SELECT ucitel_id as user_id, ucitel_email as email, ucitel_password as password, ucitel_account_id as account_id , 'UCITEL' as source
FROM ucitelia )
WHERE email='$email'

stlpec source ti povie ci to bol zaznam z tabulky ziakov alebo ucitelov

tyr · Napísal autor témy **tyr**: 09.08.2010 11:25

Dakujem za odpoved.....
ale mysql hlasi

Kód:

Every derived table must have its own alias

( mysql_error(); )

coldak · Napísal **coldak**: 09.08.2010 11:31

sorac, ja bezim na oracli. ale skus subselectu pridat alias

Kód:

select user_id, email , password, account_id, source from ( 
SELECT ziak_id as user_id, ziak_email as email, ziak_password as password, ziak_account_id as account_id, 'ZIAK' as source 
FROM ziaci 
UNION ALL 
SELECT ucitel_id as user_id, ucitel_email as email, ucitel_password as password, ucitel_account_id as account_id , 'UCITEL' as source 
FROM ucitelia ) as un
WHERE un.email='$email'

tyr · Napísal autor témy **tyr**: 09.08.2010 11:40

Dik vau funguje..... skvele

musim ten un. pozriet lebo tomu nejak nerozumiem...

coldak · Napísal **coldak**: 09.08.2010 11:46

un je len mnou vymysleny nazov aliasu pre derived table

tyr · Napísal autor témy **tyr**: 09.08.2010 11:57

cize kvazy

Kód:

FROM tabulka AS mojnazov

...myslim ze rozumiem dik :-)

coldak · Napísal **coldak**: 09.08.2010 12:09

bingo

Ako vhodne spojit tabulky?

Podobné témy