query

How to count results within results in MySQL?

I have a table of items which I'm getting from a few different online stores, like Ebay/Amazon, etc. Before today, I wanted to simply group each item based on the year it was made and the manufacturer who made it. So, the results would look something like this total year manufacturer 100 1999 man_a 32 2002 man_b Now, I wan...

Microsoft Access SQL query matching names unexpectedly

Hey all, I tried using Access for queries and it returns extremely unexpected results (where it would return records from contacts table that didn't even appear in the temp_query: SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes FROM contacts, temp_query...

Can this SQL query be made to run any faster?

I'm using mySQL InnoDB tables. The query is simple but it just takes too long to run. Its bound to run even slower once I upload it to a server (not to mention that these two tables will keep growing in size). The 'author' table size is 3,045 records. The 'book' table size is 5,278 records. SELECT author.*, count( auth_id ) AS author_c...

How would you construct this SQL query? (MySQL)

Let assume we have these tables: product product_id product_name category category_id category_name product_in_category product_in_category_id product_id category_id how would you get all products that are not in specific category in the product_in_category table (without duplicates). In other words, all products t...

SQL Server: Get table primary key using sql query.

I want to get a particular table's primary key using SQL query for SQL Server database. In MySQL I am using following query to get table primary key: SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY' What is equivalent of above query for SQL Server ?. If There is a query that will work for both MySQL and SQL Server then It will be...

Select max value rows from table column

my table look like this.. id name count -- ---- ----- 1 Mike 0 2 Duke 2 3 Smith 1 4 Dave 6 5 Rich 3 6 Rozie 8 7 Romeo 0 8 Khan 1 ---------------------- I want to select rows with max(count) limit 5 (TOP 5 Names with maximum count) that would look sumthing like... id name count -- ---- ----- 6 Rozie 8 4 Dave 6 5 Rich 3 2 Duke 2 3 Smi...

query on %age share in sql

How do we calculate the percentage market share in sql ON MYSQL 5.1 version? I need to find the %age share of each manufacture.The market share is given by number of handsets. ...

SQL Server Query Problem?

I was using MySQL's concat_ws function to get data from MySQL database and it was working perfectly. I discover this query through this SO Question. Now I have same case but this time database is SQL Server. Asking again for SQL Server: I have a table like this: id | roll_no | name --------------------- 1 | 111 | Naveed 2 | 22...

MySQL query optimization is driving me nuts! Almost same, but horribly different

I have the following two queries (*), which only differ in the field being restricted in the WHERE clause (name1 vs name2): SELECT A.third_id, COUNT(DISTINCT B.fourth_id) AS num FROM first A JOIN second B ON A.third_id = B.third_id WHERE A.name1 LIKE 'term%' SELECT A.third_id, COUNT(DISTINCT B.fourth_id) AS num FROM first A JOIN second...

to calculate sum() two alias named columns - in sql

To calculate sum() of two temp column names declared in query - in SQL stud table has only two columns m1,m2. total and total1 is given as temp name. select m1, m2, SUM(m1) + SUM(m2) as Total, SUM(m1) + SUM(m2) as Total1 from stud group by m1, m2 How to calculate grandtotal as sum(total)+sum(total1) with the col...

LINQ to SQL: Many to many with Multiple primary keys?

Ok this is something a bit simple but as I'm new to Linq and to SQL overall I guess I need a little help. Most tutorials assumed some sql knowledge and therefor weren't as helpful and also hard to search as I do not know this join name ( Inner left? ). Situation: TableParent with 2 primaryKeys, ParentKey1 and ParentKey2 TableChild with...

How to query column history from a "memo" field in Access 2007?

Trying to construct a query in Access 2007. One of the columns has the data type "Memo". Sometimes the latest version is returned and for others the display is blank. Only if I right-click and select "Show column history" on each row, can I actually see the full set of data in this column. Is there a way through the query designer or...

SQL string match numbers, varying length

I am looking up which exchange services which telephone numbers, from a table of fragmentary numbers that show which exchange services them. So my table contains, for example: id |exchcode |exchname |easting|northin|leadin | ----------------------------------------------------------------- 12122 |SNL/UC |SANDAL ...

and/or conditions in a SQL query

I have to select all the records where FIELD1 is value11 or value12 and FIELD2 is value21, value22, value23, ... or value29. All 2*9=18 pairs of admissible values for FIELD1 and FIELD2 are possible. Which is the most compact form to write down my SQL query? ...

How to write a SQL statement to use "LIKE" for 9 different values?

I have to select all the records where: where FIELD2 like '%value21%' or FIELD2 like '%value22%' or FIELD2 like '%value23%' -- repeat up to or FIELD2 like '%value29%' Here, value21, ..., value29 are parameters which the user can put in a form before submitting the query. They are (not subsequent) numerical codes, and FIELD2 is a dat...

Help with MySQL Query syntax: ERROR #1066 - Not unique table/alias

I have four tables, user, user_billingprofile, user_shippingprofile, and user_address. user: userId, dateCreated user_billingprofile: userId, address user_shippingprofile: userId, address user_address: random address crap Here is the query I have to get a users billing and shipping profiles in one shot. SELECT * FROM `user` JOIN `us...

Query to get ALL matches for a particular column

Suppose I have two tables Table1 and Table2 with the following data. Column1 Column2 Column3 AAA KKK 9 BBB LLL 7 CCC MMM 9 DDD MMM 5 EEE MMM 7 FFF NNN 9 GGG OOO 1 Column4 Column1 TTT DDD TTT BBB UUU EEE VVV BBB WWW AAA WWW BBB XX...

Magento: which catalog_product_flat I need to select from? and what is catalog_product_entity_datetime table?

Hi everyone, To know who are the customers needed to be sent the notification mail and know which are the products remain 2 hours before the deal expired, I need to select from wishlist, wishlist_item, customer_entity and catalog_product_enity table which need to join with catelog_product_flat_X But there are many catelog_product_fla...

ActiveRecord won't let me create new rows, but won't tell me what's wrong

I am using Devise with Rails and ActiveRecord. I have a User model with some base information. I also created 2 other descendants to that: Patient and Doctor. My problem is ActiveRecord is not creating new rows at this moment, and I can't figure out what is wrong. It creates rows on other tables of the db, but I don't understand why it's...

How do I select the nth element in a Collection by the number in an @IndexColumn?

I have an ItemEntity class, which has a collection of ImageEntity's. I want to be able to fetch a specific ImageEntity, given its index in the collection. For example, for an ItemEntity with 10 ImageEntity's, I want the 4th ImageEntity in as few database hits as possible. I've read Hibernate In Action and googled, but every piece of d...