Transform SQL-Query to not use subqueries Post: 302974948

Sponsored Content

Top Forums Programming Transform SQL-Query to not use subqueries Post 302974948 by stomp on Monday 6th of June 2016 01:39:02 PM

06-06-2016

Registered User

Transform SQL-Query to not use subqueries

Hi,

I have a working SQL-Query here, in which I'm using subqueries to get the needed result.

The idea behind this, is to get a list of networks which have 2 tags set in the database. Because of the way it is stored, i get a tuple for each Tag, so I get a duplicate of all networks.

The Tables and the query:

Code:

Table: IPv4Network
  id,ip,name,comment

Table: TagStorage(network_id -> tag_id like 1 -> n)
  tag_id, network_id 

Table: TagTree
  id, tag

select ip,mask,name,tag
from IPv4Network 
inner join TagStorage         on     IPv4Network.id = network_id 
inner join TagTree         on         tag_id = TagTree.id 
where 
   ip in (select ip from IPv4Network 
        inner join TagStorage on IPv4Network.id = network_id 
        inner join TagTree on tag_id = TagTree.id where tag="AUTO_PROVISIONING") 
    and ip in (select ip from IPv4Network 
           inner join TagStorage on IPv4Network.id = network_id
           inner join TagTree on tag_id = TagTree.id where tag="SALES")
group by ip;

The result without the subqueries:

Code:

+------------+------+---------+-------------------+
| ip         | mask | name    | tag               |
+------------+------+---------+-------------------+
| 1111111512 |   26 | AUTO_01 | AUTO_PROVISIONING |
| 1111111576 |   26 | AUTO_02 | AUTO_PROVISIONING |
| 1111111640 |   26 | AUTO_03 | AUTO_PROVISIONING |
| 1111111704 |   26 | AUTO_04 | AUTO_PROVISIONING |
| 1111111512 |   26 | AUTO_01 | SALES             |      
| 1111111576 |   26 | AUTO_02 | SALES             |      
| 1111111640 |   26 | AUTO_03 | SALES             |      
| 1111111704 |   26 | AUTO_04 | SALES             |      
+------------+------+----------------+------------+

Subqueries are evil in terms of performance. The last time I worked with this, I wrapped it in some lines of code and sorted out the duplicates.

I'm wondering if there's not a more clever alternative in plain SQL, which is not such a performance killer than the subqueries.

Thanks in advance.

Last edited by stomp; 06-06-2016 at 02:48 PM..

stomp

View Public Profile for stomp

Find all posts by stomp

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

& in SQL query

I have a script that looks for all jobs that contain a particular calendar. Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this? I have tried: select job_name from job where run_calendar='1&15dom' select job_name from job...

2. Shell Programming and Scripting

rsh and sql query

Hi ... I am doing a switch user and then rsh and then running a sql query . I am successfull in rsh and logging into the database , but my query doesnt run .. Here's the command : su - linus -c "rsh -l linus psmf ORACLE_SID=SMP;export ORACLE_SID;sqlplus...

3. UNIX and Linux Applications

SQL Lite query

Hello Everyone, I am looking to write a script that will run on many machines in a network at the same time. They need to write a result to a common location. I plan to use a SQLlite database as this common writing point. But the concern I have is how SQLlite will react to multiple writes that...

4. Shell Programming and Scripting

sql query problem

Hi, I am passing an argument for the script and that argument values should exist in database. bill_period_input="'""$1""'" bill_period=`sqlplus uname/pwd@dbname <<eof! set verify off set heading off set feedback off select bill_period from bill_period_ref where...

5. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s...

6. Shell Programming and Scripting

create sql query

7. Shell Programming and Scripting

problem in SQL query

I used the following code code select * from tablename where columnname Instead of printing the expected output it prints all the files in the present directory since there is a "*" in the code. Is there any way to overcome the problem? Thanks Ananth

8. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that...

9. Programming

Getting error in sql query

Hi All , I have tried many times am getting syntax error on 'UNION' can anybody tell me ... INSERT INTO table1 ( Type , num_items , num_letters , total_value ) (select type='1', num_items, num_letters=count(*), total_value=sum(letter_value) from table2 where num_items = 1

10. Programming

Help writing SQL query

Hello All, I hope I'm posting this in the right section. I have zero sql query writing skill, in fact, I've never done it before, but for some reason, a request came across my desk to get information from one of our databases. I have about 200 ticket numbers that have no information attached,...

LEARN ABOUT SUSE

tk_nameofjoinstyle

Tk_GetJoinStyle(3)					       Tk Library Procedures						Tk_GetJoinStyle(3)

__________________________________________________________________________________________________________________________________________________

NAME

       Tk_GetJoinStyle, Tk_NameOfJoinStyle - translate between strings and join styles

SYNOPSIS

       #include <tk.h>

       int
       Tk_GetJoinStyle(interp, string, joinPtr)

       const char *
       Tk_NameOfJoinStyle(join)

ARGUMENTS

       Tcl_Interp *interp (in)		 Interpreter to use for error reporting.

       const char *string (in)		 String containing name of join style: one of "bevel", "miter", or "round".

       int *joinPtr (out)		 Pointer to location in which to store X join style corresponding to string.

       int join (in)			 Join style: one of JoinBevel, JoinMiter, JoinRound.
_________________________________________________________________

DESCRIPTION

       Tk_GetJoinStyle places in *joinPtr the X join style corresponding to string, which will be one of JoinBevel, JoinMiter, or JoinRound.  Join
       styles are typically used in X graphics contexts to indicate how adjacent line segments should be joined together.  See the X documentation
       for information on what each style implies.

       Under  normal circumstances the return value is TCL_OK and interp is unused.  If string does not contain a valid join style or an abbrevia-
       tion of one of these names, then an error message is stored in interp->result, TCL_ERROR is returned, and *joinPtr is unmodified.

       Tk_NameOfJoinStyle is the logical inverse of Tk_GetJoinStyle.  Given a join style such  as  JoinBevel  it  returns  a  statically-allocated
       string corresponding to join.  If join is not a legal join style, then "unknown join style" is returned.

KEYWORDS

       bevel, join style, miter, round

Tk																Tk_GetJoinStyle(3)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

& in SQL query

Discussion started by: Lindarella

2. Shell Programming and Scripting

rsh and sql query

Discussion started by: sars

3. UNIX and Linux Applications

SQL Lite query

Discussion started by: garric

4. Shell Programming and Scripting

sql query problem

Discussion started by: ss_ss