|
You could have a look, what kind of execution plan comes out of something like this. Also check if this gives the desired result (I understood that the original query didn't):
SELECT Firms.dbo.FirmNames.id, Firms.dbo.FirmNames.FirmName
FROM Firms.dbo.FirmNames
WHERE Firms.dbo.FirmNames.id NOT IN
(SELECT FirmsInFamily.FirmID
FROM FirmsInFamily
INNER JOIN Families
ON FirmsInFamily.FamilyID = Families.FamilyID
WHERE Families.FamilyID = @FamilyID)
|
|
|
|
|
Thanks worked like a charm.
SQL sometimes reacts so different from the way I would expect.
I really need to take an indepth class on it.
|
|
|
|
|
No problem 
Yeah, SQL is a funny language. It takeas about 5 hours to learn it and about 50 years to master it, so still learning...
Sunset Towers wrote: I really need to take an indepth class on it.
Don't know if these would be useful, but I kinda like Joe Celko's books. They contain good tips and tricks if you're already familiar with the basics. Have a look at for example these: http://www.amazon.com/s/ref=nb_ss_gw?url=search-alias%3Daps&field-keywords=joe+celko[^]
|
|
|
|
|
Hello everybody,
please could anyone help me to find out answer the following scenario.
in abc college there are 30 students and 11 instructors. How I can allocate 11 instructors to 30 students automatically for their project supervision. I understand it is a mathematics question. if I get the mathematical formula I'll be able to implement this in SQL. thanks in adv for any kind help.
regard
learner
|
|
|
|
|
Hey guys, this is probably a stupid syntax error but i can seem to see what I'm doing wrong 
USE master
GO
-- Drop the database if it already exists
IF EXISTS
(
SELECT name
FROM sys.databases
WHERE name = N'RoomsForRentDB'
)
DROP DATABASE RoomsForRentDB
GO
CREATE DATABASE RoomsForRentDB
GO
USE RoomsForRentDB
GO
CREATE TABLE [User]
(
UserID BIGINT IDENTITY(0, 1) NOT NULL,
EmailAddress VARCHAR(1000) NOT NULL,
[Password] VARCHAR(1000) NOT NULL,
CONSTRAINT PK_UserID PRIMARY KEY(UserID)
)
GO
CREATE TABLE House
(
HouseID BIGINT IDENTITY(0, 1) NOT NULL,
UserID BIGINT REFERENCES [User].UserID NOT NULL,
CONSTRAINT PK_HouseID_UserID PRIMARY KEY(HouseID, UserID)
)
GO
Error
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK__House__UserID__014935CB' references invalid table 'User.UserID'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
I'm using SQL 2008 if that makes a difference
Thanks
Harvey Saayman - South Africa
Software Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
|
|
|
|
|
Instead of:
UserID BIGINT REFERENCES [User].UserID NOT NULL,
you should have:
UserID BIGINT REFERENCES [User] (UserID) NOT NULL,
Harvey Saayman wrote: CONSTRAINT PK_HouseID_UserID PRIMARY KEY(HouseID, UserID)
Also, do you really want to include UserId to primary key of House? I think there's no need since HouseId is already IDENTITY and cannot have duplicate values.
Mika
|
|
|
|
|
Mika Wendelius wrote: REFERENCES [User] (UserID) NOT NULL,
They changed SQL's syntax in 2008!? 
Mika Wendelius wrote: Also, do you really want to include UserId to primary key of House?
There's no specific reason, I'm just used to doing it like that.
Thanks for the help Mika, I'll only be able to test it when i get home tonight, I'll let you know how it goes
Harvey Saayman - South Africa
Software Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
|
|
|
|
|
Harvey Saayman wrote: They changed SQL's syntax in
Don't know if the syntax you used worked in previous versions. The one I used is actually ANSI syntax so SQL specific syntaxes me be dropped.
Harvey Saayman wrote: There's no specific reason, I'm just used to doing it like that.
If you may, I'd suggest that you would use only the identity column as primary key. This will ease yuor dml operations as well as queries (you don't have to use two fields in statements and the primary key index will be more efficient.
Also if you use foreign keys to refer to House, you would only have to use HouseID. This would be benefitial to all foreign keys and especially CASCADE operations.
Harvey Saayman wrote: I'll let you know how it goes
That would be nice.
Harvey Saayman wrote: Thanks for the help Mika
No problem
|
|
|
|
|
I'm a moron...
I just checked a create database script i wrote about a year ago(I didn't have that or my SQL books with me at home when i wrote the OP) and your right, its supposed to be REFERENCES House(HouseID) NOT NULL
Of all the things I've lost I miss my mind the most...
Harvey Saayman - South Africa
Software Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
|
|
|
|
|
Don't worry. It happens to everyone enery now and then
|
|
|
|
|
My Definitions:
script: A script contains one or more executable records which may also include other scripts.
executable record: An executable record contains a header and a data section.
data section contains a struct representing the values of the executableID. (currently the data section is binary data that gets converted by the script engine)
header contains sourceID, executableID, sizeof(header + data section).
I have thousands of modular scripts that do specific tasks and that could be used by other scripts.
For example:
MainScript01
-DoSomethingA
-DoSomethingB
-DoSomeThingC
MainScript02
-DoSomethingA
-DoSomethingD
--DoSomethingC
I would like to view the scripts in a tree.
I would like to store the scripts in a database or other text based container. If possible in ASCII format for easy editing outside the script engine application.
Question: What are some specific possibilities that I could use to store scripts?
I am trying to figure out what type of table(s) and fields I should have.
-one table holds them all? (cannot easily update structures in the Data field)
table definition: ScriptID, ScriptRecordNo, TotalRecords (in this ScriptID), Comments, Data (byteArray of executableID), Index (automatically generated by database), (other control fields)
-separate tables?
-Mainscripts (using foreign keys to subscripts and executableIDs?)
-subscripts (using foreign keys to subscripts and executableIDs?)
-each type of executableIDs (one field for variable in the struct makes changing structs and values easier)
I would like some pros and cons if possible.
Thanks
Michael
ﾠﾠﾠﾠﾠﾠﾠﾠﾠ\|/
ﾠﾠﾠﾠﾠﾠﾠ^O^O^
——o00o—0—o00o——
If we knew what it was we were doing, it would not be called research, would it? --Albert Einstein
modified on Tuesday, February 10, 2009 11:10 AM
|
|
|
|
|
hey people i hope you can help me out
im using c sharp and sqlite and when i do inserts sometimes one or two arguments in the sqlite query have the character ' and it throws an exception,so far i have only noticed this happens with just that character, anyone knows how can i insert or update records with the character ' ??
someone told me that it could be solved using parameters but i dont know how to use them
thanks in advance
|
|
|
|
|
Parameters is the correct way to do this. The parameter type depends on how you connect to the database. For example if you connect using OleDbConnection, you would use: OleDbParameter[^]
|
|
|
|
|
thanks man i got it now , appreciate it
|
|
|
|
|
|
Hi,
What's the execution order of a multiple join like this:
SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO
FROM CORPDATA.EMPLOYEE XXX LEFT OUTER JOIN CORPDATA.DEPARTMENT YYY
ON XXX.WORKDEPT = YYY.DEPTNO
LEFT OUTER JOIN CORPDATA.PROJECT ZZZ
ON XXX.EMPNO = ZZZ.RESPEMP
WHERE XXX.EMPNO = YYY.MGRNO AND
YYY.DEPTNO IN ('A00', 'D01', 'D11', 'D21', 'E11')
which query runs first, which one next , ..... ?
Best wishes
|
|
|
|
|
first it filter record according to
CORPDATA.EMPLOYEE XXX LEFT OUTER JOIN CORPDATA.DEPARTMENT YYY<br />
ON XXX.WORKDEPT = YYY.DEPTNO then resulted record is filtered to LEFT OUTER JOIN CORPDATA.PROJECT ZZZ ON XXX.EMPNO = ZZZ.RESPEMP and then finally record is filtered according to where clause
|
|
|
|
|
Display the estimated query plan for the query in Enterprise Manager.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.
|
|
|
|
|
With the information given nobody knows. The only way to find out is to run it and show the execution plan. It all depends on data volumes, indices etc etc.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
hello everybody in this forum , i would like to ask about something .
i would like to use .net Remoting from sql server , that means i need sql server to notify a windows service about some transactions that occur in the db like insertion , update or deletion . i tried to develop a managed trigger via C# but when i refer to system.runtime.remoting.channels.http in the solution , it doesn't show me all available namespaces in the solution like any other .net solutions , so is there a way to let the sql server notify the windows service about some changes , thank you
Human knowledge belongs to the world.
|
|
|
|
|
Take at look at notification services[^]. I think it will do what you require.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
hi all,
I want to fill multicolumn listbox from an recordset. How is this possible? Without using Getstring() of Recordset.
please suggest me another way.
Regards.
modified on Monday, February 9, 2009 7:06 AM
|
|
|
|
|
|
In a join which table is said Left, which one right?
It depends on List in select or order in On or ...
Best wishes
|
|
|
|
|
table1 left join table2 on table1.id=table2.id
here table1 is left table whereas table2 is right. Query return all record from table1 plus matches record from table2
table1 right join table2 on table1.id=table2.id
here table1 is left table whereas table2 is right. Query return all record from table2 plus matches record from table1
hope this clear your confusion
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
