CODEWITHSUNDEEP
phpmysqlsqljoin
Mave
Mave

Reputation: 2516

MySQL - selecting from multiple tables, possibly without joins?

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

SpecialItems

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

Upvotes: 2

Views: 6673

Answers (3)

Taryn
Taryn

Reputation: 247680

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

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I would do this with a union all in the subquery:

select *
from ((select paintedItemName as ItemName, paintedItemColor as ItemColor, visible, 'Painted' as which
       from painteditems
      ) union all
      (select specialItemName, SpecialItemColor, visible, 'Special' as which
       from specialitems
      )
     ) t
where visible = 1 and itemname = 'itemname1' and itemcolor = 'black'

This allows you to have only one set of results. In a union, the column names come from the first subquery, which this renames to more generic names. The reason I prefer this approach is because the where clause does not need to be repeated multiple times -- which can lead to errors and maintenance problems.

Upvotes: 0

Techie
Techie

Reputation: 45124

The UNION operator is used to combine the result-set of two or more SELECT statements. Defiantly You can make use of UNION as shown in the @bluefeet's answer If you meet below conditions.

  • SELECT statement within the UNION must have the same number of columns
  • The columns must also have similar data type
  • The columns in each SELECT statement must be in the same order.

Upvotes: 0

Related Questions