Re: sq select problem

From: Noor (noor_at_ngsol.com)
Date: 07/05/04 

Date: Mon, 5 Jul 2004 18:49:01 +0500

Try the following query

Create Table link_item_cato(ID int, Item_ID int, Cato_ID int)
Go
insert into link_item_cato values (1,1,1)
Go
insert into link_item_cato values (2,2,2)
Go
insert into link_item_cato values (3,3,1)
Go
insert into link_item_cato values (4,3,2)
Go
insert into link_item_cato values (5,3,3)
Go
insert into link_item_cato values (6,4,1)
Go

SELECT * FROM link_item_cato WHERE cato_id = 1 and ITEM_ID not in (select
ITEM_ID from link_item_cato where CATO_ID <> 1)
Go

Drop table link_item_cato
Go

Thanks
Noor

"sjors broersen" <sjors@.wish888bone.nl if relply strip 888 from my
email-adres> wrote in message
news:40e95766$0$62381$5fc3050@dreader2.news.tiscali.nl...
> can anybody help me with sql problem
>
> if have this table `link_item_cato`:
>
> +-----+---------+---------+
> | id | item_id | c ato_id |
> +-----+---------+---------+
> | 1 | 1 | 1 |
> | 2 | 2 | 2 |
> | 3 | 3 | 1 |
> | 4 | 3 | 2 |
> | 5 | 3 | 3 |
> +-----+---------+---------+
>
> the next thing i want do to is to select the item_id`s where the cats_id`s
> are 1 but not are 2
> then you can make a simple query like this :
>
> SELECT * FROM `link_item_cato` WHERE cato_id = 1 && cato_id != 2
>
> result:
> +-----+---------+---------+
> | id | item_id | c ato_id |
> +-----+---------+---------+
> | 1 | 1 | 1 |
> | 3 | 3 | 1 |
> +-----+---------+---------+
>
> no the problem i don`t want the row with where item_id is 3 because
> somewhere else in the table
> I sad that item_id 3 also belongs to cato_id 2.
>
> suggestions?
>
>
>
>
>
>

Relevant Pages

  • Re: averaging multiple field
    ... Not sure why it's saying that, try clearing out the expression and ... running your query without it to be sure it's not a SQL problem, ... Usually that error comes when you are totaling by a certian field, ... Are you using a "Group By" type of query? ...
    (microsoft.public.access.queries)
  • Re: averaging multiple field
    ... When I run the query without the expressions I get the other field ... expressions set up in the same query that are the same format, ... > running your query without it to be sure it's not a SQL problem, ...
    (microsoft.public.access.queries)
  • sq select problem
    ... can anybody help me with sql problem ... then you can make a simple query like this: ... I sad that item_id 3 also belongs to cato_id 2. ...
    (microsoft.public.sqlserver.clients)
  • sql select problem
    ... can anybody help me with sql problem ... then you can make a simple query like this: ... I sad that item_id 3 also belongs to cato_id 2. ...
    (microsoft.public.sqlserver.datamining)