phone

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

table A


carrier price year
op 3333 2009
ot 233 2009


table B


Carrier price year
op 1200 2008
op1 1400 2008


i need a query which can give me the following data( by combining the
tables)-


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


Thanks for the help Any SQL JOIN Query pls
 
subs said:
table A
carrier price year
op 3333 2009
ot 233 2009

table B
Carrier price year
op 1200 2008
op1 1400 2008

i need a query which can give me the following data( by combining the
tables)-

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

Any SQL JOIN Query pls
Click to expand...

Can't do that with just a join.

You need a full outer join (requires a UNION) to combine
two disparate sets of records:

SELECT A.Carrier, A.Price As Price2009,
B.Price As Price2008
FROM tableA As A LEFT JOIN tableB As B
ON A.Carrier = B.Carrier
UNION ALL
SELECT B.Carrier, Null, B.Price
FROM tableA As A RIGHT JOIN tableB As B
ON A.Carrier = B.Carrier
WHERE A.Carrier Is Null

If you had only one table with a field for the year, then
you could get what you want by using a crosstab query (and
it would work next year without having to create a new
query).
 
Back
Top