Tricky SQL Question
-
I have an Access database for which I'm trying to write a query based on a request from the users. I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value. What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records? ArtworkID / DescriptionText 123 / Motor 234 / Fuse 234 / Start Switch 456 / Stop Switch 789 / Start Switch 789 / Stop Switch 789 / Light Result = ArtworkID 789 Any assistance will be greatly appreciated. Thanks!
I think the follow query would do what you want:
SELECT ArtworkID
FROM Artwork
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2; -
I think the follow query would do what you want:
SELECT ArtworkID
FROM Artwork
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2; -
I think the follow query would do what you want:
SELECT ArtworkID
FROM Artwork
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2;Just happens to work with the example data given. It will show wrong results with duplicated entries, e.g. having two times 357,"Start Switch".
-
I have an Access database for which I'm trying to write a query based on a request from the users. I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value. What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records? ArtworkID / DescriptionText 123 / Motor 234 / Fuse 234 / Start Switch 456 / Stop Switch 789 / Start Switch 789 / Stop Switch 789 / Light Result = ArtworkID 789 Any assistance will be greatly appreciated. Thanks!
You must join the table with itself. Something like:
SELECT a.ArtworkID
FROM Artwork a
INNER JOIN Artwork b
ON a.ArtworkID=b.ArtworkID
WHERE a.DesciptionText="Start Switch" AND b.DescriptionText="Stop Switch" -
Just happens to work with the example data given. It will show wrong results with duplicated entries, e.g. having two times 357,"Start Switch".
You are right. If for some reason you can't prevent duplicate entries, then you can use the following query:
SELECT ArtworkID
FROM (SELECT DISTINCT ArtworkID, DescriptionText FROM Artwork) as t1
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2;It uses a sub-query to get the distinct values first. The rest functions just the same.
-
I think the follow query would do what you want:
SELECT ArtworkID
FROM Artwork
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2; -
You are right. If for some reason you can't prevent duplicate entries, then you can use the following query:
SELECT ArtworkID
FROM (SELECT DISTINCT ArtworkID, DescriptionText FROM Artwork) as t1
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2;It uses a sub-query to get the distinct values first. The rest functions just the same.
-
You must join the table with itself. Something like:
SELECT a.ArtworkID
FROM Artwork a
INNER JOIN Artwork b
ON a.ArtworkID=b.ArtworkID
WHERE a.DesciptionText="Start Switch" AND b.DescriptionText="Stop Switch"Well, that worked. I used 'like' for '=' and surrounded the search text with '%' characters. Now, suppose I wanted to search using up to 5 terms - would I have to nest the inner joins? This seems really complicated for something I can see with my eyes & describe easily using English words. (Also, I tried using the Filter By Form command, but it wouldn't work with my form. Thanks for the feedback.
-
I have an Access database for which I'm trying to write a query based on a request from the users. I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value. What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records? ArtworkID / DescriptionText 123 / Motor 234 / Fuse 234 / Start Switch 456 / Stop Switch 789 / Start Switch 789 / Stop Switch 789 / Light Result = ArtworkID 789 Any assistance will be greatly appreciated. Thanks!
Surely there's an easier way, but this is how I got it to work (I've updated the table and field names):
SELECT *
FROM art_callout_table
WHERE artboardnumber IN (
SELECT artboardnumber FROM art_callout_table
WHERE artboardnumber IN (
SELECT artboardnumber FROM art_callout_table
WHERE lrutext LIKE "%start%")
AND lrutext LIKE "%light%")
AND lrutext LIKE "%switch%";What confused me repeatedly during development, and what confused the users (they were expecting the search to work one way & I was assuming it to work another), is that there are two distinct search methodologies: 1) Using the built-in Access table filtering commands (or a basic SELECT FROM WHERE statement) to narrow down a table of description text values. Repeated filters can be applied to narrow the list further, but if you're looking for "switch" and "start", all you'll get is individual description text records having both values, eg "big red starting switch" or "switch start light" but not "green switch". Not terribly helpful. 2) Use a different search technique to return all ArtworkID values which have "switch" or "start" or "light" strings within their DescriptionText fields, eg don't look just for DescriptionText records similar to "switch start light blinker". I thought this was going to be much easier than it turned out to be. Thanks all for your help.
-
Surely there's an easier way, but this is how I got it to work (I've updated the table and field names):
SELECT *
FROM art_callout_table
WHERE artboardnumber IN (
SELECT artboardnumber FROM art_callout_table
WHERE artboardnumber IN (
SELECT artboardnumber FROM art_callout_table
WHERE lrutext LIKE "%start%")
AND lrutext LIKE "%light%")
AND lrutext LIKE "%switch%";What confused me repeatedly during development, and what confused the users (they were expecting the search to work one way & I was assuming it to work another), is that there are two distinct search methodologies: 1) Using the built-in Access table filtering commands (or a basic SELECT FROM WHERE statement) to narrow down a table of description text values. Repeated filters can be applied to narrow the list further, but if you're looking for "switch" and "start", all you'll get is individual description text records having both values, eg "big red starting switch" or "switch start light" but not "green switch". Not terribly helpful. 2) Use a different search technique to return all ArtworkID values which have "switch" or "start" or "light" strings within their DescriptionText fields, eg don't look just for DescriptionText records similar to "switch start light blinker". I thought this was going to be much easier than it turned out to be. Thanks all for your help.
Good you solved it! :thumbsup:
-
For reasons unknown, this didn't work. My intention was for the search text entries to more resemble wildcard text. For example, return all IDs with "switch" or "cable" or whatever in the DescriptionText field. Thanks for your response.