[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 798
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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