Nesting queries

[w424637]

Hi

I am trying to generate a table which when a user filters on a variable the variable column remains but the values in col 1 change.

example

Col1 Col2

A     4

B     5

C     6

D     7

The user has an option to filter on ABC or D.

My table /query returns (D chosen)

Col1 Col2

D     7

However I would like it to return

Col1 Col2

A     0

B     0

C     0

D     7

Im wondering if a nested querie is the answer like

Select Col1 From ColTable, (Select Col2 From Datatable Where Col1 = D) as Col2

Then I get stuck

Can any one help or suggect anything for fixing report columns

[bregent]

Could you modify the table, so it looks something like this. Then filter on the first column and return the 2nd and 3rd?

Key	Col1	Col2
A	A	4
A	B	0
A	C	0
A	D	0
B	A	0
B	B	5
B	C	0
B	D	0
C	A	0
C	B	0
C	C	6
C	D	0

[w424637]

Well I have two tables in my database one is the Variables table ABCD and one is Data containing variables and Col1 & Col2 data (very simplified)

My recordset joins these two and brings back as my example. I'm not sure how making my table look like your example would help if a user did not choose a variable. the recordset would return far to many rows?

[Guest]

I just want to understand more, did u mean that u want to list down all the variables in col1 but also the respective values in col2 although the value is NULL?

[w424637]

You have it - I basicaly would like the first column (Variables) to be a fixed list regardless of whether a user filters on it.

The problem is I don't know that list so I can't hardcode it. (Well I could but it would be very rigid if things changed in the future).

IBecause the output table is just one of many on my dashboard I would like the structure to remain the same even if the output value is Null not a value.

You have my problem I think.

[Guest]

Okay, assuming u have two tables, table1 and table2 with respective data.

table1

| id  |    var   |

| 1  |    A     |

| 2  |    B     |

| 3  |    C     |

| 4  |    D     |

table2

| id | t_id | value |

| 1  |  1   |   10   |

| 2  |  4   |   20   |

Now I want to join them using id(table1) and t_id(table2). From this mysql code

SELECT var, value FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.t_id

So the result it will be

A | 10

B | NULL

C | NULL

D | 20

[w424637]

Hi

It's not quite how my data is. though the result would be the right output.

My table are like below (simplified)

table1

| id  |    var   |

| 1  |    A     |

| 2  |    B     |

| 3  |    C     |

| 4  |    D     |

table2

| id | t_id | value |

| 1  |  1   |   10   |

| 2  |  2   |   20   |

| 3  |  3   |   30   |

| 4  |  4   |   40   |

My intial output would be:

A | 10

B | 20

C | 30

D | 40

However.

The user has a drop down/ checkbox whatever and can choose a varialble which becomes a url statement.

So user chooses B (this impacts a number of other tables on the report)

But I want the output of this table to look like this when they have chosen B

A | 0

B | 20

C | 0

D | 0

ie I would still like to show all the variables for the table even though the user has decided to filter on just one element.

[Guest]

Ok. Assuming u have that select list named as 'var_id' in your form page. Then from the view page, add this code

$v_id = $_GET['var_id'];

if(isset($v_id) && !empty($v_id)) {

$get_var = mysql_query(" SELECT var, value FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.t_id WHERE id = '$v_id' ") or die(mysql_error());

}

U can use the normal recordset and filtered in by id of table1, then from the recordset, click on the Advance tab, add the LEFT OUTER JOIN table2 ON table1.id = table2.t_id

[bregent]

QiQi86 wrote:

Ok. Assuming u have that select list named as 'var_id' in your form page. Then from the view page, add this code

$v_id = $_GET['var_id'];

if(isset($v_id) && !empty($v_id)) {

$get_var = mysql_query(" SELECT var, value FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.t_id WHERE id = '$v_id' ") or die(mysql_error());

}

U can use the normal recordset and filtered in by id of table1, then from the recordset, click on the Advance tab, add the LEFT OUTER JOIN table2 ON table1.id = table2.t_id

QiQi86, I don't think this query will work for the OP because joins are evaluated before the where clause is applied. So the outer join will return 4 rows that contain 4 matching outer rows. Then the where clause is applied which will restrict the final results to one row. You would need to use a technique that would first filter the outer table and then apply the join. You might be able to do this with a view. In any case, the results would be NULL values rather than the 0 that the OP desires. This might be ok, otherwise a stored procedure might be better.

[w424637]

Hi

You are right and it returns just the one row and not all four (with null or 0 values in the 3 not chosen by user)

This is why I was thinkng nested query?

The idea being I bring back the record set the user chose ( ie one record) and left joining to the variable table - just don't know how it is written.

I use php and mysql with DW.

thanks

[bregent]

I just looked back again at your original query:

Select Col1 From ColTable, (Select Col2 From Datatable Where Col1 = D) as Col2

I originally didn't look close because you referred to it as a nested query - which is not what this is nor what you want.  The above is really a derived table and it is what you want. You just need to join the outer table with the inner derived table. I don't have much experience with derived tables, but if you search on 'derived tables with outer joins' you will find many examples that should get you the correct syntax. If you still have trouble write back and I'll see if I can help. It looks pretty easy.

[w424637]

Many thanks for putting me onthe right track and to all who gave input.

Answer is a Derived table as suggested and I have copied below the example I found which helped me for anyone coming accross this thread in the future.

select
    Customers.CustomerID, Customers.CompanyName,
    count(dOrders.OrderID) as TotalOrders
from
    Customers
        left outer join
    /* start our derived table */
    (
        select
            *
        from
            Orders
        where
            year(Orders.OrderDate) = 1996
    ) as dOrders
    /* end our derived table */
        on
    Customers.CustomerID = dOrders.CustomerID
group by
    Customers.CustomerID, Customers.CompanyName

[bregent]

w424637, what DBMS are you using? Does it support derived tables?