Joining Two tables

asif_8 · #1 (**permalink**) 01-28-05, 09:18

hi everyone
i have got 2 tables one is customer and the other is agents. i want to display all the firstnames and lastnames of the customers from the customer table and all the agentnames from the agents table and i wana see all data under one column

Littlefoot · #2 (**permalink**) 01-28-05, 12:06

It would help if you described those tables, so now we have to guess how to join those tables. My lucky guess will be that tables look like this:

Code:

CUSTOMER (cust_id, first_name, last_name, agent_id)
AGENT (agent_id, first_name, last_name)

If that's similar to your situation, you might try this query:

Code:

SELECT c.first_name ||' '||c.last_name ||' - '||
  a.first_name ||' '|| a.last_name customer_and_agent
FROM customer c, agent a
WHERE c.agent_id (+) = a.agent_id;

Concatenation shows all the data in one column, while outer join shows agents that don't have any customers.

I hope this helps ...

r937 · #3 (**permalink**) 01-29-05, 01:12

agreed, table layouts and relationships would have been valuable information

but this sounds more like a UNION than a JOIN to me

Code:

select 'customer'                   as nametype
     , lastname ||', '|| firstname  as fullname
  from customers
union all
select 'agent   '   as nametype
     , agentname    as fullname 
  from agents

Internet Services

Web Development

Digital Marketing

Consumer Tech