During the set-up of my DB's and their tables, I was unable to setup a relationship between a table in one Database and another table in my other Database ( using the diagram ). Maybe my datastructure of multiple databases is not correct, or is their an option to set relations between multiple databases ?
PS in the future I planned to have some other databases on different servers.
I am trying to figure out the best way to build a table that will hold information about our inventory of parts. We have several issues that I have been struggeling with trying to make this work. First capturing the basic part information is a no brainer, where I am having some questions is with how to assign categories, subcategories for each of these parts. The challenge comes because each part can belong to multiple categories and subcategories. For example we have 5 main items that would have categories, subcategories, parts. For example:
Item: Trailer
Model: model1, model2, model3
Category: Chassis
Subcategory: Upper frame, lower frame, electrical
Part: could be any thing that falls under one of the subcategories
Item: Trailer
Model: model1, model2, model3
Category: Gen set
Subcategory: engine, power, electrical
Part: could be any thing that falls under one of the subcategories
repeat...
My main items are trailer, shelter, power, heat, lights. Under the trailer and shelter items parts can belong all models or just a couple or just one model. My thought was to create a table for each item, ie: TrailerModels, ShelterModels which would have 100 plus columns, 1 column for each model, using a bit datatype to show which parts could belong to which model. This however seems to be quite a bit of work, because in order to use the models to thin down lists when searching for an item it would have query this table as well as the category and subcategory tables.
Anyone have some feedback about how I should approach this? I don't know if I was able to communicate the specifics of this very well but I can clear up details if anyone wants to throw some feedback my way.
Thanks!
yAy
ASP.NET on SQL ServerI've been asked to quote for developing a system to expose data on aweb application. Most of the data will come from SQL Server DBslocated on a single box. However, some of the data will be sourcedfrom ORACLE which is located on a different box. It may be necessaryto create VIEWS and Stored Procedures joining these DBsDoes anyone have any pointers, clues, hints, tips or pitfalls that Imight consider while making my proposal? What sort of extracontingency should I allow for the connection to ORACLE? Should I doall the data retrieval on the DB server, or should I do it on the Webserver?Any thoughts would be appreciated.ThanksWilliam Balmer
View 2 Replies View RelatedHi! Hello. I have now started to build my own community. And I have some questions on the database.For the users to login I use the login control and all the users information is stored in the ASPNETDB.MDF database.In the web.config file I have created some profiles for saving some information about the users (Name, Birth, Town) and so on.Now. All the users in this community will have their own profile page, Guestbooks ++.So I was wondering if I should create tables for all features like guestbook, profile pages or should I do this by using Profile (ASP.NET).How many users does ASPNETDB support?.
View 1 Replies View RelatedIs there any sql method that takes 3 parameter like, day, month and year . And return me the date.
For example
function(10,3,2007) and it returns 10-03-2007
Thanks,
userfeatureuserName featureIda 1a 5b 1b 5b 9c 5 c 9
menuid Pid Name1 Administrator2 1 Create User3 1 Delete USer4 1 View log5 WSR6 5 X7 5 X8 5 X9 Manager10 9 Y11 9 Y
Using the above table i want to create a treeview ie based on the user login. Please let me know if there is any previous sample to this situation.
Hi
I've never done this before and I have all kinds of issues conflicting in my head (search rank, noise words, injection attacks ..etc). simply i need to search several columns in a table in the database using one search text (just like the simple search in Google). if multiple words are you used then the search should search for each of them. also manage to ignore noise words and other issues.
what is the best way of doing this? I looked at FTS in SQL 2000 but didn't know how to handle all the above mentioned issues. this should be simple, right? but i have been looking all day. I guess i don't know what im looking for because i've never implemented a web search b4.
please help
p.s. I know t-sql and asp.net/c# well.
I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF
SET @where = ''
IF @JobNoStart !='' SET @where =+ ' AND LJOB BETWEEN @JobNoStart AND @JobNoEnd'
IF @OrderDateStart !='' SET @where =+ ' AND JOBDATE BETWEEN @OrderDateStart AND @OrderDateEnd'
IF @DueDateStart !='' SET @where =+ ' AND DUEDATE BETWEEN @DueDateStart AND @DueDateEnd'
IF @ProofDateStart !='' SET @where =+ ' AND PROOFDUE BETWEEN @ProofDateStart AND @ProofDateEnd'
IF @CloseDateStart !='' SET @where =+ ' AND CLOSEDATE BETWEEN @CloseDateStart AND @CloseDateEnd'
IF @CogsDateStart !='' SET @where =+ ' AND COGSDATE BETWEEN @CogsDateStart AND @CogsDateEnd'
IF @ProductName !='' SET @where =+ ' AND PRODUCT = @ProductName'
IF @CustomerNumber !='' SET @where =+ ' AND FCUSTNO = @CustomerNumber'
IF @SalesPerson !='' SET @where =+ ' AND FSALESPN = @SalesPerson'
IF @CSR !='' SET @where =+ ' AND JOBPER = @CSR'
IF @Closed = 0 SET @where =+ ' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'
ELSE IF @Closed = 1 SET @where =+ ' AND CLOSEDATE IS NULL'
ELSE IF @Closed = 2 SET @where =+ ' AND CLOSEDATE IS NOT NULL'
IF @Canceled = 0 SET @where =+ ' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'
ELSE IF @Canceled = 1 SET @where =+ ' AND CANCDATE IS NOT NULL'
ELSE IF @Canceled = 2 SET @where =+ ' AND CANCDATE IS NULL'
IF @FinalShip = 0 SET @where =+ ' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'
ELSE IF @FinalShip = 1 SET @where =+ ' AND FINALSHIP IS NOT NULL'
ELSE IF @FinalShip = 2 SET @where =+ ' AND FINALSHIP IS NULL'
SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN WHERE LJOB IS NOT NULL @where
END
Hi,
I am having some problems trying to build an sql statement from more than one statement.
Here is the statement
select 'Insert App_Column (Table_ID, Column_Type_Transformation, Column_Name, )
Values (@table_ID,' ,'NULL,', name from payatwork..syscolumns where id in (
select id from payatwork..sysobjects where name like 'Employee_Profile')
order by colorder, ')'
What I am finding is that the bracket at the end of the statement is not appearing - how do I append statements to the end of this sql statement (i've tried various combinations of the + sign and the comma without success.
thanks,
Jim
I'm in the midst of planning how to build an NT box to host SQL 7.0 and was wondering if there is any advantage to segregating the RAID 5 Array (5 x 18GB drives) into numerous *logical* partitions to separate database and log files (I can't see what advantage there would be if the disks are all on the same array, but..)
If anyone has any pointers or links to recommended NT configurations for hosting SQL, I'd appreciate hearing them.
TIA,
RM
Help Please - JMail - SQL SPs - Confirmation Email
I'm at the last stage of my current project and an totally stuck.
I'm trying to build the body of a order processing request email. For security reasons I wish to use a SP to build and send the email. (therefore no sensitive data gets passed to the client)
I have the JMail Object running properly on the server. It collects and sends the email in the normal course of the transaction from the client. Problem is that I cannot figure out how to properly build the body of the email in the SP.
I'm looking to do add the following to a single SQL SP variable to stuff in the Jmail Object to be sent. The content of the variable should look something linke the following:
-----------------------------------------
"Order placed:" = orders.date + '<cr>?'
customers.name<cr>
orders.shippingaddress<cr>
orders.billingaddress<cr>
/*loop each record where
/*customerID and orderID match passed arguments
/***loopstart***
OrderDetails.SKU <tab> Product.Productname <tab><tab> Orderdetails.Qty <tab> OrderDetails.Price<cr>
/***loopend***
Ordertotal.GrandTotal
--------------------------------------
Any help greatly appreciated.
THX,
Charles
I am working with SQL server 2000. The database is installed in my machine. Now I have got the ".ldf" and ".mdf" file pertaining to a database from someother server.
Is it possible to build the database present in the above said files in my server.
NOTE : I dont have a direct access to the remote server from where the above said files were obtained (otherwise I think the DTS utility would have come in handy).
Hello,
This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:
Two tables:
Table 1:
Orders
Some of the fields:
ID
PropID
WorkOrderNum
OrderDesc
DateCompleted
Table 2:
OrderDetail
ID
OrderID
TenantName
As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.
Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):
SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'
Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.
thanks.
I have a SQL 2005 database containing the location of graphics files. I want to start learning how to write a C# application that will get a path from the DB and display the file. Any recommendations on sites where I can start learning how to do this?
Thanks
Hi I've been asked to design a samll database in access with 5 tables and 3 querys also a form, and reports.
I'am not to sure on what to charge the person would any1 have a idea on what a small to medium database would be cost to build.
Thanks
Can you build reports using SQL Express w/o SQL 2005. If so how, the online books are not making this very clear.
View 1 Replies View RelatedI'm stuck and uinder a bit of a time crunch. I have 5 fields I want to get out of a sql database using a function that I'm writing. I figure it sounds like an array. basically I want to make an array, and fill it up with the results of a sql select, then read the array. This is what I have so far..... String TempHRAcctCode, TempJobDescription, TempHourlyRate, TempEmplID; Array TempArray; TempJobDescription = DDDept.SelectedItem.Text; (to get KeY Value) SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCONN"].ToString()); connection.Open(); SqlCommand command = new SqlCommand("Select HRAcctCode, HourlyRate , EmplID, ... FROM TimeMyProfile WHERE JobDescription = " + TempJobDescription + " ", connection); SqlDataReader TempDataReader = command.ExecuteReader; while TempDataReader.Read ( ... OK I GIVE UP! Thanks in advance
View 2 Replies View RelatedHello,
I'm having problems building an sql stament that joins a few tables. I can seem to get my head around the structure!
I have to try and link up four different tables to try and get my result.
Here are the 4 table structures...
Web_Users----------------User_IDName
Tags_Table-----------------Tags_IDUser_IDGroup_IDTitle
Created_Groups-----------------------Group_IDGroup_Name
Tags_To_Groups------------------------Group_Link_IDGroup_IDTag_ID
Basically, this database, has four tables; One table (Web_Users) that contains a users name, and assigns a unique ID (User_ID), another table that stores a users tags they have created, and also links it to a group_ID. The created_groups table, contains group names and assigns a unique id also. And the last table, Tags_To_Groups, links tags to groups.
So this is what I'm trying to do...
I'm trying to get the Group_name field from Created_Groups table, of a tag , that belongs to a certain user. If sounds easy when I say it like that, but I've been inner joining tables all night and failing every time.
Does this make sense? Can anyone help?
Thank you
Hi there,
i have a query building question and was hoping that one of you would know the answer.
Here is what i need to do :(i am using asp.net and ado.net)
I have 1 table where I store thedata, where 5 criteria determine a unique row in this table. Now, this has recently changed as the start date was added. So there potentially can be more than one entry in the table with same 5 criteria, but different start date.
I need to retrieve the row with the latest start date (currently active). The problem arises when the users enter less than 5 criteria. In this case the results may not possess same 5 criteria. Say the user searches based on 2 criteria. Then all the rows possessing these 2 ctieria will be returned, but other 3 criteria might differ with the results set.
But, i only need the latest start date row for each row. So for example, if i searched on 2 criteria, i got back 4 rows, 2 of which possess the same 5 criteria. But between these 2 i only need to display ONE row to the user - the one with the latest date.
How do i build a query? say the table name is tbl, and criteria 1 to 5 fields are called c1 ... c5, and start date field is called start_date.
thanks in advance
Hi Everyone,I'm facing this problem now.Cannot open database "C:myDB" requested by the login. The login failed.Login failed for user 'myLaptopASPNET'.May i ask how can i resolve?Please feel free to let me know the information you need.Thank You!
View 3 Replies View RelatedI am hoping someone can point me in the right direction with this.I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:==============================Column Name || Column Value-----------------||-----------------Column Name || Column Value
-----------------||-----------------
I hope I have explained myself properly. Any help would be greatly appreciated.
Hi all,
I have a question about re-creating the sql portion on either node a or b in a clustered situation. Is it possible to do this without affecting the working node and database ? or is the best solution to make a ghosted image of the node after install is complete and if the node fails just get to a point on the affected node where you can copy the image back?
Thanks for any feedback.
P
The procedure below is for a 3 field wildcard search window.
It works fine if you enter a value such as cheese in @product_name.
If you enter cheese in @product_name1 or @product_name2 it returns nothing.
The string builds OK with values inserted where they should be, obviously there is a problem in the looping back of variable.
I've been looking at this so long I'm sure that the obvious solution is staring me in the face, but I just can't see it.
Any help would be appreciated.
Alter PROCEDURE usp_RobSearchTest
(
@product_name nVarChar(100),
@product_name1 nVarChar(100),
@product_name2 nVarChar(100),
@country_id nchar (3),
@language_id nchar (2)
)
AS
DECLARE @Variable nVarChar (100)
DECLARE @SQLString NVARCHAR(1000)
DECLARE @ParmDefinition NVARCHAR(1000)
/* Build the SQL string once. */
SET @SQLString =
N'SELECT product_name,unit_price,item_id,refund_price
FROM PRODUCT_LISTING
WHERE product_name LIKE ''%' + @Product_name + '%''
ORDER BY product_name'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@product_name nVarChar (100)'
/* Execute the string with the first parameter value. */
SET @Variable = @product_name
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name = @Variable
/* IT WORKS UP TO THIS POINT */
/* Execute the same string with the second parameter value. */
SET @Variable = @product_name1
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name1 = @Variable
/* Execute the same string with the third parameter value. */
SET @Variable = @product_name2
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name2 = @Variable
How long should it take, roughly, to build an index on a single field in a 12gb table?
View 1 Replies View RelatedHi folks,
I'm building a DTS package which needs to mail a list of users nightly
The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.
I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.
The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3....etc).
I'm a bit unsure on how to go about doing / writing this.
Can anyone help?
Thanks,
Dave
Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.
I have a table set up with my parameters and output file names.
Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?
Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).
PS: The file data cannot include cilumn headings
TIA -
bpd
Disclaimer... I am very new to SQL server!
I am trying to create a stored proc. I am passing in some variables which are used as the "data" side of where clause tests, but I also want to pass in a couple of variables to be the variable side of the where clause test, can it be done?
i.e. select id from table where a=1 and b=2 order by my_order_field
becomes
select id from table where a=@data1 and b=@data2 order by @my_variable_field
and I call the sp with exex sp 1,2,my_order_field
Any clues?
Good Afternoon!
I have a (hopefully) simple question.
I have recently been bumped into an applications developer position. I took a week of ASP training a few months ago, but outside of that my exposure to ASP and SQL has been EXTREMELY limited.
I just undertook my first project using these skills. We have an application where I track "Letters". I fill out a form with information regarding the Letter, and save it so there is a record of the letters existence and what its status is.
Afterwards, we want to update the status, so I find the saved form, make my edits, and save it.
This is where my problem starts.
When I first save the letter, I have no problems whatsoever.
When I save it a second time after making my edits, I get an error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'yearID'.
/AuditFlowChart/UpdateAudit.asp, line 206
What I am doing with the ASP is basically created a SQL statement to update the table with the new letter information. I added a line to print out the store proceedure that is being created by the following line
Set rsRecordsetObject = objCOMComponentClassObject.ExecuteSQLStatement(spStoredProcedure,vntSQLServerName,dbDatabaseCatalog)
I print out spStoredProcedure and I get the following
UpdateCorrespondence @p_idCorr = 416,
@p_dtOfLetter = '8/7/2008',
@p_dtRcvdByRespAtty = '2/19/2007',
@p_dtRcvdByAdmin = '2/23/2007',
@p_descOfEntity = 'This is a test 2009 letter. Dated 2/8/2007',
@p_ClientCode = '01074',
@p_MatterCode = '0055',
@p_dtDue = '3/15/2007',
@p_dtEffective = '4/18/2007',
@p_dtYE = '4/8/2007',
@p_respAtty = '0330',
@p_respAttyFull = 'Abelson, H. Edward',
@p_prepParalegal = 'Aldous, Anne Marie',
@p_prepAtty = 'Abromowitz, David',
@p_prepLitParalegal = 'Allegrini, J. Samuel',
@p_dtGivenToParalegal = '4/20/2007',
@p_dtGivenToAtty = '2/28/2007',
@p_dtMemoCirculated = '2/18/2007',
@p_dtRespSentToLitParalegal = '2/10/2007',
@p_dtRespSentToPrepAtty = '2/26/2007',
@p_dtSentToAccountant = '2/15/2007',
@p_memoDSid = 1111111,
@p_respLetterDSid = 2222222,
@p_sideLetterDSid = 3333333,
@p_comments = NULL,
@p_yearID = rsLetterDetail1('yearID'),
@p_opinion = 1,
@p_complete = 0,
@p_userID = 'GSSUSO1517'
When I run the above thru Query Analyzer, I get the following:
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near 'yearID'.
So now I know where my error lies, but I am not sure where to look next. Does anyone have any ideas? Is there a problem I am not seeing with the query?
Any help would be wonderful and much appreciated.
I have a problem with Insert and Update Unicode data to database
I can send Unicode data using string with N prefix (Ref 001)
Below I write some line of my SP
But It does not write Unicode data to table, it writes like ????????
How to insert data using n prefix with Declared Variable?
How can avoid this problem?
Any other method to pass unicode data using SP?
Please help me...
column UnitName nvarchar
Sample Stored Procedure (SP)
SP header:
CREATE PROCEDURE ltrsp_AddEditUnit
@UnitID char(4),
@UnitName nvarchar(20),
@UtrID int
Insert:
INSERT INTO ltrtb_Unit (UnitID,UnitName,UtrID) VALUES (@UnitID,@UnitName,@UtrID)
Update:
---
UPDATE
ltrtb_Unit
SET
UnitName=@UnitName, UtrID=@UtrID
WHERE
UnitID=@UnitID
Ref 001:
found a method to insert unicode data to table (Using N' prefix), but I want to add using declared variable
INSERT INTO ltrtb_UserLevel (UserLevel,Description) VALUES (1,N'Unicode String')
From the URL,
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
But I need to know how to pass the data using stored procedure
I am trying to import part of the following file structure into a SQL2005 table
The 1st 10 records are not needed (HEADER DATA), neither are the records that are from ‘TRAILER RECORD’
Can anyone assist me in designing a Simple SSIS to import this file to a table
The LedgerKey will be from a Global variable
Text within <> are for information and NOT contained in the file
In this file there are three records to be imported, but in other files thjere can be upwards of 20,000 records
Table
CREATE TABLE [dbo].[TempTransactions](
[LedgerKey] [varchar](20) NOT NULL,
[Licence] [varchar](6) NOT NULL,
[DestSortCode] [varchar](6) NOT NULL,
[DestAccountNumber] [varchar](8) NOT NULL,
[DestAccountName] [varchar](18) NULL,
[TransValue] [varchar](11) NOT NULL,
[DestBankRef] [varchar](18) NULL,
[TransCode] [varchar](2) NOT NULL,
[tranref] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
File
< HEADER DATA NOT REQUIRED >
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
"Header Data"
< HEADER DATA NOT REQUIRED >
"11111122222222TEST ACCOUNT 1 BANK REG 0000000020017"
"33333344444444TEST ACCOUNT 2 BANK REG 0000000150017"
"55555566666666TEST ACCOUNT 3 BANK REG 0000000030017"
<FIXED FIELDS LENGTHS 6,8,18,18,11,2 >
<TRAILER RECORD NOT REQUIRED >
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
"TRAILER RECORD"
<TRAILER RECORD NOT REQUIRED >
Hi,
I'm using 2005 management studio express.
Can I use the graphical query builder to specify tables from more than one database?
I know I can do this in sql in the format database..table.column but in the query builder it only shows tables from the current database. Can you get around this?
Thanks
I am currently moving a table from a msSQL DB to a MySQL DB.I am trying to build the bones of an insert statement by adding stringsto a select statement.SELECT '(' + job_referenceno + ', ''' + job_title + ''', ''' +job_category + ''', ''' + job_type + ''', ''' + job_location + ''', '''+ job_duaration + ''', ' + job_salary + ', ''' + job_contactperson +''', ' FROM JobDetails WHERE job_active = 1;This works perfectly until I try and pull an int column from the Table.When I add a column to the string that is INT the query returns 0 rows,with no errors.Can anyone help explain / overcome this?Regards,Tomas
View 2 Replies View RelatedI am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.
View 4 Replies View Related