POSTGRESQL return records in related table even if there is no related record

Can you please post the CREATE TABLE statement, cause your explanation is confusing (at least to me :)

Dan

Sure, I hope the following is helpful!

States Table CREATE statement:

CREATE TABLE res_country_state
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  code character varying(3) NOT NULL, -- State Code
  country_id integer NOT NULL, -- Country
  name character varying(64) NOT NULL, -- State Name
  CONSTRAINT res_country_state_pkey PRIMARY KEY (id),
  CONSTRAINT res_country_state_country_id_fkey FOREIGN KEY (country_id)
      REFERENCES res_country (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_country_state_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_country_state_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)

Partners table CREATE statement:

CREATE TABLE res_partner
(
  id serial NOT NULL,
  name character varying(128) NOT NULL,
  lang character varying(64),
  company_id integer,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  comment text, -- Notes
  ean13 character varying(13), -- EAN13
  color integer, -- Color Index
  image bytea, -- Image
  use_parent_address boolean, -- Use Company Address
  active boolean, -- Active
  street character varying(128), -- Street
  supplier boolean, -- Supplier
  city character varying(128), -- City
  user_id integer, -- Salesperson
  zip character varying(24), -- Zip
  title integer, -- Title
  function character varying(128), -- Job Position
  country_id integer, -- Country
  parent_id integer, -- Related Company
  employee boolean, -- Employee
  type character varying, -- Address Type
  email character varying(240), -- Email
  vat character varying(32), -- TIN
  website character varying(64), -- Website
  fax character varying(64), -- Fax
  street2 character varying(128), -- Street2
  phone character varying(64), -- Phone
  credit_limit double precision, -- Credit Limit
  date date, -- Date
  tz character varying(64), -- Timezone
  customer boolean, -- Customer
  image_medium bytea, -- Medium-sized image
  mobile character varying(64), -- Mobile
  ref character varying(64), -- Reference
  image_small bytea, -- Small-sized image
  birthdate character varying(64), -- Birthdate
  is_company boolean, -- Is a Company
  state_id integer, -- State
  notification_email_send character varying NOT NULL, -- Receive Messages by Email
  opt_out boolean, -- Opt-Out
  signup_type character varying, -- Signup Token Type
  signup_expiration timestamp without time zone, -- Signup Expiration
  signup_token character varying, -- Signup Token
  last_reconciliation_date timestamp without time zone, -- Latest Full Reconciliation Date
  debit_limit double precision, -- Payable Limit
  display_name character varying, -- Name
  purchase_warn character varying NOT NULL, -- Purchase Order
  sale_warn character varying NOT NULL, -- Sales Order
  picking_warn_msg text, -- Message for Stock Picking
  invoice_warn character varying NOT NULL, -- Invoice
  picking_warn character varying NOT NULL, -- Stock Picking
  sale_warn_msg text, -- Message for Sales Order
  purchase_warn_msg text, -- Message for Purchase Order
  invoice_warn_msg text, -- Message for Invoice
  CONSTRAINT res_partner_pkey PRIMARY KEY (id),
  CONSTRAINT res_partner_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_country_id_fkey FOREIGN KEY (country_id)
      REFERENCES res_country (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES res_partner (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_state_id_fkey FOREIGN KEY (state_id)
      REFERENCES res_country_state (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_title_fkey FOREIGN KEY (title)
      REFERENCES res_partner_title (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT res_partner_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)

Still confused, but less :)

This will select state codes for every row in res_partner

SELECT id as shipid, name as shipto, city as shipcity, code 
    FROM res_partner JOIN res_country_state
    ON res_partner.state_id = res_country_state.id

Select allOpen in new window

If you only want to select those rows that have a non null state_id:

SELECT id as shipid, name as shipto, city as shipcity, code 
    FROM res_partner JOIN res_country_state
    ON res_partner.state_id = res_country_state.id
    WHERE res_partner.state_id IS NOT NULL

Select allOpen in new window

Cool, I'm looking for something like the first one that returns all rows in res_partner, but if there if state_id in res_partner is NULL (thus no look up in res_country_state, and no code) I want to have an alternate value such as "Not Specified". Records that do not have a NULL value, but instead a valid state_id will return the state code from res_country_state. So:

State_id    Code
1                AL
2                NY
3                CA
NULL         Not specified

etc.

SELECT id as shipid, name as shipto, city as shipcity, 
    CASE code IS NOT NULL THEN code ELSE  "Not specified" 
    FROM res_partner JOIN res_country_state
    ON res_partner.state_id = res_country_state.id

Select allOpen in new window

LE: discovered Postgres does not have the IF construct.

Hi Dan,

Thanks again!

Getting close I think... I'm getting an error that 'column reference ID is ambiguous'. What is the correct syntax for specifying the table that I am looking for ID in? I've tried res_partner.id but that doesn't seem to work...

It should.

SELECT res_partner.id as shipid, name as shipto, city as shipcity, 
    CASE code IS NOT NULL THEN code ELSE  "Not specified" 
    FROM res_partner JOIN res_country_state
    ON res_partner.state_id = res_country_state.id

Select allOpen in new window

Thanks, I've gotten past the ambiguous columns, but now other issues...

This one:

SELECT res_partner.id as shipid, res_partner.name as shipto, 
city as shipcity, code FROM res_partner JOIN res_country_state
 ON res_partner.state_id = res_country_state.id

Select allOpen in new window

only gives me rows that have a matching value
 in res_country_state for res_partner.state_id

This one:

SELECT res_partner.id as shipid, res_partner.name as shipto, 
city as shipcity, CASE code IS NOT NULL THEN code ELSE  "Not specified"
 FROM res_partner JOIN res_country_state 
ON res_partner.state_id = res_country_state.id

Select allOpen in new window

gives me an error "syntax at or near THEN'

Further suggestions?

Yup, a union. If the first query only gives you the non null value, then select the rest with "Not specified" as code

SELECT res_partner.id as shipid, res_partner.name as shipto, city as shipcity, code 
    FROM res_partner JOIN res_country_state
    ON res_partner.state_id = res_country_state.id
UNION ALL
SELECT res_partner.id as shipid, res_partner.name as shipto, city as shipcity, "Not specified"
    FROM res_partner
    WHERE res_partner.state_id IS NULL

Select allOpen in new window

Thanks - Error: column "Not specified" does not exist

Bingo!

Could be a new question if you like - how do I specify the returned column type for 'Not specified' to be a text fixed length blank padded in the SELECT statement? Is this doable?

Usually the display of the result falls to the application. SQL just returns values.

What are you using to display the results?

Visual Foxpro - the data is coming back as a Memo field...

I'm afraid I can't help you there. Haven't used Foxpro in 18 years :)
And then it did not have the "Visual" part...

Hehehe. Really appreciate your help, I can handle it on the VFP end.

Dan the Man, very helpful!

Glad I could help!