Help with SQL query

Please bare with me in trying to get my point across, I'm new to SQL and
would appreciate the help so much.
I have two table Table1 and Table2. There is a one to many relationship
between Table1 and Table2. Table2 has a list of widgets, I need to create a
query that would show the records from Table1 along with it's related
records and all non related records from Table2. So if I have 5 widgets and
only two have related records in Table1 results should be
Table1ID(2) fkTable2 Table2(widgetname1)
Table1ID(2) fkTable2 Table2(widgetname2)
null null Table2(widgetname3)
null null Table2(widgetname4)
null null Table2(widgetname5)
When I filter the query on another Table1ID, I would need the same sort of
result for each Table1ID.try this
SELECT TABLE1.COL1,TABLE1.COL2,TABLE2.COL1,TABLE2.COL2 FROM TABLE1 RIGHT
OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL1
--
Regards
R.D
--Knowledge gets doubled when shared
"Tim Harvey" wrote:

> Please bare with me in trying to get my point across, I'm new to SQL and
> would appreciate the help so much.
> I have two table Table1 and Table2. There is a one to many relationship
> between Table1 and Table2. Table2 has a list of widgets, I need to create
a
> query that would show the records from Table1 along with it's related
> records and all non related records from Table2. So if I have 5 widgets an
d
> only two have related records in Table1 results should be
> Table1ID(2) fkTable2 Table2(widgetname1)
> Table1ID(2) fkTable2 Table2(widgetname2)
> null null Table2(widgetname3)
> null null Table2(widgetname4)
> null null Table2(widgetname5)
> When I filter the query on another Table1ID, I would need the same sort of
> result for each Table1ID.
>
>
>

Help with setting row background color

I am trying to do something where I say look through the row. When you come across the word "Start" color that box green and color all other boxes to the right in that row green as well until you come upon the word "stop". Is this possible? If I am not making sense just let me know and I will try to explain better. Thanks in advance for any help that I get.

I believe I understand what you are looking for. I'm guessing the report is time based going across? Sort of like a Gantt chart?

If this is something like your scenario, here's a solution that might work...

One row per item, instead of text 'start' and 'stop', have a field that has a start time and length of time.

Then add a cell for each time period. The background color should be:

=iif(Fields!RPlusInHours.Value <= 3 and Fields!LengthInHours.Value+Fields!RPlusInHours.Value >= 3.5, "Green", "Transparent")

This needs to be cut/paste into each cell, and then times in the IIF adjusted accordingly. That part was a painful process, but once complete, the report looks very nice. All you have to to is enter the action, the start time and how long, and a nice bar goes across. (You can also use some code to have it choose colors for you, but I skipped that here since you specified green for all)

If this doesn't match your scenario, please describe what your columns are used for more and we can try again.

|||

I am not sure how your data is structured. Also i assume you are using a table as the display tool in report manager.

What i did was create a data set that looks similar to this

select 'start' as col1, '2' as col2, '3' as col3 , '4' as col4, '5' as col5 ,'stop' as col6

result

col1 col2 col3 col4 col5 col6

start 2 3 4 5 stop

--

i then created a table in the report body. Each column in the table had a column from the data set. Starting with col1 ending with col6.

In each expression box for backgroundcolor i put the following expression.

=IIF( Fields!col1.Value ="start","green","white")

for col6 i put the following

=IIF( Fields!col6.Value ="stop","red","white")

this will make the row green until the last column which will be red.

If your data set is structured any other way post an example here and we may be able to help you find the answer .

Thank you

|||

Hey Charles, thanks. That is kind of what I am going for. However I dont want the entire column to be green just the row since each row contains different information.

Ok I thought I would add this and try to explain (Note: that I there is datae in these fields but I removed it). What is in Black is what I want to be green in my report and what is in green and Red will be transparent. What is in black varies in each row. So I need something that works per row and not just field specific. Does that make sense?

avail a

sql

Help With Query

I am new at this so your patience and help is appreciate in advance. I have
5
tables that are updated with call center stats. The common field across all
tables is the associate. Some of the tables are updated daily while others
may be updated once a w.
I want to sum all the stats by associate from these tables and using weighte
d
formulas report on their performance. I need to be able to provide the user
with reporting for a date range that will be entered through a Web page
(begin date / end date).
How do I go about doing this? Link all the tables by associate, do the sums
and then the formulas within the same query or run separate queries for each
table and then link results via associate? My main problem is being able to
pass the date range. I've done this in ACCESS with a form as the central
input for the date range, but don't know how in MS-SQL.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1If you post DDL we can give more specific advice. Please see this link for
details...
http://www.aspfaq.com/etiquette.asp?id=5006
As for the general process for doing this...
Start by joining the necessary tables together and making sure you are
retrieving the rows you want.
The add in the date criteria. Again, make sure these are the rows you want.
Then add in the summaries. Make sure these are the results you want.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6202cfc0a9dd4@.uwe...
> I am new at this so your patience and help is appreciate in advance. I
have 5
> tables that are updated with call center stats. The common field across
all
> tables is the associate. Some of the tables are updated daily while others
> may be updated once a w.
> I want to sum all the stats by associate from these tables and using
weighted
> formulas report on their performance. I need to be able to provide the
user
> with reporting for a date range that will be entered through a Web page
> (begin date / end date).
> How do I go about doing this? Link all the tables by associate, do the
sums
> and then the formulas within the same query or run separate queries for
each
> table and then link results via associate? My main problem is being able
to
> pass the date range. I've done this in ACCESS with a form as the central
> input for the date range, but don't know how in MS-SQL.
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Help with multi Join or multi tier select.

