Query Syntax

48K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am creating a simple messaging application using mysql and php.
 
This is my simple database and I have put some example data in it. You can see user 1 and 4 have been having a conversation, then there is a few other single messages.

example
I want a list of who has had a conversation with you... like it does on the iPhone…. Eg...

- user 4 should only see 1 entry for the conversation between himself and user 1.
- Then they would click that conversation to show the full messaging history between the 2 users.
 
From the db above for user 4 I would want to see…..
 
User 1 / Message 5 / 2017-07-23 10:13:00
User 2 / msg / 2017-07-23 10:20:00

Currently my simple query looks like this:

SELECT *
FROM message
WHERE userIDTo = $userID
OR userIDFrom = $userID

Open in new window


but this query shows a list of 8 messages, but i only want it to show the most recent messages... in this scenario it would be 2.

Hope my explanation makes sense and you can help.

Thanks

Steve
0
Quick Start: DOCKER
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Help me on this sql.

I have the two tables
batch_info
document_info

i need to update the STATUS = 'COMPLETED' IN Batch_info table
where the module name is = 'EXPORT' Based on Document_info table
where the release ='1' record.

Here is the example.

Batch_info

EXTERNALBATCHID,NEXTMODULE,NEXTSTATUS
36267,EXPORT,READY
36264,EXPORT,READY

Document_info
DOCGUID,RELEASED,EXTERNALBATCHID
{B7FE744A-DE4D-4b1d-AEFA-65C3F40B2608},1,36267
{CDAAD0C0-AF11-4b53-97CF-C00D62C50F98},1,36264


Regards,
Praveen
0
Hi all.

I have a 2003 AD forest, all Domains are 2008 R2. Forest has 2 domains -  one.two.com
I have SQL servers in domain two.com. My account mike@two.com has access to SQL servers.

Here's my question. If I migrate my user account to domain 'one', would I by default still have access to my sql server?

Thank you for looking
0
Hi.

I have a table A with a number of records. In table B is  a number of relating records. Say there is 100 records in table A, then there is a number of matching records (matching ID) in table B, but it may be 0 or more records for each record in table A.
When I make a query with LEFT JOIN, then I get less than 100 records. I don't understand why. My syntax seems straight forward like:

SELECT A.id, A.var1, A.var2, B.id, B.var1 LEFT JOIN B ON A.id=B.id WHERE B.var1='test'

Open in new window


Am I missing something? I tried LEFT OUTER JOIN  as well, but no difference. I want  all 100 records and just a "NULL" or something if a related record in table  B is  missing.

Best

Ulrich
0
Guys,
Any idea how do I use this kind of query in Mcsft access ? If I have one text box and I would like to get the text box having data from do.cmd sql. how  could I do that ?

For example how do I use one of the text box to display its records base on the do.cmd runsql below ?

   Public Sub DoSQL()
   
        Dim SQL QtyBorrow As String
   
        QtyBorrow  =
        "SELECT count (*)  " &; _
        "FROM MPCRHB_Fail INNER JOIN MPCRHB_Pinjam ON MPCRHB_Fail.No_Fail = MPCRHB_Pinjam.NoStaff " &; _
        "WHERE MPCRHB_Pinjam.Tarikhpulang Is Null And MPCRHB_Fail.No_Fail=Forms!MPCRHB_User!NoStaff;"
         
    DoCmd.RunSQL QtyBorrow
0
Hello,

I'm trying to get the data from other field that start with 59 and has 24 characters, I used this string:
Formulas: Mid([SEDA Transactions].[Comments],InStr([SEDA Transactions].[Comments],"59"),24)
Get the code but also the #Func! Error.  Please advise.
0
We are converting our databases from mysql to sql on a windows server. I am slowly figuring out the small changes in php coding but can't get it to pull date or time fields.

If I have a simple "Select PName from info" and echo $PName it works. but once I add in "Select PName, SCHTM, SDate from info" and try to echo $PName - $SCHTM - $SDate I get a blank page. SCHTM is a time(7) field with data like 12:45:00 and the SDate field is a date field with things like 2017-08-17    

During the conversion process the SCHTM field now shows 12:45:00.0000000 instead of just 12:45:00
0
I have a table with the following columns
Ticket Number
Assignee

There is a separate table listing all assignees - something like a list of all the staff.  Each ticket can have multiple assignees.  Assignees can work on multiple tickets.

When the user selects a ticket I want a list box to show total staff with the assignees on that ticket highlighted.  The user should be able to add and remove assignees from a given ticket.  The list of selected assignees need to be passed to an SQL stored procedure.
I'm using C# in Visual Studio 2010.
0
Hi experts, I have a problem obtaining data in sqlserver in which in my sql command I need to advance 1 day of my time end before able to obtain the data.  My data field in sql is look like this, "2017-08-17 10:00:00.000"  this means that it has a time and this is August 17, 2017. In my sql command I cannot obtain this record if i set the time end to "08/17/2017" but  "08/18/2017" meaning i need to advance 1 day. Is there anyone who can explain me why. What shall i do to make my sql command set "08/17/2017"?

