How do I put together two sql Inner joins?

Question

How do I put together two sql Inner joins?

Jeff Wood

Old Hand

Points: 360
More actions
January 24, 2007 at 6:36 pm

#62117

Dear Forum,
I am having trouble combining two inner joins.
I have MS SQL table that has two columns that are each related to other tables.
Shows Table > HeadlinerID to Headliner Table > HeadlinerID
And..
Show Table > Venue to Venues Table > Venue
I can get one Inner Join to work in my SelectStatement, but when I try to combine them, it does not work.
SelectStatement = "SELECT * FROM shows AS S INNER JOIN Headliner AS H ON S.HeadlinerID = H.HeadlinerID Where Id=" & Id

"SELECT * FROM shows AS R INNER JOIN Venues AS G ON R.Venue = G.Venue Where Id=" & Id
So if I put them together like this, it does not work:
SelectStatement = "SELECT * FROM shows AS S INNER JOIN Headliner AS H ON S.HeadlinerID = H.HeadlinerID, FROM shows AS R INNER JOIN Venues AS G ON R.Venue = G.Venue Where Id=" & Id
I am using VB asp.net. How would I write this so that it is formatted correctly?
Thanks,
Jeff
Boise, ID

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Santosh-344014 Grasshopper Points: 11 More actions · Answer 1

SelectStatement = String.Format("SELECT * FROM shows AS S INNER JOIN Headliner AS H ON S.HeadlinerID = H.HeadlinerID Where Id={0};SELECT * FROM shows AS R INNER JOIN Venues AS G ON R.Venue = G.Venue Where Id={0}", Id)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

The way Santosh wrote it , you'd get two resultsets.

If you want to join both queries to get one resultset , just use :

SelectStatement = String.Format("

SELECT ....

FROM shows AS S

INNER JOIN Headliner AS H

ON S.HeadlinerID = H.HeadlinerID

INNER JOIN Venues AS G

ON S.Venue = G.Venue

Where S.Id={0}", Id.tostring)

If you don't need all the columns from the resultlist, don't use "*" !

(btw If you want to copy-past into VS, remove the "newline"s )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

If you're not sure about SQL syntax, use one of the graphical query builders to help you build it.

In Enterprise Manager, you can right-click on a table and choose "Open table in query". In Management Studio you can use the "Design Query in Editor" toolbar button. There is probably a similar function somewhere in Visual Studio.

You should almost never use a "SELECT * FROM" query in a VB program. Aside from the performance issues, if any of the tables involved are modified (columns added or removed) you will probably have to recompile the program.

Jeff Wood Old Hand Points: 360 More actions · Answer 4

Thanks everyone who helped me. alzdba yours is the one that worked for me the best. You are awesome.

Thanks

Jeff