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

    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    How to join two tables


    can you help me, how to type right syntax to join two tables.

    CREATE TABLE "DimStat"
    (
    "StatKey" integer NOT NULL,
    "Stat_ID" character varying(25) NOT NULL,
    "Stat_Name" character varying(100) NOT NULL,
    "Region" character varying(100),
    "Lat" numeric(7,4),
    "Lon" numeric(7,4),
    CONSTRAINT "PK_Stat" PRIMARY KEY ("StatKey")
    )

    CREATE TABLE "FactAWS"
    (
    "Date" date NOT NULL,
    "Time" time with time zone NOT NULL,
    "StatKey" integer NOT NULL,
    "Temperature" numeric(10,4),
    "WinDir" integer,
    "WindSpeed" numeric(10,4),
    CONSTRAINT "PK_Facts" PRIMARY KEY ("Date", "Time", "StatKey"),
    CONSTRAINT "FactAWS_StatKey_fkey" FOREIGN KEY ("StatKey")
    REFERENCES "DimStat" ("StatKey") MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
    )

    and I try to join both table using the field "Starkey" as its key join. And the syntax is

    SELECT DimStat.Date as Tanggal, FactAWS.Time as Waktu, FactAWS.Temperature as Temperatur, FactAWS.WinDir AS "Arah Angin", FactAWS.windSpeed AS "Kecepatan Angin", DimStat.Stat_Name AS "Nama Stasiun" FROM "DimStat" JOIN "FactAWS" ON DimStat.StatKey=FactAWS.StatKey

    and the error is column "dimstatdate" does not exist, how do I fix this error? Can you help me? Thanks
    NB: I'm using PostgreSQL version 1.18.1
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,733
    Rep Power
    1959
    Looks like you posted the wrong query, as "dimstatdate" is not mentioned in it.
    Did you copy/paste the exact error message?
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    no offense, but this is a mess. You have a wild mixture of quoted identifiers, unquoted identifiers, nonstandard identifiers with spaces and whatnot.

    I'd be surprised if this query does anything.

    You definitely need to get familiar with using standard identifiers: a single word, all-lowercase, no quotes. I understand that you like FaNcY wOrD aRt, but databases are about holding data reliably, not pretty designs. If that's what you want, use Excel.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    NB: I'm using PostgreSQL version 1.18.1
    PostgreSQL is currently at version 9.3 so I don't think that 1.18.1 is the version of your PostgreSQL.

    The abundance of doule-quotes and camelcase makes me think that you are using a tool to create and run your queries, and *that* is probably at version 1.18.1.

    The DimStat table does not have a date column I gues you meant to select FactAws.Date, not Dimstat.Date.

IMN logo majestic logo threadwatch logo seochat tools logo