Set rs = cn.Execute("Select Proname, TheDate,  from sTable where TheDate Between '" & "08/17/2017" & "' And '" & "08/17/2017") & ") 

Open in new window

0
DB2 10.5 on Windows

I have a query I am trying to optimize and I want to test a couple of different things.  The issue is that after I run it for the first time the data is in cache so first run is 5 minutes and next run is 15 seconds.  In MS SQL I would clear the execution plan for the query before each run to compensate for that.  Is there something similar in DB2?

Thanks!
0
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Using SQL SERVER 2008 R2

I have a script that does some table renames so I need "GO" between the Renames.  I do some checks at the top to verify the table content appears normal before I allow the script to proceed.  However, this will not run because the "GO" cancels out the label at the end that I want to jump to and causes an error.

What is the best way to write this to accomplish this?

Thanks,


DECLARE @RecCount Int

SET @RecCount = (SELECT COUNT(*) FROM TEST_SAPbacklogInputTabDlm)

IF (@RecCount = 0)  
	BEGIN
		PRINT 'ERROR - ZERO RECORDS in SAPbacklogInput'
		GOTO EXIT_ME
    END;

-- Check if the file size is less than 80% of previous days file
DECLARE @OldCount Int
DECLARE @NewCount Int

SET @OldCount = (SELECT Count(*) AS OldCount FROM TEST_DashboardData) 
SET @NewCount = (SELECT Count(*) AS NewCount FROM TEST_TheNewDashboardData) 

IF (@NewCount / @OldCount) < 0.8  
	BEGIN
		PRINT 'New File Size Less Than 80% Of Old File'
	    UPDATE FileCheck  
        SET ErrorFlag = 'Y',  
            JobEndFlag = 'N', 
            Comments = 'New File Less Than 80% Of Old File'  
        WHERE FileLocation LIKE '%PeruOpenOrderExtract.xls'
        GOTO EXIT_ME
    END;


-- Looks like we are good to rename tables.

 
UPDATE FileCheck 
SET LastStepCompleted = 'Renaming Files', ErrorFlag = 'N', JobEndFlag = 'Y', RenameFlag = 'Y'
WHERE FileLocation LIKE '%OpenOrderExtract.xls' 
	   
	  
Print 'DashboardData table Renames...'
GO

execute sp_rename 

Open in new window

0
I have this VBA code: -
    objCmd.CommandText = "Exec [sp_UpdateSysproDispatch] '" & TxtOrderNo & "', '" & PurchaseOrderNumber & "', " _
    & "'" & SalesOrderNumber & "', '" & SalesOrderLineNumber & "', '" & SysProDespatchNumber & "', '" & SysProDespatchLineNumber & "', " _
    & "'" & DespatchDate & "', '" & StockItemCode & "', '" & QuantityToRecieve & "', '" & Warehouse & "', '" & SageStatus & "', " _
    & "'" & SysProStage & "', '# & DeliveryDate & #'"
    objCmd.ActiveConnection = adoConn_Sage
    Set objRs = objCmd.Execute

Open in new window


But I get this error: -
SP ErrorCan Anyone help?
0
I have installed SQL Server 2016 with  all the features, but when I open the management studio I am unable to connect to SSIS, all other services are availble and the SSIS  service is running. I read an article which suggested using Dcomcnfg.exe to add users to the permission for integration services which i have done. still unable to connect though

SQL server connector - Browsing for servers
0
Hi

I have to generate a report in PDF from a table in SQL Server in a VB.Net app. I have defined a report (.rdlc file), but I don't know how to generate the report. I have only found a MS article on LocalReport, but the code shown in it does not work, I'm using VisualSudio 2013.

Can someone guide me through the process ? Or should I make a WinForms app instead ?

Thnaks for help
Bernard
0
Hi

I have been trawling the net to find this but Microsoft don't seem to have put much helpful info up.
Why is there not a simple way in my Azure account to go online and set up a User that can only see and not
edit a View in my SQL database?
0
Hi,

I have data below,needs to identify top sales increase week,need to compare week wise and identify which week made highest sales increase. below query answer is 23 (Increase) column.How to achieve this please

Week    Sale          Increase
20          10000          0
21          10023          23
22          10012          12


Thanks
0
Hi Folks
I'm not sure why this isn't working. I need to retrieve all records except where a few IDs are input.
I tried NOT IN (0,1,2) and I get all data (no filter).
if I use NOT IN (0) the '0's are excluded (as required), same for NOT IN (1)
is it valid to use a comma-separated set of integers in the IN clause? (or just strings in quotes?)
thanks for any useful comment.
0
I am trying to convert a system from MySQL to Miscrosft SQL and things are going pretty smooth except for loop inside of a loop.

I have this simple statement which puts a suite number on each line:
      $sql = "SELECT * from suites WHERE Suite > '0' Order By Suite ASC" ;
      $result = sqlsrv_query ($conn,$sql);
        while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC) ) {
      $SuiteNo = $row['Suite'];
      echo"$SuiteNo<br />";
            }
What I want to do is pull data from table "info" and list it next to the suite number. I know I should do a join statement, but have tried INNER JOIN, LEFT JOIN, and others and it will only pull suites where something is in info. I need it to list the suite no and then nothing next to it if nothing is in info that matches.
Where Cust# and Date pulls from info

Output would be

Suite       Cust#            Date
2              1234156      8/1/2017
3  
4
5              985621         8/5/2017
6              548871         8/6/2017
7
8
9
0
I Need to find all the Wordpress Post that do not have a specific meta data value

select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID');

Open in new window


gives all the published posts with a DocID.
I need to filter it further on another m.meta_key that is missing from some posts

in sudo code it would look something like
select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID')
and not exists (m.meta_key = 'wanted')

Open in new window


Hoping this makes sense and someone  can show me a better way
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hello all,

I have a view that does the following as an example:

WITH cte
(
SELECT CustomerID,
              CustomerName,
              RelatedCustomerID
FROM Customers
)
SELECT 'C_' + C.CustomerID AS CustomerID,
               CustomerName,
              'R_' + RelatedCustomerID
FROM CustomerActive A --Another Table not important
            JOIN cte C ON C.CustomerID = A.CustomerID
UNION
SELECT 'C' + C.CustomerID AS CustomerID,
               CustomerName,
              'R_' + RelatedCustomerID
 FROM CustomerInactive I --Another table not important
            JOIN cte C ON C.CustomerID = I.CustomerID

Now what I need to do is get a grouped by on the RelatedCustomerID's and append to this result set the data from the CTE joined on the RelatedCustomerID without the 'R_' to the CTE CustomerID.  I don't want the non appended CustomerID (C_ and R_) just the field in the result it should be the same fields as above in the return.  One way would be to surround the whole UNION and then JOIN again but I am wondering if there is any better cleaner way etc.  It should all be a distinct list as well of the records.
0
Hi Guys,

I have got two databases on a server. Both of them have got the same amount of data, structure and stored procedures are also the same. One is for my internal testing and the other is for the dev testing.

For some reasons the same query on dev testing database calle (Spaces_SBDeployment) is very slow as compared to my own dev/test database (Spaces).

Please find below the images of the query execution results and time taken from both the databases using the same stored procedure.

This is the shared dev database which is very slow takes 21 seconds to bring back results
Shared dev database
This is the database for my own dev/test  takes 3 seconds to bring back the results for the same query and from the same amount of data
Internal dev/test database
Kind regards
0
Hi
I logged into my SQL Azure database in SSMS and set up a user called AngloUser
I then tried to run the following code to allow access to a table called zPerformanceAnglo
but got the error further on

GRANT SELECT ON schema.zPerformanceAnglo TO [AngloUser]
GO

Open in new window


Cannot find the object 'zPerformanceAnglo', because it does not exist or you do not have permission.
0
Hi

In Azure SQL is it possible to set up a user or connection string that can only see one table?

Thanks
2
I have a form linked to a sql table (tbl_employee). On the employee table I added a new column called category. Back on the form I created a combo box via the wizard. In the drop down you have three choices. General-Professional, Executive, and Manager. The selection is then stored in the table (category field). I went through a bunch of records and selected the appropriate choices in the drop down. I then verified the selections were being stored in the table. Then I accidentally deleted my combo box. No when I try and repeat the process the combox is blank even though they are still stored in the table. If I click on the combox the choices I re-typed are there but selecting them does nothing. At the bottom of the form it says: control can't be edited; its bound to unknown field category. My control source says category but still no luck. How do I get this back to its original state?
0
Hi,
I have a two tables

1) Batch_Info
2) Document_Info

In the Batch Info table having the batch information along with deleted document information. In the table we have two columns there
Document GUID and Document Count
Document Guid ={3B7F8368-DC22-41a5-BFBA-65D77672881D}|{8F72352D-1F58-4119-96F2-80603F32F06C}|{AC45F547-07A7-4534-A6AD-8A732BC38E43} ( Each document seperatd by pipe '|')
Document Count = 3

I need to write the sql to update those documents delete flag as true in the document_info table...

Like
Document Guid                                                                   Deleted
{3B7F8368-DC22-41a5-BFBA-65D77672881D}                   True
{8F72352D-1F58-4119-96F2-80603F32F06C}                       True
{AC45F547-07A7-4534-A6AD-8A732BC38E43}                    True


Regards
0

Query Syntax

48K

Solutions

19K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.