[Postgresql] Combining queries?

Elijah · 08-08-2004, 10:16 PM

I need to fetch data from two tables - organizationinfo and temp_organizationinfo,

Code:

select ctrl_no, organization_name, domain_name from organizationinfo, temp_organizationinfo where ctrl_no = '1420';

ERROR: Column reference "ctrl_no" is ambiguous

But I get the error above, how do I fetch from these two tables in one query??? another problem is that both tables have the same column names, so doing 'organizationinfo.ctrl_no' results in having me too choose between the two... but I want both tables.

Anyone help?

paulsm4 · 08-09-2004, 12:09 AM

You need to perform a SQL "join" on your two tables. For example:

Code:

  select a.ctrl_no, organization_name, domain_name
    from organizationinfo a, temp_organizationinfo b
    where a.ctrl_no = b.ctrl_no
    and a.ctrl_no = '1420';

This does the following:
[list=1][*]Gives the aliases 'a' and 'b' to tabkes 'organizationinfo' and 'temp_organizationinfo'[*]Relates ("joins") table 'organizationinfo' to 'temp_organizationinfo" via the field 'ctrl_no'[*]Further restricts the query to only those records where 'ctrl_no' in 'organization_name' equals 1420[*]Prints out the fields ctrl_no, organization_name and domain_name for each record that matches your search criteria[/list=1]

I qualified "a.ctrl_no", to distinguish it from "ctrl_no" in the other table (temp_organizationinfo). If some of the other columns match, you'll have to qualify those column names, too.

If the same data is in both tables, I'm not sure why you'd even need a join: just do a simple "select" from one table or the other (but not both at the same time).

Finally, if you're planning on doing much database work, I'd *highly* recommend the following book:

"SQL Queries for Mere Mortals", Michael J. Hernandez and John L. Viescas

Hope that helps .. PSM

Elijah · 08-09-2004, 12:28 AM

Well, both tables has different data.. but they're the same in their columns.

Quote:

Hope that helps .. PSM

Yes, this helps a LOT! Thanks!

Hko · 08-09-2004, 11:14 AM

Quote:

Originally posted by Elijah
Well, both tables has different data.. but they're the same in their columns.

So if understand you well, you have two tables like this:

Code:

  Table "person1"
  +----------+---------+
  | name     | country |
  +----------+---------+
  | John     | NL      |
  | Benny    | DE      |
  | Robert   | UK      |
  +----------+---------+


  Table "person2"
  +----------+------+
  | nm       | ctry |
  +----------+------+
  | Richard  | US   |
  | Robert   | UK   |
  +----------+------+

And then you need as an answer to your SQL-query:

Code:

  +----------+---------+
  | John     | NL      |
  | Benny    | DE      |
  | Richard  | US      |
  | Robert   | UK      |
  +----------+---------+

If this is your question, then you need an "union", not a "join".
For the example above, your query would be:

Code:

  SELECT name, country
  FROM person1
  UNION DISTINCT
  SELECT nm, ctry
  FROM person2

Note that "DISTINCT" here means you don not want duplicate rows in the result. (like in this example, there's only one "Robert", "UK". If you do want duplicates, use "ALL" instead of "DISTINCT". Then you'll have two rows "Robert", "UK".