Skip to main content
W
w424637
Known Participant
July 2, 2009
Question

Nesting queries

  • July 2, 2009
  • 2 replies
  • 1840 views

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

This topic has been closed for replies.

2 replies

A
Anonymous
July 3, 2009

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?

W
w424637Author
Known Participant
July 3, 2009

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.

A
Anonymous
July 3, 2009

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

B
bregent
Participating Frequently
July 2, 2009

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

NestedQueryTest

Key Col1 Col2
AA4
AB0
AC0
AD0
BA0
BB5
BC0
BD0
CA0
CB0
CC6
CD0

W
w424637Author
Known Participant
July 3, 2009

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?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices