SELECT FROM ? HOW 1

[straatlang]

I have two tables :

Articles

ID Name
123 AAA
456 BBB
789 CCC
888 DDD

Prices

ID Price
123 10
456 20
789 30
999 40

The query should result in a list where all information that is available
is visiable. SO infact in need to combine these two tabels.

ID Name Price
123 AAA 10
456 BBB 20
789 CCC 30
888 DDD
999 40

Does anyone knows if this possible with SQL.

 

[swampBoogie]

select coalesce(a.id,p.id) as id, a.name, a.price
  from articles a full outer join prices p
    on a.id = p.id

 

[straatlang]

Hi Swampboogie,

Nice to hear from you.

Actualy I am using MS-Access I have tried your SQl but if is given some errors do you also have a solution which I can use in MS-Access.

Thanks

 

[swampBoogie]

Access, I see. Access does not support full outer joins nor coalesce.

select iif(aid is null,pid,aid) as id, name, price
  from (select a.id as aid, p.id as pid,a.name,p.price
          from articles a left outer join prices p
          on a.id = p.id 
        union
       select a.id, p.id,a.name,p.price
          from articles a right outer join prices p
          on a.id = p.id 
       ) dt

 

[straatlang]

Dear Swampboogie,

Fantactic it works. I copy and past it into Access and the result is exactly what I needed.

Thanks again

Straatlang

 

[rycamor]

>Actualy I am using MS-Access

Umm... this happens to be the PostgreSQL forum. Unless you are using Access as a front-end to PostgreSQL, I don't see why this was posted here. -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[straatlang]

Sorry about this.

Regards

Straatlang

 

[rycamor]

No problem ;-). By the way, in case you haven't found them yet, there are a couple good Access forums in the DBMS Packages section:

http://www.tek-tips.com/threadforum.cfm?lev2=4&lev3=27

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html