DTP


 
Lively discussions on the graphic arts and publishing — in print or on the web


Go Back   Desktop Publishing Forum > General Discussions > Web Site Building & Maintenance

Reply
 
Thread Tools Display Modes
Old 03-07-2007, 07:17 PM   #1
CarlSeiler
Member
 
CarlSeiler's Avatar
 
Join Date: Oct 2005
Location: Denton, TX
Posts: 271
Default SQL Help Needed

Need to get my head wrapped around some common SQL techniques. We've got data in three different linked tables.

customers
cust_id cust_name
1 Bill
2 Mary
3 John

custaction
custact_id cust_id
11 1
12 1
13 3
14 2

warrants
warrant_id custact_id
101 12
102 13

Now, I want to insert a warrant 103 for Mary (customer 2). I can easily do this using:

Code:
INSERT INTO warrants (warrant_id, custact_id) VALUES (103, 14);
but this works because I can see that one of the custact_id that's associated with Mary is 14, so I can feed it a correct custact_id, but what if I don't know the custact_id? Since I know Mary is number 2 (from the first table) is there some way to construct the query so that it's something like this pseudo code:

Code:
INSERT INTO warrants (warrant_id, custact_id) 
VALUES (103, [SELECT custact_id from custactions WHERE cust_id = 2]);
In other words, I want the query to figure out on its own that it needs to put 14 the second value of the INSERT by looking it up in the custactions table. Does this make sense at all? I think I have the right idea, but not the right syntax. Am I looking for something like:

Code:
INSERT INTO warrants (warrant_id, custact_id)
SELECT 103, custact_id FROM custactions WHERE cust_id = 2
Carl

(Sorry, I accidentally hit "save" save earlier before I was finished.)

Last edited by CarlSeiler; 03-07-2007 at 08:01 PM. Reason: Couldn't figure out how to delete it.
CarlSeiler is offline   Reply With Quote
Old 03-08-2007, 07:13 AM   #2
dthomsen8
Member
 
dthomsen8's Avatar
 
Join Date: Aug 2005
Location: Philadelphia, PA 19130
Posts: 2,158
Default What is happening?

Let me ask just what is happening here.

1. You want to create a warrant for a customer, Mary. You can look with your own eyes and see that Mary is cust_id 2 in the customers table.
2. With this information, you can look in the custaction table, and see that cust_id is custact_id 2.
3. Now you want to insert a new warrant into the warrants table. You say that would be warrant 103. Does this mean that the warrants are numbered sequentially, and whatever the last number is, the next one is that number plus one? I am supposing that is the case.

Procedure: do a SELECT on the customers table with the key Mary, and get back a variable with the cust_id in it (Step 1 above). Do another select on the custaction table, and get back a variable with the custact_id. Then insert the custact_id with the next warrant_id in the warrants table. You never want code with specific data in it, only code that refers to fields in tables or variables.

This may be too procedural to get the code written, but I hope it clarifies what is actually wanted . I do wonder what happens when Bill is the customer and there are two cust_id numbers for him in the custaction table.

Last edited by dthomsen8; 03-08-2007 at 07:15 AM. Reason: Correction
dthomsen8 is offline   Reply With Quote
Old 03-09-2007, 04:10 AM   #3
CarlSeiler
Member
 
CarlSeiler's Avatar
 
Join Date: Oct 2005
Location: Denton, TX
Posts: 271
Default

(BTW, using MS SQL Server here, but may be moving to MySQL or PostGreSQL later)

OK, I was trying to do this all in one query, avoiding variables. But I could do something like this with variables.

Code:
 DECLARE @custactvar int;
SELECT @custactvar = custactions.custact_id WHERE cust_id = 2; 
/* I already know "2" from a previous query, but I could have a variable here */
INSERT INTO warrants (warrant_id, custact_id) VALUES
  (103,@custactvar);
/* Instead of 103, I use a variable or some function that determines next 
counter in the warrants table. I'm assuming there's a built-in function, but
 I don't know it yet. */
Am I more on track?

Anyway, with regard to duplicate cust_id's in the custactions table, yes indeed, I know that's a problem that needs to be considered, and I specifically put that kink in there since I noticed it. What I need to do is have some sort of error handling that will allow the user to either select which custaction record they want. Right now, I'm lucky in that Mary only has one entry in custactions. Down the road, I may not be so lucky.
CarlSeiler is offline   Reply With Quote
Old 03-09-2007, 05:33 AM   #4
dthomsen8
Member
 
dthomsen8's Avatar
 
