SQL select all but not if it is already in an other column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQL select all but not if it is already in an other column
# 1  
Old 02-10-2010
SQL select all but not if it is already in an other column

I know I know..
for sure one of the easier mysql statements. But somehow I can not figure out this.

I expect to see all distinct items of 'data_12' where 'kwroot' has 'straxx' in, and in the same row 'data_12' ist (not = 'kwsearched' in any existing row)

Code:

data_12                   kwroot        kwsearched 
straxx password           straxx          straxx
xxxx                     yyy            straxx password

Code:
SELECT data_12, kwroot, kwsearched
FROM sc
WHERE kwroot LIKE 'straxx'
AND (data_12 NOT like kwsearched)
GROUP BY data_12

Result:
Code:
data_12                   kwroot        kwsearched 
straxx password        straxx          straxx

I see this statement works correct, but how can I tell him to check that "AND (data_12 NOT like kwsearched)"<== but also not in whole 'kwsearched'??

I expect the result to be empty
# 2  
Old 02-11-2010
If I understand correctly:
Code:
SELECT data_12, kwroot, kwsearched
FROM sc
WHERE kwroot = 'straxx'
AND data_12 NOT IN (
  SELECT DISTINCT kwsearched
  FROM sc
  );

or:

Code:
SELECT a.data_12, a.kwroot, a.kwsearched
FROM sc a
LEFT JOIN sc b
ON a.data_12 = b.kwsearched
WHERE a.kwroot = 'straxx'
AND b.kwsearched is NULL;


Last edited by radoulov; 02-11-2010 at 08:05 AM..
# 3  
Old 02-11-2010
Yes, works great!
Many thanx!

EDIT:

Yes works great, but now, when I use this on about 2000 rows of data I get this: "Database has gone away..."

or the statement takes more than one minute to execute for both examples.

Is there something I can do to optimize?

Last edited by lowmaster; 02-11-2010 at 09:28 PM..
# 4  
Old 02-12-2010
Could you provide the table structure (desc tablename;)?
Which version of MySQL server you are using?
# 5  
Old 02-13-2010
hi radoulov,

this is the structure:
I added a short part to your statement to make work better for me into:
Code:
SELECT data_12 FROM sc 
WHERE kwroot LIKE '$term' and kwsearched like '$term'
and data_12 not in (select kwsearched from sc) 
GROUP BY data_12 
ORDER BY relevance_13 DESC 
LIMIT 1;

mysql 5.1.36

Code:
+------------------------+-------------+------+-----+---------------------+----------------+
| Field                  | Type        | Null | Key | Default             | Extra          |
+------------------------+-------------+------+-----+---------------------+----------------+
| id                     | int(11)     | NO   | PRI | NULL                | auto_increment |
| kwroot                 | varchar(50) | YES  |     | NULL                |                |
| kwsearched             | varchar(50) | YES  |     | NULL                |                |
| period                 | varchar(15) | YES  |     | NULL                |                |
| datamonth              | varchar(10) | YES  |     | NULL                |                |
| type                   | varchar(4)  | YES  |     | NULL                |                |
| m1_0                   | varchar(4)  | YES  |     | NULL                |                |
| m2_1                   | varchar(4)  | YES  |     | NULL                |                |
| m3_2                   | varchar(4)  | YES  |     | NULL                |                |
| m4_3                   | varchar(4)  | YES  |     | NULL                |                |
| m5_4                   | varchar(4)  | YES  |     | NULL                |                |
| m6_5                   | varchar(4)  | YES  |     | NULL                |                |
| m7_6                   | varchar(4)  | YES  |     | NULL                |                |
| m8_7                   | varchar(4)  | YES  |     | NULL                |                |
| m9_8                   | varchar(4)  | YES  |     | NULL                |                |
| m10_9                  | varchar(4)  | YES  |     | NULL                |                |
| m11_10                 | varchar(4)  | YES  |     | NULL                |                |
| m12_11                 | varchar(4)  | YES  |     | NULL                |                |
| data_12                | varchar(50) | YES  |     | NULL                |                |
| relevance_13           | varchar(20) | YES  |     | NULL                |                |
| monthly2_14            | varchar(13) | YES  |     | NULL                |                |
| monthly_avg2_15        | varchar(15) | YES  |     | NULL                |                |
| searched_this_month_16 | varchar(15) | YES  |     | NULL                |                |
| monthly_avg_17         | varchar(15) | YES  |     | NULL                |                |
| competitor_18          | varchar(10) | YES  |     | NULL                |                |
| curr_19                | varchar(5)  | YES  |     | NULL                |                |
| euro_20                | varchar(4)  | YES  |     | NULL                |                |
| cent_21                | varchar(2)  | YES  |     | NULL                |                |
| micricent_22           | varchar(10) | YES  |     | NULL                |                |
| avg_pos_23             | varchar(5)  | YES  |     | NULL                |                |
| f1_24                  | varchar(4)  | YES  |     | NULL                |                |
| f2_25                  | varchar(4)  | YES  |     | NULL                |                |
| f3_26                  | varchar(4)  | YES  |     | NULL                |                |
| maxmonth_27            | varchar(4)  | YES  |     | NULL                |                |
| minus_kw_28            | varchar(50) | YES  |     | NULL                |                |
| how_exact_29           | varchar(5)  | YES  |     | NULL                |                |
| created                | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| cur_timestamp          | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
+------------------------+-------------+------+-----+---------------------+----------------+
38 rows in set (0.01 sec)


