How to Combine Two Separate Inner Join Queries into One

[EricRex]

Have been struggling to create a single Histogram query that generates Bucket Names and counts for two separate fields. The two following queries work fine separate, but can't be used in a Modern Chart that can only handle one query.



Query1:

SELECT RHistogramBuckets.MaxValue AS Bucket, Count(StockTrades.cPosSizeR) AS PosSizeRTrades
FROM StockTrades INNER JOIN RHistogramBuckets ON (StockTrades.cPosSizeR<[RHistogramBuckets].MaxValue) AND (StockTrades.cPosSizeR >= [RHistogramBuckets].MinValue)
GROUP BY RHistogramBuckets.MaxValue, RHistogramBuckets.MinValue
ORDER BY RHistogramBuckets.MinValue, RHistogramBuckets.MaxValue;

Query2:

SELECT RHistogramBuckets.MaxValue AS Bucket, Count(StockTrades.cIStopR) AS PosSizeRTrades
FROM StockTrades INNER JOIN RHistogramBuckets ON (StockTrades.cIStopR<[RHistogramBuckets].MaxValue) AND (StockTrades.cIStopR >= [RHistogramBuckets].MinValue)
GROUP BY RHistogramBuckets.MaxValue, RHistogramBuckets.MinValue
ORDER BY RHistogramBuckets.MinValue, RHistogramBuckets.MaxValue;

I've tried bunch of different variations on those queries, but most generate syntax or expression errors.

[CJ_London]

Use a union query for separate rows or a third query to combine your two queries on one row- but would need something you can join on unless you want a Cartesian query

[EricRex]

Use a union query for separate rows or a third query to combine your two queries on one row- but would need something you can join on unless you want a Cartesian query

Thanks, CJ! For a Modern Chart, Clustered Column, all of the data must be on the same row.

Too bad I haven't been able to convince it to use my Bucket Labels, but it's good enough for now.

Somewhat final versions of the queries and the combining query:

SELECT RHistogramBuckets.MaxValue, Count(StockTrades.cPosSizeR) AS PosSizeR
FROM StockTrades INNER JOIN RHistogramBuckets ON (StockTrades.cPosSizeR<[RHistogramBuckets].MaxValue) AND (StockTrades.cPosSizeR >= [RHistogramBuckets].MinValue)
GROUP BY RHistogramBuckets.MaxValue
ORDER BY RHistogramBuckets.MaxValue;

SELECT RHistogramBuckets.MaxValue, Count(StockTrades.cIStopR) AS IStopR
FROM StockTrades INNER JOIN RHistogramBuckets ON (StockTrades.cIStopR<[RHistogramBuckets].MaxValue) AND (StockTrades.cIStopR >= [RHistogramBuckets].MinValue)
GROUP BY RHistogramBuckets.MaxValue
ORDER BY RHistogramBuckets.MaxValue;

SELECT qrycPosSizeRHistogram.MaxValue, qrycPosSizeRHistogram.PosSizeR, qrycIStopRHistogram.IStopR
FROM qrycPosSizeRHistogram INNER JOIN qrycIStopRHistogram ON qrycPosSizeRHistogram.MaxValue = qrycIStopRHistogram.MaxValue;

[madpiet]

Thanks, CJ! For a Modern Chart, Clustered Column, all of the data must be on the same row.

Too bad I haven't been able to convince it to use my Bucket Labels, but it's good enough for now.

Somewhat final versions of the queries and the combining query:

SELECT RHistogramBuckets.MaxValue, Count(StockTrades.cPosSizeR) AS PosSizeR
FROM StockTrades INNER JOIN RHistogramBuckets ON (StockTrades.cPosSizeR<[RHistogramBuckets].MaxValue) AND (StockTrades.cPosSizeR >= [RHistogramBuckets].MinValue)
GROUP BY RHistogramBuckets.MaxValue
ORDER BY RHistogramBuckets.MaxValue;

SELECT RHistogramBuckets.MaxValue, Count(StockTrades.cIStopR) AS IStopR
FROM StockTrades INNER JOIN RHistogramBuckets ON (StockTrades.cIStopR<[RHistogramBuckets].MaxValue) AND (StockTrades.cIStopR >= [RHistogramBuckets].MinValue)
GROUP BY RHistogramBuckets.MaxValue
ORDER BY RHistogramBuckets.MaxValue;

SELECT qrycPosSizeRHistogram.MaxValue, qrycPosSizeRHistogram.PosSizeR, qrycIStopRHistogram.IStopR
FROM qrycPosSizeRHistogram INNER JOIN qrycIStopRHistogram ON qrycPosSizeRHistogram.MaxValue = qrycIStopRHistogram.MaxValue;

Not a whole lot to it. If you can't UNION stuff together (because thats the way the chart is), then you just join on as many matching columns as you need to, and rename stuff. no other way around it.