Hello,

I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables.
PlanID is the primary in Plan and

PlanProviderProviderLocationLookups
--------------
PlanIDProviderIDProviderIDLookupType
PlanNamePlanIDProviderStatusLookupKey
RegionIDLastName...LookupValue
...FirstName...

Given a PlanID I want all the Providers with a ProviderStatus = 0

I can get the query to work just fine if there are records but what I
want is if there are no records then I at least want one record with
the Plan information. Here is a sample of the Query:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
left outer JOIN Provider p ON pln.PlanID = p.PlanID
left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey
WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

I know the problew the ProviderStatus on the Where clause is keeping
any records from being returned but I'm not good enough at this to
another select.

Can anybody give me some suggestions?

Thanks

DavidTry moving the predicate "AND PL.providerstatus = 0" into the ON clause:

FROM Plans AS PLN
INNER JOIN Lookups L3
ON L3.LookupType = 'REGN'
AND PLN.regionid = L3.lookupkey
LEFT OUTER JOIN Provider AS P
ON PLN.planid = P.planid
LEFT OUTER JOIN ProviderLocation AS PL
ON P.providerid = PL.providerid
AND PL.providerstatus = 0
LEFT OUTER JOIN Lookups AS L1
ON L1.lookuptype = 'PLRM'
AND PL.reasonmain = L1.lookupkey
LEFT OUTER JOIN Lookups AS L2
ON L2.lookuptype = 'PLX1'
AND PL.reasonsub = L2.lookupkey
WHERE PLN.planid = '123456789'

--
David Portas
SQL Server MVP
--|||No that didn't work becase then it all the providers ... and I think
only the locations with with providerstatus = 0.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RpOdnffKDN6GPVfdRVn-sQ@.giganews.com>...
> Try moving the predicate "AND PL.providerstatus = 0" into the ON clause:
> FROM Plans AS PLN
> INNER JOIN Lookups L3
> ON L3.LookupType = 'REGN'
> AND PLN.regionid = L3.lookupkey
> LEFT OUTER JOIN Provider AS P
> ON PLN.planid = P.planid
> LEFT OUTER JOIN ProviderLocation AS PL
> ON P.providerid = PL.providerid
> AND PL.providerstatus = 0
> LEFT OUTER JOIN Lookups AS L1
> ON L1.lookuptype = 'PLRM'
> AND PL.reasonmain = L1.lookupkey
> LEFT OUTER JOIN Lookups AS L2
> ON L2.lookuptype = 'PLX1'
> AND PL.reasonsub = L2.lookupkey
> WHERE PLN.planid = '123456789'|||I understood that you wanted to include rows from Plans which didn't have
corresponding rows from ProviderLocation - in which case they won't have a
ProviderStatus. It may be easier to understand your requirements if you post
DDL, sample data INSERTs and show your required result based on that sample
data. (http://www.aspfaq.com/5006)

--
David Portas
SQL Server MVP
--|||>> I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables. <<

Mind posting some DDL? When see data element names as poorily written
as "LookupType", "LookupKey" and "LookupValue", it is a pretty sure
sign that the basic schema design is wrong. To be is to be something
in particular and those names imply that you have a "One True Lookup
Table" (OTLT) flaw. Yes, like many diseases or disasters, it is
common enough to have a name! Google it; I wrote a column on it in
INTELLIGENT ENTERPRISE magazine.|||[posted and mailed, please reply in news]

David Logan (ibflyfishin@.yahoo.com) writes:
> I can get the query to work just fine if there are records but what I
> want is if there are no records then I at least want one record with
> the Plan information. Here is a sample of the Query:
> SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
> p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
> pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
> l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
> pl.InvalidData
> FROM Plans pln
> INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
> AND pln.RegionID = l3.Lookupkey
> left outer JOIN Provider p ON pln.PlanID = p.PlanID
> left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
> left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
> AND pl.ReasonMain = l1.LookupKey
> left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
> AND pl.ReasonSub = l2.Lookupkey
> WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
> ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum
> I know the problew the ProviderStatus on the Where clause is keeping
> any records from being returned but I'm not good enough at this to
> another select.

As David said, it is always a good idea to include CREATE TABLE and
sample data. But I think I have a guess what will work for you:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
LEFT JOIN (Provider p
JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
AND pl.ProviderStatus = 0
JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey)
ON pln.PlanID = p.PlanID
WHERE pln.PlanID = '123456789'
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

The point here is that the thing in parathensis is sort of a logical
table, and you make an outer-join to that logical table.

This is the normal way of doing things when you want to join a
left-joined table with a lookup table (should not be necessary to
left-join the lookup table). In this case it also necessary, to
exclude providers which does not have any location with status = 0.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp