[SQL] (no subject)

[onin]

--
"A whim, a thought, and more is sought... awake, my mind... thy will be 
wrought!"



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] table joining duplicate records

[onin]

hi all,

somebody please help me with my sql statement.
or point me to the right documentation that i need to read.

what i want to working on right now is to create a db for a survey app
my tables look this

surveys
survey_id:survey

1: survey1

categories
category_id:category

1:category1

questions
question_id:question

1:question1

answers
answer_id:answer

1:answer1
2:answer2

questions_answers
question_answer_id:survey_id:category_id:question_id:answer_id
1 :1:1  :1
1 :1:1  :2

and my query looks like this

select s.survey, c.category, q.question, a.answer from questions_answers 
qa, answers a, questions q, categories c, surveys s
where qa.answer_id = a.answer_id and qa.question_id = q.question_id and 
qa.category_id = c.category_id and qa.survey_id = s.survey_id;



after executing the code,
i get results like this.

--query results--
survey :category :question :answer
survey1:category1:question1:answer1
survey1:category1:question1:answer2

how can i elimate duplicates on my query results?
an also am i using the right 'table joining' or table design for my 
survey app?



any help would be appriciated.
thanks in advance.

--
"A whim, a thought, and more is sought... awake, my mind... thy will be 
wrought!"



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] SELECT substring with regex

[T E Schmitz]

Rodrigo De Leon wrote:

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:


Sorry, but that would also capture something like
10-30-59mm

The pattern describes either a single length (120 millimeters) or a
range (30 to 70 millimetres), hence:

\\d+(-\\d+)?mm

The ? quantifier refers to the combination of '-' and digits and has to
be bracketed.

...

Still, I'd be interested to know whether there is a 'more elegant' 
solution.



OK, last try using regex:

SELECT
NAME
, substring(NAME, '^(\\d+(-\\d+)?mm)') AS BASE_NAME
, regexp_replace(NAME, '^\\d+(-\\d+)?mm (.*)', '\\2') AS SUFFIX
FROM MODEL


Is regexp_replace a new feature?
I am running v 7.4.

--


Regards,

Tarlika

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] table joining duplicate records

[Anthony Kinyage]

In your case Survey , Categories, Questions and Answers TABLES are parents tables, and Question_answers TABLE is a Child Table.      Since you want to have survery, from Survey Table, Category from Category Table, Question from Question Table and Answer from Answer Table and alll these Atributes are on Question_answer Table, in your case you can use the following:        SELECT * FROM question_answer;     Try this STATEMENTonin <[EMAIL PROTECTED]> wrote:  hi all,somebody please help me with my sql statement.or point me to the right documentation that i need to read.what i want to working on right now is to create a db for a survey appmy tables look thissurveyssurvey_id:survey1 :
 survey1categoriescategory_id:category1 :category1questionsquestion_id:question1 :question1answersanswer_id:answer1 :answer12 :answer2questions_answersquestion_answer_id:survey_id:category_id:question_id:answer_id1 :1 :1 :11 :1 :1 :2and my query looks like thisselect s.survey, c.category, q.question, a.answer from questions_answers qa, answers a, questions q, categories c, surveys swhere qa.answer_id = a.answer_id and qa.question_id = q.question_id and qa.category_id = c.category_id and qa.survey_id = s.survey_id;after executing the code,i get results like this.--query results--survey :category :question :answersurvey1:category1:question1:answer1survey1:category1:question1:answer2how can i elimate duplicates on my query results?an also am i using the right 'table
 joining' or table design for my survey app?any help would be appriciated.thanks in advance.-- "A whim, a thought, and more is sought... awake, my mind... thy will be wrought!"---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster 
		 
Try the all-new Yahoo! Mail . "The New Version is radically easier to use" – The Wall Street Journal

[SQL] unsubscribe

[Patrice OLIVER]

unsubscribe

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] Celko's Puzzle Number 5

[Michael Fuhr]

On Fri, Jul 07, 2006 at 11:34:57PM -0700, Richard Broersma Jr wrote:
> "How do you ensure that a column will have a single alphabetic
> character string in it? (That means no spaces, no numbers, and
> no special characters.)"

