Help Required in SQL



sql 2000 Question 

i have following ddl and sample data

CREATE TABLE [Model_data] (
	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
	[model_1_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_2_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_3_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_4_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_1_Value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
	[model_2_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_3_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_4_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


CREATE TABLE [tbl_status] (
	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
	[status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


CREATE TABLE [data_table] (
	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
	[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
	CONSTRAINT [PK_data_table] PRIMARY KEY  CLUSTERED 
	(
		[main_id],
		[group_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [work_table] (
	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[data_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


insert into data_table values('A001','1','AB900')
insert into data_table values('A001','2','AB100')
insert into data_table values('A001','3','AB2000')
insert into data_table values('A001','4','AB700')

insert into data_table values('A002','1','CF1000')
insert into data_table values('A002','3','CF4000')
insert into data_table values('A002','4','CF9000')

insert into data_table values('A003','3','DE4000')
insert into data_table values('A003','4','DE9000')

insert into data_table values('A004','1','GT4000')
insert into data_table values('A004','4','GT9000')

insert into Model_data
values('A001','Y','Y','N','Y','ZZ3000','ZZ100',null,'ZZ700')
insert into Model_data
values('A002','Y','Y','Y','Y','QT5000','QT4000','QT3000','QT9000')
insert into Model_data
values('A003','N','N','Y','Y',null,null,'MM4000','MM9000')

insert into tbl_status values ('A001','T')
insert into tbl_status values ('A002','T')
insert into tbl_status values ('A003','T') 


group_id  1,2,3,4 against any main_id in data_table forms complete set
of data 
i want to insert only those values in work_table by joining tbl_status ,
data_table and model_data where 
status is 'T' and in data_table group_id  = 1 and in model_data value of
model_1_Group is 'Y' then data from data_table and model_data sholud be
inserted in work_table like following
 
 
main_id  Group_id  data_Value  Model_Value
A001      1       AB900        ZZ3000 
A002      1       CF1000       QT5000 

if group_id 1 does not exist then group_id 2 should be checked in
data_table with model_2_Group = 'Y' and data should be inserted in
work_table
if group_id 2 does not exist then group_id 3 should be checked in
data_table with model_3_Group = 'Y' and data should be inserted in
work_table
if group_id 4 does not exist then group_id 4 should be checked in
data_table with model_4_Group = 'Y' and data should be inserted in
work_table

for example complete data  data inserted in work table should be like
according to above sample data 

main_id  Group_id  data_Value  Model_Value
A001      1       AB900        ZZ3000 
A002      1       CF1000       QT5000 
A003      3       DE4000       MM4000


Regards
  


*** Sent via Developersdex http://www.developersdex.com ***
0
Jami
9/10/2010 6:34:15 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
1170 Views

Similar Articles

[PageSpeed] 39

On Thu, 09 Sep 2010 23:34:15 -0700, Jami <jami.khan@yahoo.com> wrote:

>
>
>
>sql 2000 Question 
>
>i have following ddl and sample data
>
>CREATE TABLE [Model_data] (
>	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>,
>	[model_1_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_2_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_3_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_4_Group] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_1_Value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
>	[model_2_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_3_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_4_value] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
>) ON [PRIMARY]
>GO
>
>
>CREATE TABLE [tbl_status] (
>	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>,
>	[status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
>) ON [PRIMARY]
>GO
>
>
>CREATE TABLE [data_table] (
>	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>,
>	[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>	[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>	CONSTRAINT [PK_data_table] PRIMARY KEY  CLUSTERED 
>	(
>		[main_id],
>		[group_id]
>	)  ON [PRIMARY] 
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [work_table] (
>	[main_id] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[group_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[data_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>	[model_value] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
>) ON [PRIMARY]
>GO
>
>
>insert into data_table values('A001','1','AB900')
>insert into data_table values('A001','2','AB100')
>insert into data_table values('A001','3','AB2000')
>insert into data_table values('A001','4','AB700')
>
>insert into data_table values('A002','1','CF1000')
>insert into data_table values('A002','3','CF4000')
>insert into data_table values('A002','4','CF9000')
>
>insert into data_table values('A003','3','DE4000')
>insert into data_table values('A003','4','DE9000')
>
>insert into data_table values('A004','1','GT4000')
>insert into data_table values('A004','4','GT9000')
>
>insert into Model_data
>values('A001','Y','Y','N','Y','ZZ3000','ZZ100',null,'ZZ700')
>insert into Model_data
>values('A002','Y','Y','Y','Y','QT5000','QT4000','QT3000','QT9000')
>insert into Model_data
>values('A003','N','N','Y','Y',null,null,'MM4000','MM9000')
>
>insert into tbl_status values ('A001','T')
>insert into tbl_status values ('A002','T')
>insert into tbl_status values ('A003','T') 
>
>
>group_id  1,2,3,4 against any main_id in data_table forms complete set
>of data 
>i want to insert only those values in work_table by joining tbl_status ,
>data_table and model_data where 
>status is 'T' and in data_table group_id  = 1 and in model_data value of
>model_1_Group is 'Y' then data from data_table and model_data sholud be
>inserted in work_table like following
> 
> 
>main_id  Group_id  data_Value  Model_Value
>A001      1       AB900        ZZ3000 
>A002      1       CF1000       QT5000 
>
>if group_id 1 does not exist then group_id 2 should be checked in
>data_table with model_2_Group = 'Y' and data should be inserted in
>work_table
>if group_id 2 does not exist then group_id 3 should be checked in
>data_table with model_3_Group = 'Y' and data should be inserted in
>work_table
>if group_id 4 does not exist then group_id 4 should be checked in
>data_table with model_4_Group = 'Y' and data should be inserted in
>work_table
>
>for example complete data  data inserted in work table should be like
>according to above sample data 
>
>main_id  Group_id  data_Value  Model_Value
>A001      1       AB900        ZZ3000 
>A002      1       CF1000       QT5000 
>A003      3       DE4000       MM4000
>
>
>Regards
>  
>
>
>*** Sent via Developersdex http://www.developersdex.com ***


Hi

Thanks for the DDL and example data it is refreshing to get such a
comprehensive post. This will do what you have asked:


SELECT d.main_id, d.group_id, d.model_value as [data_value],
m.model_1_value as [Model_Value]
FROM data_table d
JOIN model_data m ON m.main_id = d.main_id
JOIN tbl_status t ON t.main_id = d.main_id and t.status = 'T'
WHERE d.group_id  = 1
UNION ALL
SELECT d.main_id, d.group_id, d.model_value, m.model_2_value
FROM data_table d
JOIN model_data m ON m.main_id = d.main_id
JOIN tbl_status t ON t.main_id = d.main_id and t.status = 'T'
LEFT JOIN data_table l ON l.main_id = d.main_id AND l.group_id  = 1
WHERE d.group_id  = 2
AND l.main_id IS NULL
UNION ALL
SELECT d.main_id, d.group_id, d.model_value, m.model_3_value
FROM data_table d
JOIN model_data m ON m.main_id = d.main_id
JOIN tbl_status t ON t.main_id = d.main_id and t.status = 'T'
LEFT JOIN data_table l ON l.main_id = d.main_id AND l.group_id  = 1
LEFT JOIN data_table l2 ON l2.main_id = d.main_id AND l2.group_id  = 2
WHERE d.group_id  = 3
AND l.main_id IS NULL
AND l2.main_id IS NULL
UNION ALL
SELECT d.main_id, d.group_id, d.model_value, m.model_4_value
FROM data_table d
JOIN model_data m ON m.main_id = d.main_id
JOIN tbl_status t ON t.main_id = d.main_id and t.status = 'T'
LEFT JOIN data_table l ON l.main_id = d.main_id AND l.group_id  = 1
LEFT JOIN data_table l2 ON l2.main_id = d.main_id AND l2.group_id  = 2
LEFT JOIN data_table l3 ON l3.main_id = d.main_id AND l3.group_id  = 3
WHERE d.group_id  = 4
AND l.main_id IS NULL
AND l2.main_id IS NULL
AND l3.main_id IS NULL


John
0
John
9/10/2010 7:50:00 AM
Reply:

Similar Artilces:

Help with Task Pane in Pub 2003
Okay, how do I turn this darned Task Pane thing off by default? I tried looking in Tools|Options|General. I can turn off the "New Publication" task pane but that's not doing the trick. I don't want that pane - if I change my mind, I know where to find it. -- JoAnn Hi JL Amerson (JL_Amerson@hotmail.com), in the Microsoft� newsgroups you posted: || Okay, how do I turn this darned Task Pane thing off by default? I || tried looking in Tools|Options|General. I can turn off the "New || Publication" task pane but that's not doing the trick. I don't want || th...

Need Help With Outlook 2000 #2
Is there a way to change the order the contacts are placed in the TO field of an email? Heres my scenario....When creating an email to multiple recipients in my address book I open a new message and click the TO button.I click 3 or 4 names and have them go to the message reciepient box in a certain order then click ok. When they show up in the TO field of the email message they are exactly backwards of the way I chose them. Is there a setting Im missing? Just upgraded from Outlook 97 and didnt have this problem. Thanks ...

If funtion help
Hello, I am in a racing league and have a page in my spreadsheet which wil distribute points to a driver depending upon where he/she has finishe in the race. As an example, the driver who finishes in the 1st postion receives 2 points, 2nd position receives 17 points, etc. The list contains a tota of twelve drivers. My intention was to enter the finishing position o each driver in a particular cell, and in another cell the formula wil calculate the appropriate points. I have attempted to use the "If function, as in IF(L22="1","21", If(, etc: However, a well know l...

I need some help with userforms
Private Sub CommandButton1_Click() If CheckBox1 = True Then Unload UserForm1 Sheet2.Activate UserForm2.Show Else End If End Sub "userform1, in sheet1, contains the above code. So when i select the checkboox "true" and click on the command but...then it unloads userform1, activates, sheet2 and loads userform2....everything is working perfectly...this far....then userform2 contains the following code: Sub UserForm_Initialize() LabelZoom.Caption = ActiveWindow.Zoom With ScrollBarZoom 'zoom ..Min = 10 ..Max = 400 ..SmallChange = 1 ..LargeChange = 10 ..Value = ActiveWindow.Zoo...

Need a help icon and stuff.
I do not have a lot of experience with Excel. I have taken a class but never had to use anything until work. But I need to add a question mark (help) icon to a excel document. Then it needs to go to either a word document or a excel document. I have been looking for the past couple of days in help. But I can not find what I am looking for. Any help would be greatly appreciated. HAve you thought about adding a hyperlink to the document that would link to yoru help document "Autumn" wrote: > I do not have a lot of experience with Excel. I have taken a class but never &...

Help with computing date\Time between previous record
I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K Use in your query: Datediff("d",[firstdatefield],[seconddatefield]) The "D" is for days. -- Milton Purdy ACCESS State of Arkansas "Dave" wrote: > I am trying to calculate how lo...

sql prob
SQL Server memory usage problem ------------------------------- I have been working with a Server application (in VC++ using MFC) which interacts with MS SQL(7.0) database. I have some procedures in my database which needs to be executed from my server application. I have put a sample code below, that shows how I call my stored procedures from inside VC++. //CCheckLoginEX is a class which implements connection to database (USING ODBC) and execution of respective procedure. CCheckLoginEx mCheckLogin; mCheckLogin.m_mloinParam=buff->mLo; mCheckLogin.m_mpwdParam=buff->mPa; mCheckL...

Open File Dialog Help
I am trying to set the m_ofn.lpstrInitialDir to "My Music" on an open file dialog, but can't seem to get it to work right. I would like it to default to the c:\documents and settings\user\My Documents\My Music, regardless of the machine the application is running on. The project is being built using Visual C++ 6.0 Professional, on a Windows 2K machine, but may be running in any Windows environment. Thanks, SHGetSpecialFolderPath(CSIDL_PERSONAL) gets you to My Documents ...

help with formula #2
Here is the answer of my question sent to Google on 2003- 07-15 00:41:33 PST : """ Tim wrote in message: > > I've got a workbook with many worksheets on it(about 60). On 2 columns > on a sheet in this workbook i have data and i need to export this data > to particular cells in other worksheets. For example on columns A1:A20 > and B1:B20 on a worksheet is my data and I want to export the contents > of cells A1B1 to cells A1B1 in worksheet1 then data from A2B2 to cells > A2B2 in worksheet2 and so on. > What is the easiest way to do this job? > ...

Outlook Help Needed!!! Need to Recover Contacts and Email.
Hi All, I've had some problems with flaky hardware (either RAM, CPU or MB). Well the other night the system froze on me and when I was finally able to reboot Outlook wouldn't start. Since many programs were affected (the registry was corrupt and a recover didn't work), I got a new hard disk and started from scratch to rebuild my system. Also changed CPU. It is an XP Pro machine with Outlook 2003 (just as the previous configuration). Now I want to import my contacts, emails, etc. into the newly created sytems. I can't locate any .PST files on the old drive. I've unhi...

Please help! Move and size with cell is not avaliable
We're trying to get the option, but no matter what we do when we make new checkbox, the option is never avaliable. Is there any possibly wa that we can get that? I really don't know what to do, please, someone help me out! Heathe -- FloridaHeathe ----------------------------------------------------------------------- FloridaHeather's Profile: http://www.officehelp.in/member.php?userid=502 View this thread: http://www.officehelp.in/showthread.php?t=125638 Posted from - http://www.officehelp.i If you use a checkbox from the Control toolbox toolbar, you'll see that option....

SQL 2005 Hierarchial Table Structure
I have a requirement to store hierarchial data stored over 3 levels. Every level contains the same type of data. Is there a feature in sql server that takes advantage of this? or if somebody know of a site that has neat design trick, I would appreciate if it could be shared. Thanks in advance Take a look at the following article and the examples for BOM structure and organizational chart. Recursive CTEs can help you to query the hierarchical data (examples in the article). http://msdn.microsoft.com/en-us/library/ms345144(SQL.90).aspx -- Plamen Ratchev http://www.SQLS...

Need help re settting default font for Create Mail
Can anyone help me with this? When I go to Options>Compose>Font Settings (Outlook 6), I change it to Courier New, size 12. But when I hit Compose button to the Create Mail button, to compose a new e-mail, it gives me Arial Font size 10. Now, I tried creating a Stationery template that would give me Courier New size 12. I opened an e-mail, set it to Courier New size 12, and then used Save As and saved it as a Stationery under the name Courier. But when I use the drop down menu under Create Mail and choose that template, and go to write the e-mail, it does not give me the Courier that ...

Visio 2002 requires re-activation after SP2
I'm just trying to patch our administrative installation of Visio 2002 with the Service Pack 2 (German Version). Everything seems to work fine - but after the clients get the new version (we distribute it using group policies), on the client the Visio Installation demands a re-activation of the product because of a major configuration change. Has anyone seen this before? And if so - has anyone a solution to this problem? Thanks in advance Holger Schaal I just forgot to mention: the Visio 2002 I use is a Microsoft Select Version - so it should need no activation at all ... Holge...

Get help in Office newsgroup....again
(Note the Office newsgroup messages from the Mactopia forum interface have not been showing up for users that use a newsreader. This problem happened around Sept 24. Your questions are not being ignored. We just can't see them.) Ask your questions using Entourage's newsreader. <http://www.entourage.mvps.org/support_options/subnews.html>) -- Diane Ross, Microsoft Mac MVP Entourage Help Page <http://www.entourage.mvps.org/> Entourage Help Blog <http://blog.entourage.mvps.org/> Hi Diane - I appreciate what you're hoping to do & applaud the effort but ...

Help settle a dispute..
We're running a pure Ex2k3 environment. Currently users can access their email outside the network via OWA only. I've recently begun testing RPC over HTTP. I'm inclined to make that the standard for our users since they can use Outlook and access their PST's at the same time. The questions is this: Does RPC/HTTP use more bandwidth that OWA? I say no, but that opinion is not unanimous. The "fat" Outlook client uses more local workstation resources than OWA, but the network traffic it generates between the client and server should be the same, yes? (This is...

HELP! 16gb limit.
HELP Hit the 16gb limit on my priv store. Also ran out of room on the drive that has the mdbdata logs. What i have noticed so far: 1) hit 16gb limit 2) Ran out of room on c:\...mdbdata dirve 3) had norton AV scaning m: and excha folders 4) Logs were being created every minute (5120kb) since way back when never noticed before. - Excluded m: and the exchange folders from the norton av log are no longer being created every minute - still have a over 4000 logs in the mdbdata log directory (file name : E000160,E000161,E000162 ....) 5) log files are on c...

windows help message board?
Hi, I'm looking for some help with something in Windows 2000 (I want to see if there is a way to protect files from being dragged). I've found some message boards, but they don't seem to see much action, and I was hoping to figure this out today. I'm wondering if anyone here knows of a message board I may want to check out for help with Windows? Thanks! -- blazon ------------------------------------------------------------------------ blazon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27409 View this thread: http://www.excelforum.com/sho...

Access Application Help Documentation
Is there a way to that I can write Application help documentation to help user manuever my access system Thanks There are many applications available via the internet that will create a ..chm help file for your ACCESS database file. Do a search for them via Google. -- Ken Snell <MS ACCESS MVP> "lalexander" <lalexander@bsiinsurance.com> wrote in message news:e9eMYmANGHA.420@tk2msftngp13.phx.gbl... > Is there a way to that I can write Application help documentation to help > user manuever my access system > > Thanks > > > ...

i need help
what is an incoming and outgoing server how do i find it? You ask your ISP/mail server, or see if the information is on their Website. Windows Mail: Setting up an account from start to finish http://windows.microsoft.com/en-US/windows-vista/Windows-Mail-setting-up-an-account-from-start-to-finish -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "miabearr" <miabearr@discussions.microsoft.com> wrote in message news:26DF94D8-9DFC-4E23-B2EF-6B94561D5790@microsoft.com... > what is an incoming and outgoing server how do i find it? ...

Help! Pivot table need to make Pie Chart
Hi, Can anybody help me, i need to make a pie chart after i made the pivot table. Here what i have for data: Year, Month, Company's name, product and Revenue. I need to make a pie chart of What % of total sales did each product group contribute on the same sheet. Francis In the pivot table, put Product in the row area, and revenue in the data area. Remove the other fields from the pivot table Select a cell outside of the pivot table, and click the Chart Wizard button Select the Pie chart type, and one of the subtypes Click Next For the data range, ...

Auto Calculate Question / HELP!
When I click on the fill handle to drag the data down the cells fill with data grabbed veritcally, for example: When I drag the fill handle down the formulas look like: =D21 =D22 =D23 =D24 =D25 But is there a way for Excel to be adjusted so that they attach the information laterally instead of veritcally? For example, I want to drag the auto fill DOWN but want the cells to read: =D21 =E21 =F21 =G21 =H21 Thank you! Try this... Assume you enter the first formula in cell A1 =INDEX(D$21:H$21,ROWS(A$1:A1)) Drag down as needed. Adjust for the correct end of range ...

Insert copied area help
Hi all, Looking for a way to insert an area copied from one worksheet into another worksheet - using a macro. We use Excel for payroll, and at the beginning of every pay period, I need to copy a blank payroll sheet and insert it into each of 12 employee worksheets. I can record a macro that will begin at an employees sheet, insert blank lines, go to the "MASTER" sheet, copy the blank payroll sheet, return to the "active" employees sheet and paste in the new blank payroll area. However, when I do that (as you probably have already guessed) all it EVER does is past a blan...

Excel help needed.
Greetings! I am learning Excel myself. Right now I'm in a problem doing something I have a workbook with three columns. Column 1 contains Email addresses Column 2 contains First Name and Column 3 contains Last Name. My second workbook contains only email addresses (a few) from firs work book. How I can get other fields easily from first workbook? mean first name and last name? Can anyone help me regarding this? Any help will be appreciated. Regards -- razibhasan Any formula that you use will need to contain the path to the other WB within that formula. Since you're learning XL...

Sincronize Windows Live Mail and Hotmail Help
Hello. I download the inbox of hotmail with Windows Live Mail and now i cant read the messages in the inbox of hotmail. Only in WLMail. Is it possible to sincronize them to inbox in hotmail again??? Help please, it is a big problem for me. Look at Tools (ALT+T), Accounts, Mail, Hotmail Properties, Servers. Is the account set up as POP3 or HTTP? I'm guessing that it's POP3 and on the Advanced tab it is not checked to Leave a copy on the server. That will happen if you imported the account from Outlook Express. If you were to just check the Leave a copy on the serve...