Fetch data from different tables on same row

Home » SQL and Databases » Fetch data from different tables on same row
Photograph of a SSD Drive Array

I came across an interesting problem this week. The requirement was to get row counts from two different tables and return these on the same row. The tables had a common column, lets call it category.

The first thought was to use GROUP BY in T-SQL, but there is a problem with this:

The problem with this code is that it gives the cartesian product of A and B, but I would like to know the count of A and the count of B on the same row.

There may well be better ways of doing it, but the way I solved it this time was to use CTE’s (common table expressions). The example T-SQL code using CTE’s would be as follows:

My live example was a little more complicated than this but I hope this demonstrates the concept.

About Phil

I have been working as a software developer since 1983. This blog could have been called "From Fortran 77, C and Cobol to C# in 20 (not so) easy years", but it doesn't sound quite right somehow. Besides I'm talking about what's happened since 2003, not before!

Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">




Top Posts & Pages

Categories

Recent Posts

Recent Comments

Archives

Blogroll

  • Mike Cohn's Blog
  • Scott Hanselman's Blog
- mike@mountaingoatsoftware.com

Too often, agile teams are expected to finish everything every iteration. This expectation is harmfu [...]

- mike@mountaingoatsoftware.com

When a product backlog becomes too big, it hinders agility. Discover four steps your team can take t [...]

- mike@mountaingoatsoftware.com

Pervasive myths about agile get in the way of success. It’s time to bust six of those myths. [...]

- mike@mountaingoatsoftware.com

Agile teams strive to finish work in the same iteration in which its begun. Here’s why that is so im [...]

- mike@mountaingoatsoftware.com

I wrote 25 blog posts during 2018. In case you missed some of them, here are the most popular. [...]

- Scott Hanselman

My Xbox user name is Glucose for a reason. This is a passion project of mine. You've likely see [...]

- Scott Hanselman

I've been really enjoying my Xbox lately (when the family is asleep) as well as some fun Retrog [...]

- Scott Hanselman

So you've been asked to parse some dates, except the years are two digit years. For example, da [...]

- Scott Hanselman

I've been working on a little idea where I'd have an app (maybe a mobile app with Xamarin [...]

- Scott Hanselman

"EditorConfig helps maintain consistent coding styles for multiple developers working on the sa [...]

Meta