January 27, 2007 at 6:07 am
Hi
I have a quey that performs some joins and then return a result containing a numbner of items existing in a table called mainItems.
Some of these items exist in another table(called tableExtra), and in this table they have a reference to a parent item.....if my query against table mainItems find record/records that also exist in my tableExtra table, then I dont want to output the item number from mainItems quesry, instead I would like to output the related parent item from the tableExtra table......
If the item only exist in the mainItems table (and not in tableExtra table) I would of course output the mainItems item.....the "replacement" to the parent Item from the tableExtra should only be done if the item is in this table...
Very greatful for help on this one..please respons to the forum...
//Helmut
January 27, 2007 at 7:16 am
Please post a simplified DDL and your SQL. For more details, see
http://www.aspfaq.com/etiquette.asp?id=5006
create table mainItems
(MainItemNumber integer not null
,MainDescription varchar(255) not null
, MainColorvarchar(255) not null
, constraint mainItems_P primary key (MainItemNumber )
)
go
create table ParentItems
(ParentItemNumber integer not null
,ParentDescription varchar(255) not null
, constraint ParentItems_P primary key (ParentItemNumber )
)
go
create table tableExtra
(MainItemNumber integer not null
,ParentItemNumber integer not null
, constraint tableExtra_P primary key (MainItemNumber )
)
go
select 'mainItem' as ItemType
,MainItemNumber,MainDescription
from mainItems
whereMainColor = 'Purple'
andnot exists
(select 1
from tableExtra
wheretableExtra.MainItemNumber = mainItems.MainItemNumber
)
union all
select'ParentItem' as ItemType
,ParentItemNumber
,ParentDescription
fromParentItems
whereexists
(select 1
from mainItems
jointableExtra
on tableExtra.MainItemNumber= mainItems.MainItemNumber
wheretableExtra.ParentItemNumber= ParentItems.ParentItemNumber
andMainColor = 'Purple'
)
SQL = Scarcely Qualifies as a Language
January 27, 2007 at 8:53 am
Hi again,
This is my main query against the mainItem table
SELECT Select [mainItem.ItemNumber] AS [Main_Id_ConstraintGenerated]
FROM [mainItem] [Main]
WHERE [mainItem].[Active] = 'Y'
ORDER BY [Main].[ItemNumber] ASC
......let's pretend that this query returns
the following items numbers:
11222
11233
11244
My second table called tableExtra has got two fields : parentItem and mainItemNumber...let's pretend the content of this
table extra table is like below:
parentItem mainItemNumber
---------- --------
10 11222
20 11233
What I now want to achieve is the following, if any item from my first query (against mainItem)
could be found as a mainItemNumber in the table tableExtra, then I would like to output the corresponding
parentItem....
So when I apply the tableExtra information I would like the query to return the final output consisting of these THREE records:
10 (itemNumber 11222 exist in tableExtra and point to parent=10)
20 (itemNumber 11233 exist in tableExtra and point to parent=10)
11244 (itemNumner 11244 NOT exist in tableExtra)
help appreciated !!
/Helmut
January 27, 2007 at 11:18 am
Please post create table and insert statements not lists of data values.
insert into mainItems
(MainItemNumber ,MainDescription, MainColor)
select 11222, '11222', 'Purple' union all
select 11233, '11233', 'Purple' union all
select 11244, '11244', 'Purple'
insert into tableExtra
(ParentItemNumber , MainItemNumber )
select 10 , 11222 union all
select 20 , 11233
select 'mainItem' as ItemType
, MainItemNumber
from mainItems
where MainColor = 'Purple'
and not exists
(select 1
from tableExtra
where tableExtra.MainItemNumber = mainItems.MainItemNumber
)
union all
select 'ParentItem' as ItemType
,tableExtra.ParentItemNumber
from tableExtra
whereexists
(select 1
from mainItems
where mainItems.MainItemNumber = tableExtra.MainItemNumber
and MainColor = 'Purple'
)
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply