March 20, 2008 at 6:54 pm
I'm having a bit of a problem with the following union
SELECT incspc_test.incspc_type, item.item_sc as type
FROM incspc_test
INNER JOIN item ON incspc_test.incspc_item = item.item_id
WHERE (((incspc_test.incspc_type)=20))
UNION ALL SELECT incspc_test.incspc_type, product.product_sc as type
FROM incspc_test
INNER JOIN product ON incspc_test.incspc_type = product.product_id
WHERE (((incspc_test.incspc_type)=10))
It is only returning the type for the first select, not the second. I'm not sure I have the query written correctly, would you guys just take a look and see if the syntax looks correct, please.
Thanks in advance,
Diann
March 20, 2008 at 7:47 pm
I'm not sure what problem you are having. Please review what I have done and tell me what I may have missed.
create table dbo.incspc_test (
incspc_type int,
incspc_desc char(10)
)
create table dbo.item (
item_id int,
item_sc char(5)
)
create table dbo.product (
product_id int,
product_sc char(5)
)
insert into dbo.incspc_test (incspc_type, incspc_desc)
select 10,'First' union
select 20,'Second' union
select 30,'Third'
insert into dbo.item (item_id, item_sc)
select 10,'a' union
select 20,'b'
insert into dbo.product (product_id, product_sc)
select 10,'a' union
select 20,'b'
select * from dbo.incspc_test
select * from dbo.item
select * from dbo.product
SELECT
incspc_test.incspc_type,
item.item_sc as type
FROM
dbo.incspc_test
INNER JOIN dbo.item
ON incspc_test.incspc_type = item.item_id
WHERE
incspc_test.incspc_type = 20
UNION ALL
SELECT
incspc_test.incspc_type,
product.product_sc as type
FROM
dbo.incspc_test
INNER JOIN dbo.product
ON incspc_test.incspc_type = product.product_id
WHERE
incspc_test.incspc_type = 10
drop table dbo.incspc_test
drop table dbo.item
drop table dbo.product
😎
March 20, 2008 at 8:15 pm
That would have been a good one to ask for some data on... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2008 at 8:16 pm
Thanks for the response Lynn, at the same time I got your response I think I figured out all my issues. Here's what I am using now and it works.
select
CASE incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
product.product_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.inc_display_now
FROM incspc inner join product on product.product_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 10 and incspc.stat_flag = 'n'
UNION ALL
select
CASE incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.inc_display_now
FROM incspc inner join item on item.item_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 20 and incspc.stat_flag = 'n'
UNION ALL
select
CASE incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
bldng_room.bldng_room_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.inc_display_now
FROM incspc inner join bldng_room on bldng_room.bldng_room_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 20 and incspc.stat_flag = 'n'
I've still got a few unions to write but it seems to be working now, thanks to all who assisted.
Diann
March 20, 2008 at 8:24 pm
Jeff Moden (3/20/2008)
That would have been a good one to ask for some data on... 😉
Yea, not being very consistant on that am I. But I had some time to waste so I put something together.
😎
March 20, 2008 at 8:38 pm
sorry about that guys, the actual problem I was having was that - and this is fairly embarressing - on the second union I was joining the wrong column.....:blush:
March 21, 2008 at 9:00 am
Thanks for the feedback, Selena... I've made the same mistake many a time. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply