1

I have made 2 simple queries which output the forename and surname of people based upon the value of a column "role" in a junction table they are associated with.

Lets say that the output is:

Kevin Baker
Julie Willis
Gregory Hilliard
Joe Swanson
Patrick Day
Bethany Row

for one of the query outputs and:

Kevin Baker
Michael Winter
Ellie Patterson

for the other.

I want to be able to make a query which outputs the values that appear in both of these tables i.e. I want it to output Kevin Baker in this case.

Is there a simple way to do this based upon the two queries I have already made?

PotWashMike
  • 199
  • 1
  • 2
  • 8
  • 1
    Use UNION statement between one query and other. – Jorge Campos Dec 12 '13 at 11:33
  • 1
    Is the syntax for this: QUERY1 UNION QUERY2 ? Does it need any brackets etc...? I tried it this way but the output was incorrect. I thought UNION was similar to saying "or" so instead of finding the mutual results, it finds the results in QUERY1 or QUERY2. Am I wrong? – PotWashMike Dec 12 '13 at 11:33
  • No it doesn't need any brakets. If you post your query it will be easier to provide you an appropriated answer. In order to do a UNION you have to have the same number of columns on the to sql commands and all types needs to be the same. – Jorge Campos Dec 12 '13 at 11:46
  • Also UNION by default makes a DISTINCT over the two queries, to do it without the distinc you have ot use UNION ALL – Jorge Campos Dec 12 '13 at 11:47
  • I need something similar to INTERSECT which is not included in MySQL syntax. I will refer to this thread for further research http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql – PotWashMike Dec 12 '13 at 11:47
  • Post your query and the desired result based on the data you show. – Jorge Campos Dec 12 '13 at 11:48

2 Answers2

1

I have managed to do it myself. I am not sure if it is the best method but it was simple and works.

I kept the first query the same and added

AND IN()

to the end of the WHERE statement.

I then pasted the second query into the IN statement and altered it to only SELECT the id of the related attributes in the outer query.

PotWashMike
  • 199
  • 1
  • 2
  • 8
0

If you need an intersect just JOIN these queries:

Select Q1.Name FROM (QUERY1) Q1
JOIN 
(QUERY2) Q2 On Query1.Name=Q2.Name
valex
  • 23,966
  • 7
  • 43
  • 60
  • How do I name the queries in order to do this? At the moment, the two queries are two SELECT-FROM-WHERE blocks. – PotWashMike Dec 12 '13 at 12:15
  • Just replace `Query1` and `Query2` with your queries. – valex Dec 12 '13 at 13:02
  • or right, are you actually assigning the names Q1 and Q2 to the queries at that point? Could you also use (QUERY1) AS Q1 like you do when abbreviating tables? – PotWashMike Dec 12 '13 at 15:48