You can check patterns with regular expressions.  PostgreSQL supports
SIMILAR TO, which is standard SQL since SQL:1999, and POSIX regular
expressions; see "Pattern Matching" in the "Functions and Operators"
chapter of the documentation:

http://www.postgresql.org/docs/8.1/interactive/functions-matching.html

Any of these CHECK expressions should work:

  CHECK (alpha_only SIMILAR TO '[A-Za-z]+')
  CHECK (alpha_only ~ '^[A-Za-z]+$')
  CHECK (alpha_only ~* '^[a-z]+$')

Unfortunately, even though SIMILAR TO has been standard SQL for
several years, not all databases implement it.  Many databases
do support regular expressions but generally via a non-standard
syntax (as PostgreSQL does with its ~, ~*, !*, and !~*  operators).

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] table joining duplicate records

[Richard Broersma Jr]

> --query results--
> survey :category :question :answer
> survey1:category1:question1:answer1
> survey1:category1:question1:answer2
> 
> how can i elimate duplicates on my query results?
> an also am i using the right 'table joining' or table design for my 
> survey app?

What duplicates?  You have two unique answers for question1.

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Celko's Puzzle Number 5

[Richard Broersma Jr]

> http://www.postgresql.org/docs/8.1/interactive/functions-matching.html
> 
> Any of these CHECK expressions should work:
> 
>   CHECK (alpha_only SIMILAR TO '[A-Za-z]+')
>   CHECK (alpha_only ~ '^[A-Za-z]+$')
>   CHECK (alpha_only ~* '^[a-z]+$')
> 
> Unfortunately, even though SIMILAR TO has been standard SQL for
> several years, not all databases implement it.  Many databases
> do support regular expressions but generally via a non-standard
> syntax (as PostgreSQL does with its ~, ~*, !*, and !~*  operators).

Thanks for the link!

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] SELECT substring with regex

[T E Schmitz]

Aaron Bono wrote:
On 7/7/06, *T E Schmitz* <[EMAIL PROTECTED] 
> wrote:



I am trying to come up with a semi-automatic solution to tidy up some
data. If it's got to be done manually via the GUI it would mean a lot of
dummy work [for the customer].


I would recommend you alter the GUI to NOT allow any more bad data to 


I absolutely agree that the user should not be able to enter 'bad' data 
in the first place.
However, in this case it is not a hard and fast rule, rather a 
suggestion how to organize the data sensibly. The pattern is suitable 
for 98% of the lenses; there are some, albeit very few, exceptions.


Also: the MODEL.NAME is a free-form field; only when referred to by a 
PRODUCT which is of CATEGORY 'Lens', would the pattern apply.


The only way one could maybe assist the user would be a warning when he 
tries to save a 'Lens' PRODUCT, which deviates from the pattern.


You might also consider adding the base and suffix columns with a 
trigger that parses the name field and sets the values of base and 
suffix and also does any integrity checks during inserts and updates.  
Finally, after the data is cleaned up and the client apps are changed to 
use base and suffix and not name, get rid of the name column.


The suffix should have been entered in TYPE.NAME, which references 
MODEL. The structure exists, it just needs to be used efficiently.


--


Regards,

Tarlika

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] SELECT substring with regex

[Rodrigo De Leon]

On 7/8/06, T E Schmitz <[EMAIL PROTECTED]> wrote:

Is regexp_replace a new feature?
I am running v 7.4.


Given the patch history:

http://archives.postgresql.org/pgsql-patches/2004-07/msg00471.php
http://archives.postgresql.org/pgsql-patches/2005-06/msg00515.php
http://archives.postgresql.org/pgsql-patches/2005-07/msg00260.php

And the release notes:

http://www.postgresql.org/docs/8.1/static/release.html

I'd say at least:

7.4.9

8.0.4

8.1

Anyone care to verify this?

Regards,

Rodrigo

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] table joining duplicate records

[Richard Broersma Jr]

> can i have a result set. just like the outer join returns.
> but on my case. return null on duplicates.
> like this one.
> 
> survey :category :question :answer
> ---
> survey1:category1:question1:answer1
>: : :answer2

hmmm..  I am no sure that you are going to get what you want from a simple 
select query.

A reporting software could do this easily however.

also be sure to include the list in your replies :-).

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings