Joins for mysql in advanced queries

Hi 

I have three tables TableA, TableB and TableC.

MySQL agrregate query in advanced table is as mentioned below. 

Select {TableA}.[Col1], {TableA}.[Col2], SUM( {TableA}.[Col3])

FROM {TableA}

WHERE {TableA}.[Fild4] in ('Settlement A', Settlement B')

AND {TableA}.[Col5] in ('Refinance', 'Sales')

AND {TableA}.[Col6] between @Datestart and @DateEnd

Group BY {TableA}.[Col1]

Union


Select {TableA}.[Col1], {TableA}.[Col2], SUM( {TableA}.[Col3])

FROM {TableA}

WHERE {TableA}.[Fild4] in ('Settlement A', Settlement B')

AND {TableA}.[Col5] in ('Refinance', 'Sales')

AND {TableA}.[Col6] between @Datestart and @DateEnd

Group BY {TableA}.[Col1]



Now my requirement is to have TABLE A Should have left join on Other two tables TableB and TableC where TableB and TableC should be innerjoined.

It should be same columns and conditions selected except like to have left join on two inner join table

TABLE A Left Join (TABLEB Inner JOIN TableC) with WHere conditions

Union

TABLE A Left Join (TABLEB Inner JOIN TableC) with WHere conditions


I am not getting the right way of writing the query as it throws me a syntax error all the time.


Can you please help.


Thank you

Hi Ramya,

First you should have to left join on Table B and after that inner join with Table C like below format-

Select Columns Table A left Join

Table B on Table A.column=Table B.column Inner join

Table C on Table C.column=Table B.column

where condtion


https://www.w3schools.com/sql/sql_join.asp


Hope this will help you

Regards 

Rahul Sahu

Hi,

You requirement is very vague and why do you want to union same query, it doesn't make sense and union is little costly operation and in this case I feel can be avoided.

You can do an inner query on TableB and TableC something like below

Select {TableA}.[Col1], {TableA}.[Col2], SUM( {TableA}.[Col3])
FROM {TableA}
LEFT JOIN (
    Select {TableB}.[col1] as [col7], {TableC}.[col1] as [col8]
    FROM {TableB}
    JOIN {TableC} on ({TableB}.[col1]={TableC}.[col1])
) tempTable on (tempTable.[col7] = {TableA}.[Col1])
WHERE {TableA}.[Fild4] in ('Settlement A', Settlement B')
AND {TableA}.[Col5] in ('Refinance', 'Sales')
AND {TableA}.[Col6] between @Datestart and @DateEnd
Group BY {TableA}.[Col1]

Regards.

Hi,

Created this online example for you to check how it should work.

Regards,

Marcelo

Hi Ramya,

just a little side note, maybe this is not the issue, but I notice there is a ' missing

you have 

 in ('Settlement A', Settlement B')

it should be 

 in ('Settlement A', 'Settlement B')