need help with Queries (1 Viewer)

[SantoR]

i have two tables: [ignore the dash(---)]
tablexx
valuexx--c1--c2---c3 .... (Column names)
AA-------1-----0----1
BB-------1-----1----1
CC-------------------1
DD ------------1
EE -----1------1
FF------1------------1
.
.

tableyy
valueyy---c1---c2---c3 .... (Column names)
AAA-------------1----1
BBB-------------------
CCC-------1
DDD-------------------
EEE-------------1-----1
FFF--------1----------1
.
.
.

now i need a query to give output in this way, using these two tables
valuexx heading1 valueyy
AA---------c1---------CCC
BB---------c1---------FFF
EE---------c1-------------
FF---------c1
BB-------- c2---------AAA
DD---------c2--------EEE
EE---------c2
.
.
.

 

[CJ_London]

you data isn't clear enough to see how you want your result. For example, why does valueexx=AA for c1 return CCC but not FFF?

 

[SantoR]

let me explain what those tables are. there are two different table connected to each other by same 'column names'

i want to get value's in VALUEXX AND VALUEYY from these two tables for 'c1' (lets say) then all the NOT NULL fields in tablexx and tableyy for 'c1' will be selected ...

so the data will be
valuexx
AA------c1
BB------c1
EE------c1
FF------c1


valueyy
CCC------c1
FFF------c1

but the real challenge is to display in the format in first post..

Valuexx, valueyy ,c1,c2 ...are field names in table

 

[CJ_London]

so to answer my question

why does valueexx=AA for c1 return CCC but not FFF?

you are saying it does, just not showing it in the example

you will have to use a union query, one element per column

something like

 [B]SELECT[/B] tablexx.valuexx, [COLOR=red]"c1"[/COLOR] AS Heading, tableyy.valueyy
FROM tablexx, tableyy
WHERE (((tablexx.[COLOR=red]c1[/COLOR]) Is Not Null) AND ((tableyy.[COLOR=red]c1[/COLOR]) Is Not Null));
[B]UNION SELECT[/B] tablexx.valuexx, [COLOR=red]"c2"[/COLOR] AS Heading, tableyy.valueyy
FROM tablexx, tableyy
WHERE (((tablexx.[COLOR=red]c2[/COLOR]) Is Not Null) AND ((tableyy.[COLOR=red]c2[/COLOR]) Is Not Null));
[B]UNION SELECT[/B] tablexx.valuexx, [COLOR=red]"c3"[/COLOR] AS Heading, tableyy.valueyy
FROM tablexx, tableyy
WHERE (((tablexx.[COLOR=red]c3[/COLOR]) Is Not Null) AND ((tableyy.[COLOR=red]c3[/COLOR]) Is Not Null));

 

[namliam]

This is a data normalization issue, which can be fixed by creating Union queries, but preferably you want to not have your C1, C2, C3 in column names.... these look like pivot tables...
As for a "real" solution
Are you getting these pivots some place? Can you get the actual source?
Are you importing them? Can you fix the import to "fix" your table?

As for the meanwhile...
The Union query would look something like

Select "C1", ValueXX from TableXX Union
Select "C2", ValueXX from TableXX Union
Select "C3", ValueXX from TableXX

Rince repeat for the other table.

Now it is a simple matter of making a simple unmathed query on top of the two just created queries.
Wont be fast(est), wont be pretty, wont be maintainable, isnt perfect, but it will work.

I see CJ just beat me by seconds thought with a more comprehensive sample code.

 

[SantoR]

 [B]SELECT[/B] tablexx.valuexx, [COLOR=red]"c1"[/COLOR] AS Heading, tableyy.valueyy
FROM tablexx, tableyy
WHERE (((tablexx.[COLOR=red]c1[/COLOR]) Is Not Null) AND ((tableyy.[COLOR=red]c1[/COLOR]) Is Not Null));

[/QUOTE]



this will not yield right result what it will give is as follows :

valuexx ------ Heading --------Valueyy
AA-----------------c1--------------CCC
BB-----------------c1--------------CCC
EE-----------------c1--------------CCC
FF-----------------c1--------------CCC
AA-----------------c1--------------FFF
BB-----------------c1--------------FFF
EE-----------------c1--------------FFF
FF-----------------c1--------------FFF


AND I NEED
valuexx ------ Heading --------Valueyy
AA-----------------c1--------------CCC
BB-----------------c1--------------FFF
EE-----------------c1--------------
FF-----------------c1--------------

 

[namliam]

Santo have you tried my suggestion?

 

[CJ_London]

this will not yield right result

because you didn't answer this question

For example, why does valueexx=AA for c1 return CCC but not FFF?

I assumed

you are saying it does, just not showing it in the example

without knowing the answer to my question, I'm really not prepared to spend a lot of time trying to guess what you mean

 

[SantoR]

because you didn't answer this question
I assumed
without knowing the answer to my question, I'm really not prepared to spend a lot of time trying to guess what you mean

sorry for the misunderstanding..

but there is no relation of AA with CCC or FFF they are just values..... for column name(Valuexx or Valyeyy) i. e valuexx is not related to valueyy

the only relation the two tables have is that they have same 'column names' like c1 c2....


yes i have tried your approach to this problem before you gave it to me......


as i have told you earlier getting value is not a problem but displaying it in a format is a problem...

 

[namliam]

So you are just assigning random values from one table to the other??? That cant be true....

Tell CJ (and/or me and/or all the other readers) what business rules makes tableXX + tableYY = your output.

I concure with CJ, there doesnt (at this time) seem to be a local pattern to your chaos.

 

[CJ_London]

I'll ask again

you say

the only relation the two tables have is that they have same 'column names' like c1 c2....

so we have
valuexx--c1--c2---c3 .... (Column names)
AA-------1-----0----1

and

valueyy---c1---c2---c3 .... (Column names)
CCC-------1
FFF--------1----------1

AA has a value in C1
CCC and FFF have a value in C1

so why does valueexx=AA for c1 return CCC but not FFF?

 

[SantoR]

CJ and Namliam,
as i have already informed, there is no relation b/w valuexx and valueyy

so how AA gets CCC and not FFF is not related , they are just displayed for our understanding that valueyy has 2 values, CCC and FFF that must fall across c1 rows

so for your example output
AA-----c1-----CCC
--------c1-----FFF

or

AA-----c1-----FFF
--------c1-----CCC

since the result which we are getting out of query is
AA-----c1----CCC
AA-----c1----FFF

i.e repeated value of AA

i thought there would be a way to control repetition

i know there is no logic that is why i have posted it in order to get some help on how to approach this problem....

but as namlian said they are not linked so output we're getting is random....

sorry guys for consuming your time....

 

[CJ_London]

as i have already informed, there is no relation b/w valuexx and valueyy

We get that - my example code is based on there 'being a value' not 'what that value is'

so wrap the example in a group by query

 SELECT Valuexx, heading, first(valueyy) as YY
 from (SELECT tablexx.valuexx, "c1" AS Heading, tableyy.valueyy
FROM tablexx, tableyy
WHERE (((tablexx.c1) Is Not Null) AND ((tableyy.c1) Is Not Null))) as T
 GROUP BY Valuexx, heading

but my guess is you are then going to say

BB-----------------c1--------------FFF

is returning CCC rather than FFF

 

[namliam]

If your problem is the repeat of AA, then the only way to solve that is to make a report.

There is no way to prevent the repeat of AA in a query.