Solved

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

Posted on 2014-03-10
18
671 Views
Last Modified: 2014-03-10
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
Comment
Question by:Maritimed
  • 9
  • 9
18 Comments
 
LVL 34

Expert Comment

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

Dan
0
 

Author Comment

by:Maritimed
ID: 39918006
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918036
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
 

Author Comment

by:Maritimed
ID: 39918055
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918065
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
 

Author Comment

by:Maritimed
ID: 39918244
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918248
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
 

Author Comment

by:Maritimed
ID: 39918309
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918335
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Maritimed
ID: 39918565
Thanks - Error: column "Not specified" does not exist
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39918581
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
 

Author Comment

by:Maritimed
ID: 39918619
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918627
Usually the display of the result falls to the application. SQL just returns values.

What are you using to display the results?
0
 

Author Comment

by:Maritimed
ID: 39918636
Visual Foxpro - the data is coming back as a Memo field...
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918677
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
 

Author Comment

by:Maritimed
ID: 39918685
Hehehe. Really appreciate your help, I can handle it on the VFP end.
0
 

Author Closing Comment

by:Maritimed
ID: 39918688
Dan the Man, very helpful!
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39918693
Glad I could help!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now