Joining Tables Question 1

[mmorgensen]

I have this query

Select company, count(ticketnumber) as counter From Tickets group by company

It runs and I get this
Company counter
1 5
2 6
3 4

Now I need to pull data from the Company table, and match the company field from the above table. My company table is like this:

ID NAME
1 company1
2 company2
3 company3


I would like this end result.

Company counter name
1 5 company1
2 6 company2
3 4 company3



Rookie to SQL, tried this for the last hour and could not get it to work

thanks!

mm

 

[MeanGreen]

Here you go:

Select company, count(ticketnumber) as counter,Name
From Tickets join Company
on tickets.company = Company.ID group by company,Name

Hope this helps.

 

[mmorgensen]

bingo! I appreaiate your help!

thanks again!!

mm

 

[jpittawa]

Microsoft SQL Server and Access both use a JOIN statement in their SQL generators. The JOIN can be confusing to new SQL programmers and Oracle does not recognize it. There is an older alternative that works on both SQL Server and Oracle.

SELECT Company, Count(TicketNumber), Name
FROM Ticket, Company
WHERE Ticket.ID = Company.Company
GROUP BY Company, Name

Note that the WHERE clause specifies the relationship between tables.

I learned the SQL language by using a graphical query building tool, Microsoft Access, to build the query I wanted and then viewed it in SQL text. Enterprise manager will do the same thing. I find Enterprise Manager's JOIN generation easier to read then the one in Access.

 

[mmorgensen]

Where in Ent. Manager do you find the graphical query tool?

thanks!

mm

 

[mmorgensen]

must learn to look before I write. I found it 'views' correct?

mm

 

[SQLSister]

If you right click on a table name and select Open table, then query, you will get also get the query generator.

Also the older version may not be supported in future versions, I would suggest you not use it. I'd use Mean Green's version. Besides it is really important to understand joins if you want to make sure you are getting exactly the results you want from your query.

 

[jpittawa]

>must learn to look before I write. I found it 'views' correct?

When I'm just investigating a database, I use the Tables tab. Right mouse click any table. Move the mouse to the "Open Table >" dialog. SQL Server 2000 offers "Return All Rows", "Return Top...", and "Query". "Query" puts directly into the visual query builder mode.

If you have SQL 7, you will not have a "Query" option. No problem. Select either of the first two options. After the Results Pane is painted, you can setup the visual query builder. Just let the mouse hover over the tool bar icons on the upper left and you will see the remaining three panes for query builder. Play with them for a while.

The "+" icon on the right opens a dialog that lets you add other tables to the diagram pane. Make your relations by drawing a line between the respective table keys.

BTW, I have to take exception to SQLSister's suggestion that the use of the WHERE clause for relations is going away simply because the JOIN is around. It will remain in use for as long as the provider's want to stay in business.

Enterprise Manager is one of the neatest tools on the market. I have found that Crystal Reports queries, which use a very weird JOIN statement, can be pasted into the E-M SQL pane and they work! Not only that, once E-M runs them, it translates them into its version of SQL JOIN, which is more readable then the on MS-Access.

Enjoy!!!