Querying multiple tables using one script

Question

Post reply

Querying multiple tables using one script

terrence_daniels

SSC Enthusiast

Points: 180
More actions
April 14, 2008 at 11:11 am

#120955

I have been attempting to query two tables using one script what is the correct syntax for this to occur. 😀

Viewing 13 posts - 1 through 12 (of 12 total)

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

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

are you asking how to join two tables? Lookup Join in BOL

-- Cory

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

terrence_daniels (4/14/2008)
I have been attempting to query two tables using one script what is the correct syntax for this to occur. 😀

Heh... my neighbor's car won't start... can you tell me what's wrong with it?...

... of course you can't because you haven't seen the car...

... and we haven't seen your query attempt. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 3

Excuse me,

Here is what I have so far...

SELECT distinct [table1].Application_name, [table2].Application_Vendor

FROM [table1] INNER JOIN

[table2] ON [table1].Business_Area = [table2].Business_Area

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

You're on the right track... you may have to add extra criteria in the WHERE clause to narrow down the correct matches.

Looks like you're using a native tool to write the code... you might want to take a look at a couple of things... first, the tool sucks for formatting. Second, if you add a table alias to each table (t1 and t2 respectively in the code below), it can make the code a lot easier to read. Table aliases should be meaningful shorthand for the table name... a,b,c probably wont get it and neither will t1, t2, etc.

Lookup aliases in Books Online and see what a powerful tool they can be... they can actually let you join a table to itself.

Here's what your code might look like after a bit of formatting and aliasing...

SELECT DISTINCT

t1.Application_name, t2.Application_Vendor

FROM table1 t1

INNER JOIN table2 t2

ON t1.Business_Area = t2.Business_Area

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 5

thanks I am looking into Books online...but my question is why is the script looping through the tables? 😉

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Terrence -

there's no looping going on in what it being displayed - the result should display all of the app names and vendor names in a single query.

What is it you're trying to get at doing? It sounds like you're not giving us all of the details, and that something else is happening. Give us something to go on, so that we can help you...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 7

I would like my results to just display only the applications with a specific business area...so I wish to query both tables and just get those results

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Then you need to specify which business area you want to display....That's a where clause...

SELECT DISTINCT

t1.Application_name, t2.Application_Vendor

FROM table1 t1

INNER JOIN table2 t2

ON t1.Business_Area = t2.Business_Area

WHERE t1.business_Area='MyBusinessArea' --<-- replace that with the correct business area

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

terrence_daniels (4/15/2008)
thanks I am looking into Books online...but my question is why is the script looping through the tables? 😉

If you're looking at the execution plan and seeing the loop, that's because SQL Server decided to use a loop in the underlying code. It's at the machine language level so it'll be very fast. Sometimes you can get it to do a "Merge Join", which does not use a loop behind the scenes, by specifying WITH RECOMPILE in the code or sometimes a "MERGE" in the JOIN clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 10

What tool can you recommend I use to do a recompile?

I have not been able to get 'Merg Join' to work

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

True enough... you won't always be able to use something like INNER MERGE JOIN because the optimizer doesn't always like it.

Teaching a man to fish... the WITH RECOMPILE option is one of the options you can specify in the CREATE PROCEDURE statement... lookup CREATE PROCEDURE in Books Online for all the details of how to use it. If you "play" with the WITH ENCRYPTION option, make real sure you have the original source code somewhere before you do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 12

terrence_daniels

SSC Enthusiast

Points: 180

April 16, 2008 at 9:56 am

#803193

Will do