Pulling Records from Three Tables 1

[byrne1]

I need help in creating a SQL statement that will pull records from three tables. The three tables that I have are as follows:

ITEMS
- Itemnbr
- DepartmentNum
- VendorNum

ITMDESCR
- Itemnbr
- Description1

VENDOR
- VendorNum
- Name

I need a query that will pull itemdesc.description1, item.vendornum and vendor.name for all items in the item table whose departmentnum=61. I need the resulting set grouped by itemdesc.description1.

Everything I've tried so far has failed. I either don't get what I need or I get a SQL query that times out.

TIA!!!

 

[lespaul]

How's this:

SELECT ID.description1, I.vendornum, V.name FROM ITEMS I INNER JOIN ITEMDESC ID ON I.Itemnbr = ID.Itemnbr INNER JOIN VENDOR V ON I.VendorNum = V.VendorNum WHERE DepartmentNum = 61

I must point out however, that in most cases when you see a field named 'Description1', it is a red flag that the tables are not normalized. You may want to review 'The Fundamentals of Relational Database Design'


Leslie

 

[r937]

select ITMDESCR.description1
     , ITEMS.vendornum 
     , VENDOR.name 
  from items
inner
  join ITMDESCR  
    on ITEMS.Itemnbr
     = ITMDESCR.Itemnbr
inner
  join VENDOR  
    on ITEMS.VENDORNum
     = VENDOR.VENDORNum     
 where ITEMS.departmentnum = 61  
order
    by ITMDESCR.description1

if you prefer html to word docs (i know i do, which is why i got permission from paul litwin to mirror it), you can also read the same article on the web here -- Fundamentals of Relational Database Design

rudy

http://r937.com/

SQL Consulting

 

[byrne1]

Ba-da-bing!

That did the trick. Thank you very much!