Trouble with Compound Query

[bxdobs]

Not sure how to bracket this single query to get the response I am looking for. ... If I Separate this in to 3 simple Queries it works ... the concept I think I am missing is how to tell the SQL processor to pre-process the "SKIP" and "CREW" records first before doing the LEFT JOIN ... I suspect the where clause is most likely closing the door on having a null right side to the LEFT JOIN. ... I tried adding an OR [Members1].type = null but its not the field that is null it's the entire row for Members1.account = accounts.id that will be null



Select
[Accounts].id As MID,
[Members].name As SKIP,
[Members1].name As CREW,
[Accounts].balance As BALANCE
From
([Members] Inner Join [Accounts] On [Members].account = [Accounts].id)
Left Join [Members] [Members1] On [Members1].account = [Accounts].id
Where
[Accounts].status = "Active" And [Members].type = 0 And [Members1].type = 1

Given:

Table 1: Members
id as integer
account as integer
name as text
type as text

Table 2: Accounts
id as integer
balance as float
status as text

Members
ID ACCOUNT NAME TYPE
1 101 abc 0
2 102 def 0
3 102 ghi 1
4 103 xyz 0

Accounts
ID BALANCE STATUS
101 0.0 InActive
102 12.12 Active
103 103.34 Active

Compound Query

only returns:

MID SKIP CREW BALANCE
102 def ghi 12.12

Expected:

MID SKIP CREW BALANCE
102 def ghi 12.12
103 xyz 103.34


3 Query method works running Q3

Q1:
Select
[Accounts].id As MID,
[Members].name As SKIP,
[Accounts].balance As BALANCE
From
([Members] Inner Join [Accounts] On [Members].account = [Accounts].id)
Where
[Accounts].status = "Active" And [Members].type = 0;


Q2:
Select
[Accounts].id As MID,
[Members].name As CREW,
From
([Members] Inner Join [Accounts] On [Members].account = [Accounts].id)
Where
[Accounts].status = "Active" And [Members].type = 1;

Q3:

Select
[Q1].MID,
[Q1].SKIP,
[Q2].CREW,
[Q1].BALANCE
From
([Q1] Left Join [Q2] On [Q1].MID = [Q2].MID)
Order by Q1.MID;

[June7]

If you want one SQL statement, the queries must be nested. Try:

Select
[Q1].MID,
[Q1].SKIP,
[Q2].CREW,
[Q1].BALANCE
From
(Select [Accounts].id As MID, [Members].name As SKIP, [Accounts].balance As BALANCE
From [Members] Inner Join [Accounts] On [Members].account = [Accounts].id
Where [Accounts].status = "Active" And [Members].type = 0) AS [Q1]
Left Join
(Select [Accounts].id As MID, [Members].name As CREW
From [Members] Inner Join [Accounts] On [Members].account = [Accounts].id
Where [Accounts].status = "Active" And [Members].type = 1) AS [Q2]
On [Q1].MID = [Q2].MID
Order by Q1.MID;

[bxdobs]

Thank-you ... that worked Great ... 30+ years of computer software and database development and I still can't solve a simple SQL query problem sigh! Must be 4GL withdrawal syndrome.

[June7]

I use the query builder to help build nested statements.

First build and save each query object. Then in SQL View copy/paste the inner query statements into the final query. Than delete the query objects no longer needed.