All Records From Two Tables 3

[handle87]

Hi,

I have two tables lets say table A and Table B..
Table A Fields:
Name
Currency
Income

Table B Fields:
Name
Currency
Credits

I need to return all the rows from both tables and sort of combine them into a new view that would look like..

Table New Fields:
Name
Currency
Income
Credits

The comon fields are Name and Currency... each table has a different number of rows and if name/currency are the same in both tables they should be returned in the same row..

does that make sense?

Any ideas?

 

[checkai]

Select Name, Currency, Income, 0 From TableA
UNION
Select Name, Currency, 0, Credits From TableB

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[jbenson001]

Give this a try:

Select a.Name, a.Currency, a.Income, b.Credits
From a
   Inner Join B on a.Name = b.Name AND
                   a.Currency = b.Currency

 

[DrSql]

Yes,, it possible.. Step one create a new Table C with fileds Name, IncomeCurrency, Income, CreditCurrency, Credits.

Step 2 is to Insert into Tbale C

SELECT dbo.TableA.Name, dbo.TableA.Currency, dbo.TableA.Income, dbo.TableB.Credit, dbo.TableB.Currency AS Expr1
FROM dbo.TableA INNER JOIN
dbo.TableB ON dbo.TableA.Name = dbo.TableB.Name

step 3
insert into table c
SELECT dbo.TableA.Name, dbo.TableA.Currency, dbo.TableA.Income, dbo.TableB.Credit, dbo.TableB.Currency AS Expr1
FROM dbo.TableA LEFT OUTER JOIN
dbo.TableB ON dbo.TableA.Name = dbo.TableB.Name
WHERE (dbo.TableB.Name IS NULL)

Step 3
SELECT dbo.TableA.Name, dbo.TableA.Currency, dbo.TableA.Income, dbo.TableB.Credit, dbo.TableB.Currency AS Expr1, dbo.TableB.Name AS Expr2
FROM dbo.TableA RIGHT OUTER JOIN
dbo.TableB ON dbo.TableA.Name = dbo.TableB.Name
WHERE (dbo.TableA.Name IS NULL)


Hope this clarify your question

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[handle87]

Thanks for the quick replies... been havin a hard time with this...
I can't do te inner join like above because then I would only retrieve rows where the two tables match the criteria... but thatnks for the stab at it...
the union will do the trick but is there a way to make it so that if there is a matching record in both tables (matching on name/currency) they could show up in the same row?

I'm guessing that I'll have to do another query on the union query..

 

[micha123]

I havn't got a chance to test this but I think what you need is this:

select ISNULL(B.Name,A.Name) as Name,
       ISNULL(B.Currency,A.Currency) as Currency,
       A.Income, B.Credits
from A cross join B 
on B.Name = A.Name and B.Currency = A.Currency

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[handle87]

Hey checkai... do you know why I am getting the error:

The column '' was specified multiple times

 

[handle87]

Sorry, when I am trying to create a view... though it does run ok when I execute the statement without the create

 

[amrita418]

What error are you getting? Can you post your create view sql?

Regards,
AA

 

[DrSql]

Yes, becasue you have currency filed twice in your view, you might need to think about renaming to income_currency and credit_currency.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[handle87]

I was just about to reply to you but I figured it out... just needed to add the 'AS'

Select Name, Currency, Income, 0 As Credits From TableA
UNION
Select Name, Currency, 0 As Income, Credits From TableB

There isn't a way to do a GROUP BY is there... Ideally I would like to GROUP BY Name, Currency.. cant seem to be able to use the GROUP BY on the view that I created..

I'm kinda new... thanks again

 

[handle87]

Thanks Dr SQL.. I was thinking about doing it the create temp table way but was trying to avoid creating another table in the db.. though I suppose that it might run faster than a UNION..

 

[JamesLean]

I think micha123 was close but you need a FULL join not a CROSS join:

select ISNULL(B.Name,A.Name) as Name,
       ISNULL(B.Currency,A.Currency) as Currency,
       A.Income, B.Credits
from A FULL join B
on B.Name = A.Name and B.Currency = A.Currency

--James

 

[handle87]

Thanks a lot for all the replies... James had the answer that I was unltimately looking for with the FULL JOIN... I'm sure that will be very handy in the future as well.. thanks!