help on SQL

June 30, 2006 12:47 PM

Need some help with combining SQL queries. New to SQL - some programming back ground.

I have two very simple SQL statements, I am VERY new to SQL so I am trying to "learn as I go."
I have two queries I would like to combine to save time:

select rtf_seq from enc_documentation
where enc_seq =

select * from text_document
where text_document_seq =
for update


As a note:
text_documentation seq = rtf_seq (just different names on different tables).

Thank you

posted by lutzla23 to computers & internet (9 comments total)

ok so something like,

select * from enc_documentation, text_document
where text_documentation_seq = rtf_seq AND enc_seq = whatever_value_you_are_querying_for

This "joins" the two tables based on text_documentation_seq = rtf_seq. It will return all fields from both tables, for all rows where these two fields are equal AND enc_seq equals the desired value.


Note that it is good practice to keep the same field name for fields that exist in multiple tables.
posted by utsutsu at 12:58 PM on June 30, 2006

Fabulous,
thank you!

Do you recommend a good tutorial/book for sql, most of what i'll be doing is querie based and should not be very complex.

thank you again utsutsu
posted by lutzla23 at 1:06 PM on June 30, 2006

it looks like your query didn't make it all the way through. Additionally, specifying the database platform would be a good idea, as each has weird issues and tricks. Finally, 'combine' can have several different meanings in database terms.

However, let's say that you meant that you want to get all records from the two tables "text_document" and "enc_documentation" where they both share a sequence number ("rtf_seq" or "seq"), and to ignore all the records which have a sequence number that is not shared in the other table. This operation is called an "inner join" in database terminology and is the easiest and most common way of combining data in sql.

First, you should fix the tables so that their sequence identifiers are the same; "seq" being equivalent to "rtf_seq" is just nasty and dangerous. Nevertheless, continuing as if that wasn't fixed:

select t.*
from text_document t, enc_documentation e
where t.seq = r.rtf_seq
for update

note that the 'for update' syntax will either work or not depending on what platform and db you're on.

There are other ways to combine the data; there are 'outer joins', with which you can say that you want all the rows from one table, and any matching rows if available from
the other table, and so forth.

You might profitably invest some time in typing "SQL introduction" into google.
posted by felix at 1:09 PM on June 30, 2006

Galaxql is an excellent and engaging SQL tutorial.
posted by Mr. Six at 1:10 PM on June 30, 2006

This is for the case then there is a one-to-one correspondence from enc_documentation -> text_document:


SELECT *
FROM enc_documentation
INNER JOIN text_document ON text_document.text_document_seq = enc_documentation.rtf_seq
WHERE enc_documentation.enc_seq = ?


For other relationship types, you would need to use a different JOIN. Can you give more information on the exact relationship between the two tables?
posted by ijoshua at 1:16 PM on June 30, 2006

Thanks felix, actually all of the query made it through.
And the two querys work fine independantly.

I understand what you are saying for the database and different seq names and I agree, but I didn't create the database.

The query sent through by utsutsu worked great.

I don't quite understand your query as far as where do I input the enc_seq to drive the query?

Like I said I am new to this.
posted by lutzla23 at 1:18 PM on June 30, 2006

select rtf_seq from enc_documentation
where enc_seq =

is, by itself, not a valid query. SQL needs you to fill in the thing after the equals sign. It's possible that your tool/development environment/database thing/whatever is finding a way to fill that out for you, but it's unclear how it might.
posted by felix at 1:32 PM on June 30, 2006

I find the following books invaluable:

BOOK: Learning SQL
by Alan Beaulieu
Publisher: O'Reilly
Pub Date: August 2005
ISBN: 0-596-00727-2
Pages: 306

This one's a great introduction.

BOOK: SQL Cookbook
by Anthony Molinaro
Publisher: O'Reilly
Pub Date: December 2005
ISBN: 0-596-00976-3
Pages: 628

This one's great for study and solving real-world problems.
posted by elderling at 1:44 PM on June 30, 2006

The W3Schools SQL tutorials are really good because they actually have a database that you query online and see the results live. See this example for instance.
posted by AmbroseChapel at 4:08 PM on June 30, 2006