sql server new users: Multiple table query -- sql server new users

You're in the

sql server new users

group:

Multiple table query

Sid Price
3/26/2007 10:12:18 AM

sql server new users:

I have an application that has a number of tables that I need to query and I
am having no success in selecting the data I need.

T1 has a set of columns that describe a business entity (name, address,
etc), T2 has a set of columns that describe various business categories,
and T3 is a table that has an entry for each business/category pairing. A
business may have multiple categories.

I need to write a query that will select all the business entities in T1
that have a certain business category; I also need to order the businesses
alphabetically. I have been advised to use a "left join" to achieve this but
I am at a loss understanding how to achieve this.

In T3, the table of business/category pairs, each row has the business
entity ID and category ID, these IDs are the primary keys of T1 and T2
respectively.

Any help would be much appreciated,
Sid.

Re: Multiple table query

Sid Price
3/26/2007 2:48:30 PM

Thank you so much Roy, your first example worked great for me. Maybe one
more piece of advice, can you recommend a good book for SQL, I find that the
syntax is well described and there are plenty of basic examples but I could
not find anything that would have pointed me to this solution.

Again thanks,
Sid.

[quoted text, click to view]

Re: Multiple table query

Roy Harvey
3/26/2007 2:56:43 PM

One of these should get you started, which one depending on which of
my guesses about the tables and data was closest to the truth.
Assuming I understood the question, of course.

SELECT A.*
FROM T1 as A
JOIN T3 as B
ON A.businessID = B.businessID
WHERE B.categoryID = 'banana'
ORDER BY A.name

SELECT A.*
FROM T1 as A
JOIN T3 as B
ON A.businessID = B.businessID
JOIN T2 as C
ON B.categoryID = C.categoryID
WHERE C.categoryname = 'banana'
ORDER BY A.name

Roy Harvey
Beacon Falls, CT

On Mon, 26 Mar 2007 10:12:18 -0600, "Sid Price" <sid@nowhere.com>
[quoted text, click to view]

Re: Multiple table query

Roy Harvey
3/27/2007 12:00:00 AM

On Mon, 26 Mar 2007 14:48:30 -0600, "Sid Price" <sid@nowhere.com>
[quoted text, click to view]

Sorry, but I'm not a very good person to ask about books on learning
SQL because I learned it so long ago with just the manual and reading
on-line examples in a discussion forum. I did think the original
version of Ken Henderson's "The Guru's Guide to Transact-SQL" was
pretty good at the time, but have not looked at any of the later
editions and variations.

Roy Harvey

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn