Getting information based on two tables with the same column names

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi
Am getting problem retrieving information from two tables with exact the same colums.

The tables are:
Year 2009
projNr | location | Amount
A.023 East 10000€
A.023 East 2000€
A.023 East 5000€
C.100 East 8000€
Z.253 East 25000€

Year 2010
projNr | location | Amount
A.023 East 500€
A.023 East 500€
C.100 East 1000€
Z.253 East 5000€

Now i wanna find out the Amount from Year 2009 and 2010
based on projNr A.023 or maybe dynamically.

That what i query but it kept repeating the results:

SELECT a.proNr, a.Amount, a.location, b.Amount
FROM Year 2009 AS a
INNER JOIN Year 2010 AS b
ON a.projNr = b.projNr
WHERE a.projNr = 'A.023' AND b.projNr = 'A.023'

Can someone please help me? Instead of 5 records it repeating the records till 12 rows

Posted 15-Oct-14 0:10am

mikybrain1

Add a Solution

Comments

Rajesh waran 15-Oct-14 6:25am

How could u get 5 records? if you use inner join with projNr, u will get only 2 records.then how it is possible by using inner join?

Rajesh waran 15-Oct-14 6:27am

bcoz only 2 projNr are matching. i cant get ur idea, be clear with ur question

mikybrain1 15-Oct-14 6:32am

Ok. i want to get all the amount in year 2009 and 2010 based on the ProjNr.
That means if i set the projNr to A.023, sql server shd give me the amounts regarding A.023 in Year 2009 AND year 2010.
Do u get me now?

mikybrain1 15-Oct-14 6:44am

I thougt maybe something like this:
Year2009 A.023 East 10000€
Year2009 A.023 East 2000€
Year2009 A.023 East 5000€
Year2010 A.023 East 500€
Year2010 A.023 East 500€

mikybrain1 15-Oct-14 6:28am

i also dont know. i just wanna get the Amount and location for both years

[no name] 15-Oct-14 6:38am

Is expected Output something like this?
Year projNr TotalAmount
2009 A.023 17000
2010 A.023 1000

mikybrain1 15-Oct-14 6:42am

I thougt maybe something like this:
Year2009 A.023 East 10000€
Year2009 A.023 East 2000€
Year2009 A.023 East 5000€
Year2010 A.023 East 500€
Year2010 A.023 East 500€

[no name] 15-Oct-14 6:49am

Then one Option is using UNION

SELECT CAST('Year2009' AS CHAR(29) Year,
projNr,
location,
Amount
FROM Year2009
WHERE projNr = 'A.023'
UNION
SELECT CAST('Year2010' AS CHAR(29) Year,
projNr,
location,
Amount
FROM Year2010
WHERE projNr = 'A.023'

mikybrain1 15-Oct-14 6:57am

Thnx Dude. It´swas a great help. hope i can contact u again in case i get a problem.
Thnx very much

[no name] 15-Oct-14 7:02am

You are welcome.
Anyway think about to Change your database layout and make one table for all these data and add a field 'YEAR' :)

Rajesh waran 15-Oct-14 6:58am

ok then try Union Concept as Bruno Sprecher said,

King Fisher 15-Oct-14 7:13am

you cant apply join .bad table Design..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NaibedyaKar · Answer 1 · 2014-10-15T00:56:00

HI, can you please try with the below query,

SQL

SELECT Distinct a1.projNr
      ,a1.location
      ,a1.Amount
FROM
(
SELECT projNr
      ,location
      ,Amount
  FROM [dbo].Year2009

  UNION
SELECT projNr
      ,location
      ,Amount
  FROM [dbo].Year2010
  ) a1
  WHERE a1.projNr = 'A.023'

VC.J · Answer 2 · 2014-10-15T02:01:00

this query is inspired by the query in previous answer I have made few modification like instead of Union I used Union all

SQL

SELECT  a1.projNr
      ,a1.location
      ,a1.Amount
FROM
(
SELECT projNr
      ,location
      ,Amount
  FROM table_2009

  UNION ALL
SELECT projNr
      ,location
      ,Amount
  FROM table_2010
  ) a1
  WHERE a1.projNr = 'A.023'

Surendra0x2 · Answer 3 · 2014-10-15T02:22:00

Hi Try this Query

SQL

Select tb2.projNr,SUM(tb1.Year2009Amount+tb2.Year2010Amount) as Total,tb2.location from (select projNr,SUM(Amount) as Year2009Amount,location from year2009 group by projNr,location)tb1 inner join (select projNr,SUM(Amount) as Year2010Amount,location from year2010 group by projNr,location)tb2 on tb1.projNr=tb2.projNr group by tb2.projNr,tb2.location

or Use Union All

SQL

Select tbl.projNr,tbl.location,Sum(tbl.Amount) as Total from (select * from year2009
union all
select * from year2010)tbl group by  tbl.projNr,tbl.location

Getting information based on two tables with the same column names

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0