January 26th, 2013, 03:01 AM
|
|
Contributing User
|
|
Join Date: Apr 2010
Posts: 76
Time spent in forums: 17 h 24 m 53 sec
Reputation Power: 4
|
|
|
Simple query help
Hi,
I have this table:
Code:
CREATE TABLE nyelvek
(
nyelvkod character(2) NOT NULL,
nyelv character varying(15) NOT NULL,
alapert boolean,
CONSTRAINT nyelvkod PRIMARY KEY (nyelvkod)
)
WITH (
OIDS=FALSE
);
ALTER TABLE nyelvek OWNER TO postgres;
and this table:
Code:
CREATE TABLE idegenmegnev
(
tipus character varying(3) NOT NULL,
azonosito character varying(10) NOT NULL,
nyelvkod character(2) NOT NULL,
idegenmegnev character varying(60),
jellszoveg character varying(40),
CONSTRAINT imegnev PRIMARY KEY (tipus, azonosito, nyelvkod),
CONSTRAINT nykod FOREIGN KEY (nyelvkod)
REFERENCES nyelvek (nyelvkod) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE idegenmegnev OWNER TO postgres;
I would like to select all the records form table1 and all the records from table2, where table2.tipus='SZ' and table2.azonosito='111000' with a join. The common field is 'nyelvkod'.
F.e. I get 3 records from table1 (all the rows are unique rows) and one row from table2, where one value from field 'nyelvkod' from table1 = field 'nyelvkod' from table2.
So at the end, I have 3 records, one column (nyelvkod) from table1 and 2 columns (idegenmegnev, jellszoveg) from table2.
Can you help me out?
EDIT: no need to answer
Code:
select nyelvek.nyelvkod, table1.idegen_megnev, table1.jellszoveg from nyelvek
left join
(select nyelvkod, idegen_megnev, jellszoveg from idegen_megnev where
tipus='SZ' and azonosito='111000') as table1
on nyelvek.nyelvkod=table1.nyelvkod
Last edited by derstauner : January 26th, 2013 at 07:26 AM.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
