MySQL combine two queries

Question

I have two MySQL queries

QUERY:

SELECT sodnik_1 FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_1 != ''
UNION 
SELECT sodnik_2 FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_2 != ''
UNION
SELECT sodnik_3 FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_3 != ''
UNION
SELECT sodnik_4 FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_4 != ''

QUERY:
SELECT value FROM notification_sodniki WHERE user_id='16'

OUTPUT LOOKS LIKE THIS:

Name 1
Name 2
Name 3
Name 4

IN BOTH TABLES

They give me 1 column. I'd like to perform a cross join and return only the values that are present in both select queries. Is that possible ?

Marc · Accepted Answer · 2012-11-15T15:45:18.713

31

How about

SELECT * FROM
(
    SELECT sodnik_1 as value FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_1 != ''
    UNION 
    SELECT sodnik_2 as value FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_2 != ''
    UNION
    SELECT sodnik_3 as value FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_3 != ''
    UNION
    SELECT sodnik_4 as value FROM prihodnji_krog  WHERE file_id='8778' AND sodnik_4 != ''
) x INNER JOIN 
(
    SELECT value FROM notification_sodniki WHERE user_id='16'
) y 
ON x.value = y.value

When you use subqueries in a FROM clause, it's like if you are making temporary tables. Then with the alias you can refer to them and do a INNER JOIN

edited Nov 15 '12 at 15:45

answered Nov 15 '12 at 15:33

Marc

16,170
20
76
119

It gives me Illegal mix of collations. But I think you're very close. – litenull Nov 15 '12 at 15:37
Yes, the encoding is different. One moment. – litenull Nov 15 '12 at 15:38
I think you could use `COLLATE` like in this post http://stackoverflow.com/questions/3029321/how-to-solve-illegal-mix-of-collations-in-mysql or change the encoding to make it the same for both – Marc Nov 15 '12 at 15:40
Glad to help. Do you understand the query? If you don't I will update my answer cause it's really important to understand everything before using it – Marc Nov 15 '12 at 15:43
I updated my answer. Don't forget to accept by checking the white check beside the answer if it helped :) – Marc Nov 15 '12 at 15:46
I understand but if you want, you could update because I did a million searches and found a million queries but no comments. Someone who is searching for an answer looks for the explanation too, not just the code. – litenull Nov 15 '12 at 15:47

MySQL combine two queries

1 Answers1

Linked