Postgres Retrieve Values Conditionally?

Question

not sure if my title is exactly waht I want to accomplish but it was a guess. I'm trying to see if something like the following is possible in PGSQL.

Basically, I have a set of data that I want to pull no matter what. But, if certain columns of data arent null, then I want it to join another table and pull data referencing that data. So, for example, let's say I have data like:

User_Accounts:

userid	companyname	first name
25df	Test Company 1	Bob
921f	Test Company 1	Lawrence
882s	Company test 2	NULL
8234	Test Company 1	Cleo
8244s	Company test 2	Paul
825q	Test Company 1	Bruce
5552	B Alpha Company	Lenny

Baseball_Rosters:

userid	baseball_team	company	position
25df	Atlanta Aliens	Test Company 1	P
882s	Boston Bruisers	Company test 2	DH
8234	California Craisins	Test Company 1	1B
8244s	Tampa Titans	Company test 2	P
null	Tampa Titans	Test Company 1	P
5552	Tampa Titans	B Alpha Company	2B
5552	Tampa Titans	B Alpha Company	SS
921f	California Craisins	Test Company 1	P
825q	Boston Bruisers	Test Company 1	P

How would I perform a query to get a result like:

userid	baseball_team	company	first name	position
null	Tampa Titans	Test Company 1	null	P
25df	Atlanta Aliens	Test Company 1	Bob	P
825q	Boston Bruisers	Test Company 1	Bruce	P
921f	California Craisins	Test Company 1	Lawrence	P

So essentially, inserting the firstname & lastname field if and only if userid is not null? And then to order by userid null first and then in order? Thanks for the help.

The question seems like a LEFT OUTER JOIN which is a SQL 101 topic. If you will be dealing with PostgreSQL regularly I'd suggest to use w3schools.com/sql as a reference to get familiar with the generic SQL syntax and commands. Or if you want a reference specific to PostgreSQL visit this site postgresqltutorial.com . — Ed Mendez
– Ed Mendez, Commented May 3, 2021 at 14:18

Alan Ainsworth · Accepted Answer · 2021-05-03 14:12:08Z

1

This looks like a LEFT OUTER JOIN.

Like this:

SELECT baseball_rosters.user_id, baseball_team, comapny, first_name, position
FROM baseball_rosters
LEFT OUTER JOIN user_accounts ON user_accounts.user_id = baseball_rosters.user_id;

answered May 3, 2021 at 14:12

Alan Ainsworth

781 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Retrieve Values Conditionally?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related