0

I'm not a SQL guy, and I've just tried every JOIN MySQL offers.

  1. I primarily need data from view1 using WHERE, ORDER BY, LIMIT with offset (the data is incorrect without those conditions.)
  2. view1 is not limited by view2
  3. Data also needs to be pulled from view2, but view2 has its' own WHERE (or whatever's best) condition
  4. view2 may not have corresponding data, but it shouldn't be grabbed without being linked to view1 (NULLs are just fine!)

I'll just give you the two SELECTs that work for me rather than the hashes I've come up with.

For view1:

SELECT * FROM view1 
WHERE column1 IS NULL OR column1 = 1 
ORDER BY dateColumn DESC LIMIT index1, count1;

(index1 and count1 are INs i use for a stored proc)

For view2:

SELECT * FROM table2 
WHERE column1 = ? AND table1_id IN (
  SELECT id FROM view1 
  WHERE column1 IS NULL OR column1 = 1 
  ORDER BY dateColumn DESC LIMIT index1, count1
) 
GROUP BY table1_id

(? is any arbitrary value i put in)

I'm at wits end. I have no idea how to fuse these two.

Specifics

Please note that the IN for view2 is almost identical to the view1 query. Thanks!

  • Why you need the Order By in the subquery? – rae1 Dec 29 '12 at 08:42
  • @rae1n I guess it could order by `id`, but either way the data in this case is time sensitive. Social news articles ordered by time, in this case. –  Dec 29 '12 at 08:44
  • @MahmoudGamal Are we dancing tonight? ;)) `table2` should only grab information if `table2.table1_id = table1.id AND table2.column1 = ?', where `?` is some arbitrary yet necessary constant. `table1`'s data must be there regardless of `table2`'s ability to accomodate. Thanks guys! –  Dec 29 '12 at 08:56

3 Answers3

1

You can write a subquery for table2 and add WHERE clause there, also you can add WHERE clause at the ent of the query - 

SELECT * FROM table2 t2
  JOIN (
       SELECT id FROM table1
       WHERE column1 IS NULL OR column1 = 1
       ORDER BY dateColumn DESC
       LIMIT index1, count1
       ) t1
    ON t1.id = t2.table1_id
WHERE
  t2.column1 = ?
GROUP BY
  t2.table1_id

In this example I used JOIN instead of WHERE IN condition.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • I so wanted that query to be correct for its' inverted nature. The problem is that it's dependent upon table2. It got me almost all of the results needed. It excluded data from table1 where it should've ignored where table2 was lacking. Thank-you very much for this cool query! –  Dec 29 '12 at 08:51
  • Ha! Woah! `LEFT JOIN` was a little too happy! lol. It gave me the phone book, but most importantly, it gave a ton of `NULL`s for `table1.id`, and left out some `table1.id`s when it shouldn't've. Thanks again! –  Dec 29 '12 at 09:03
0

How about this query:

Select t1.id 
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.column1 IS NULL OR t1.column1 = 1
Ibu
  • 42,752
  • 13
  • 76
  • 103
  • I can't live without the `ORDER BY`. The data is time sensitive in this case, and if I lose that, I have nothing. Thank-you for trying tho! –  Dec 29 '12 at 08:52
0

i think it should help you 

Select TableName1.id 
FROM TableName1 
JOIN TableName2 ON TableName1.id = TableName2.id
WHERE TableName1.column1 IS NULL OR TableName1.column1 = 1
Jignesh.Raj
  • 5,776
  • 4
  • 27
  • 56
  • Thank you for your help! But I'm shackled to the `ORDER BY`. The results must be in chronological order from the `LIMIT` offset. –  Dec 29 '12 at 09:09