Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: Joining Two tables

    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
    Last edited by asif_8; 01-28-05 at 11:56.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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 ...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •