www.webdeveloper.com
Results 1 to 5 of 5

Thread: How to combine data using SQL from multiple rows into a single result

  1. #1
    Join Date
    Dec 2013
    Location
    Melbourne, Australia
    Posts
    26

    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:

    Code:
    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
    ...
    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..

    i.e.
    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???)

    Code:
    SELECT id, 
    	 type,
    	 tech,
    	 (SELECT name FROM ReportData WHERE report_id = 9 AND tech = tech AND line_type = '1') as "name",
    	 sold_amt,
    	 sold_hrs
    FROM ReportData WHERE report_id = 9 AND name = "INTERNAL"
    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...

  2. #2
    Join Date
    Dec 2013
    Posts
    63
    Hmmm, to say the truth I don't understand your problem.

    I have an idea. Show us two tables. One table similar to that one you showed above but with a larger number of example rows. And the second table containing rows that you expect to see after running your report (fill this second table manually). If you do this (I mean showing us these two tables) it should be easier for us to see what do you expect from this report and to work on it.

  3. #3
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,348
    surely report_id = 9 AND tech = tech should be report_id='9' AND tech ='2354'
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,334
    Maybe something like this?
    Code:
    SELECT
    	rd1.id, 
    	rd1.type,
    	rd1.tech,
    	rd2.name,  -- note different table alias
    	rd1.sold_amt,
    	rd1.sold_hrs
    FROM ReportData AS rd1
    INNER JOIN ReportData AS rd2 USING(tech, report_id)
    WHERE
    	rd1.report_id = 9      AND
    	rd1.name = 'INTERNAL'  AND
    	rd2.type = 1
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Dec 2013
    Location
    Melbourne, Australia
    Posts
    26
    "NogDog" - once again, you've provided the goods!

    "blasphemy" - For clarity, what I was trying to achieve (and what NogDog was able to help solve) was:

    from table:

    Code:
    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
    ...
    When Searching for name = INTERNAL, return all employees INTERNAL sales like this:

    Code:
    tech | name     | sold  | hours 
    2354 | JOHN DOE | 98.45 | 4.8   
    ...
    i.e. I am using the data from the internal line but rolling it up to the selling employee (JOHN DOE)

    "\\.\" - That's the core of the problem - I want to be able to identify what the "tech" is so I can match it to the name

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles