Join Query to Single Set of Columns 1

[PCHomepage]

I am trying to fetch results from three tables but I want the columns to be "on top" of one another rather than being side by side. In other words, there should be a single column with u.ID, another with both b.DarkCurr and p.Bin, the p.GreenR, p.Red, p.Blue, p.GreenB, p.RGr, p.BGb each in their own columns, and the b.HistCounts and p.Total in the last column. Can this be done and how?

SELECT
	u.FileName AS FileName, u.ID, b.DarkCurr, p.Bin, b.HistCounts, p.GreenR, p.Red, p.Blue, p.GreenB, p.RGr, p.BGb, p.Total
FROM dcs_uploads u
    INNER JOIN dcs_bench b ON u.ID = b.FileID
    INNER JOIN dcs_probe p ON u.ID = p.FileID
WHERE u.ID IN (2, 3, 4, 7, 11, 8, 10, 6)

 

[r937]

SELECT u.FileName
     , u.ID
     , p.Bin
     , p.GreenR
     , p.Red
     , p.Blue
     , p.GreenB
     , p.RGr
     , p.BGb
     , p.Total
  FROM dcs_uploads u
INNER 
  JOIN dcs_probe p 
    ON u.ID = p.FileID
 WHERE u.ID IN (2,3,4,7,11,8,10,6) 
UNION ALL
SELECT u.FileName AS FileName
     , u.ID
     , b.DarkCurr
     , NULL
     , NULL
     , NULL
     , NULL
     , NULL
     , NULL
     , b.HistCounts
  FROM dcs_uploads u
INNER 
  JOIN dcs_bench b 
    ON u.ID = b.FileID
 WHERE u.ID IN (2,3,4,7,11,8,10,6)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

It works perfectly and is fast too! Thanks!

 

[PCHomepage]

Looking a the resulting data more carefully, it seems to be giving odd results. For example, if the ON values are (1,2,3,4), for example, those with ID of 1 are from dcs_probe and should have all columns populated. Those from 2,3,4 are from dcs_bench and should have data only in u.ID and in Total. The problem is that they need to be sorted on u.ID and p.Bin but when doing so, every other line with u.ID = 1 has most columns empty. Can you take another look at it? Thank you.

 

[r937]

most columns empty is what the query does when it's populating the row with columns from dcs_bench "on top of" dcs_probe

i'm afraid i no longer understand how you want the data re-arranged

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Yes, dcs_bench has only two populated data columns and also a primary ID key and a FileID that relates to dcs_uploads' ID key field. Dcs_probe is similar but with all columns populated and the data that results from the query is used in a JPGraph plot that automatically compensates for any empty columns by ignoring them.

Since this data is being used to create a plot, it needs to be sorted on the dcs_uploads ID field (or dcs_bench and dcs_probe FileID fields) so that data for a particular set is kept together, and on the X-axis field, which is Bin in dcs_probe and DarkCurr in dcs_bench. Maybe what I need to do is to fetch b.FileID and u.FileID rather than u.ID.

Since dcs_probe contains multiple Y-axis values with color information and dcs-bench does not, it seemed best to put dcs_bench's single Y-axis data in with the dcs_probe Total column. I wasn't sure how to order it with the multiple queries but it's important that the data be sorted somehow that keeps it coherent. The dcs_bench and dcs_probe tables' ID field is not used for anything.