Complicated MYSQL query selecting from two tables

Question

I need to do a MYSQL query to select users from "table_in" which has the same format as table_1

 table_1
 id  Field_id  user_id  value
 1      9        1     "hello"
 2      10       1     "Multi"
 3      9        2     "Something"
 4      10       2     "Single"

 table_2
 id  user_id  status        specs
 1      1     "Busy"       "this is a test"
 2      2     "Idle"       "this is another test"
 3      2     "Busy"       "another test again"
 4      1     "Relaxing"   "something else"

I need to select users from table_in, where either one of the following is true

1. table_1 field_id=10 contains "Multi"
2. table_2 status is NOT Busy

Then save the result in a new table table_out

So what i got so far is this

  CREATE TEMPORARY TABLE table_out
    SELECT * FROM table_1 WHERE user_id in (SELECT user_id from table_in) 
    AND (field_id=10 AND value='Multi')
    OR (SELECT * from table_2 where status!='Busy')

Im not SQL expert and the MY-SQL statement i have above dont work. I think maybe the last OR statement is wrong where its trying to select from table_2 where the status is not busy.

So basically the users that gets put into table_out must all come from "table_in" and either have (field 10 set to multi), or the same user in table_2 (status!=Busy)

Does anybody know how to do it?

UPDATE: Found my own solution. Using table_in, create a second table by selecting only those values from it that is not "multi", then from that query table_2 for those user_id that are not busy put that in another table, then remove those values from table_in.

Thanks anyway

Answer 1

You could try it with a union statement. I do not have any mysql database here to test it, but it should kind of look like that

-- since table_in is a temporary table, we need to copy it first.
CREATE TEMPORARY TABLE table_in2 SELECT * FROM table_in;

CREATE TEMPORARY TABLE table_out SELECT table_out_alias.*
                             FROM (
                                    SELECT
                                      user_id AS user_id,
                                      value   AS value,
                                      ''      AS specs
                                    FROM table_1
                                    WHERE user_id IN (SELECT user_id
                                                      FROM table_in)
                                          AND (field_id = 10 AND value = 'Multi')
                                    UNION ALL
                                    SELECT
                                      user_id AS user_id,
                                      ''      AS value,
                                      specs   AS specs
                                    FROM table_2
                                    WHERE status != 'Busy' AND user_id IN (SELECT user_id
                                                                           FROM table_in2)
                                  ) AS table_out_alias

if i got your question right :)

Answer 2

From what I'm seeing... maybe something like this would work... Granted, I'm SQL Server, but I think this would work in MySQL.

Select t1.*
From Table_in TIN
JOIN Table_1 T1 ON TIN.User_ID = T1.User_ID
JOIN Table_2 T2 ON TIN.User_ID = T2.User_ID
Where (T1.Field_ID = 10 AND T1.Value = 'Multi')
OR (T2.Status != 'Busy')

Answer 3

You must use UNION ALL operator.

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns

• The columns must also have similar data types

• The columns in each SELECT statement must also be in the same order.

  CREATE TEMPORARY TABLE table_out
  SELECT * FROM table_1 WHERE user_id in (SELECT user_id from table_in) AND (field_id=10 AND value='Multi')
  

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

UNION ALL
    SELECT t2.id, t_in.field_id, t2.user_id, t_in.value
    FROM table_2 AS t2
    INNER JOIN table_in AS t_in
    ON t2.user_id = t_in.user_id
    WHERE t2.status != 'Busy';

Answer 4

I think you'll find this does what you're looking for:

SELECT *
  FROM table_in ti
  INNER JOIN table_1 t1
    ON t1.user_id = ti.user_id
  LEFT OUTER JOIN table_2 t2
    ON t2.USER_ID = ti.USER_ID
  WHERE (t1.field_id = 10 AND t1.value = 'Multi') OR
        t2.status <> 'Busy'

To have this work as intended table_2 should be an outer (optional) join. Actually, as I don't have a complete grasp on the problem it may well be that BOTH table_1 and table_2 should be outer joins. Well, it's something further for you to play with.

I had to make some assumptions, which I encapsulated in this SQLFiddle. Note that in the fiddle I only populated table_in with a user_id of 1, just to test it out. Change it around as needed to test further.

Best of luck.

EDIT

The code from the fiddle is:

create table table_in(user_id int);

insert into table_in(user_id) values (1);



create table table_1(id int, field_id int, user_id int, value varchar(20));

 insert into table_1 (id,  Field_id,  user_id,  value) values (1,      9 ,       1,     'hello');
 insert into table_1 (id,  Field_id,  user_id,  value) values (2,      10,       1,     'Multi');
 insert into table_1 (id,  Field_id,  user_id,  value) values (3,      9 ,       2,     'Something');
 insert into table_1 (id,  Field_id,  user_id,  value) values (4,      10,       2,     'Single');

 create table table_2 (id int, user_id int, status varchar(10), specs varchar(25));

insert into table_2(id,  user_id,  status, specs) value (1,      1,     'Busy'     ,  'this is a test');
insert into table_2(id,  user_id,  status, specs) value ( 2,      2,     'Idle'     ,  'this is another test');
insert into table_2(id,  user_id,  status, specs) value ( 3,      2,     'Busy'     ,  'another test again');
insert into table_2(id,  user_id,  status, specs) value ( 4,      1,     'Relaxing' ,  'something else');

Putting it here because SQLFiddle seems to be going on and off line like a ping-pong ball today. :-(