Select Records Issue

  • Hi all,

    I have two tables one having company data named COMPANY and other have Landmarks data named LANDMARK.

    COMPANY have two fields(name and keywords) and LANDMARK (area,landmarkName)

    i want to select the whole data from both tables as one word/column.

    just like this shows me

    select name from COMPANY

    how i can do?

  • This question is not detailed enough.

    Please provide sample data, showing source data and desired output, as described in the link in my signature.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • engrshafiq4 (10/12/2011)


    Hi all,

    I have two tables one having company data named COMPANY and other have Landmarks data named LANDMARK.

    COMPANY have two fields(name and keywords) and LANDMARK (area,landmarkName)

    i want to select the whole data from both tables as one word/column.

    just like this shows me

    select name from COMPANY

    how i can do?

    Hello and welcome to SSC!

    It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.

    When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

    For now, I've knocked up what I've interpreted from your description: -

    --First, lets create some sample data to play with

    IF object_id('tempdb..#COMPANY') IS NOT NULL

    BEGIN

    DROP TABLE #COMPANY

    END

    IF object_id('tempdb..#LANDMARK') IS NOT NULL

    BEGIN

    DROP TABLE #LANDMARK

    END

    --50,000 Random rows of data

    SELECT TOP 50000 IDENTITY(INT,1,1) AS CompanyId,

    LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS Name,

    SUBSTRING(CONVERT(VARCHAR(255), NEWID()), 1, ABS(CAST(NEWID() AS binary(6)) % 25) + 1)+','+

    SUBSTRING(CONVERT(VARCHAR(255), NEWID()), 1, ABS(CAST(NEWID() AS binary(6)) % 25) + 1) AS Keywords

    INTO #COMPANY

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

    --Add a Primary Key

    ALTER TABLE #COMPANY

    ADD CONSTRAINT COMPANY_PK_CompanyId

    PRIMARY KEY CLUSTERED (CompanyId) WITH FILLFACTOR = 100

    --1,000 Random rows of data

    SELECT TOP 1000 IDENTITY(INT,1,1) AS LandmarkId,

    LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS Area,

    LEFT(CONVERT(VARCHAR(255), NEWID()),50) AS LandmarkName

    INTO #LANDMARK

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2

    --Add a Primary Key

    ALTER TABLE #LANDMARK

    ADD CONSTRAINT LANDMARK_PK_LandmarkId

    PRIMARY KEY CLUSTERED (LandmarkId) WITH FILLFACTOR = 100

    So, using my sample data above, if we wanted to get all of the data displayed together we're lucky because the data-types are the same.

    All we need to do is UNION the results together: -

    SELECT Name AS AllData FROM #COMPANY

    UNION ALL

    SELECT Keywords FROM #COMPANY

    UNION ALL

    SELECT Area FROM #LANDMARK

    UNION ALL

    SELECT LandmarkName FROM #LANDMARK

    We're expected a result set of 102,000 (50,000 names + 50,000 keywords + 1,000 areas + 1,000 landmarknames) and that's what we get.

    If this isn't what you were after, please follow the previous advise and post DDL and readily consumable test data along with expected results.

    Thanks!


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • IF i run this QUERY

    select top 3 COMPANY,Keywords from COMPANIES

    give me the following

    COMPANY|Keywords

    A | Q

    B | W

    C | E

    NOw if i run this query

    select top 3 landmark,area from LANDMARK

    it five me the following

    landmark|area

    D | R

    F | T

    G | Y

    Now i want to get like this

    A

    B

    C

    Q

    W

    E

    D

    F

    G

    R

    T

    Y

    How i will get this result..hope now you will easily understand what i want

  • Would this work:

    SELECT name FROM company

    UNION ALL

    SELECT keywords FROM company

    UNION ALL

    SELECT area FROM landmark

    UNION ALL

    SELECT landmarkName FROM landmark

    Or does the ordering of the data within the one column differ from this?

  • Yes this worked for me.....thanks alot

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply