Probably a SQL Basic I Just dont understand

[simonsoot]

I hope this is placed in the correct forum...I am a novice to the forum and SQL, but using Access for what I thought would be a simple query:

I have two tables, and I have written the below and is successful:

SELECT DISTINCT dbo.imitmidx_sql.item_no, dbo.imitmidx_sql.item_desc_1, dbo.imitmidx_sql.item_desc_2, dbo.bmprdstr_sql.item_no AS Expr1
FROM dbo.imitmidx_sql INNER JOIN
dbo.bmprdstr_sql ON dbo.imitmidx_sql.item_no = dbo.bmprdstr_sql.comp_item_no
WHERE (dbo.imitmidx_sql.activity_cd = 'A') AND (dbo.imitmidx_sql.item_no LIKE 'F%') AND (NOT (dbo.imitmidx_sql.item_desc_1 LIKE 'M%')) AND (NOT (dbo.imitmidx_sql.item_desc_1 LIKE '%KIT%')) AND (NOT (dbo.imitmidx_sql.item_desc_1 LIKE '%PACK%')) AND (NOT (dbo.imitmidx_sql.item_desc_2 LIKE '%KIT%')) AND (NOT (dbo.imitmidx_sql.item_desc_2 LIKE '%PACK%')) AND (dbo.bmprdstr_sql.comp_item_no LIKE 'F%') AND (dbo.bmprdstr_sql.activity_fg = 'A')

the step I can not complete is to utilize the results of the query, and take the column of dbo.bmprdstr_sql.item_no and use that column to display dbo.imitmidx_sql.item_desc_1 and dbo.imitmidx_sql.item_desc_2

so if this is unclear, I have the results of the above query, but I want is to add two more columns that display the item description 1&2 but the data is from the second table.

I am sure this is fairly basic, but after many a Google searches, I have failed and any help is appreciated. thanks!

 

[PHV]

Something like this ?
SELECT DISTINCT I1.item_no, I1.item_desc_1, I1.item_desc_2, B.item_no, I2.item_desc_1, I2.item_desc_2
FROM dbo.imitmidx_sql I1
INNER JOIN dbo.bmprdstr_sql B ON I1.item_no = B.comp_item_no
INNER JOIN dbo.imitmidx_sql I2 ON B.item_no = I2.item_no
WHERE I1.activity_cd = 'A' AND I1.item_no LIKE 'F%'
AND I1.item_desc_1 NOT LIKE 'M%' AND I1.item_desc_1 NOT LIKE '%KIT%' AND I1.item_desc_1 NOT LIKE '%PACK%'
AND I1.item_desc_2 NOT LIKE '%KIT%' AND I1.item_desc_2 NOT LIKE '%PACK%'
AND B.comp_item_no LIKE 'F%' AND B.activity_fg = 'A'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[simonsoot]

PHV,

Thank you for your reply! After a better understanding if what the additional INNER JOIN did, I was able to alter and get what I want!

thank you very much!