Re: [GENERAL] Left join help

Поиск
Список
Период
Сортировка
От Arup Rakshit
Тема Re: [GENERAL] Left join help
Дата
Msg-id 9DA3AF67-FE8E-438A-B8A8-D19BE4310C05@outlook.com
обсуждение исходный текст
Ответ на Re: [GENERAL] Left join help  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Дерево обсуждения
Hello Adrian,

The way John taught me this query with CTE and then how that can be using with JOIN really helped me to understand. The query below is from John and it worked.

SELECT *
FROM missions
LEFT JOIN submissions
INNER JOIN members 
ON submissions.member_id = members.id AND members.id = 1
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7

Result is attached.

When I added CASE expression to the above query it was giving me the error as I mentioned in previous email. I managed the CASE expression query with CTE as below:

with member_submissions as (
SELECT submissions.*
FROM submissions
INNER JOIN members ON submissions.member_id = members.id
AND members.id = 1
)
SELECT *, CASE WHEN member_submissions.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status
FROM missions
LEFT JOIN member_submissions ON missions.id = member_submissions.mission_id
WHERE missions.track_id = 7

But still would like to know why it didn’t work with JOIN.

Thanks,
- A


On Jun 24, 2017, at 10:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 06/24/2017 08:01 AM, Arup Rakshit wrote:
Hi,
Thanks everyone for taking time to explain this. I tried to add a case statement and getting errors. Can you tell me how should I add a column to mark which mission is completed and which is not. My try is not working. Instead of the new CASE expression, the query works as expected.

Can you show what works?

SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id AND members.id = 1
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7

I am not how Postgres would determine which ON refers to which JOIN here?

To get back to your original request would the below work?:

SELECT
missions.*, CASE WHEN sub.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status
FROM
missions
LEFT JOIN
(SELECT * FROM submissions WHERE member_id = 1) AS sub
ON
missions.mission_id = sub.mission_id
WHERE
missions.track_id = 7

====
ERROR: missing FROM-clause entry for table "missions"
LINE 1: SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'in...
On Jun 24, 2017, at 4:53 AM, John W Higgins <wishdev@gmail.com <mailto:wishdev@gmail.com>> wrote:

SELECT *
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id =members.id <http://members.id/>ANDmembers.id <http://members.id/>= 1
ONmissions.id <http://missions.id/>= submissions.mission_id
WHERE missions.track_id = 7


--
Adrian Klaver
adrian.klaver@aklaver.com

Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Left join help
Следующее
От: Fabiana Zioti
Дата:
Сообщение: [GENERAL] Configure Qt Creator to work with PostgreSQL to extensionsdevelopment