Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: 'Backreferencing' in SQL?
Top Forums Programming 'Backreferencing' in SQL? Post 302307179 by Housni on Tuesday 14th of April 2009 09:44:22 PM
Old 04-14-2009
'Backreferencing' in SQL?
My SQL is very rust and I'm having a problem with a query.
First, here are the tables involved.

Code:
Table `os`:
+--------------------------------+
| id | distro       | version    |
+--------------------------------+
| 1  |  CentOS      |  5.2       |
| 2  |  RHEL        |  5         |
| 3  |  RHEL        |  4         |
+--------------------------------+


Table `admins`:
+----------------+
| id |    title  |
+----------------+
| 1  | Trovalds  |          
| 2  | Stallman  |
| 3  | Housni    |
+----------------+


Table `servers`:
+------------------------+
| id |  title   |  os_id |
+------------------------+
| 1 |  Portal   |  1     |
| 2 |  Staging  |  1     |
| 3 |  Live     |  3     |
+------------------------+


I have a table `server_admin` which holds two foreign keys: server_id and admin_id
+----------------------+
| server_id | admin_id |
+----------------------+
|     1     |    1     |
|     1     |    3     |
|     1     |    4     |
|     2     |    7     |
|     3     |    2     |
+----------------------+



I know the query below has a syntax error but I'm using it here to illustrate what I want to do.
I want `servers`.`title` of the AND clause for the nested select to match with `origname`.
What I want to achieve is something similar to backreferencing in Regular Expressions.
Code:
SELECT CONCAT_WS( ' - ', `servers`.`title` AS `origname`, (
        SELECT CONCAT_WS( ' - ', `distro`, `version` ) AS `disver` 
        FROM `os` , `servers` 
        WHERE `os`.`id` = `servers`.`id` AND `servers`.`title` = `origname`) 
) AS `servname`, `server_admin`.`server_id` 
FROM `server_admin`, `servers`
WHERE (`server_admin`.`server_id` = `servers`.`id`)
GROUP BY `servers`.`title`;

Any ideas how I can do this?
Thanks.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Asking on SQL

Hi, not sure whether anybody can help me with sql plus .. I try to run the sql with (select count(*) from table) and setting the set heading off and it will give me this result as shown: T_APPLICANT:1542 ... (2 Replies)
Discussion started by: blueberry80
2 Replies

2. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

3. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare... (2 Replies)
Discussion started by: djehres
2 Replies

4. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

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... (2 Replies)
Discussion started by: Nareshp
2 Replies

6. Shell Programming and Scripting

grep backreferencing question

Hello, My input would be : ###Anything int b,c,a; int a,b,b; ###Anything int c,d,c; int k,l; ###ANYTHING Many declarations interspersed with other statements. I am trying to find only the declarations where a line has a variable declared more than once. The output for... (24 Replies)
Discussion started by: prasanna1157
24 Replies

7. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

8. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

9. 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... (2 Replies)
Discussion started by: Kapom
2 Replies

10. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

LEARN ABOUT LINUX

bssh

bssh/bvnc/bshell(1)					      General Commands Manual					       bssh/bvnc/bshell(1)

NAME

       bssh/bvnc/bshell - Browse for SSH/VNC servers on the local network

SYNOPSIS

       bssh

       bvnc

       bshell

DESCRIPTION

       bssh/bvnc/bshell  browses  for  SSH/VNC	servers  on  the  local  network, shows them in a GUI for the user to select one and finally calls
       ssh/vncviewer after a selection was made.

       If the binary is called as bssh only ssh servers will be shown. If the binary is called as bvnc only VNC servers  will  be  shown.  If  the
       binary is called as bshell both VNC and SSH servers are shown.

OPTIONS

       -s | --ssh
	      Browse for SSH servers (and only SSH servers) regardless under which name the binary is called.

       -v | --vnc
	      Browse for VNC servers (and only VNC servers) regardless under which name the binary is called.

       -S | --shell
	      Browse for both VNC and SSH servers regardless under which name the binary is called.

       -d | --domain= DOMAIN
	      Browse in the specified domain. If omitted bssh/bvnc/bshell will browse in the default browsing domain (usually .local)

       -h | --help
	      Show help.

AUTHORS

       The Avahi Developers <avahi (at) lists (dot) freedesktop (dot) org>; Avahi is available from http://avahi.org/

SEE ALSO

       avahi-browse(1), ssh(1), vncviewer(1)

COMMENTS

       This man page was written using xml2man(1) by Oliver Kurth.

Manuals 							       User						       bssh/bvnc/bshell(1)
All times are GMT -4. The time now is 12:19 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy