How to combine data using SQL from multiple rows into a single result
I have created a little bit of a messy database and I am debating on restructuring or, hopefully finding an easy solution here to work around it and just deal with it.
I have setup rows that look like this:
Basically, line 1 is a total of all data and every additional line is a subset of this information (type identifies if it is Total = 1 or Sale Type = 2 line..
id | type | tech | name | sold | hours | report_id
1 | 1 | 2354 | JOHN DOE | 224.23 | 11.5 | 9
2 | 2 | 2354 | INTERNAL | 98.45 | 4.8 | 9
3 | 2 | 2354 | CUSTOMER | 88.35 | 3.7 | 9
Line 1 = Is a total line because type = 1 so the name field is the name of the employee
Line 2 = Is type = 2 so is a subset of data which Identifies how much of John Doe's sales were of type=INTERNAL
Line 3 = Is type = 2 so is a subset of data which Identifies how much of John Doe's sales were of type=CUSTOMER
When I report on Total - it's easy - just grab the line where type = 1.
Problem is when I run a report on internal sales - Everything reports back fine EXCEPT the "name" - every staff members "name" is returning INTERNAL or CUSTOMER (which is to be expected)
What I want to do is somehow say (if the line type = 2 use name of matching tech
So when displaying INTERNAL sales, I see it is line type = 2 so grab the name WHERE line type = 1 AND tech = 2354...
Help? Here is what I started playing with but it returns all names (instead of matching tech = tech - is there a way to match this???)
If I can't do it like this, I'll just make a separate call to retrieve the name line by line but am just trying to find the most efficient way of doing this...
(SELECT name FROM ReportData WHERE report_id = 9 AND tech = tech AND line_type = '1') as "name",
FROM ReportData WHERE report_id = 9 AND name = "INTERNAL"