• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 851
  • Last Modified:

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

What is the SELECT syntax in POSGRESQL to retrieve the all of the records from table A which has a STATE_ID field related to a STATES table having the STATE_ID and a STATE_CODE field when some of the records in table A don't have a NULL value in STATE_ID.

Ideally, I'd like to have a value such as "Unspecified" returned in lieu of the STATE_CODE when it is not present.


select id as shipid,name as shipto,city as shipcity, state_id, states.state_code from res_partner A, states where state_id = states.state.id
0
Maritimed
Asked:
Maritimed
  • 9
  • 9
1 Solution
 
Dan CraciunIT ConsultantCommented:
Can you please post the CREATE TABLE statement, cause your explanation is confusing (at least to me :)

Dan
0
 
MaritimedAuthor Commented:
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
)
0
 
Dan CraciunIT ConsultantCommented:
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

Open 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

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
MaritimedAuthor Commented:
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.
0
 
Dan CraciunIT ConsultantCommented:
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

Open in new window


LE: discovered Postgres does not have the IF construct.
0
 
MaritimedAuthor Commented:
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...
0
 
Dan CraciunIT ConsultantCommented:
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

Open in new window

0
 
MaritimedAuthor Commented:
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

Open 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

Open in new window

gives me an error "syntax at or near THEN'

Further suggestions?
0
 
Dan CraciunIT ConsultantCommented:
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

Open in new window

0
 
MaritimedAuthor Commented:
Thanks - Error: column "Not specified" does not exist
0
 
Dan CraciunIT ConsultantCommented:
Yup, he's right, I have to name the "Not specified" constant.
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' AS code
    FROM res_partner
    WHERE res_partner.state_id IS NULL

Open in new window

0
 
MaritimedAuthor Commented:
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?
0
 
Dan CraciunIT ConsultantCommented:
Usually the display of the result falls to the application. SQL just returns values.

What are you using to display the results?
0
 
MaritimedAuthor Commented:
Visual Foxpro - the data is coming back as a Memo field...
0
 
Dan CraciunIT ConsultantCommented:
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...
0
 
MaritimedAuthor Commented:
Hehehe. Really appreciate your help, I can handle it on the VFP end.
0
 
MaritimedAuthor Commented:
Dan the Man, very helpful!
0
 
Dan CraciunIT ConsultantCommented:
Glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now