February 3, 2016 at 10:50 am
Hello, I have 2 tables: Child_Table and Health_Table:
Child_Table has columns: App_ID, Major
Health_Table has columns used: Re_ID, Plan
Sample data:
Child Table
App_ID Major
004540036 GEBOX
004540036 GEBOX
004540036 GEBOX
004540036 GEBOX
Sample data:
Health Table WITH COLUMNS
Re_ID Major
U1 GECCE
U2 REDSG
U5 GFRTY
U7 GEBOX
U8 JKIUTY
Sample Output Needs to be in Temp Table
** MUST SKIP Re_ID for this record but may be used for the next App_ID if Major does not match in both tables
App_ID Re_ID
004540036 U1
004540036 U2
004540036 U5
004540036 U8
I need to insert the App_ID and Re_ID into a temp table WHERE the Major and Plan can NOT be the same. Also each App_ID will be in the table 4 times and needs to have a different RE_ID assigned. Any ideas?
February 3, 2016 at 11:02 am
lauriesmith1890 (2/3/2016)
Hello, I have 2 tables: Child_Table and Health_Table:Child_Table has columns used: App_ID, Major
Health_Table has columns used: Re_ID, Plan
I need to insert the App_ID and Re_ID into a temp table WHERE the Major and Plan can NOT be the same. Also each App_ID will be in the table 4 times and needs to have a different RE_ID assigned. Any ideas?
Hi and welcome to SSC. We need a bit more detail here to offer any coded solutions. Here is a good place to look for the kinds of information required. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
If you just need more of a push in the right direction we can do that to but I don't really understand the problem at this point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2016 at 11:25 am
Thank you for the information. I edited my post to reflect more information.
February 3, 2016 at 11:47 am
You certainly showed more information, but you didn't post it as it was requested. You're new here so I'm including the full example.
Be aware that if you don't have enough rows in your Health table, the results might not be the ones you expect.
I'm guessing that you have design problems, but there's not enough information to be sure about that.
--Sample Data
CREATE TABLE Child(
App_ID char(10),
Major char(6)
);
INSERT INTO Child VALUES
( '004540036', 'GEBOX'),
( '004540036', 'GEBOX'),
( '004540036', 'GEBOX'),
( '004540036', 'GEBOX');
CREATE TABLE Health(
Re_ID char(2),
Major char(6)
);
INSERT INTO Health VALUES
( 'U1', 'GECCE '),
( 'U2', 'REDSG '),
( 'U5', 'GFRTY '),
( 'U7', 'GEBOX '),
( 'U8', 'JKIUTY');
--Solution: uncomment the desired option
WITH cteChild AS(
SELECT DISTINCT
App_ID,
Major
FROM Child
)
-- Option A) Insert into an existing table
--INSERT INTO #TempTable
SELECT
c.App_ID,
h.Re_ID
--Option B) Create and Insert into a table
--INTO #TempTable
FROM cteChild c
CROSS APPLY (SELECT TOP 4
ih.Re_ID
FROM Health ih
WHERE c.Major <> ih.Major) h;
GO
--Clean up
DROP TABLE Child, Health;
February 3, 2016 at 12:50 pm
Thank you for your reply. I already know that the Health table will not have as many records as the Child table. The rev_id can be assigned to more than one App_ID (as long its not assigned to the one with the same Major). Do you recommend a loop?
February 3, 2016 at 1:00 pm
Raxter (2/3/2016)
Thank you for your reply. I already know that the Health table will not have as many records as the Child table. The rev_id can be assigned to more than one App_ID (as long its not assigned to the one with the same Major). Do you recommend a loop?
Please post a new set of sample code that includes the situation where the rev_id is assigned to more than one App_ID. Also please post it in the format shown by Luis. Add expected output and an explanation; with all those ingredients you can help us help you.
February 3, 2016 at 1:19 pm
Raxter (2/3/2016)
Do you recommend a loop?
No, definitively not a loop. However, I'm not sure what to recommend without more information and sample data.
February 3, 2016 at 1:52 pm
--Sample Data
CREATE TABLE Child(
App_ID char(10),
Major char(6)
);
INSERT INTO Child VALUES
( '004540036', 'GEBOX'),
( '004540036', 'GEBOX'),
( '004540036', 'GEBOX'),
( '004540036', 'GEBOX'),
( '124544037', 'JKIUTY'),
( '144565899', 'GECCE'),
( '124544037', 'JKIUTY'),
( '124544037', 'JKIUTY'),
( '124544037', 'JKIUTY'),
( '144565899', 'GECCE'),
( '144565899', 'GECCE'),
( '144565899', 'GECCE');
CREATE TABLE Health(
Re_ID char(2),
Major char(6)
);
INSERT INTO Health VALUES
( 'U1', 'GECCE '),
( 'U2', 'REDSG '),
( 'U5', 'GFRTY '),
( 'U7', 'GEBOX '),
( 'U8', 'JKIUTY');
[/CODE]
Sample output should be:
App_ID Re_ID
004540036 U1
004540036 U2
004540036 U5
004540036 U8
124544037 U1
124544037 U2
124544037 U5
124544037 U7
144565899 U2
144565899 U5
144565899 U7
144565899 U8
I have added more sample data to show the output. I need to retrieve every app_id in the Child table and assign a rev_id to it but the app_id and rev_id can not have the same major. Hope this is the type of format you are asking for. Thanks for all the input.
February 3, 2016 at 2:07 pm
The code that I posted previously is giving the expected results (after correcting your sample data script). What's your question/problem?
February 4, 2016 at 12:03 pm
Thank you. That did answer the question that I had posted.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply