August 22, 2016 at 12:58 pm
Hello,
I have a table, we can name it table B that has two fields, parent_id and child_id. I want to insert into a third table, table C if both fields from table B are in table A. Table A is a products table. I need to verify that the parent_id and child_id, which both are foreign keys of table A's primary key, product_id.
I have sql that looks to see if parent_id exists in table A and if child_id exists in table A (both are referenced to product_id). If so I need to insert into table C. Currently, I am getting too many results and then not enough results. My reason for doing this is because I can;t just insert due to a foreign constraint (if child_id is not in table A)
August 22, 2016 at 1:14 pm
You should post what you've already tried. It's going to be easier for you to understand if it's just a minor change in your existing code rather than having to understand something completely new.
You should also post table definitions and sample data as outlined in the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2016 at 1:16 pm
Drew is correct. But here is an outline of what I think you are trying to do:
insert tableC (c1, c2, c3)
select c1, c2, c3 from tableB b
where exists (select 1 from tableA a where a.product_id = b.parent_id) and
exists (select 1 from tableA a where a.product_id = b.child_id)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 22, 2016 at 2:46 pm
Thanks for the responses. Here is my code currently:
INSERT INTO item_relation_table
(parent__id,
child__id,
relation)
SELECT xm.1_id, xm.2_ID,
'A',
from tablexm xm
where xm.1_id != xm.2_ID and
exists(select 1 from item ip WHERE ip.mfg = xm.1_id and active = 'Y') AND
exists(select 1 from item ip where ip.mfg = xm.2_ID and active = 'Y')
order by xm.1_id
Tables
1.tablexm(1_id, 2_id)
2.item_relation_table(parent_id,child_id,relation)
3.item (product_id,item_name,item_desc,active)
August 22, 2016 at 2:57 pm
Francis Twomey (8/22/2016)
Thanks for the responses. Here is my code currently:
INSERT INTO item_relation_table
(parent__id,
child__id,
relation)
SELECT xm.1_id, xm.2_ID,
'A',
from tablexm xm
where xm.1_id != xm.2_ID and
exists(select 1 from item ip WHERE ip.mfg = xm.1_id and active = 'Y') AND
exists(select 1 from item ip where ip.mfg = xm.2_ID and active = 'Y')
order by xm.1_id
Tables
1.tablexm(1_id, 2_id)
2.item_relation_table(parent_id,child_id,relation)
3.item (product_id,item_name,item_desc,active)
You don't need that ORDER BY. It's probably slowing you down and does not change the results.
-- Itzik Ben-Gan 2001
August 22, 2016 at 3:45 pm
Francis Twomey (8/22/2016)
Thanks for the responses. Here is my code currently:
INSERT INTO item_relation_table
(parent__id,
child__id,
relation)
SELECT xm.1_id, xm.2_ID,
'A',
from tablexm xm
where xm.1_id != xm.2_ID and
exists(select 1 from item ip WHERE ip.mfg = xm.1_id and active = 'Y') AND
exists(select 1 from item ip where ip.mfg = xm.2_ID and active = 'Y')
order by xm.1_id
Tables
1.tablexm(1_id, 2_id)
2.item_relation_table(parent_id,child_id,relation)
3.item (product_id,item_name,item_desc,active)
You didn't supply data types for your table definitions. Your where clause references item.mfg, but there is no mfg column in your item table. You also didn't supply sample data. Can you also list the primary key and any unique constraints on your item table?
Shouldn't you be using product_id instead of mfg? I assume that mfg will be the manufacturer id and that several products may have the same manufacturer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2016 at 4:14 pm
Sorry Drew.
Tables
1.tablexm
(1_id(nvarchar(50),
2_id(nvarchar(50))
2.item_relation_table
(parent_id (nvarchar(50),
child_id(nvarchar(50),
relation(char(1)))
3.item
(product_id(nvarchar(50),
item_name(nvarchar(750)),
item_desc(text),
mfg(nvarchar(50)),
active(char(1)))
Sample Data
tablexm
BAT 12355, BER 222222
BAT 12355, CVF 234455
ART 11111, VFF HHHHH
item_relation
BAT 12355, BER 222222, A
BAT 12355, CVF 234455, A
item
BAT 12355, Baseball bat, Normal bat, BAT 12355, Y
BAT 12355, Basbeall bat 12, Kids bat, BAT 12355, Y
VFF HHHHI, Tshirt, Mens football shirt, VFF HHHHI, Y
I am trying to insert 1_id and 2_id from tablexm into item_relation table. The product_id of item table can be a parent as well as an accessory item. I need to check if both 1_id and 2_id exist before I can insert them. If they do not exist yet, I will get an foreign constraint error. parent_id and child_id in the item_relation are both PK and FK to product_id in the item table
I cannot change the structure of any of the tables.
August 22, 2016 at 7:58 pm
Like this? I had to change the data to make some combination work with what you are describing. Your sample gave NO combination which would work. Also, in the future, please set up the problem like I have with create table and insert statements, and expected results from the input data. That way the people who are trying to help you don't have spend their time doing it. It's not only polite, it will result in more people working on your problem quicker. Nobody here is being paid for this, so help us help you 🙂
if OBJECT_ID(N'tempdb..#tablexm') is not null D ROP TABLE #tablexm
if OBJECT_ID(N'tempdb..#item_relation_table') is not null D ROP TABLE #item_relation_table
if OBJECT_ID(N'tempdb..#item') is not null D ROP TABLE #item
Create table #tablexm
([1_id] nvarchar(50),
[2_id] nvarchar(50))
GO
Create table #item_relation_table
(parent_id nvarchar(50),
child_id nvarchar(50),
relation char(1))
GO
Create table #item
(product_id nvarchar(50),
item_name nvarchar(750),
item_desc varchar(max),
mfg nvarchar(50),
active char(1))
GO
insert into #tablexm
values('BAT 12355', 'BER 222222'),
('BAT 12355', 'CVF 234455'),
('ART 11111', 'VFF HHHHH')
--insert into #item_relation_table
--VALUES ('BAT 12355', 'BER 222222', 'A'),
--('BAT 12355', 'CVF 234455', 'A')
insert into #item VALUES
('BAT 12355', 'Baseball bat', 'Normal bat', 'BAT 12355', 'Y'),
('ART 11111', 'Basbeall bat 12', 'Kids bat', 'BAT 12355', 'Y'),
('VFF HHHHH', 'Tshirt', 'Mens football shirt', 'VFF HHHHI', 'Y')
select * from #tablexm
select * from #item_relation_table
select * from #item
insert into #item_relation_table(parent_id,child_id,relation)
SELECT [1_id],[2_id],'A'
from #tablexm
where exists (select 1 from #item where [1_id] = product_id)
and exists (select 2 from #item where [2_id] = product_id)
select * from #item_relation_table
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 22, 2016 at 8:05 pm
Please help us help you with readily consumable code. You're almost there. Please see the first link in my signature line below under "Helpful Links". Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2016 at 6:12 am
Yes. Thank you. Will do in the future.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply