Jump to content

SELECT DISTINCT and multiple table JOIN


epl

Recommended Posts

Hello all, I new to the forum. ;D

 

I think this question is general SQL but since I am using a postgres server I though I could ask for you guys here.

 

Here is the problem:

 

I have 4 tables to join. They are linked by one column, say ID.

Tab1 has (let's round and reduce to make the example clear) 30 rows.

Tab2 has 20 rows.

Tab3 has 10 rows.

Tab4 has 5 rows.

 

I need to join them using ID column but not duplicating any column that may appear in more than one table.

 

The final table must have 30 rows (the length of Tab1) with null values for columns in that does not have match in the other 3 tables.

 

Aditionally, I need to add extra columns in the created table (say tablejoined), but those columns will come from other tables (say tableA and tableB) that has many, many more rows than these four tables that I want to join and I don't want to add extra rows by doing that.

 

So far I am trying the following code:

 

CREATE TABLE tablejoined AS

SELECT DISTINCT Tab1.*, TabA.field1, TabB.field1

FROM Tab1, tabA, tabB

NATURAL JOIN Tab2;

NATURAL JOIN Tab3;

NATURAL JOIN Tab4;

 

It is running now... but I am not quite sure it will give the result that I want. Would that be better to use LEFT OUTER JOIN ... ON (ID)??

 

I would appreciate if you experts could give me some advise on that.

 

Thank you in advance.

Link to comment
Share on other sites

  • 2 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.