need help on writing a sub-query
-
Hi,
I have a table product_cities that has product_id+city_id as its key.
I need to find all products that are listed within a selected list of cities. The product should be listed in each and every city selected.
Suppose I select Boston, Chicago and Denver, the select should list all products that is listed in each of these cities.
Hope I made myself clear.
And thx for all the help.
Vis.
-
So
select product, city
from product_cities
where city = 'boston' or city = 'chicago' or city = 'denver'
is not what you want? (I know you're using IDs, but the theory's the same) Can you give an example of what you'd like the output to look like?
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
If you are trying to determine products that are IN Boston AND IN Chicago AND IN Denver you will need to create three DISTINCT sub-queries
FROM (SELECT Product FROM Product_Cities WHERE City = 'Boston') 1
INNER JOIN (SELECT Product FROM Product_Cities WHERE City = 'Chicago') 2 ON 1.ID = 2.ID
INNER JOIN (SELECT Product FROM Product_Cities WHERE City = 'Denver') 3 ON 1.ID = 3.ID
AFAIK this is the only way to determine that the product is in ALL the cities NOT JUST in ONE city.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Actually this is much simpler :
select productid
from dbo.product_cities
where city in ('boston', 'chicago', 'denver')
GROUP by productid
having count(*) = 3 --number of cities in the in clause.
-
Hi,
Thx for the tip. Just to complicate it a little further, if each product can have multiple start and end dates in each city and I keyed in the start date to make it product_id+city_id+start_date, how can I modify the above query to still get my answer.
Thx.
Vis.
-
never mind, I think if I put a max(start_date) to it, it should do the trick. Thx anyway. U guys are great.
Vis.
-
Depends how the EndDate is defined... Assuming that it's defined to null for the active products, then :
Select ...
where DateEnd is null
group by...
if it's set to a time way far in the future then :
where GetDate() between StartDate and EndDate
-
Hi,
Ran into a glitch. When I included the start_date, had to include the city_id too and then then count() part is no longer valid. So, I am still searching for the answer. Any help is appreciated.
Thx.
Vis.
-
Can you post the query you are using?
This is something you must do with a derived table, then inner join... if I understand what you need correctly.
Could you also post the expected results vs what you are getting now?
-
Hi Remi,
Finally got it. I re-wrote to use sub-queries. Here's a sample :
select distinct prod_id
from prod_cities a
where a.city_id in (207,208)
and exists (select city_id from prod_cities
where city_id = 207
and a.prod_id = prod_id)
and exists (select city_id from prod_cities
where city_id = 208
and a.prod_id = prod_id)
Thx for the support. I can sleep tonight.
Vis.
-
Same query, usable for any amount of cities :
select prod_id
from dbo.prod_cities
where city_id in (207,208)
GROUP by prod_id
having count(*) = 2 -- number of cities in the in list
But where do you take into consideration the time period?
-
Finally, here's a version where you can search for all occurances, or 1, or any number you want. The list of wanted items is passed as a string so you can use it in a proc
.IF Object_id('fnSplit_Set') > 0
DROP FUNCTION fnSplit_Set
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter)) - PkNumber - len(@vcDelimiter)) as EachID
FROM dbo.Numbers
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
IF Object_id('Numbers') IS NULL
BEGIN
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
Declare @i as int
set @i = 0
while @i = @ItemsCount
)
ORDER BYName
SET NOCOUNT OFF
GO
--find all items
Exec dbo.SearchColumns 'name,id', 2-- 4 row(s) affected
Exec dbo.SearchColumns 'name,id,indid', 3-- 1 row(s) affected
Exec dbo.SearchColumns 'id', 1-- 12 row(s) affected
--find at least 2
Exec dbo.SearchColumns 'name,id,indid', 2-- 5 row(s) affected (instead of 1)
--find at least 1
Exec dbo.SearchColumns 'name', 1-- 10 row(s) affected
Exec dbo.SearchColumns 'name,indid', 1-- 11 row(s) affected
GO
DROP PROCEDURE SearchColumns
--DROP FUNCTION fnSplit_Set
GO
-
Hi,
Thx for the help again. But, my boss wants a sql solution. And in the previous case, since there are multiple start and end dates and we can't exactly predict how many a city will have, the having count(*) will not work, unless there is someway to ignore the dates and just count the cities. In the solution that I had posted, in fact the IN clause is not required. Just the EXISTS for the occurence of each city will do. The only problem with that is the sql then becomes dynamic depending on the no. of cities in the list. How do we make that solution generic or I need to find another one like the one provided by you (HAVING COUNT() = x).
One fix that I am looking at currently is to generate a sql query on the fly to list DISTINCT products with an EXISTS for each city in the list in a sub-query. May sound crazy but running out of time.
Maybe, I will show the code in your previous mail and try to convince the guys involved.
Once again, call tell u how much I appreciate all the help.
Vis.
-
Read my previous post carefully, execute the code one part at the time if you need to. This can handle any number of cities at the same time. You can also decide how many cities it takes to have a hit (if you supply 5 cities in the list, you can ask the server to send any product that is in 3 cities (@ItemsCount)).
This is an ultra fast static sql that you won't be able to beat with your proposed method.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply