SQL / Reporting Help Please
October 27, 2010 10:29 AM   Subscribe

I'm looking for help with either SQL or with MS Reporting Services; not sure which, to be honest. I have a table of people (userID, username), a table of vegetable(s) each person bought (userID, vegatableID), and a table of fruit(s) each person bought (userID, fruitID). I'm trying to develop a report that has column A = name, column B = vegetables bought, and column C = fruits bought; I'm running into problems.

Each person can buy 0, 1 or many fruits or veggies. I'd like the report to look like this:
Name1 -- Vegetable1 -- Fruit1
      -- Vegetable2 -- Fruit2
      -- Vegetable3 -- [blank]
      -- Vegetable4 -- [blank]
Name2 -- [blank]    -- Fruit1
      -- [blank]    -- Fruit2
Right now the result I'm getting is repeating either the fruits or veggies (whichever there is less of). I'm just not sure if this is a problem that should be fixed in the stored procedure called by the report, or in the formatting of the report. Tips? If any additional details are needed/wanted, just post or memail. Thanks!
posted by inigo2 to Computers & Internet (14 answers total) 2 users marked this as a favorite
Sounds like there might be an issue with the grouping or join statement, can you get to the query itself?
posted by samsara at 10:48 AM on October 27, 2010

select p.userid, p.username, isnull(count(v.id),0), isnull(count(f.id),0)
from people p
left outer join vegetables v on v.userid=p.userid
left outer join fruits f on f.userid = p.userid
group by p.userid,p.username
posted by sanko at 10:50 AM on October 27, 2010

On (not preview) it looks like I misread your question - that query will give the total of fruits and vegetables bought by each person.
posted by sanko at 10:51 AM on October 27, 2010

This isn't a problem that naturally lends itself to SQL, so you might want to use a reporting tool.

There's no relationship between Fruit 1 and Vegetable 1 (why not pair Fruit 1 with Vegetable 2), so you have to make one up. One way is to add a counter column that has 1 for Person1's first fruit, 2 for their second fruit, etc.

First we sort the table of fruits by each person's name:

create table fruits2 as select * from fruits sort by name;
Then we add our counter column:
alter table fruits2 add counter integer;
update fruits2 set counter=rownum - (select rownum from people where people.name=name)

This uses rownum, which is a special column which keeps track of the current row in Oracle. I'm sure MSSQL has an equivalent but I don't know what it is.

Now do the same thing for vegetables.

Now, you can use a full outer join to combine fruits2 and vegetables2 by their counter column, leaving a blank wherever fruits has a value but veggies doesn't and vice versa. This is called a full outer join:

create table fruitsandveggies as select name, fruitid, vegetableid from fruits full outer join veggies on fruits2.counter = vegetables2.counter

The syntax for full outer join also varies between database systems.

Finally add back in the Name column using a regular join with
select * from people, fruitsandveggies where people.name=fruitsandveggies.name

There may be a much easier way to do all this that I don't know about, I'm not an expert on these things.
posted by miyabo at 11:21 AM on October 27, 2010

Your report is trickier than it looks: It seems that it is composed by a bunch of (person,fruit,vegetable) rows, but it isn't: There is no relation between the fruit and vegetable in each row, they just happen to be side by side in the output format you want.

Does select statement looks like the following?

SELECT person, fruit,vegetable
FROM people AS p
LEFT JOIN vegetables AS v ON v.userID = p.userID
LEFT JOIN fruits AS f ON f.userID = p.userID

If it does, it's producing one row for each (person,fruit,vegetable) combination, this is why you are getting repeated values. If person A buys one fruit F and three vegetables V1,V2,V3, the output table will look like so:


You can probably coax SQL into giving you the output you want using black arts like cursors or something like that, but I don't think there's a Good way to do it - although there might be something I'm missing. I would suggest laying out your report in a more SQL-friendly manner.
posted by Dr Dracator at 11:21 AM on October 27, 2010

Oops, for creating the counter it should be more like:

update fruits set counter=rownum - select min(rownum) from fruits where fruits.name = name
posted by miyabo at 11:22 AM on October 27, 2010

Sounds like there might be an issue with the grouping or join statement, can you get to the query itself?

Yeah, I can get to/modify anything involved here (besides the data, I suppose). I can't really post the full current query (bunch of other stuff going on in there), but it's basically doing:

SELECT u.UserID, f.fruitID, v.vegetableID FROM users u
LEFT OUTER JOIN fruits f ON u.UserID = f.UserID
LEFT OUTER JOIN vegetables v ON u.UserID = v.UserID

So, could definitely be an issue with the grouping statement, since I don't seem to have one right now :)
posted by inigo2 at 11:25 AM on October 27, 2010

Or, just like Dr Dracator guessed it would look like... I need to learn to preview.
posted by inigo2 at 11:27 AM on October 27, 2010

If you don't mind getting more columns returned, a super easy way would be:

select people.userID, people.username, vegetable.userID, vegetable.vegetableID, fruit.userID, fruit.fruitID from people, vegetable, fruit WHERE people.userID = vegetable.userID AND people.userID = fruit.userID
posted by bikergirl at 11:32 AM on October 27, 2010

That does something slightly different -- it gives every possible pair of fruits and vegetables for a user, not just all the vegetables + all the fruits. Which may be fine for this application.
posted by miyabo at 11:39 AM on October 27, 2010

I think you might want to consider using temporary tables. Select all fruits purchased by the person into the temp table (using INSERT...SELECT syntax), then select all vegetables purchased by the person into the temp table. You are then free to select a two-column report that lists all fruits, and all vegetables, purchased by the person. Hopefully, the two-column format is acceptable.
posted by richyoung at 12:20 PM on October 27, 2010

I don't have SQL Server in front of me right now so I can't check any of this code will work, but...

I would get the fruit and vegetables in one go and then split them up again in the report, something like:

SELECT u.UserID, f.FruitID AS PlantID, 'F' as Tag FROM Fruit f
LEFT OUTER JOIN User u ON f.UserID = u.UserID
SELECT u.UserID, v.VegetableID AS PlantID, 'V' as Tag FROM Vegetables v
LEFT OUTER JOIN User u ON v.UserID = u.UserID

I'm assuming you don't really care about the ID in the report and can just pull the relevant name column.

An alternative approach may be to return the fruit and vegetables as comma separated lists.
posted by robertc at 3:16 PM on October 27, 2010

I went with robertc's UNION approach, and then split them into respective columns in the report itself. Not ideal (end up with blank lines now) but better than duplicates...

I guess the lesson I should take is that I shouldn't be so stubborn in how I want the reports to look.

Thanks to everyone for their suggestions!
posted by inigo2 at 7:08 AM on November 2, 2010

(I'm holding off for a little on the "resolved" tag, in case someone pops in with some new fancier solution that will avoid the blanks. I'll add it in a couple days regardless, though.)
posted by inigo2 at 7:09 AM on November 2, 2010

« Older Location-aware photos = virtual reality on an ipad...   |   She's a BOXER: I should have named her Cassius... Newer »
This thread is closed to new comments.