Multiple Join Clause

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
MosheYou can have multiple JOIN clauses. I find it helpful to put them on separate lines, plus I always indicate the JOIN type since I never can remember which type is the default, and it is liable to change on someone's whim in the future. Plus normally I'd want to return the User's information whether or not I was able to successfully find the corresponding Group or Company -- which means OUTER JOINs:

SELECT
 Users.UserId,
 Users.GroupId,
 ISNULL(Groups.Name,'**unknown**'),
 Users.CompanyId,
 ISNULL(Companies.Name,'**unknown**')
FROM
 Users
LEFT OUTER JOIN
 Groups ON Users.GroupId = Groups.Id
LEFT OUTER JOIN
 Companies ON Users.CompanyID = Companies.CompanyID

Terri|||Thanks,
It was helpfull

Multiple data insertion with For clause

Suppose I have a table named test with two fields(sl int,age int).
I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
Is it possible to insert in SQL SERVER following c protype?
What is the fastest way for inserting multiple sequevcial data?
SubhasishFor relatively small sequential sets, do something like this...

INSERT INTO TEST(sl, age)
SELECT a.i, a.i+21
FROM (
SELECT i = 1 UNION
SELECT i = 2 UNION
SELECT i = 3 UNION
SELECT i = 4 UNION
SELECT i = 5 UNION
SELECT i = 6 UNION
SELECT i = 7 UNION
SELECT i = 8 UNION
SELECT i = 9 UNION
SELECT i = 10 ) as a

You can build up the derived table query quickly with cut-and-paste, then go back and fix the values

For bigger sequential sets, build yourself a temporary table of sequential integers like this:

CREATE TABLE #i
(x INT IDENTITY(1,1),
y INT)

INSERT INTO #i
VALUES(NULL)

INSERT INTO #i
SELECT y FROM #i

Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.

INSERT INTO test(sl, age)
SELECT #i.x, #i.x+21
FROM #i
WHERE #i.x < 100 -- for 100 rows|||I think s/he's looking for a loop as well...

USE Northwind
GO

CREATE TABLE myTable99 (sl int,age int)
GO

DECLARE @.x int, @.y int

SELECT @.x = 1, @.y = 1

WHILE @.x < 100
BEGIN
INSERT INTO myTable99 (sl, age)
SELECT @.X, 1*@.y UNION ALL
SELECT @.X, 2*@.y UNION ALL
SELECT @.X, 3*@.y UNION ALL
SELECT @.X, 4*@.y UNION ALL
SELECT @.X, 5*@.y UNION ALL
SELECT @.X, 6*@.y UNION ALL
SELECT @.X, 7*@.y UNION ALL
SELECT @.X, 8*@.y UNION ALL
SELECT @.X, 9*@.y UNION ALL
SELECT @.X, 10*@.y

SELECT @.x = @.x + 1, @.y = @.y + 1
END

SELECT COUNT(*) FROM myTable99
GO

DROP TABLE myTable99
GO

multiple criteria in WHERE clause (was "T-SQL")

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?Uhmm...how do you ever expect Field1 to equal 1 AND equal 2 at the same time? Curious mathematical alternate universe you live in, but since your server seems to be bound to this particular reality, I suggest you look up the "OR" clause in Books Online.|||Maybe you can put this statement inside of a loop and run it until @.@.rowcount > 0. If it runs long enough, math rules might change to include an uncertaintly principal where a finite object can have more than one value at one time. Just an idea.|||blindman,

Actual Field1 is the QuestionID and Field2 is the Answer

Basically one question would have 5 different choice of ans

Ex: Question1 Ans: Excellent, Good....etc

So i need to say in my query that return all rows where QuestionID 1 has Excellent QuestionID2 has Excellent|||Breaking out the crayons today...
WHERE (dbo.Field1= 1 AND Field2= 'Test')
OR (dbo.Field1= 2 AND Field2= 'Test')
OR (dbo.Field1= 3 AND Field2= 'Test')
OR (dbo.Field1= 4 AND Field2= 'Test')
OR (dbo.Field1= 5 AND Field2= 'Test')
...which is logically equivalent to: WHERE dbo.Field1 = IN (1, 2, 3, 4, 5) AND Field2= 'Test'

...I smell an MS Access GUID addict who thinks every new line is a separate predicate. You need to read up on SELECT statements in Books Online before you go any further with SQL programming.|||blindman,

No, i'm not breaking the Crayons today :-)

I understand what you are saying in your query but in my case it won't work since i'm trying to find all questions which has answer = Excellent.

This is how the questions are ask
Question1 :...........?
Ans: you are given five diff choice and ask to choose one|||Dude, I see references to "Field1", "Field2", 1, 2, 3, 4, 5, and 'test' in your query. Nowhere do I see any reference to 'Excellent'. It is also becoming more and more obvious that you have not even tried to figure this out yourself using the resources available in Books Online. So either do a little more research, or state your question more clearly.|||This query is obviously the output of a tool like Access. The solution in SQL would be simple. It's time to take the leap from GUI created logic to writing your own SQL. You will be glad after the initial learning curve.

OR

You could jump over to the Access forums and get better help.|||No, I don't think this is direct Access output, because it doesn't make syntactical sense. I think it is the output of someone who thinks that if they format their SQL statement to LOOK like the Access Query GUI grid it will behave like an Access Query.
Regardless, it's time to either take off the training wheels or hire a chauffeur to drive you around. Books Online.|||blindman,

Thanks for your help. I figure it out.

And for your quote " it's time to either take off the training wheels or hire a chauffeur to drive you around."

I think u need to take your quote for ur self since i figure out the query without online book reference.|||Well, gosh, how great for you. Maybe next time you can do that BEFORE posting such a simplistic question.|||Do I have to send you to your rooms?

Good Lord.

Dude, did you read the sticky at the top of the forum? It would really help us if you follow that model...

Blind dude...at least buy them dinner first....|||Heck no, it's dutch or nothin'.