x

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.

more ▼

asked Oct 12 '09 at 07:37 PM in Default

RMeier gravatar image

RMeier
11 1 1 1

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...
Oct 12 '09 at 07:49 PM Matt Whitfield ♦♦
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.
Oct 12 '09 at 10:08 PM RBarryYoung

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.
Oct 16 '09 at 12:55 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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.

more ▼

answered Oct 21 '09 at 05:58 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 20 '09 at 11:48 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 13 '09 at 05:55 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left
Sure, Thank you Usman..
more ▼

answered Jan 20 '12 at 01:55 AM

Meena gravatar image

Meena
21 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x341
x107

asked: Oct 12 '09 at 07:37 PM

Seen: 1388 times

Last Updated: Oct 15 '09 at 05:43 AM