INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Join Query to Single Set of Columns

Join Query to Single Set of Columns

(OP)
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?

CODE

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) 

RE: Join Query to Single Set of Columns

CODE

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

RE: Join Query to Single Set of Columns

(OP)
It works perfectly and is fast too! Thanks!

RE: Join Query to Single Set of Columns

(OP)
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.

RE: Join Query to Single Set of Columns

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

RE: Join Query to Single Set of Columns

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close