MySQL - 从多个表中选择，可能没有连接？

Question

It's been a while since I needed help, but today I'm here to basically get assistance from your knowledge. I'm currently quite stuck on a very annoying SQL problem, which is the following.

I have two tables. Painteditems, and specialitems. Both tables have unique column names (painteditemid, specialitemid etc), yet both tables share similar values. I want to get results from both tables.

Let's say this is my setup:

PaintedItems

• paintedItemName
• paintedItemColor
• visible

SpecialItems

• specialItemName
• specialItemColor
• visible

I used this query:

SELECT *
FROM `painteditems` AS pa,
     `specialitems` AS sp
WHERE (pa.`visible` = 1
       OR sp.`visible` = 1)
  AND (pa.`painteditemname` = 'itemname1'
       OR sp.`specialitemname` = 'itemname1')
  AND (pa.`painteditemcolor` = 'black'
       OR sp.`specialitemcolor` = 'black')

That resulted in:

Showing rows 0 - 29 ( 259,040 total, Query took 39.4352 sec)

even though both tables contain only 10.000 rows altogether. Adding this did nothing:

GROUP BY pa.`painteditemid`, sp.`specialitemid`

Still 260k rows. How should I approach this?

Thank you in advance.

edit: fixed spacing, code blocks

Answer 1

Sure sounds like you want a UNION between the two tables. Right now, you are getting a cartesian product which is why the results are so large:

select *, 'painted' Source
from painteditems
where visible = 1
    and painteditemname = 'itemname1'
    and painteditemcolor = 'black'
union all
select *, 'special' Source
from specialitems
where visible = 1
    and specialitemname = 'itemname1'
    and specialitemcolor = 'black'

You will need to replace the SELECT * with your column names. Also the number of columns and datatypes must match in both queries.

UNION ALL will return all rows from both tables, if you only want DISTINCT rows then you will want to use UNION