0

I'm just wondering if anyone could help on how to bring two columns from two tables together in one table.

These table have id's but they are not linked although they have other columns that I can join them on. But for the sake of getting the count of individual ID's I'm not sure if inner or outer join will give me the result that I want.

Here are the tables:

TABLE A

ID
1
2
3
4
5

TABLE B

2
4
5
7
8
9
10
11
12

I should be able to retrieve:

  1. select count(a.id) from the combined_table
  2. select count(b.id) from the combined_table

closed as off-topic by Jack Douglas Dec 24 '14 at 9:16

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

1

If there's no relationship between them...

Table A is customers and Table B is employees, and you really want to know how many bodies were involved with a production of Spamalot! I would look at a query like

SELECT
    A.RC + B.RC AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
) A
CROSS JOIN
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) B;

Read that as Add the RC column from A to the same column in B. A and B are derived in line and will only ever have one value returned as we're computing the scalar count of all the rows.

I'm using a CROSS JOIN because I need everything from both sets. I could have done this as a UNION ALL between the counts. The ALL is important, otherwise UNION eliminates duplicate rows. A variant on B would be to use a COUNT in the outer query and just select all the IDs. Assuming the data types match, it'd work either way.

-- Option B

SELECT
    SUM(D.RC) AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
    UNION ALL
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) D(RC);

or just done it inline from the SELECT

-- Option C
SELECT (SELECT COUNT_BIG(*) AS RC FROM dbo.A A) + (SELECT COUNT_BIG(*) AS RC FROM dbo.B B) AS TotalBodies;

Some of that may be database dependent but works fine for SQL Server.

SQLFiddle

Not the answer you're looking for? Browse other questions tagged or ask your own question.