CODEWITHSUNDEEP
sqlpostgresql
Pasted
Pasted

Reputation: 864

Combining the results of two seperate SQL queries with 'OR'

Framework: Rails 2.3.8 Database; PostgreSQL

I currently have the following SQL statements (generated by Rails)

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "bacterial_agents" ON bacterial_agents.outbreak_id = outbreaks.id INNER JOIN "bacteria" ON "bacteria".id = "bacterial_agents".bacterium_id WHERE (bacteria.name ILIKE E'%VTEC O157%')

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "viral_agents" ON viral_agents.outbreak_id = outbreaks.id INNER JOIN "virus" ON "virus".id = "viral_agents".virus_id WHERE (virus.name ILIKE E'%NOROVIRUS%')

With the following tables (I've skipped the non-used attributes and the layout / key naming is down to Rails conventions)

outbreaks
  id

bacterial_agents
  id
  outbreak_id
  bacterium_id

bacteria
  id
  name

viral_agents
  id
  outbreak_id
  virus_id

viruses
  id
  name

Any ideas how I would join the two above SQL statements with an "OR" condition, to select outbreak records which are related to the bacteria table where the bacteria.name is like "VTEC O157" OR related to the viruses table where the virus.name is like "NOROVIRUS"?

*EDIT

Abit of clarification on the Rails generation of the SQL - it currently outputs the following SQL

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "bacterial_agents" ON bacterial_agents.outbreak_id = outbreaks.id INNER JOIN "bacteria" ON "bacteria".id = "bacterial_agents".bacterium_id INNER JOIN "viral_agents" ON outbreaks.id = viral_agents.outbreak_id INNER JOIN "viruses" ON "viral_agents".virus_id = "viruses".id WHERE ((bacteria.name ILIKE E'%VTEC O157%') AND (viruses.name ILIKE E'%NOROVIRUS%')) LIMIT 1

Essentially I want to output the combined results from the first two statements into a similar format as the above statement (with an "OR" condition in place of the "AND"). However if this isnt possible I'll just have to write a scope to handle the SQL union instead.

Think judging by the replies below I'll go with a union of the two seperate statements, cheers :)

Upvotes: 1

Views: 1758

Answers (2)

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

Just place a UNION ALL between the queries

SELECT "outbreaks".* 
  FROM "outbreaks"
  INNER JOIN "bacterial_agents"     ON bacterial_agents.outbreak_id = outbreaks.id 
  INNER JOIN "bacteria"         ON "bacteria".id = "bacterial_agents".bacterium_id
WHERE (bacteria.name ILIKE E'%VTEC O157%')

UNION ALL

SELECT "outbreaks".*
   FROM "outbreaks"
   INNER JOIN "viral_agents"    ON viral_agents.outbreak_id = outbreaks.id 
   INNER JOIN "virus"       ON "virus".id = "viral_agents".virus_id
 WHERE (virus.name ILIKE E'%NOROVIRUS%')

Upvotes: 2

David Božjak
David Božjak

Reputation: 17617

I think you are confusing OR with SQL union operator to join the returns of the two queries.

OR is used to manage conditions in the WHERE part of the statement while union is used to append one query to another.

You should note that union will only work if the two queries have the same domains. From quick look at your queries it should do exactly what you have in mind.

Try this:

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "bacterial_agents" ON bacterial_agents.outbreak_id = outbreaks.id INNER JOIN "bacteria" ON "bacteria".id = "bacterial_agents".bacterium_id WHERE (bacteria.name ILIKE E'%VTEC O157%')
UNION
SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "viral_agents" ON viral_agents.outbreak_id = outbreaks.id INNER JOIN "virus" ON "virus".id = "viral_agents".virus_id WHERE (virus.name ILIKE E'%NOROVIRUS%')

Upvotes: 5

Related Questions