Problem with results of query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with results of query

Author

Topic

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 13:04:51

I am having some trouble getting the desired results from a query I'm writing.

There are 4 tables:
hpreference - preferences
-preferencename
hcategory - categories
-categoryname
hpreferencevalue - value for the preferences
-preferencevalue
hdatadictionaryitem - table containing preference groups
-name

Relationships:
hpreference related to hcategory (1:1)
hpreference related to hpreferencevalue (1:1)
hpreferencevalue related to hdatadictionaryitem(1:1)

Here is a quick synopsis of what's in the tables.
There can be multiple preference groups (1,2,3,4,5,6) these are contained in the (hdatadictionaryitem table). Each preference group can have preferences assigned to them (located in the hpreference table), and the value of the preference in the hpreferencevalue table. Preferences are then grouped into categories in the hcategory table.

Here is my query:

select cat.categoryname,pref.preferencename,
pval.preferencevalue,dd.name
from hpreference pref with (nolock)
inner join hcategory cat with (nolock)
on pref.category_oid=cat.objectid
left outer join hpreferencevalue pval with (nolock)
on (pref.objectid=pval.preferenceid)
left outer join hdatadictionaryitem dd with (nolock)
on (pval.groupid=dd.objectid)
where pref.preferencelevel='3'
and (dd.itemtypecode='PrefGroup'and dd.active=1)
order by cat.categoryname,pref.preferencename,dd.name

This query is only giving me all of the rows where a preference group, has a preference value. I want to see all preferences for each preference group even if it doesn't have a preference value. I figured the left joins would keep all preference names for each preference group, and show nulls for a preference value if it wasn't valued.

I think the problem is that I am retrieving the preference group name from the hpreferencevalue table.

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 13:18:13

I tried to narrow down the query to investigate the results further:

select cat.categoryname,pref.preferencename,
pval.preferencevalue,dd.name
from hpreference pref with (nolock)
inner join hcategory cat with (nolock)
on pref.category_oid=cat.objectid
left outer join hpreferencevalue pval with (nolock)
on (pref.objectid=pval.preferenceid)
left outer join hdatadictionaryitem dd with (nolock)
on (pval.groupid=dd.objectid)
where pref.preferencelevel='3'
and (dd.itemtypecode='PrefGroup'and dd.active=1)
and categoryname='General Worklist'
and preferencename='WKL_ALERTSINGLEPATIENT'
order by cat.categoryname,pref.preferencename,dd.name

I filtered the results to only 1 categoryname and preferencename.
There are 24 preference groups, so I want to get 24 rows. I'm only getting 20 rows, because 4 of the preference groups don't have that preference assigned, but I want them to be in the list, and show null for the value.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 13:57:19

[code]select cat.categoryname,pref.preferencename,
pval.preferencevalue,dd.name
from hpreference pref with (nolock)
inner join hcategory cat with (nolock)
on pref.category_oid=cat.objectid
left outer join hpreferencevalue pval with (nolock)
on (pref.objectid=pval.preferenceid)
right outer join hdatadictionaryitem dd with (nolock)
on (pval.groupid=dd.objectid and dd.itemtypecode='PrefGroup'and dd.active=1)
where pref.preferencelevel='3'
--and (dd.itemtypecode='PrefGroup'and dd.active=1)
and categoryname='General Worklist'
and preferencename='WKL_ALERTSINGLEPATIENT'
order by cat.categoryname,pref.preferencename,dd.name[/code]

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:02:12

That's what I figured I could do too. Use a right outer join instead of a left outer join, but I'm still getting only 20 records, not the ones that don't have the preference valued.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 14:04:21

not the ones having preference valued meaning preferencename='WKL_ALERTSINGLEPATIENT'??

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:05:16

I'm trying to go backwards now and write a query to get me the preferencegroups (dd.name) that don't exist in the list, but I'm having issues:

select dd.name,pref.preferencename,pval.preferencevalue
from hdatadictionaryitem dd with (nolock)
left outer join hpreferencevalue pval with (nolock)
on dd.objectid=pval.groupid
right outer join hpreference pref with (nolock)
on (pval.preferenceid=pref.objectid and pref.preferencelevel='3')
where (dd.itemtypecode='PrefGroup'and dd.active=1)
and pref.preferencename='WKL_ALERTSINGLEPATIENT'
and pval.objectid is null
-- order by pref.preferencename,dd.name
order by dd.name

I'm not getting any results back on this.

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:06:40

quote:
Originally posted by sakets_2000

not the ones having preference valued meaning preferencename='WKL_ALERTSINGLEPATIENT'??

Yes, I'm only getting the preference groups that have the 'WKL_ALERTSINGLEPATIENT' preference valued. I'm missing the other 4 prefernce groups which don't have 'WKL_ALERTSINGLEPATIENT' valued. I want to have NULL for the preferencevalue for those 4 preference groups

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:07:33

I tried right and left joins, but still not getting the 4 entries I need returned

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 14:08:12

Don't put it as a part of the where clause. Include that in the join clause.

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:11:42

select dd.name,pref.preferencename,pval.preferencevalue
from hdatadictionaryitem dd with (nolock)
left outer join hpreferencevalue pval with (nolock)
on (dd.objectid=pval.groupid and pval.groupid is null)
left outer join hpreference pref with (nolock)
on (pval.preferenceid=pref.objectid and pref.preferencelevel='3')
where (dd.itemtypecode='PrefGroup'and dd.active=1)
and pref.preferencename='WKL_ALERTSINGLEPATIENT'

returned 0 records......

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 14:16:17

What does this give you,

select dd.name,pref.preferencename,pval.preferencevalue
from hdatadictionaryitem dd 
left outer join hpreferencevalue pval  
on (dd.objectid=pval.groupid and pval.groupid is null and dd.itemtypecode='PrefGroup'and dd.active=1)
left outer join hpreference pref 
on (pval.preferenceid=pref.objectid and pref.preferencelevel='3' and  pref.preferencename='WKL_ALERTSINGLEPATIENT')

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:25:02

That gave me all the items in the hdatadictionary.

I tried this, and I get all 24 preference groups, but I'm not getting NULL values in the preferencename and preferencevalue for all of them.

select dd.name,pref.preferencename,pval.preferencevalue
from hdatadictionaryitem dd
left outer join hpreferencevalue pval
on (dd.objectid=pval.groupid and pval.groupid is null)
left outer join hpreference pref
on (pval.preferenceid=pref.objectid and pref.preferencelevel='3' and pref.preferencename='WKL_ALERTSINGLEPATIENT')
where dd.itemtypecode='PrefGroup'and dd.active=1
order by dd.name

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:29:01

This shouldn't be that hard, and normally I have no problem writing queries to find entries that don't exist in other tables, but this one is a real bi***

dmorand
Starting Member

20 Posts

Posted - 2009-01-07 : 14:35:22

I think I can't get those 4 preference groups because of the relationship between the tables.
I need to have a preferenceid from the preferencevalue table in order to get the preferencename

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources