query

Comparing SQL Table to itself (Self-join)

I'm trying to find duplicate rows based on mixed columns. This is an example of what I have: CREATE TABLE Test ( id INT PRIMARY KEY, test1 varchar(124), test2 varchar(124) ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'A', 'B' ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' ) Now if I run this query: SELE...

SQL query to return top X sequential descending rows, by group, for a particular value..

I need a query to return, by group, a true or false if the most recent x number of sequential rows, in descending date order, have a column with a false value where x can be different for each group. For example, a Configuration table would have the number of records that have to match sequentially by companyId and serviceId: CompanyI...

Comparing rows from two tables and and ouputting the result

Hello guys I've been trying to compare two similar tables that have some different columns Table 1 has columns ID_A, X, Y, Z and Table 2 has columns ID_B, X, Y, Z If both values from columns X or Y or Z are = 1 the result of the query would output columns ID_A, ID_B, X, Y, Z I thought it would be an intersect statement in there, ...

Creating an MS Access query based on system, status, and date

I am just learning Access and have been tasked with creating a database for system access. We need a report that shows what systems have been in a pending status for 0-30 days, 31-60 days, 61-90 days, and 91+ days. I am soooo close to getting this but I’m at a dead end. For the examples below I will just use NFC and eOPF as the system...

How to get pages.path

I have the following two tables. I want to get all from menus table and also path from pages table. I tried it but I am not able to get the pages.path. Can anyone point out my mistakes please. CREATE TABLE IF NOT EXISTS `pages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `keywords` varchar(2...

Complicated query - help NEEDED!

Hi, really hope someone can help me on this one! I have 6 tables: Products prodid Prodequipment prodid equipclassmain equipclassor Equipclasses classid Equipfunctions equipid classid Equipment equipid Workshopequipment workshopid equipid Products – a list of some products Equipment – a list of some equipment Prodequipment – lists...

MS Access: searching a column for a star/asterisk

I'm looking for a way to search a column of string datatype which contains a * - the problem is that the star or asterisk is a reserved symbol. The following query doesn't work properly: select * from users where instr(pattern,"*") How can you write an Access query to search a column for an asterisk? ...

Query to get Max/Min row details for multiple fields

I have a table structure similar to the following example: DateTime V1 V2 V3 V4 10/10/10 12:10:00 71 24 33 40 10/10/10 12:00:00 75 22 44 12 10/10/10 12:30:00 44 21 44 33 10/10/10 12:20:00 80 11 88 12 With DateTime field being the unqiue and key field, I want...

Getting all possible combinations which obey certain condition with MS SQL

I need to constract an SQL query but I have no idea how to do it. If someone helps, I'll appriciate it very much. I have the following table GroupedBYField ConditionField ToBeSummeField 1 1 1 1 1 2 1 ...

Left Joins that link to multiple rows only returning one

I'm trying to join two table (call them table1 and table2) but only return 1 entry for each match. In table2, there is a column called 'current' that is either 'y', 'n', or 'null'. I have left joined the two tables and put a where clause to get me the 'y' and 'null' instances, those are easy. I need help to get the rows that join to r...

Search entire table? PHP MySQL

Hello, I have made the following search script but can only search one table column when querying the database: $query = "select * from explore where site_name like '%".$searchterm."%'"; I would like to know how I can search the entire table(explore). Also, I would need to fix this line of code: echo "$num_found. ".($row['site_name']...

why does mysql inner join query take so much time

in mysql i have two tables tableA col1 col2 SIM1 ..........col24 ----------------------------------- a x 1 5 b y 1 3 c z 0 2 d g 2 1 tableB colA colB SIM2 ------------------- x g 1 y f 0 x s 0 y e 2...

How do I allow the user to select data from a combo box in a Query

I have a database which lists students who have went on exchanges from 1986 onwards to about 30 institutions. I have set up the file as follows: File name: 1986 onwards Table: Incoming Students Field: Institution I set up the query with the following parameter: [Enter Institution] - the problem is the institution can be spelt in vari...

SQL Query to compare

Hi, I am working on an Access database and in it I need to extract several reports. For one of them I need to compare two lists of numbers, List1 and List2(I get both from separate queries) and display only those numbers that are in List1 but not in List2. Can anyone help please? Thank you. ...

LINQ to Entities Group By Driving Me Crazy

I have a LINQ query written in VB.Net that uses Joins to return records from a relational database. I am trying to add a GROUP BY statement, but whichever way I cut it, I either get an error or Visual Studio Intellisense does not allow my format. This is what my query looks like without the GroupBy: Public Function GetBadges(ByVal ser...

Solid white screen after a simple PHP search query even though there is html

I'm making a very simple search program for my database. When I run the query, if I search for data that I know is there, it returns the info I wanted it to and displays it. If I enter data that I know does not exist, it goes to a solid blank page even though there is html behind it. Why? Any clue? I wish I had a better way to descri...

how i cant select from mysql without duplication

my problem is too complex now i have files table I selected from it the files which added in last seven days. it returns many ids like 1,2,3,4,5 ok it's great. Now the problem: i have tutorial table every tutorial has some files of files table now i selected from files which last added from 7 days and make list ordered by files do...

MySQL LIKE statement

Hi guys. I have a SQL query SELECT * FROM table WHERE column LIKE '%pdd%'. The problem is I need to get all results excluding those which start with "pdd", I mean find everything where "pdd" is not at the beginning. How could it be done? Thank you. UPD. Sorry, I should've clarified my question a little bit more. I do need to match "p...

SQL query in MySQL using GROUP BY

Okay so this query should be easy but I'm having a bit of difficult. Let's say I have a table called 'foo' with columns 'a', 'b'. I'm trying to figure out the following in one query: select how of column 'a' are available of type column 'b', this is done with the following: mysql> select count(a),b from foo GROUP BY b; that's straigh...

Select newest entry from a joined MySQL table

I have stock quantity information in my database. 1 table, "stock", holds the productid (sku) along with the quantity and the filename from where it came. The other table, "stockfile", contains all the processed filenames along with dates. Now I need to get all the products with their latest stock quantity values. This gives me ALL th...