sql stmt

sql stmt

Post by cappycaf » Mon, 03 Jun 2002 17:35:52



Hi,

Table A
----------------------------------------------------
Index    circle         color        Number
---------------------------------------------------
1           circle        pink             3
2           square      whiet           1
3           circle         pink           3
4           circle         pink           3
5           circle         pink           3
6           square       blank          7

etc..

I have an "A" table with those data and I'd like to find indexes which have
a same value for circle, color, and number field.
(For this table, the output will be index 1, 3, 4, and 5.)
Also, values for circle, color, and number are various, so I don't think I
can use 'IN' operator in a sql statement.
What should I write for the conditions and filters clause in order to fetch
the data that I want?

Thank you in advance.

SELECT index
   FROM A
 WHERE  ?

-cappycafe

 
 
 

sql stmt

Post by Dejan Sark » Mon, 03 Jun 2002 19:03:44


Hi!

Having the DDL and Insert statements would help us to try to solve your
problem faster and easier. Here is a solution:

SELECT DISTINCT a1.[index]
  FROM a a1 INNER JOIN a a2
        ON a1.circle=a2.circle AND
           a1.color=a2.color AND
           a1.number=a2.number
 WHERE a1.[index] <> a2.[index]

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> Hi,

> Table A
> ----------------------------------------------------
> Index    circle         color        Number
> ---------------------------------------------------
> 1           circle        pink             3
> 2           square      whiet           1
> 3           circle         pink           3
> 4           circle         pink           3
> 5           circle         pink           3
> 6           square       blank          7

> etc..

> I have an "A" table with those data and I'd like to find indexes which
have
> a same value for circle, color, and number field.
> (For this table, the output will be index 1, 3, 4, and 5.)
> Also, values for circle, color, and number are various, so I don't think I
> can use 'IN' operator in a sql statement.
> What should I write for the conditions and filters clause in order to
fetch
> the data that I want?

> Thank you in advance.

> SELECT index
>    FROM A
>  WHERE  ?

> -cappycafe


 
 
 

sql stmt

Post by Joe Celk » Mon, 03 Jun 2002 23:01:44


Read the note at the bottom of this posting.  Is this what you meant?  

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
 shape CHAR(8) NOT NULL,
 color CHAR(8) NOT NULL,
 foo_nbr INTEGER NOT NULL);

Did you want to get all the subsets with more than one element?  Or did
you want to specify the values of (shape, color, foo_nbr)?   Based on
your narrative and sample data, you can use:  

SELECT F1.foo_id, F1.shape, F1.color, F1.foo_nbr
  FROM Foobar AS F1
 WHERE foo_id
       IN (SELECT F2.foo_id
             FROM Foobar AS F2
            GROUP BY shape, color, foo_nbr
           HAVING COUNT(*) > 1)
 ORDER BY shape, color, foo_nbr;

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

sql stmt

Post by Uri Diman » Tue, 04 Jun 2002 17:33:57


try it
SELECT *
  FROM #ww AS a
 WHERE index1
       IN (SELECT b.index1
             FROM #ww AS b join #ww a on
            a.index1 <> b.index1
            and a.color=b.color and a.number=b.number)


Quote:> Read the note at the bottom of this posting.  Is this what you meant?

> CREATE TABLE Foobar
> (foo_id INTEGER NOT NULL PRIMARY KEY,
>  shape CHAR(8) NOT NULL,
>  color CHAR(8) NOT NULL,
>  foo_nbr INTEGER NOT NULL);

> Did you want to get all the subsets with more than one element?  Or did
> you want to specify the values of (shape, color, foo_nbr)?   Based on
> your narrative and sample data, you can use:

> SELECT F1.foo_id, F1.shape, F1.color, F1.foo_nbr
>   FROM Foobar AS F1
>  WHERE foo_id
>        IN (SELECT F2.foo_id
>              FROM Foobar AS F2
>             GROUP BY shape, color, foo_nbr
>            HAVING COUNT(*) > 1)
>  ORDER BY shape, color, foo_nbr;

> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

sql stmt

Post by cappycafe cappycaf » Wed, 05 Jun 2002 00:49:53


Thank you.
It works!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

sql stmt

Post by cappycaf » Wed, 05 Jun 2002 01:03:50


Thank you.
It works!


Quote:> try it
> SELECT *
>   FROM #ww AS a
>  WHERE index1
>        IN (SELECT b.index1
>              FROM #ww AS b join #ww a on
>             a.index1 <> b.index1
>             and a.color=b.color and a.number=b.number)

 
 
 

1. sql:batch vs sql:stmt

ss 7.0 sp3

i'm trying to track down some some problems using the profiler.
i'm just looking at the sql:batchcompleted and sql:stmtcompleted events.

why do i see a ton of sql:batchcompleted events that have no associated
sql:stmtcompleted events?  the text shown for the sql:batchcompleted
event is a plain old select statement so why isn't there a
sql:stmtcompleted event for that same statement?  some queries do give
me both the stmt and batch events, but the majority only give me a batch
event.  all of the queries are coming from different apps using ado.

charlie

2. Pervasive.SQL 2000 and Maximizer

3. Calling procedures from PL/SQl functions (called from a SQL stmt)

4. db2audit extract

5. SQL stmt problem. ( SUM )

6. simple SQL Query .. or is it !

7. SQl stmt.

8. Counts in a sql statement

9. result set in dynamic sql stmt

10. Qualifying table name in SQL stmt

11. Which SQL Stmt is better and quicker?

12. Request SQL Stmt Tuning Help