Last edited by lowmaster; 02-13-2010 at 07:01 AM..
# 6  
Old 02-13-2010
In order to speed up the query you should consider adding an index (or indexes).
What index you should add, depends on your data: you should start by indexing the most selective column(s) used in the where clause.

I would start with something like this:

Code:
create index sc_kwr_dt_i on sc(kwroot, data_12);
create index sc_ksc on sc (kwsearched);

You should consider indexing only subset of the data, if that subset is selective.
You should also consider the space needed for your indexes and their impact on the DML operations.

If the query is slow after indexing (some of) the columns in the where clause, post the output of the following statement:
Code:
explain select data_12 FROM sc ...

I would verify the performance of both versions (in vs outer join) after creating the indexe(s).

Could you post also the output of the following command:

Code:
show create table sc;


Last edited by radoulov; 02-13-2010 at 12:53 PM..
# 7  
Old 02-14-2010
radoulov, you are the greatest...!

I did exactly what you said. And now, on over 200.000 rows so far, this query takes about 0.01 sek now...

Compared to about a minute and more before, this is a plus, I would say.


SmilieSmilieSmilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parse SQL text and only format first SELECT statement.

Hi Forum. Need your expertise on the following question. I have the following file which I would like to parse, find first block of SELECT statment and concatenate all input fields as 1 field (~ delimited): Old File: SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */ ... (5 Replies)
Discussion started by: pchang
5 Replies

2. Programming

Sql select replace

Hi All, I had a query related to sql select replace command. i have a table named clusters and it looks like this Code: name characteristics sample 1.1 parent sample 1.2 ... (2 Replies)
Discussion started by: siya@
2 Replies

3. Programming

[Solved] SQL SELECT REPLACE

Hi All, I had a query related to sql select update replace command. i have a table named clusters and it looks like this name model characteristics sample1.1 +123 parent sample1.2 -456 clone sample1.3 +122 ... (5 Replies)
Discussion started by: sonia102
5 Replies

4. Programming

I'm going crazy with SQL select. Please help!

Hello people, as wrote in title I'm going crazy with a "complex" SQL select. This is the seelct: select T_ADDRESS, T_MCC,T_MNC,T_MSIN,T_IM_MNC, COUNT(*) FROM TABLETEST WHERE T_MCC=123 AND (T_MNC=11 OR T_MNC=01) GROUP BY T_ADDRESS,T_MCC,T_MNC,T_MSIN,T_IM_MNC HAVING count(*) > 5;This select... (7 Replies)
Discussion started by: Lord Spectre
7 Replies

5. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

6. Shell Programming and Scripting

Select SQL Queries Option

count.sh#!/bin/ksh SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3` SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3` LIST="Count Select_All" select i in $LIST do if then echo $SQL1 elif then echo $SQL2 fi done (2 Replies)
Discussion started by: killboy
2 Replies

7. Shell Programming and Scripting

SQL Select and awk

Dear All , I have file1.txt contain values like the following: ---------- 23 24 25 and I have shell script which has the following : more file1.txt | awk '{print "select 'DUMP',CODE1||'|'||CODE2||'|'||CODE3 from CODE where CODE1='" $1 "';"}' > file2.sql all I need is to have the... (6 Replies)
Discussion started by: habuzahra
6 Replies

8. Web Development

SQL Select inside Insert

I have following. . . . $userid = 2 . $query = "select username from users where userid = ".$userid.";"; . $username = $line; $data="Some Data Here"; . $query = "insert into logger (username, data) valuse ($username, $data);"; . I would like to not have 2 database calls. (3 Replies)
Discussion started by: Ikon
3 Replies

9. Shell Programming and Scripting

formatting the sql select result

Hi, I have about 12 columns and 15 rows to be retrived from sybase isql command through unix. But when i output the sql into a file and see it, the formatting is going for a toss. can someone please suggest how can i get the result correctly in the output file ? Thanks, Sateesh (2 Replies)
Discussion started by: kotasateesh
2 Replies

10. UNIX for Dummies Questions & Answers

Pipe SQL select statement results to script

Hello I would like to perform a select from a oracle table and return those values to my shell script For example: site=head -1 $infile | cut -c1-15 | awk '{printf "s%", $0} sqlplus -s /nolog |& #Open pipe to sql select col1, col2, col3, col4 from oracle_table where col5 =... (6 Replies)
Discussion started by: houtakker
6 Replies
Login or Register to Ask a Question