Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Merge 2 Sql Selects

    Is the following possible in a select statement without a join operator as there is nothing linking the 2 tables.

    Table1

    id Firstname

    1 Peter

    Table2

    3 Smith

    Code to be something like:

    (Select firstname from Table1 where id=1 + ' ' + select Lastname from Table2 where id=3)

    Which would return

    Peter Smith

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT one.firstname + ' ' + two.lastname
      FROM ( SELECT firstname
             FROM table1 
            WHERE id = 1 ) AS one
    CROSS
      JOIN ( SELECT lastname
             FROM table2 
            WHERE id = 3 ) AS two
    as requested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Bingo!

    THanks, hadnt come across Cross Join before

  4. #4
    Join Date
    Sep 2011
    Posts
    44

    further question

    The below works if the records exist. However if just one exists eg Firstname or lastname I want that displayed


    create table table1
    ( id varchar(2),
    firstname varchar(20))

    create table table2
    ( id varchar(2),
    lastname varchar(20))

    insert into table1 (id,firstname) values ('1','PETER')
    insert into table2 (id,lastname) values ('3','SMITH')

    select * from Table1
    select * from Table2


    SELECT one.firstname + ' ' + two.lastname
    FROM ( SELECT firstname
    FROM table1
    WHERE id = '2' ) AS one
    CROSS
    JOIN ( SELECT lastname
    FROM table2
    WHERE id ='3' ) AS two

    I would require this just to return
    SMITH

    How can I do this?

    Thanks

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select
        (COALESCE((SELECT firstname + ' ' FROM table1 WHERE id = '2'), '') +
         COALESCE((SELECT lastname FROM table2 WHERE id ='3'), '')) AS FullName
    Hope this helps.

Tags for this Thread

Posting Permissions

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