Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have made a draft remote application on the top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functionning of the application. For each final business result that I produce, it happens that I call something like 40 select clause (over tcpip).

I have reminiscences from SQL-Server reminding me to minimize the number of interactions between my remote application and the database. Having analyzed my selects, I do think I could reduce this number to 3 select clause, using jointures. But I don't remember the syntax for using the result of a select in another select.

e.g. :

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

this other select would be simply of the kind:

SELECT identifier FROM another_table WHERE something='something'

How would you write that ? What is the name for describing the action of redirecting one select into another , if any name ?

EDIT: in the answer given i've been ask to give the tables layout. Here it is simplified, declined a number of times for different item_types... ( 3 totally different types, hence the 3 sql queries if optimized).

table passage
PK: id_passage
text business_field_passage

table item
PK: id_item
FK: id_passage
text business_field_item

table item_detail
PK: id_item_detail
FK: id_item
text business_field_item_detail
bytea image_content

there are several id_item for one id_passage. there are several id_item_detail for one id_item.

share|improve this question
1  
postgresql.org/docs/9.1/static/… –  dezso Jan 25 '13 at 15:41
    
are you referring to 7.2.1.3. Subqueries ? –  Stephane Rolland Jan 25 '13 at 15:44
    
Possibly yes, together with the JOIN part. –  dezso Jan 25 '13 at 15:47
add comment

2 Answers

up vote 3 down vote accepted

Is this what you're aiming for? Make sure the fields that are being compared are comparable (i.e. both fields are numeric, text, boolean, etc).

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = (SELECT someID FROM table WHERE blahblahblah)

If you wish to select based on multiple values:

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId IN (SELECT someID FROM table WHERE blahblahblah)
share|improve this answer
    
can it be that straight forward ?? does it still work if SELECT someID FROM table WHERE blahblahblah has multiple records ? I'm gonna check that right now. –  Stephane Rolland Jan 25 '13 at 16:38
    
Which query is selecting multiple records? It can work if you're selecting multiple records, but if you could show us your table layouts that would help us refine the answer. –  Angry Spartan Jan 25 '13 at 16:40
    
WHERE Individual.IndividualId IN... looks good. –  Stephane Rolland Jan 25 '13 at 17:00
add comment

You can just rewrite that as another JOIN. This is normally simplest and fastest:

SELECT i.*, p.*
FROM   individual    i
JOIN   publisher     p USING (individualid)
JOIN   another_table a ON a.identifier = i.individualid
WHERE  a.something = 'something'

I also simplified somewhat and did away with the gratuitous CamelCase spelling of identifiers.

share|improve this answer
1  
Yes, this. I die a little inside whenever I see IN (SELECT ..) syntax. –  Mark Storey-Smith Jan 26 '13 at 12:56
    
@MarkStorey-Smith Do you mean that it's more than simpler and faster: this a standard of sql coding to use another join instead of a in ( select...) In such case I should also attribute the good answer to Erwin. –  Stephane Rolland Jan 26 '13 at 19:27
    
@StephaneRolland Whether its faster or not will be platform and version dependent. SQL Server 2008+ for example will generate identical execution plans for INNER JOIN and IN (SELECT ...) syntax. No idea on whether same applies to PostgreSql. Performance aside, the IN (SELECT ...) style leaves me wondering if the author has fully grasped the semantics and concepts of SQL. AngrySpartan has answered your original question correctly. ErwinBrandstetter has shown you the way you should do it :). –  Mark Storey-Smith Jan 26 '13 at 19:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.