sql queries

[subs]

carrier price year
op 3333 2009
ot 233 2009


table B


Carrier price year
op 1200 2008
op1 1400 2008


i need a JOIN query which can give me the following data( by combining
the
tables)- left join or right join but it should combine all the data
from both tables irrespective of whether the joined fields are equal
or not


Carrier price for 2009 price for 2008
op 3333 1200
ot 233 -
op1 - 1400

 

[Bob Barrows]

carrier price year
op 3333 2009
ot 233 2009


table B


Carrier price year
op 1200 2008
op1 1400 2008


i need a JOIN query which can give me the following data( by combining
the
tables)- left join or right join but it should combine all the data
from both tables irrespective of whether the joined fields are equal
or not


Carrier price for 2009 price for 2008
op 3333 1200
ot 233 -
op1 - 1400

You need a full outer join which is not supported in Jet unfortunately. What
you can do is UNION the results of a left join and a right join:

select a.carrier,a.price as PriceFor2009,b.price as PriceFor2008
FROM [Table A] as a left join [Table B] on a.carrier=b.carrier
UNION
select b.carrier,a.price as PriceFor2009,b.price as PriceFor2008
FROM [Table A] as a right join [Table B] on a.carrier=b.carrier

Since the union does not use the "ALL" keyword, duplicates will be
eliminated.

 

[Bob Barrows]

carrier price year
op 3333 2009
ot 233 2009


table B


Carrier price year
op 1200 2008
op1 1400 2008

PS. Why are these carriers in different tables? If they were in a single
table you could accomplish your task using the crosstab query wizard.

 

[Michel Walsh]

You can also use two successive LEFT joins, if you already have a table
(query) with all possible Carriers:

Carriers 'table Name
carrier ' field name
op
ot
op1 ' data value (as example)


then the initial full outer join between tableA and tableB could become:



SELECT whatever
FROM (carriers LEFT JOIN tableA
ON carriers.carrier = tableA.carrier)
LEFT JOIN tableB
ON carrier.carrier = tableB.carrier




Vanderghast, Access MVP

carrier price year
op 3333 2009
ot 233 2009


table B


Carrier price year
op 1200 2008
op1 1400 2008


i need a JOIN query which can give me the following data( by combining
the
tables)- left join or right join but it should combine all the data
from both tables irrespective of whether the joined fields are equal
or not


Carrier price for 2009 price for 2008
op 3333 1200
ot 233 -
op1 - 1400

You need a full outer join which is not supported in Jet unfortunately.
What you can do is UNION the results of a left join and a right join:

select a.carrier,a.price as PriceFor2009,b.price as PriceFor2008
FROM [Table A] as a left join [Table B] on a.carrier=b.carrier
UNION
select b.carrier,a.price as PriceFor2009,b.price as PriceFor2008
FROM [Table A] as a right join [Table B] on a.carrier=b.carrier

Since the union does not use the "ALL" keyword, duplicates will be
eliminated.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"