Query syntax help required: JOIN? Sub-query? Which? 1

[iannorthwood]

I have 3 tables: one holds details of task requests (T_Request), one holds application details (T_App) and the last holds application vendor details(T_Vendor), the last to avoid users trying to use non-standard or shortened names e.g. 'MS' instead of 'Microsoft'

I'm trying to create a query on T_Request which will return all requests with apps from a particular vendor. Relevant structure details are:

T_Request
=========
RequestID, AppID

T_App
=====
AppID, AppVendorID, AppName

T_Vendor
========
VendorID, VendorName

T_Request.AppID will contain numbers from T_App.AppID, T_App.AppVendorID will contain numbers from T_Vendor.VendorID.

So, I want a query which will allow users to enter a 'fuzzy' vendor name and see all requests for apps with that vendor:

"Show me all requests from a vendor whose name begins with 'Aspect'"

My attempt:
<code>
SELECT
T_Request.RequestID,
T_Request.RequestAppID,
T_Request.RequestedBy,
T_Request.RequestedOn,
T_Request.RequestRequiredBy,
T_Request.RequestStatusID,
T_Request.RequestPackagerID,
T_Request.RequestSignedOffOn,
T_Request.RequestSignedOffBy,
T_Request.RequestTypeID,
T_Request.RequestPriorityID,
T_Request.RequestReleaseLocation
FROM
T_Request,
T_App
WHERE
RequestAppID
=
(SELECT AppID FROM T_App WHERE AppVendorID = (SELECT VendorID FROM T_Vendor WHERE VendorName LIKE 'Aspect*'))
</code>

Would it help to rename the fields so that they match, e.g. rename T_App.AppVendorID to T_App.VendorID? I did that to avoid having to prefix field names with table names in queries used elsewhere but the project is at an early stage so it wouldn't be too much trouble to change them.

Lastly, please bear in mind that I am a complete SQL novice so would appreciate actual 'code' rather than "Use the SQL [blah] command"-type advice.

 

[AlexCuse]

Hi Ian,

The way I would approach this is to add another join to your vendor table. Then filter (within this second join) by using VendorName LIKE in your join conditions.

For what its' worth, your query may have worked if you used RequestAppID IN rather than =. You will notice I specified the joins in this query, only because I find the other way of doing it rather clunky.

Anyways, try this:

[COLOR=blue]SELECT[/color]
T_Request.RequestID,
T_Request.RequestAppID,
T_Request.RequestedBy,
T_Request.RequestedOn,
T_Request.RequestRequiredBy,
T_Request.RequestStatusID,
T_Request.RequestPackagerID,
T_Request.RequestSignedOffOn,
T_Request.RequestSignedOffBy,
T_Request.RequestTypeID,
T_Request.RequestPriorityID,
T_Request.RequestReleaseLocation
    [COLOR=blue]FROM[/color]
T_Request
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
T_App
[COLOR=blue]ON[/color] T_Request.AppID = T_App.App_ID
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
T_Vendor
[COLOR=blue]on[/color] T_App.VendorID = T_Vendor.VendorID
and T_Vendor.VendorName like [COLOR=red]'Aspect%'[/color]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[iannorthwood]

Wow! Awesomely quick response, thanks!

However, the JOIN query results in an error message (even with your typos corrected):

Syntax error (missing operator) in query expression 'T_Request.AppID = T_App.AppID INNER JOIN T_Vendor on T_App.AppVendorID = T_Vendor.VendorID'

Replacing '=' with 'IN' in my original query kind of worked but only returned 2 records instead of 4. Given that the VendorID is noted the same in all 4 records, I'm stumped.

 

[iannorthwood]

Apologies: forget my comment about the number of records: a colleague had deleted the other 2. D'oh! Looks like the simple substitution of IN for = will work for me. Thanks again.

 

[AlexCuse]

That sounds like an MS Accesss error. Are you sure you're in the right forum? Access usually wants all kind of wacky parentheses when you do multiple joins it seems. If it is Access, your best bet next time would be forum701

Anyway, glad you got it working

Ignorance of certain subjects is a great part of wisdom

 

[iannorthwood]

It *is* Access, you're right, but I want to keep the SQL generic so that I can migrate when the wasters in my client's IT department finally get me a SQL Server to migrate it to.

Many thanks again for your help.

 

[aspvbnetnerd]

It should work putting the parentheses like this.

[COLOR=blue]SELECT[/color]
T_Request.RequestID,
T_Request.RequestAppID,
T_Request.RequestedBy,
T_Request.RequestedOn,
T_Request.RequestRequiredBy,
T_Request.RequestStatusID,
T_Request.RequestPackagerID,
T_Request.RequestSignedOffOn,
T_Request.RequestSignedOffBy,
T_Request.RequestTypeID,
T_Request.RequestPriorityID,
T_Request.RequestReleaseLocation
    [COLOR=blue]FROM[/color]
T_Request
[COLOR=blue][COLOR=red]([/color]INNER[/color] [COLOR=blue]JOIN[/color]
T_App
[COLOR=blue]ON[/color] T_Request.AppID = T_App.App_ID[COLOR=red])[/color]
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
T_Vendor
[COLOR=blue]on[/color] T_App.VendorID = T_Vendor.VendorID
and T_Vendor.VendorName like [COLOR=red]'Aspect%'[/color]

 

[AlexCuse]

aspvbnetnerd (George?)

Right idea, but your parentheses is just one line off. Should look like this:

(T_Request
INNER JOIN
T_App
ON T_Request.AppID = T_App.App_ID)

iannorthwood - you might be better off using Joins (rather than IN) from a performance perspective. If your intention is to use generic syntax that will port to SQL Server easily, you may consider checking the ANSI-92 syntax option in Access' settings. This should help with *some* of the differences between Jet SQL and T-SQL.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[aspvbnetnerd]

Alex, you are absolutely right, with those parentheses it should work for him.

George

 

[iannorthwood]

LOL...switched to ANSI-92, pasted in my query. Result?

JOIN expression not supported



I'll just code in a conditional in my project so that, if the target is MDB, it'll use what I have now and, if it's SQL Server, use the "correct" syntax.

 

[AlexCuse]

With ANSI-92 I don't think you would need the parentheses (if you were using them). I have never use that option, because I got used to all the differences before it was available to me ;-) Sorry I can't be of more help

Alex

Ignorance of certain subjects is a great part of wisdom