question

RMeier avatar image
RMeier asked

Advanced SQL query help request

First my table structure.

http://img38.imageshack.us/img38/8028/tables.png

ok...

The main table "tblProcessSignoff" stores my core data. the table "Tblprocesssignofflog" stores a log of when someone signsoff on the process. and the table "tblRequiredsignofflist" stores the list of all the groups required to signoff on that request.

Here is my problem... The report that is pulled right now, is pulled with all of this data in one table. The data that is needed is first form the core table. Then i need the following columns to be a list of group names from the tblProcessGroupsList. Then the field values will be one of 3 options. If the group is required for that signoff it has no value if it is not complete yet, or has the specific value from the log table. If it is not required it has "N/A". I have done lots of looking around but have found nothing. Any help would be appreciated.

sql-serverdatabase-design
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RBarryYoung avatar image RBarryYoung commented ·
I agree with Matt, it's not really possible for us to figure out what you want so far. I would suggest first posting the DDL for the tables (images look nice, but are of little practical use for us to help you), second include some sample data in the form of INSERT commands and finally, show us an example of what you are looking for in the output. Failing that, then take this to the main site ( www.SQLServerCentral.com) where we can have a better dialog on the matter.
2 Likes 2 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
You might want to consider trying to revise the text of your question a bit - I've read it a few times now and I just don't understand it... This might be a good question for the main SSC site...
1 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
I will add the chorus asking for a little clarification, but from what I do understand, it sounds like the core of your answer will end up being a case statement. They can be very powerful and flexible if used properly. If the cast statement makes the overall query too hard to read, you can break it out into either a CTE or a view to separate it from the rest of the logic of the query.
0 Likes 0 ·

4 Answers

·
Tom Staab avatar image
Tom Staab answered

Unfortunately, I cannot download the image because the firewall at my office blocks it. Regardless, I believe you can get what you need using LEFT JOIN and CASE as Rob suggested, but listing the groups as separate columns is a bit tricky. I think the best approach there is a PIVOT, but it's hard to know for sure without the table structure. Also, that might not be a good approach if the group list is dynamic because the column labels (i.e. group names) have to be hard-coded.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

Use LEFT JOIN to grab the fields you're interested in from the other tables, if they're there. Make sure that your ON clause includes something about the condition in which you want to involve the other table, and if possible, make sure you're using FK lookups, so that you don't end up with your Process list getting duplicates. In your SELECT clause, you can use CASE to be able to grab the appropriate field from the tables you've joined to.

If you need multiple rows in a second table to be shrunk into one, you may prefer to use a sub-query that uses FOR XML PATH('') or something to be able to get your list out.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

I have the same problem as Tom - our network denies access to the image location but I agree with everything posted so far. the question is complicated to understand but I think the LEFT JOIN suggested will achieve what I think you allude to needing.

I might add that COALESCE() may also help you for the rows that return NULL for the Tblprocesssignofflog and tblRequiredsignofflist tables. With that you can show the 'N\A' that you mention
eg COALESCE(tblRequiredsignofflist.mycolumn,'N\A')

Jonathan

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Meena avatar image
Meena answered
Sure, Thank you Usman..
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.