mysql querying from multiple tables

Question

Ok, I was having some trouble accomplishing this and, quite frankly, don't even know if it is possible to do what I want.

I have the following table structure: Table Structure

Tags (t)
============
ID
LINE_ID
TAG
ACTIVE

Dashboard Lines (dl)
===============
ID
LINE_ID
GAUGE_TAG_ID
DISPLAY_NAME
ACTIVE

Dashboard Points (dp)
================
ID
DASHBOARD_ID
TAG_ID
DISPLAY_NAME

What I am trying to do is pull all of the t.TAG into a single result based on all of the IDs in the 'Dashboard Points' table. The one exception is the GAUGE_TAG_ID in the Dashboard_Lines table. I also need to pull that tag as well. I have been able to successfully pull all of the t.TAG into a single result joining on dp.TAG_ID = t.ID. Unfortunately I have not figured out how to also include joining on the dl.gauge_tag_id to get the correlating t.tag associated with the ID.

My current query (not pulling the gauge_tag_id)

SELECT GROUP_CONCAT(DISTINCT t.TAG), dl.gauge_tag_id, dl.line_id
FROM tags t
JOIN dashboard_lines dl ON dl.line_id = t.line_id
JOIN dashboard_points dp ON dp.tag_id = t.id AND dp.dashboard_id = dl.id
WHERE t.line_id = '1'                                                       
AND t.active = 'Y'

This successfully groups all the t.TAG with correlating idson t.ID and dl.TAG_ID

Is it possible to also include the t.TAG for the dl.GAUGE_TAG_ID?

Edit: http://sqlfiddle.com/#!9/ace98d

Edit 2: Desired Results:

GROUP_CONCAT(t.TAG)
Line1_Over_Cnt, Line1_Case_Count, Line1_MaxCap..

(all the correlating tag_ids along with the gauge_tag_id -- displayed as the t.TAG)

This doesn't have to be all in one column (GROUP_CONCAT). It can be a traditional result set with all of the t.TAG so long as it includes all of them (the dp.TAG_ID on t.ID) and (dl.GAUGE_TAG_ID on t.ID)

TAG
Line1_Over_Cnt
Line1_Case_Count
Line1_MaxCap
Line1_Idle
Kleins_Line1_Reset
L1_STD_Rate
Line1_Rate_Temp
Line1_Rate

Edit3: Possible Solution

SELECT t1.tag
FROM tags t1
JOIN dashboard_lines dl ON dl.line_id = t1.line_id
JOIN dashboard_points dp ON dp.tag_id = t1.id AND dp.dashboard_id = dl.id
WHERE t1.line_id = '1'                                                       
AND t1.active = 'Y'
UNION
SELECT t2.tag
FROM tags t2
JOIN dashboard_lines dl ON gauge_tag_id = t2.id AND dl.line_id = '1'
WHERE t2.line_id = '1'                                                       
AND t2.active = 'Y'

This gives me all the tags listed in individual rows. Is this the best way? Is there a way to get them all into a single, concatenated result?

-- End edits --

Any help is appreciated, thanks.

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented Dec 10, 2015 at 15:14
I thought I explained the desired results, but I have edited to try and be more clear. Added a multiple row result example that would suffice. — Imperialized
– Imperialized, Commented Dec 10, 2015 at 15:34

Richard St-Cyr · Accepted Answer · 2015-12-10 16:23:20Z

1

After Edit3, you are almost there. Encapsulate everything in another select that can do the group concat for you.

SELECT GROUP_CONCAT(tags) FROM 
(
  SELECT t1.tag as tags
  FROM tags t1
  JOIN dashboard_lines dl ON dl.line_id = t1.line_id
  JOIN dashboard_points dp ON dp.tag_id = t1.id AND dp.dashboard_id = dl.id
  WHERE t1.line_id = '1'                                                       
  AND t1.active = 'Y'
  UNION
  SELECT t2.tag as tags
  FROM tags t2
  JOIN dashboard_lines dl ON gauge_tag_id = t2.id AND dl.line_id = '1'
  WHERE t2.line_id = '1'                                                       
  AND t2.active = 'Y'
) A;

answered Dec 10, 2015 at 16:23

Richard St-Cyr

9951 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Imperialized Over a year ago

This is it! Exactly what I was trying for. Thank you!

Collectives™ on Stack Overflow

mysql querying from multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related