Join Date: Aug 2005
Location: Philadelphia, PA 19130
Posts: 2,158
Default MySQL, No Luck

Quote:
Originally Posted by CarlSeiler View Post
(BTW, using MS SQL Server here, but may be moving to MySQL or PostGreSQL later)

Anyway, with regard to duplicate cust_id's in the custactions table, yes indeed, I know that's a problem that needs to be considered, and I specifically put that kink in there since I noticed it. What I need to do is have some sort of error handling that will allow the user to either select which custaction record they want. Right now, I'm lucky in that Mary only has one entry in custactions. Down the road, I may not be so lucky.
I commend to you MySQL and PHP as the way to go. I am back on the learning curve on those two things, using XAMPP locally to learn.

As for luck, Murphy's Law says things will fail, and at the worst possible time. When it comes to programming, Murphy was an optimist.
dthomsen8 is offline   Reply With Quote
Old 03-14-2007, 04:42 AM   #5
CarlSeiler
Member
 
CarlSeiler's Avatar
 
Join Date: Oct 2005
Location: Denton, TX
Posts: 271
Default

Quote:
Originally Posted by dthomsen8 View Post
I commend to you MySQL and PHP as the way to go. I am back on the learning curve on those two things, using XAMPP locally to learn.
If this were only me, and I was starting out from scratch, I would definitely do that. I'm already shifting my old static ways to dynamic web content with PHP and various flavors of SQL. However, this particular project is for my boss, and they've got MS SQL Server and Visual Studio already installed. The network application is done in Powerbuilder and MS Access but sales web application is done with Visual Studio and .NET.

I've been told that I can use whatever technology that I choose, and I have a limited budget for training if I need to get it. However, I'm concerned that if I go too far away from the current architecture that I'll be starting so far back from scratch my training budget won't be able to cover what I need.

In the short term, however, I'm needing to write basic queries in Query Analyzer that correct some data issues.

I'm not convinced I'm the person for the project, though. I'm not a computer scientist, and I've only had one course in Pascal in 1990--no experience whatsoever in GUI development. I know, though, that there are plenty of other people who've found themselves in a similar position and been able to chart a new career path as a result.
CarlSeiler is offline   Reply With Quote
Old 03-14-2007, 05:40 AM   #6
dthomsen8
Member
 
dthomsen8's Avatar
 
Join Date: Aug 2005
Location: Philadelphia, PA 19130
Posts: 2,158
Default Person for the project?

Quote:
Originally Posted by CarlSeiler View Post
In the short term, however, I'm needing to write basic queries in Query Analyzer that correct some data issues.

I'm not convinced I'm the person for the project, though. I'm not a computer scientist, and I've only had one course in Pascal in 1990--no experience whatsoever in GUI development. I know, though, that there are plenty of other people who've found themselves in a similar position and been able to chart a new career path as a result.
If you want to chart a new career path, go for it.

If you want the business to have the project done well and promptly, you should consider hiring a consultant or new staff person who has experience with the operating system and software tools and can do the work without a big learning curve to overcome. This choice may not be cheap, but what is the project worth to the company if it is done well and fairly quickly?
dthomsen8 is offline   Reply With Quote
Old 03-14-2007, 07:41 PM   #7
CarlSeiler
Member
 
CarlSeiler's Avatar
 
Join Date: Oct 2005
Location: Denton, TX
Posts: 271
Default

Quote:
Originally Posted by dthomsen8 View Post
If you want to chart a new career path, go for it.
If you want the business to have the project done well and promptly, you should consider hiring a consultant or new staff person who has experience with the operating system and software tools and can do the work without a big learning curve to overcome. This choice may not be cheap, but what is the project worth to the company if it is done well and fairly quickly?
One of the issues is that we've had plenty of consultants come and go who have spent loads of time and cost lots of money just have us only marginally improve the application. Hiring a staff member is definitely a possibility, but I'm not sure how that fits in with budgeting, either.
CarlSeiler is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Needed: someone to manage a Simple Machines Forum mksf Web Site Building & Maintenance 13 04-05-2007 07:02 AM
Quark Xpress Tutor Needed Ash Mansukhani Print Design 2 10-24-2006 12:47 PM
Redesign Suggestions Needed BobRoosth Web Design 11 01-09-2006 09:57 AM
Word vs Acrobat guru needed!! ktinkel Print Production & Automation 7 05-13-2005 09:51 AM
RGB-CMYK Tutorial Needed BobRoosth Print Production & Automation 15 02-28-2005 07:44 PM


All times are GMT -8. The time now is 06:06 PM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Contents copyright 2004–2014 Desktop Publishing Forum and its members.