MYSQL subquery with a join

Question

I am trying to retrieve records from 4 tables and want to use a sub query within a left Join. So basically, i have table a is the table for openings o,id, o.namesdesc

table b is the table of all the projects b.id, b.titledesc,b,status,b.edate

table a_b is the table for all the opening that have changed to projects acts like a junction table for one to many o.id, b.id

table br is the relationship table with other projects b.id b_rel_code (this is also a b.id as in related to which other projects)

I want to get all the openings,check whether they have project codes from the table a_b table get the projects codes relationship with other project codes check the status of the projects and get all the end_dates of the projects

I would really appreciate your help on this. I have been banging my head from ages. I think i am just not seeing it through

SELECT 
  o.id,
  o.namesdesc,
  GROUP_CONCAT(ab.pcode) as junctionpcode,
  GROUP_CONCAT(br.scode) as proj_link_code, 
  GROUP_CONCAT(b.status) 
FROM table_ab as ab 
  Left JOIN tablea as o on ab.oid = o.id 
  Left Join tableb as b on ab.pcode= b.id 
  LEFT Join tablebr as br on br.b_rel_code=b.id 
where b.status='E' 
GROUP BY o.id order by junctionpcode

Many Thanks, Ok, Probably i have not explained well. May be sample data should help this

Tablea lists description of openings id,namedesc 1,test desc1 2,test desc2

Tableb list of all the projects

pcode,pname,status,edate
001,test pr1,E,30-10-2017
002,test pr2,E,30-05-2017
003,test pr3,A,30-10-2018

Table br is the relationship table for the projects that are related to other projects

brid pcode rel_pcode
1    001    003
2    003    001

Table a_b which is the junction table between tablea and tableb

abid pcode id
1    001   1
2    002   2
3    003   3

So what i want to achieve is get all the openings(from tablea) that has project codes associated with it(from tablea_b) and get the status and enddates of the projects for only with status E(from table b). But it has needs to consider if that project code has the link to the project code(from table br) which has status and edate.

The output that i am expecting is

a.id,concat(pcode+relcodeany),concat(pstatus,relstatus),concat(pedate,relcodeedate)
1   ,(001,003),(E,A),(30-10-2017,30-10-2018)

I hope i have provided enough info. Sorry for the long post

Can you please provide the table structure and some sample data, and the desired result? — Guy Louzon, Oct 12 '18 at 08:36
You will get the best and quickest response at this site if you provide an example of the queries you have tried and what is their result. Maybe add this to your question by editing it. — bcperth, Oct 12 '18 at 08:37
Ok, i have tried with the below query SELECT o.id,o.namesdesc,GROUP_CONCAT(ab.pcode) as junctionpcode,GROUP_CONCAT(br.scode) as proj_link_code, GROUP_CONCAT(b.status) FROM table_ab as ab Left JOIN tablea as o on ab.oid = o.id Left Join tableb as b on ab.pcode= b.id LEFT Join tablebr as br on br.b_rel_code=b.id where b.status='E' GROUP BY o.id order by junctionpcode I hope i am making sense — Teju Desai, Oct 12 '18 at 08:56
Due to the edit text restriction i had to put this in a new comment. I want to get all the openings,check whether they have project codes from the table a_b table get the projects codes relationship with other project codes check the status of the projects and get all the end_dates of the projects — Teju Desai, Oct 12 '18 at 09:00
Please do not clarify via comments, edit your post. Please read & act on [mcve]. You are not clear. Please use enough words & sentences to be clear. Don't try to cram everything into one sentence or paragraph. Use standard grammar & punctuation. Talk about parts of some example(s). — philipxy, Oct 12 '18 at 09:20
Learn what LEFT JOIN returns: INNER JOIN rows plus unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of a LEFT JOIN. A WHERE or ON that requires a right table column to be not NULL after a LEFT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN rows, ie "turns LEFT JOIN into INNER JOIN". You have that. Also it seems likely you are left joining multiple times on a non-key of the first table which gives duplicate rows & spurious aggreagations when you wan the join on the same key of separate aggregations. — philipxy, Oct 12 '18 at 09:28
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. — philipxy, Oct 12 '18 at 09:30
Possible duplicate of [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/q/45250646/3404097]) — philipxy, Oct 12 '18 at 09:38

slaakso · Answer 1 · 2018-10-12T09:41:19.757

0

What was the problem with the query?

You did not add any sample data or expected result, but couple of notes from the query:

table tablebr is joined through table tableb (br.b_rel_code=b.id), if tableb does not contain any rows for ab.pcode nor you will get any rows from tablebr
The b.status='E' where clause turns the left join for tableb into ordinary join (only rows that match will be included). If you want the left join to be effective move the condition after the ON (on ab.pcode= b.id and b.status='E')

Please provide sample data and expected result.

edited Oct 12 '18 at 09:41

answered Oct 12 '18 at 09:17

slaakso

8,331
2
16
27

1

Please don't answer unclear questions, comment for clarification. It makes a mess of obsolete comments, versions & votes. – philipxy Oct 12 '18 at 09:24
You can comment on the question & delete this & later edit for a clear question & undelete if you want to avoid downvotes. – philipxy Oct 12 '18 at 09:43

MYSQL subquery with a join

1 Answers1