|
Actually, I think you'd want to use two LEFT OUTER JOINS, as you'll want to return a match on FunctionID1 or FunctionID2 (unless I've read this all incorrectly):
select M.MID, M.MName, F1.FName [F1Name], F2.FName [F2Name]
from Materials M
left outer join Functions F1 on F1.FID = M.Function1ID
left outer join Functions F2 on F2.FID = M.Function2ID
where F1.FID is not null or F2.FID is not null
The WHERE clause will elimitate records where neither the Function1ID nor Function2ID match the input value. And the double LEFT OUTER JOINs will cause records where either the Function1ID or Function2ID match the supplied input value to be pulled.
Hope in one hand and poop in the other; see which fills up first. Hope and change were good slogans, now show us more than words.
|
|
|
|
|
Hello,
I am new to Writing Triggers in SQL Server 2000.
I have not written any triggers uptil now.
So it would be great if someone helps me in this problem.
I have a table with a field Serial No and SrCount
I want to write a trigger such that when a record is added in the table, it will take the serial no of the new record and get the count of that serial no present in the table and update the SrCount field in the new record with the Count Results.
Please anyone could help, I am totally new to triggers.
Any help could be of great help.
Thanks in Advance. 
|
|
|
|
|
Do you know how to update the SrCount without using a trigger?
I are troll 
|
|
|
|
|
Feels like a design error to me, I would ask why you need to count stored in another table.
Triggers are EVIL, debugging triggers can become a support nightmare. If it was essential I would do the update in the insert procedure rather than use a trigger (achieves the same thing, almost, but is easier to support).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Stay away from triggers. You should do that within the stored procedure that does the insertion.
Also, be aware of concurrency issues (like race conditions).
|
|
|
|
|
Hi
Can i convert an SQL Server 2005 bank file to SQL Server 2000?
|
|
|
|
|
In theory, and depending entirely on the structure of the 2005 database, possibly.
But given the way you have worded the question, I doubt it.
Why would you want to?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Helloo all..I need to identify the entities and relationship for a company's database and then draw a suitable set of E-R diagrams. Draw a DFD and perform a structure Analysis to produce and appropiate Structure Chart. I am stuck with this and would like to ask if anyone could help me out with this.
Need a database to keep track of the stock in each outlet as well as customer information. It has supplies of electrical goods which include such items as:
cables
plugs and sockets
light bulbs
fire alarms
security alarms
CCTV equipment
these goods are produced by different manufacturers, a particular outlet will have different manufacturer's versions of the same item. In the new database must be stored the following:
the address and telephone numbers of all the outlets
a stock inventory for each outlet, stating the quantity of each item currently in stock and the date of the next delivery. for each item must be listed:
a full specification of the item
the manufacturer's contact details
the item's current price
any discounts currently available
|
|
|
|
|
This smells like homework so I refer you to all the responses that such questions elicit on this forum.
Regards
David R
|
|
|
|
|
I Have 3 Tables
1.
Productmaster:
Columns:ProductId,PName,PDescription
2.
Attributes:
Columns:AttributeID,attName
this Table has Three Entries
1 Brand
2 Category
3 Artist
3.ProductAttributeValues
Columns:paId,ProductId,AttributeID,AttributeValues
Conditions:1 Product can have Multiple Attributes
Needed Output
--------------------
ProductId | ProductDesc | Brand | Category | Artist..
1 sadasd Brand1 Category1 Artist1
2 sadasds Brand2 Category3 Artist4
I NOt Getting What should I Do To Get This O/p
Pls Help Me
Please
Thanks in Advance
|
|
|
|
|
Try This
select * from Productmaster a,ProductAttributeValues b,Attributes c
where a.ProductId=b.ProductId and b.AttributeID=c.AttributeID
Thanks
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
u r not getting this is not so easy
there r 3 entries for each product in productattributevalues table
thats why i am adding 3 columns to the output with values displaying attValue
|
|
|
|
|
|
Leave It ,
Thanks Yar, I Got The Answer
|
|
|
|
|
can u post the answer..,
am also working with complex quries on database.,
Your Answer may be helpful for me.
Thanks
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
USE [rhythmhouseDynamic]
GO
/****** Object: View [dbo].[vw_SearchStationery] Script Date: 04/17/2009 16:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_SearchStationery]
AS
SELECT dbo.ProductLine.prodlineID as 'ProductLineId', dbo.ProductLine.prodlineName as 'ProductLineName', dbo.ProductMaster.ProductId, dbo.ProductMaster.ProductTitle,
dbo.ProductMaster.ProductDescription, dbo.ProductMaster.ProductShortdescription, dbo.ProductMaster.ProductSynopsis
,ProductAttributeValues_Brand.ComputedAttributeValue AS [Brand]
----Add New Attributes Here
FROM dbo.ProductLine INNER JOIN dbo.ProductMaster
ON dbo.ProductLine.prodlineID = dbo.ProductMaster.ProductlineID
LEFT OUTER JOIN dbo.ProductAttributeValues AS ProductAttributeValues_Brand
ON dbo.ProductMaster.ProductId = ProductAttributeValues_Brand.ProductId
----Add New Tables And Join Conditions Here
WHERE (dbo.ProductLine.prodlineID = 7)
AND (ProductAttributeValues_Brand.attId = 265 or ProductAttributeValues_Brand.attId is null)
----Add AttributeId Where Condition Here
.............................................
Bye Best Of Luck Keep In Touch
|
|
|
|
|
I would try a natural join first since the fields joining the tables have the same names.
|
|
|
|
|
Hi all,
I have one problem with Identity Specification in sql server 2008,
i.e. Once I am having records in one table and after that if I want to change identity from Yes to No / No to Yes. Unable to do it 
Am i missing something or is it bug ??
Any solution for that?
Thanks in advance...
Krishnraj
|
|
|
|
|
SyncFolder is a very easy to use but powerful solution for the synchronization of two or more folders/computers. It can backup all your important data into a zip-archive and has many settings to customize the synchronization... SyncFolder
|
|
|
|
|
Pay for advertising you tight ass.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hello...
I'm using T-SQL. What I'm trying to do is (within a function) read a value from a specified dynamic column, and return that value for usage elsewhere. The following lines would do the trick:
[code]
SET @SqlString = 'SELECT @Value = [' +@ColumnName +'] FROM SettingTable WHERE ID = 50'
exec sp_executeSql @SqlString,N'@Value int OUTPUT', @Value OUTPUT
[/code]
However, I can't use that within a function (Only functions and extended stored procedures can be executed from within a function), even though sp_help tells me that sp_executeSql is an extended stored procedure. Is there some way I should be calling the executesql proc that would allow it? Or is there another way I should be generating my dynamic query that would be allowed within a function, and which will allow me to get an output value from the query?
|
|
|
|
|
If I have the following code
<br />
Exec('Select Count(*) From table')<br />
how can I put the value of the execution into an integer type variable
|
|
|
|
|
|
Hi,
If you just what to store the COUNT value in a variable (@a) you could do this.
declare @a int
select @a = (SELECT COUNT(*) FROM table)
Now select the count value from the variable...
select @a
|
|
|
|
|
Hii,
I have a table in a database called dictionary.
I have four columns namely id, word , keyword and meaning.
I have indexed word, keyword and meaning column in a catalog for full text searching of any word.
I want full text searching of any word in these two column and return meaning of that word found in word and keyword column.
I searched the table for the word using following query :
string wordSearched=textBox1.Text;
"SELECT meaning FROM dictionary WHERE CONTAINS(word, keyword, '+"wordSearched"+')";
But am unable to get the full text search result.
Am i wrong in my syntax for querying full text search.
or may be can anyone help it out giving the proper syntax to query word and keyword column.
Thanks in Advance!
Aspiring Techie,
Vishnu Nath
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
