#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    76
    Rep Power
    5

    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.

IMN logo majestic logo threadwatch logo seochat tools logo