how to join two Fields

[tamethetikbalang]

I have this table named facility
ID aircon1 aircon1brand aircon2 aircon2brand
1 yes brandx no
2 yes Brandy yes brandy
3 yes brandx yes brandz
4 no yes branda
5 yes brandz yes brandx

how do I join aircon1brand and aircon2brand together?
the output I want in html is

brandname
branda
brandx
brandy
brandz

 

[Shaggs]

join them giving each of the facility tables aliases

select distinct brand1.aircon1brand
from facility brand1
inner join facility brand2
on brand1.aircon1brand = brand2.aircon2brand

the join i gave here will only return those brand names that are in both aircon1brand and aircon2brand. If you were after the brand names regardless left join them.

I hope thats what you wanted

 

[tamethetikbalang]

shaggs,

i tried your code but it only takes the brandnames from aircon1brand field. I also tried left join but also from aircon1brand only.

tnx.

 

[Shaggs]

that's cause that's all that was specified in the select statement

with the Left join try somthing like

select case when brand1.aircon1brand is not null then brand1.aircon1brand else brand2.aircon2brand end.
from .......

To be honest I'm not really sure what you're trying to achieve. Be specific.

 

[tamethetikbalang]

what I want to achieve is select all the unique brand names (i.e. branda, brandx, brandy etc) from aircon1brand and aircon2brand fields and join then together without duplicates even both brand are in the two fields.

ID aircon1 aircon1brand aircon2 aircon2brand
1 yes brandx no
2 yes Brandy yes brandy
3 yes brandx yes brandz
4 no yes branda
5 yes brandz yes brandx

output I want in html
branda
brandx
brandy
brandz

 

[Shaggs]

Is this what your after

select aircon1brand
from Facility
where aircon1 = 'yes'
union
select aircon2brand
from Facility
where aircon2 = 'yes'
order by aircon1brand

 

[shaddow]

Are you using SQL?
didnt tested but this shoould work on SQL, ACESS

SELECT * FROM (SELECT DISTINCT aircon1brand as allbrands
FROM facility WHERE aircon1brand<>'') UNION SELECT * FROM (SELECT DISTINCT aircon2brand as allbrands
FROM facility WHERE aircon2brand<>'')

This should make a list with what you want.

________
George, M

 

[shaddow]

I forgot to order

SELECT * FROM (SELECT DISTINCT aircon1brand as allbrands
FROM facility WHERE aircon1brand<>'') UNION SELECT * FROM (SELECT ISTINCT aircon2brand as allbrands
FROM facility WHERE aircon2brand<>'') ORDER BY allbrands

________
George, M