Access SQL Inner Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access SQL Inner Join

Author

Topic

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 19:13:46

Write a query the will return category name, supplier name, product name and the total amount on units in stock and units on order (name this column TotSpent). Only dispay rows with "lager" found in the product name. Order the results by category name, supplier name, and decending product name.

I am comming up with this:

select c.CategoryName,
b.CompanyName,
a.ProductName,
a.QuantityPerUnit,
a.UnitsOnOrder as TotSpent
from Categories c,Suppliers b, Products a
where c.ProductName = 'Lager'
order by a.CategoryName,b.CompanyName,c.ProductName desc;

What am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 19:19:08

You are missing the join conditions in the where clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 19:30:58

You mean this:
Where a.supplierID = b.supplierID and a.ProductName = 'lager'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 19:35:37

ProductName = 'lager' is just your filter.

But yes I mean for supplierID. You need two join conditions since you've got three tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 19:43:32

Ok so all together I have:

select c.CategoryName,
b.CompanyName,
a.ProductName,
a.QuantityPerUnit,
a.UnitsOnOrder as TotSpent
from Categories c Suppliers b, Products a
where a.supplierID = b.supplierID and a.ProductName = 'lager'
order by a.CategoryName,b.CompanyName,c.ProductName desc;

When I run the query I am getting just the table and no information. What am I doing wrong now?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 19:47:44

You are still missing a join condition. You need to include Categories as well.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 19:52:04

That is from the From clause right or am I missing something

select c.CategoryName,
b.CompanyName,
a.ProductName,
a.QuantityPerUnit,
a.UnitsOnOrder as TotSpent
from Categories c, Suppliers b, Products a
where a.supplierID = b.supplierID and a.ProductName = 'lager'
order by a.CategoryName,b.CompanyName,c.ProductName desc;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 19:53:20

Yes it's in the FROM part, but you haven't added a join condition in your WHERE clause yet. I don't know your tables, so I don't know what column to join on, otherwise I'd just show you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 20:12:07

Ok..Here it is.. I have it and it is still not working for me.

select c.CategoryName,
b.CompanyName,
a.ProductName,
a.QuantityPerUnit,
a.UnitsOnOrder as TotSpent
from Categories c, Suppliers b, Products a
where a.supplierID = b.supplierID
and a.CategoryID = c.CategoryID
and a.ProductName = 'lager'
order by c.CategoryName,b.CompanyName,a.ProductName desc;

I am still missing something..I dont know what

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 20:14:51

You need to explain what you are seeing as I can't read your mind nor see your monitor.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 20:27:49

OK.. I am not seeing anything in the query table when I input the SQL code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 20:37:15

Does it show anything when you run this query:

SELECT * FROM Products WHERE ProductName = 'lager'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mdixon44
Starting Member

26 Posts

Posted - 2008-05-21 : 21:11:28

I have tried what you have requested.. I am getting the headings but no information in the query table. I have converted the db from Access 97 to Access 2007. Could be a possible conversion problem but how do I know that for sure.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-05-22 : 07:09:49

"I am getting the headings but no information in the query table"....looks like you have a table setup, with no data in it. on an inner join, 1 empty table will return no data for the full query.
maybe experiment and build up the query in stages....select from 1 table 1st, and then move on to adding in a 2nd table and checking the results, etc

in general, you should also try to write your sql like

select everythingIneed
from table1
(inner or left) join table2 on table1col = table2col
(inner or left) join table3 on table1o2col = table3col
where table1or2or3col = somevalueetc

it's more readable then....and you can seperate your fileter clauses out from your join clauses...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 13:09:11

quote:
Originally posted by mdixon44

I have tried what you have requested.. I am getting the headings but no information in the query table. I have converted the db from Access 97 to Access 2007. Could be a possible conversion problem but how do I know that for sure.

If you aren't getting any data back with that simple query, then no data matches your ProductName = 'lager' criteria, hence your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources