How to find products with similar components?

I am trying to find items that have at least 75%+ similar components in them, we have thousands of products. My table has 2 columns, Item and Component. Example: +------+-----------+ | Item | Component | +------+-----------+ | AAA | screw | | AAA | m

SQL & mdash; Counting with a nested subquery

I have a collection of tables in a relational database products categories orders line_items customers Products has a many-to-many relationship with categories (join table categories_products) and also has and belongs to many orders through line_item

Transform 2 column subqueries into 1 with MySQL

I'm having a little trouble with a specific query. Suppose you have a website like ebay, and you want to select the users that sold at least 2 objects, but which are of different types (clothes, electronics, etc.) Here is what I came up with : Select

The MySql update does not recognize the temporary table

I have the following query script that is resulting in an error: SET @row_number = 0; Drop Table If Exists testtable2; Create Temporary Table testtable2 SELECT * FROM (SELECT row_names, (@row_number:=@row_number + 1) AS num, date_add(date_time, INTER

using group by in the sql subquery

how to get around this error : Unable to use an aggregate or a subquery in an expression used in the GROUP BY list of a GROUP BY clause. here is my query : select Id, name,dayA,monthA,yearA, sum(x) as x, (select SUM(x) group by month) as total, from

TSQL How can I reuse a record in subqueries

I have a query similar to the following where I need to return a few fields from another table as subqueries. Is there a way I can only call the query to TABLE2 once and use the results of that query to populate the various fields I need? I'm looking

How can I select all childless entries in MySQL

The table has the following columns CODE VARCHAR(255) -> the code for the class (NOT UNIQUE) (for example test or test2) FULLPATH VARCHAR(255) -> the full path including all parent class codes (UNIQUE) (for example test-test2) NAME VARCHAR(255) ->

How to write a query that joins 3 tables

I am having issues with this query that utilizes three different tables. I also believe I will be using the key words NOT EXISTS and NOT WITHIN but I am not completely positive on this. I will post the query definition, the tables I am using, and wha

Subquery to find a value in a column

There are two tables, hotel and hotel_booking. The hotel_booking table has two columns: hotel_id date_booked The hotel_id goes from 1-10, and for each one, a date will be entered if it has been booked. I need to search for all the hotel_id's that hav

subquery in the IN clause?

I have two tables named as product_Category CREATE TABLE `product_category` ( `pid` int(11) NOT NULL, `cid` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `product_category` -- /*!40000 ALTER TABLE `product_cate

Subqueries / join on the same table

I have a table that looks like the following Table tbl_veh VIN Record DateChange 11223344 123A 6/24/2012 11223344 121G 7/20/2013 11223344 2D54 2/24/2013 55445588 44D4 2/27/2012 55445588 855D 3/15/2013 So I would like to select the VIN and record but

MySQL - UPDATE WHERE IN field query with LIMIT refused

Is there any way to do the following query in MySQL? UPDATE url_source_wp SET url_source_wp.hash = "ASDF" WHERE url_source_wp.url IN (SELECT url_source_wp.url FROM url_source_wp LEFT OUTER JOIN url_done_wp ON = url_done_wp.url_s

Subquery - Join

I'm attempting to get a subquery in addition to a join statement that would display the FirstName and LastName of all customers who have had an order with an Item named 'Dress Shirt'. Then I have to report the results sorted by LastName in ascending

Max record selection for each user

This seems if it should be fairly simple, but I'm stumbling in trying to find a solution that works for me. I have a member_contracts table that has the following (simplified) structure. MemberID | ContractID | StartDate | End Date | ----------------

Counting the number of rows displayed in a table in Access

I'd like to show how many times a name appears in a field in an Access 2003 table. here's the table structure and content: I used this example here: Tabel: 1 - Name1 1 - Name2 2 - Name1 2 - Name3 3 - Name1 3 - Name2 Result Name1 - 3 Name2 - 2 Name3 -

SQL subquery returns more than 1 value

Hi I am trying to execute a query to get row between certain number , Like i am trying to get rows between 10-20. so i am using subquery so that i can use row_number() function The query fails with the error: SQL subquery return more that 1 value So

Mysql help needed to optimize the group by subquery

I seem to be a little stuck. It's quite a straight-forward query. If I run the queries separately it is not that slow but when I combine them its very slow. I'm not sure how to optimise it. Any help would be much appreciated. I'm basically only wanti

MySQL - COUNT and retrieve n rows from a subquery

Context: I have an app that shows posts and comments on the home page. My intention is to limit the number of posts shown (ie, 10 posts) and... Limit the number of comments shown per post (ie, 2 comments). Show the total number of comments in the fro

The mysql subquery returns an error

SELECT upd.*, usr.username AS `username`, usr.profile_picture AS `profile_picture` ,( SELECT COUNT ( FROM likes as like WHERE upd.update_id = like.item_id AND like.uid = 118697835834 ) as liked_update FROM updates AS upd LEFT JOIN users AS us

MySQL subquery - Find only the first record in a LEFT JOIN

I'm trying to display a list of member records, and I have a few tables I'm using to display what I need. That's the easy part. The part I need help with is with a table that has many records to each member record: Login history I want to display onl

Subquery in mysql with full-text query

I am trying to query a database. I already have a file that includes some primary keys out of this whole database. Now I want to filter these primary keys and get only those primary keys which also agree to "other condition". My primary keys are

Nested subquery T-SQL

Not being a SQL expert, and also only being semi-competent in CTE, how can I code this statement use the resultset from the following subquery within the main query, as our SQL Server is 2000. declare @subcategoryConcatenate varchar(3999) set @subcat

SQL query with a multipart identifier problem in a subquery

i've a query that is supposed to return the sum for "status"-duration entries. The duration is calculated by using datediff(n, datestamp, (subquery that returns the datestamp ending the current status, i.e. finds the next fitting "status ch