50494

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

    <li>paintedItemName</li> <li>paintedItemColor</li> <li>visible</li> </ul>

    SpecialItems

      <li>specialItemName</li> <li>specialItemColor</li> <li>visible</li> </ul>

      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

      Answer1:

      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

      Answer2:

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

        Answer3:

        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.

Recommend

  • Making Toplevel resize itself to fit the title
  • Perl Moose accessors generated on the fly
  • Get plots from CombinedDomainXYPlot (and remove them)
  • Postgresql: removing spaces between certain type of digits
  • Can we replace the 'Derivative' terms in sympy coming from the differentiation of sympy.Fu
  • onTouchListener togglebutton, ignores first press?
  • Python subprocess change dir via client/server
  • Remove PNG plot margins
  • Upload to Azure Blob using SAS and REST
  • javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake while inserting
  • how to add listview in listview in android?
  • Using DI in ConfigureService-Methods (especially IApplicationLifetime) in .NET Core
  • UWP XAML ImageBrush.imageSource from networkshare
  • Why __weak object will be added to autorelease pool?
  • AngularFire httpsCallable Object(…) is not a function
  • No resource found that matches the given name: attr 'homeHint'
  • Two ways to execute a Stored procedure in VBA, Which one is better?
  • How to remove just the index name and not the content in Pandas multiindex data frame
  • InputDispatcher Error
  • Programmatically check if PHP is installed using Python
  • Prevent focus to URL bar with CTRL + L
  • SSIS package hangs while running
  • css font-size and line-height not matching the baseline
  • Certain Arabic text gets incorrectly shown while other Arabic text gets showed normally?
  • SQL: Getting the physical size of a subset of a table
  • How to implement Deep Linking in Roku SG application?
  • Best practice to eliminate magic numbers within a member function
  • How to plot large time series (thousands of administration times/doses of a medication)?
  • pip in virtualenv gets ConnectTimeoutError
  • Marklogic : Query response time is very high
  • ActiveRecord query for a count of new users by day
  • Alert pop up with LWUIT
  • SSO with signing and signature validation doesn't work
  • vba code to select only visible cells in specific column except heading
  • Display Images one by one with next and previous functionality
  • Do I've to free mysql result after storing it?
  • Transpose CSV data with awk (pivot transformation)
  • File upload with ng-file-upload throwing error
  • SQL merge duplicate rows and join values that are different
  • Sorting a 2D array using the second column C++