SQL Server Performance

can't match the data!!!

Discussion in 'Getting Started' started by piglet, Aug 17, 2007.

  1. piglet New Member

    Hi! I try to pull out data from three tables, and face a problem.
    Here is my query:
    SELECT Census2000_1.GEO2000,
    SUM(2003_Full].DEPSUMBR) AS TOTAL,
    V032006.GEO2000 AS V032006
    FROM [2003_Full] FULL OUTER JOIN
    Census2000_1 ON FDIC.dbo.[2003_Full].FIPS = Census2000_1.GEO2000
    V032006 ON FDIC.dbo.[2003_Full].FIPS = V032006.GEO2000
    GROUP BY [2003_Full].FIPS, Census2000_1.GEO2000,V032006.GEO2000
    I can get three tables match one another if the data exists in all three tables.
    But with those data only exists in two of the tables, it'll show up in the table and again in the other table, like the following example:
    Column1 Column2 Column3
    2 2 2
    3 3 3
    What can I do to make it in order and leave blank if the data doesn't exist in that file.
    Column1 Column2 Column3
    1 1
    2 2 2
    3 3 3
    7 7
    9 9
    Does anyone have an idea to solve the problem?
    Thanks in advance
  2. piglet New Member

    Hi! I just changed a little bit of my query:
    SELECT ResearchAssessment.Francisca.Census2000_1.GEO2000,
    ResearchAssessment.Francisca.V032006.GEO2000 AS V032006,
    FROM ResearchAssessment.Francisca.Census2000_1 FULL OUTER JOIN
    ResearchAssessment.Francisca.V032006 ON ResearchAssessment.Francisca.Census2000_1.GEO2000=ResearchAssessment.Francisca.V032006.GEO2000
    (SELECT FDIC.dbo.[2003_Full].FIPS AS GEO,
    SUM(FDIC.dbo.[2003_Full].DEPSUMBR) AS TOTAL
    FROM FDIC.dbo.[2003_Full]
    GROUP BY FDIC.dbo.[2003_Full])ON ResearchAssessment.Francisca.V032006.GEO2000=FDIC.dbo.[2003_Full].FIPS
    I wanna use the outcome of aggregation as a table, and then make full outer join with other two tables to catch every record in three tables.
    But it can't work!!!
    Could anyone kindly tell me where I dod wrong?
    Thank you
  3. Adriaan New Member

    For a FULL JOIN, you still need to tell SQL Server which columns you're joining ON.
    If you're looking for the cartesian product, use CROSS JOIN.

Share This Page