Thread: join+query

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    1
    Rep Power
    0

    Unhappy join+query


    Hello Guys,

    I have a query :

    select id,name,address
    from
    (select * from
    (select id,name,address from call)
    union all
    (select id,name,address from call2)
    )

    it gives me for example 3 records from table call and call2 :
    1 jack USA
    2 tom USA
    4 harry canada

    I have another table subscription containing the coloums : name, subscription_type containing the data:

    jack prepaid
    tom postpaid
    harry normal

    I want to modify the query so that I get the id,name,address from call and call2 and subsription_type from subscriber table for the name 'jack'
    The results sshould be like this : 1 jack USA prepaid

    Thanks in advance

    Smith
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select id,c.name,address, subscription_type 
    from call c, subscription s
    where c.name = 'jack'
    and c.name = s.name
    union
    select id,c.name,address, subscription_type 
    from call2 c, subscription s
    where c.name = 'jack'
    and c.name = s.name

IMN logo majestic logo threadwatch logo seochat tools logo