Three Table Exception 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 2008 Forums

Transact-SQL (2008)

Three Table Exception Query

Author

Topic

Turvey87
Starting Member

3 Posts

Posted - 2011-06-01 : 09:42:45

First off I'm still a bit new to the creation of queries so go easy on me!

I'm working on checking transactions and whether they're "inbound", "outbound", or "other". As it stands, I'm able to collect the inbound and outbound information fine (although I'm not sure it's the most efficient query but we'll save that for another day) but am struggling when it comes to finding out the best method of acquiring the "other". I have "facility_id" on the clinic table (which is my outbound) and then "facility_id" on the facility table (which is my inbound). However, I have "facilityid" on my auditentry table which contains the facility_id's from both the clinic and facility table as well as some extra. I'm looking to grab those extra values that aren't in the other two tables. Would this require a couple queries to pull that information from those two tables and then comparing it? Any guidance would be appreciated!


select 'OUTBOUND' as direction, clinic.facility_id, Count(auditentry.controlid), ' ' As "Direction", 'April' AS "Month", ' ' AS Mnemonic 
from auditentry 
right outer join clinic on (auditentry.facilityid = clinic.facility_id) 
and(timestamp between '2011-04-01' and '2011-04-30') and (auditentry.status != 0) and (auditentry.FacilityName Is Not NULL) 
group by clinic.facility_id
union
select 'INBOUND' as direction, facility.facility_id, Count(auditentry.controlid), ' ' As "Direction", 'April' AS "Month", ' ' AS Mnemonic 
from auditentry 
right outer join facility on (auditentry.facilityid = facility.facility_id) 
and(timestamp between '2011-04-01' and '2011-04-30') and (auditentry.status != 0) and (auditentry.FacilityName Is Not NULL) 
group by facility.facility_id;

For what it's worth, here's what I have so far on the third query.


select 'OTHER' as direction, auditentry.facilityid, count(auditentry.controlid), 'April' AS "Month" 
from auditentry left outer join facility on (auditentry.facilityid = facility.facility_id) 
and (auditentry.facilityid != facility.facility_id) and(timestamp between '2011-04-01' and '2011-04-30') and (auditentry.status != 0) and (auditentry.FacilityName Is Not NULL) 
group by auditentry.facilityid;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-01 : 10:08:48

select a.facility_id, type = case when c.facility_id is not null then 'outbound' when f.facility_id is not null then 'inbound' else 'other' end
from (select distinct facility_id from auditentry) a
left join (select distinct facility_id from clinic) c
on a.facility_id = c.facility_id
left join (select distinct facility_id from facility) f
on a.facility_id = f.facility_id

That should give you a start - you could just use that as another entry in your union statement with a
where f.facility_id is null and c.facility_id is null
to get just the other entries.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Turvey87
Starting Member

3 Posts

Posted - 2011-06-01 : 11:22:30

quote:
Originally posted by nigelrivett

select a.facility_id, type = case when c.facility_id is not null then 'outbound' when f.facility_id is not null then 'inbound' else 'other' end
from (select distinct facility_id from auditentry) a
left join (select distinct facility_id from clinic) c
on a.facility_id = c.facility_id
left join (select distinct facility_id from facility) f
on a.facility_id = f.facility_id

That should give you a start - you could just use that as another entry in your union statement with a
where f.facility_id is null and c.facility_id is null
to get just the other entries.

Thank you very much! I decided to throw it all in to that one query but I'm still coming across a separate issue. I'm trying to run it in PostgreSQL 8.3.9 and have been able to change the syntax such that everything but the case statement works. I don't suppose you've had any experience with PSQL and would have any additional insights there? I realize this isn't a PSQL board and you've already helped more than I could have hoped so don't feel obligated to go out of your way but I figured I might as well ask!


select a.facilityid, 'Direction' = case when c.facility_id is not null then 'outbound' when f.facility_id is not null then 'inbound' else 'other' end, count(a.controlid), 'April' AS "Month"
from (select distinct facilityid, controlid, timestamp, status, facilityname from auditentry) as a 
left join (select distinct facility_id from clinic) as c
on a.facilityid = c.facility_id
left join (select distinct facility_id from facility) as f
on a.facilityid = f.facility_id
where(a.timestamp between '2011-04-01' and '2011-04-30') and (a.status != 0) and (a.FacilityName Is Not NULL)
group by a.facilityid, c.facility_id, f.facility_id;

For the count columns it's coming back with a column header of "?column?" and then all rows have "f" as the value.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-01 : 11:34:17

You probably need to group by the case statement or better, put a max around it.
You don't have a name for the count column

count(a.controlid) as rowcount ,

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Turvey87
Starting Member

3 Posts

Posted - 2011-06-01 : 11:59:54

It looks like it was a matter of pulling out the "'Direction' =" and assigning it a column name by enclosing it in parenthesis and then "AS direction". Thank you again for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources