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:
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
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
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.
<li>SELECT statement within the UNION must have the same number of
<li>The columns must also have similar data type</li>
<li>The columns in each SELECT statement must be in the same order.</li>
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.