This is producing a weird result
-
This procedure below was working fine until i added the :
==========================================
Product.ProdcutID, Product.CatagoryID
Inner JOIN Product ON Product.ProductID = Company.CompanyID
==============================================
When ever i remove the text in the middle of the lines every thing works fine; however, i added the other only to be able to pass the other IDS as Parameters to a requesting page for a datalist.
This is in vb.net== Any suggestion??
=================================================
ALTER PROCEDURE
GetBlindCompanys
@IDDepartment
int
AS
SELECT
Company.CompanyName, Company.CompanyID, Company.CompanyShortDescription, Company.CompanyPhoto, Company.DepartmentID, Product.ProductID, Product.CategoryID
FROM
Company
INNER JOIN Product ON
Product.CompanyID = Company.CompanyID
WHERE
DepartmentID = @IDDepartment
Dam again!
-
Define 'weird result'!
I assume CompanyID is unique in Company?
Is DepartmentID unique to the Company table?
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Right now i have DepartmentID set up as a regular field in the company table. It is not an identity column.
Normaly i only receive the 5 companys the are connected to the departmentID that is passed from this query, and now that i added the INNER JOIN it dissplays only 2 companys but does it 15 times each.
The reason that i have added this inner join is that i am tring to pass two parameters from a databinder.eval. so that my requesting page may load information into datalist based not only on the companyid but also the departmentid. I am adding the secong parameter so that only certaing into goes into one datalist and other goes into another datalist, Since all of the data being passed shares the same companyid but not the same departmentid. If i do not add this second parameter all of the infor will go into one both datalist creating two datalist with the same information.
Please sift through the Newieness....and point me in the right direction. I have been dealing with this one for a while and working around it so now i am faced with this same situation again; therefore, i have got to get some sort of resolution.
Thank You,
Erik
Dam again!
-
Maybe you could try reproducing the problem by running in Query Analyser - this way, you will narrow down where the problem is going wrong - obviously the SQL itself is the prime suspect. My suspicion is that companyid to companyid is a one-to-many relationship (and therefore that you get multiple records returned because the product table contains the same company ID many times).
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I have uploaded a snap shot of the database for you to take a peak at it.
I looks like i have a many to many with the department table., with that said can i still write a query to accomplish what i am tring to do with the query that i mentioned above.?
Thanks! Alot~
Dam again!
-
Prob not the dept table, as that is not included in the section of your code which is causing you problems. Update: there is no dept table - you must have meant company!
If you are happy that the results are fine, except for the duplication, try using the DISTINCT keyword:
SELECT DISTINCT fieldlist etc etc
Otherwise we'll have to look at some sample data to work out what query will give you what you want.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I am sorry about that, i started this fourm question with an example in mind and when i showed the real deal it was different.
I do appoligze.
By putting the SELECT DISTINCT fieldlist in front of the entire query this will only pull the each id once?
Dam again!
-
select distinct
will return unique rows, at field-by-field level. If any of the fields in the rows are different, the row will be returned. Therefore the fewer fields you return, the fewer records you return.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
When trying to run the query in query analyzer receive the following error.
(Is it the fieldlist)
Server: Msg 170, Level 15, State 1, Procedure GetBlindCompanyInformation, Line 7
Line 7: Incorrect syntax near '.'.
and in vb.net envirment i receive this error.
Incorrect syntax near fieldlist
==============
I have also tried the Word Column in the place of Fieldlist, For some reason fieldlist is not showing up as a key word..
Dam again!
-
Sorry - I meant for you to replace 'fieldlist' with your list of fields to be selected. Fieldlist is not a keyword
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks.
Let me dig into this.
Dam again!
-
I wanted to update this fourm on what i did to solve this one day (5 minute problem)
No need to worry about seperating the information (inside the query string) the databinder.eval.
Since i am using two datalist i made two seperate commands and binded them to there own datalist.
===============================First Command--------
ALTER PROCEDURE
GetBlindCompanyInformationCT32
@IDCompany
int
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName,Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
WHERE
Company.CompanyID = @IDCompany
AND
CategoryID = 32 <-----------First command
==========---------------------------------------------
=======================Second Command=====-----------------
ALTER PROCEDURE
GetBlindCompanyInformationCT32
@IDCompany
int
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName,Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
WHERE
Company.CompanyID = @IDCompany
AND
CategoryID = 16 <--------Second Command
=======------------------------
Thanks!
Erik
Dam again!
-
On the Second Command The alter Procedure is GetBlindCompanyInformationCT16 For those Nameing convention freaks out their.
---------------------------------------------
ALTER PROCEDURE
GetBlindCompanyInformationCT16 <-----------
@IDCompany
int
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName,Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
WHERE
Company.CompanyID = @IDCompany
AND
CategoryID = 16 <----------
Dam again!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply