[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Query Syntax

52K

Solutions

20K

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

Hello experts!

I need to make a joined query in PHP to get data from one table, depending on which user makes the request. In a previous question, I learned how to do exactly that but, this time I also need to get data from a second table. This is my schema:

DB schema
What I need can be described in the following scenario:

- User John (user_id 1) needs to see all the entries to which he has access to.
- PHP fetches him all the entries because the matrix table says that users with the user_category 1 can see every entry from entry_category 1 and 2.

I've got so far. The problem is that I also need the info for the author of the entry. The way I'm getting it, it brings me the entries and the info for the user requesting it. I guess (a wild guess) I need to add an OUTTER JOIN or something like that to my query? This is what I have:

SELECT entry.*, user.*
FROM entries entry
INNER JOIN matrix mx ON entry.entry_category = mx.matrix_entry_category
INNER JOIN users user ON mx.matrix_user_category = user.user_category
WHERE user.user_id = :uid
ORDER BY entry.entry_id ASC
LIMIT 10

Open in new window

Later, the :uid param will be binded to the user requesting the entries (browsing the page).

This will give me a multidimensional array with one key for each entry and user info, but not the user that wrote the entry. It will just repeat 10 times the info for the person browsing.

What I need would be something like this:

Array
(
    [0] => Array
     

Open in new window

0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

I created a scalar function to encrypt the plaintext CreditCardNumber using this SQL function with a strong phrase and varbinary "salt" of tableid and recordid.  The server is also encrypted and physically secure...the passphrase is stored in a text file that requires admin user account to access and must be read each time the function is executed.  

The decrypt function that makes it to front end UI is masked with only the last 4 showing.

Is this enough to be compliant?
0
Hello Experts,
                         We are attempting to disable TLS 1.0 and 1.1 in our servers. When we did that, our classic asp application did not work and we found that it is using SQL SERVER driver with SQLSRV32.DLL. It threw SSL Security error.
 
This is the actual connection string we use for Classic asp:
DRIVER={SQL Server};SERVER=XXXXXXXX;DATABASE=XXXXXXX;uid=XXXXXX; pwd=XXXXXXX
 
We changed the driver to the SQL SERVER NATIVE CLIENT 11.0. and it solved the problem. Everything else worked except for one page, the monthly calendar page. In the page, we generate the whole HTML for the page data in the database base table and append it directly on the .asp page using response.write.  The page does not throw any error, it simply does not load the data that we get from Database using a Stored Procedure.
 
When this did not work we used AJAX to get the html data. Even this did not work.
 
We tried saving HTML data in a variable which also did not happen.
 

We tried adding Response.ContentType = "text/html"
https://msdn.microsoft.com/en-us/library/ms524327(v=vs.90).aspx
 
we installed Microsoft® OLE DB Driver 18 for SQL Server with SQL Server Native Client 11 mentioned as Provider in the connection string. we got other errors.
 
 Is there any restriction with TLS older versions when we get the HTML directly and append it in a page?
 
 
0
Can someone please help me to change my query slightly?

I have a simple database table  which is named "City". It includes the id of the district, and last names of people in the district.
As you can see in the sample table the last names occur more than once, as there are many people in the city with the same last name in different districts.

I have a query which counts the number of unique names in the city by district. The name will not be counted more than once. Meaning if it was already counted in a previous district, it won't be counted again. So for example you can see in the query result that last name "Marconi" was only counted once, as it was counted in District 1, but not District 2.

The query works great, but it needs to be modified slightly so that it will also display the total of all of the names in a district, regardless if there are repeat names in a district. Please see the "Desired result" below to see what I need my query to produce. Attached is a copy of my table with data which can be recreated in sql server by running the attached script. My sql is very generic, so that it runs on other sql platforms like Sybase and Oracle.


My Working Query :
select t.Cityid, t.DiD, count(FamilyName) AS DistinctFamilyNames
from (
    select
            CityId, MIN(DistrictId)AS DiD,FamilyName  
    from City WHERE CityId = 'Austin' Group By CityId,FamilyName
    ) t
group by t.CityId, t.DiD;



City table :

CityId  DistrictId        …
0
help with a pivot table.  
i have a query that will correctly pivot on all the unique instances of variables for the specified column except for variable.
This variable is a blank.   i can't get it pivot for that column.
Please look at the line that has the yellow highlight in the attachment.   The data in the column doesn't have null fields.
pivot-table.JPG
0
I have read turning on auto shrink on databases in SQL Server is a bad idea as it can cause 'fragmentation'. What is your view on this - and from a tech free risk assesment what may fragmentation cause in terms of performance or any other visisble symptom to  a database and its over arching application. Is there any official guidance from microsoft on the risk vs reward of turning on auto shrink on databases.
0
I don't know if there's another way of doing this besides what I have in mind and I'll note it below...

Some users have access to all Scheds and some users only to specific ones. When FilterSched is false, it means user has access to everything and has to get all the rows. If FilterSched is true, means user has to get all the rows.

I'll go step by step with an example.

1. I create the temp tables
CREATE TABLE #Main -- main table that has all the rows
(
  SchedID int
)

CREATE TABLE #Authuser -- all the users
(
  ID INT,
  FirstName VARCHAR(100),
  FilterSched bit
)

CREATE TABLE #AuthuserSched -- users and the scheds they have access to
(
  ID INT,
  SchedID int
)

 CREATE TABLE #FilterScheds -- holds list of scheudles user has access to
 (
   SchedId int
 )

Open in new window


2. Populate tables

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 88, 'Edward', 1 -- has filter and will get data with the INNER JOIN I have because #FilterScheds table holds whatever Sched this user has access to

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 14307, 'Camillia', 0 -- doesn't have filter so this user gets all the data (*** but that INNER Join is going to fail because #FilterScheds doesn't get populated)

----

INSERT INTO #Main -- all my data
(
    SchedId
)
SELECT 9999

INSERT INTO #Main
(
    SchedId
)
SELECT 555

INSERT INTO #Main
(
    SchedId
)
SELECT 4567

---

INSERT INTO #AuthuserSched -- user 88 has access to Sched 9999
 SELECT 88,9999

Open in new window


4. Now I join to get the data for user (I have this in a stored proc )

 --1. see if this user has a filter
 DECLARE @filter BIT
 DECLARE @userId INT = 88 --- run it for user 88 first, then user 14307
 
 SELECT @filter = filtersched FROM #Authuser
 WHERE id = @userId

 -- 2. now that user has filter, get their Scheds
 IF @filter = 1 --user 88 needs filtering
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
  
  ---3. now get the results. I have all Scheds user has access to. So, I'm doing an inner join

  SELECT * FROM #Main
    INNER JOIN #FilterScheds ON #FilterScheds.SchedId = #Main.SchedID --ISSUE is here --*** for user 88, I only need what Scheds he has access to. For user 14307...users that don't have filter...all the rows should come back
	                                                                      -- 
  
  TRUNCATE TABLE #FilterScheds -- clear the table and run it again for user 14307

Open in new window


My solution is to do this. A better way of doing this?

IF @filter = 1
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
else
 begin
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #Main --- get all the scheds that MAIN has for users that have access to everything. So, #FilterScheds is always populated
end 

Open in new window

0
I have an SSIS project that I created in Visual Studio.  It includes two packages.  One package exports data from SQL tables into an Excel spreadsheet.  The other iimports data from an Excel spreadsheet into a SQL table.  When I run it in Visual Studio on my development machine, both packages work properly.  But when I deploy it to another server as an Integration Services Catalog SSISDB package, the export from SQL to Excel works, but the import from Excel to SQL fails with the following message:

"SSIS Error Code. DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager 'SQLSERVER' failed with error code 0xC0202009."

Then a second error pops up:

"An OLE DB record is available. Source: 'Microsoft SQL Server Native Client 11.0'. Hresult: 0x80004005. Description: 'Named Pipes Provider. Could not open a connection to SQL Server [53]."

Both packages use the same connection manager (called SQLSERVER).  I made sure that the Run64BitRuntime setting is set to False and DelayValidation is set to True.

Again, the export from SQL to Excel runs without error.  It is the import from Excel to SQL that is throwing the errors.

T
0
Hello,

I have to select all items from a MySQL table using PHP but from categories that the user is allowed to see. I've tried having all the categories in an array and then imploding them while using the IN statement in the SQL query (tried like 10 different methods with INT and STRING arrays) with no luck. The problem is that I'm getting dizzy while trying to figure out a somewhat complex (at least for me) JOIN statement.

I have the following tables:

Sample tables
As you can see, it should be pretty easy except that a user may have more than one category assigned to it. How can I show Helen in this example all the products and/or categories she can see in a single query?

Thanks in advance.
0
I just upgraded a series of SSRS reports from SQL 2014 to SQL 2017. For some reason on two of these reports SSRS is inserting page breaks in between the groupings. There are no breaks and there were none when displayed using SQL 2014. What can I can examine that would explain this? I am using VS 2017 Community to deploy the reports.
0
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Looking for a query to query all non-system tables in an Oracle 11g database for a given value
Additionally, If you can given a flavour of the same query which can search all the table starting with "ENV"  or "DOC"  , it is much appreciated
0
Hello all,

I often calculate frequencies of values in SQL as follows:

SELECT MyField, COUNT(*) AS [Frequency]
FROM MyTable
GROUP BY MyFIeld

This is a simple quick thing to do and I can type it out in a couple seconds when I'm working through a file trying to get a sense of what's in there.  

What I would really like to do is to add the Percentage to my results (just as quickly).

Is there like a secret trick to be able to do that?  

TIA!
0
Hello!

I have a simple SQL query in a c# project that I have to convert to LINQ but am struggling with it due to my very limited experience and understanding of LINQ queries.  What's stumping me is how to handle the summing and grouping.  This is a pretty time sensitive project for me and once I see how it's done I'll be able to understand and build on it.
Here is the query:
SELECT Dept, Shift, ShiftDate, FLOOR(SUM(RunningGoal)) AS Target, SUM(UnitsProd) AS Produced 
FROM Vw_PRODCOUNTER_HourlyProduction 
WHERE Dept = '3500A' AND Shift = '1' AND ShiftDate = '12/13/2018' GROUP BY Dept, Shift, ShiftDate

Open in new window

Thanks for your time and help in advance.  Have a good day!
JeffH
0
In SQL Server 2012, Is there a way to create a table variable that references a table already in the database?  Note: I have a string variable that contains the name of the table.

DECLARE @tablename As varchar(MAX)
DECLARE @TB As Table

SET @tableName = 'table1'
SET @TB = table with name 'table1'
SELECT * FROM @TB

Note: I'm asking if I could do this without dynamic sql,  I already know that I could do 'EXEC sp_executesql 'SELECT * FROM ' + @tableName.
0
Hello Expert,

Need to identify all tables in database
that begin with prefix  'D_'.

In PL/SQL (Oracle 12c) have tried

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE
WHERE TABLE_NAME LIKE 'D_%'

SELECT table_name
FROM INFORMATION_SCHEMA.tables
WHERE name = 'd_%';

Both of these return
'Invalid table name'

Think these may be for SQL Server

Tried

SELECT TABLE_NAME
FROM ccm.user_tables
WHERE TABLE_NAME LIKE 'D_%'

This returns 'Table or view does not exist'

Thanks.

Allen in Dallas
0
I can't figure out why this SQL brings back the same amount of rows with and without the where caluse (been at it for a day...took joins out, etc and I still can't figure it out). I'll keep at it and if I figure it out, I'll post back.

1. I can't attach the SQL here. It's too big to attach here. You can download it from here and create the table with the data. https://drive.google.com/open?id=1WVbU7EeC-r748RM8dVa74guQvR-DAxbk
   It's 55645 rows of data inserted into a table.

2. This works fine. Brings back 53678 rows with the "where" clause

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 

  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN 

Open in new window

0
The database has a set of tables that, when an user clicks the Edit button,
creates a draft copy that preserves a copy of the record in case the user
discards the edit. The d_address table mirrors the address table.
The d_employer table mirrors the employer table. There are dozens of
pairs.

For rather complex reasons drafts that are older than a couple of months cause
primary key constraint violations and errors in the front end application.

So the boss says delete all drafts older then three months.

A SQL script is written:
Delete FROM
    ccm.d_address
   where trunc(last_update_date) < '03-SEP-18'

And so several dozen of these scripts should be
written. All the table begin with d_  with syntax
d_tablename.

Is there a way to loop thru (or other method) the tables deleting older
draft records?  

Thanks.

Allen in Dallas
0
SQL Server 2012 Database. I have a table with a Clob column (This Table was Loaded from Oracle DB via Net Change. The source Oracle table stores BLOB(PDF files) as CLOB for ease of query).

I have been trying to convert the CLOB column back to PDF.

An Example: -----Begin Example-----------------------

%PDF-1.5 %ýýýý 1 0 obj <>>> endobj 2 0 obj <> endobj 3 0 obj <>/XObject<>/ProcSet[/PDF/Text/ImageB/ImageC/ImageI] >>/MediaBox[ 0 0 595.32 841.92] /Contents 4 0 R/Group<>/Tabs/S/StructParents 0>> endobj 4 0 obj <> stream xýýX[oý6 ~,ýEýývýýý ,ýý <9ý'ý)ýý ýT ýýyý ýýy6ýl/ýSý"ýrýýýýýýýýýýýýýýýýýýý/ýýýýýý ýýýr?ýýý ýýýý*ýýrT\ýýý0%" )ýý %jýoýýz!ýW ýýýý ýý7ýiý3ý)ý ýDý0G wý ýHFýbý(ýýRX ýý\ýý yPýýý! VA ýýýCý ýGýQx ý'ýCqýý b"ýýV94ý?ýNýý ýý ýýý3>ý 8týxý ýCý #ý ýqý ýýrý] ýCýýý8 ýBSýCýW cýRýýa ý 'ý 0Sý< ýaývWýý 3ý:ý9ý6ýýjý ýmxýo#ýYý2ýýz ýqýmýW ýýV ýýý)ýýýnoýýýýýý ýýýpýýý ýýn _ýN6 ýnýý ;ýýH ý#ýý nO$Qýý-ýý}ýýHýpýý= ný`ý^yýpýýý , 'ý Frý3ýý# ýýýSýbý1GýCýýýýý ýýýýý2aý ýýýýýýpýýýýý ýýg"ýýýýsýýnvýZýýýý ý&aýýýý ý DýýGýýý ýýýýýý[Lý%G bý/- ý:Psý/ > stream xýý} Týýýýý5ý9ýýýýýýýýýZ{ý=ý

-----End Example-----------------------

I know Adobe should be able to interprete the code(example abve): http://lotabout.me/orgwiki/pdf.html

Is there a way to convert the above example back to PDF using anything possible, Adobe tool, any other tool, Structured Query Language(SQL) etc?

Kindly assist.
0
Is there any way a simple SQL Delete statement using a WHERE numeric column = numeric value can result in an Arithmetic overflow error converting numeric to data type varchar without a trigger?

delete OrderMeetings  where OrderMeetings.ORDER_NUMBER = 71935.00
Arithmetic overflow error converting numeric to data type varchar.

Open in new window


I have a customer saying that they are getting errors when deleting a row in a table. They can literally go into SQL and run a delete statement - as shown above - and it gives an Arithmetic overflow error converting numeric to data type varchar. They swear there are no triggers on the table. They sent us a copy of their database - no triggers but we can't replicate it either. However they say it always happens every time for them.

The ORDER_NUMBER column is numeric(15,2)

Any ideas?

They are using Microsoft SQL Server 2016.
0
CompTIA Cloud+
LVL 12
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Hello Experts

Can some please take a look at the attached table and help me write a SQL query that will

a.      Group by 'FK_DATE' column and order.
b.      For each value of FK_DATE, lookup on a static table identify the Date_Range_ID
c.      Append the records for that date to a <Date_Range_ID> file.

I hope this makes sense to you guys

Thanks
testsql.xlsx
0
Hi
Finally, after buying a second-hand book on SQL Server two days ago I managed to spot a recommended code on linking access app to SQL server database tables see below what they are saying:
When writing VBA code in Access, ADO (ActiveX Data Objects) is the preferred object model for working with SQL Server. However, the DAO (Data Access Objects) object model is more efficient and full-featured for working with Access objects, and tables linked to SQL Server are Access objects. So although you could use ADOX code to create links, the code presented in this chapter uses DAO.


I want to put the attached code in a module and call it using an autoexe macro at start , now can somebody help on the following items within the code:

(1)      Set tdf = db.TableDefs(LinkedTableName) How to list 120 tables here, show me an example ??
(2)      db.TableDefs.Delete LinkedTableName (What do I put here? Any example)
(3)      Set tdf = db.CreateTableDef(LinkedTableName) How to list 120 tables here, kindly show an example
(4)      tdf.Connect = ConnectionString  (Is it the ODBC with Less DSN File string????)
(5)      tdf.SourceTableName = SourceTableName (How to list 120 tables here, kindly an example??????????)


Public Function LinkTableDAO( _
LinkedTableName As String, _
SourceTableName As String, _
ConnectionString As String) As Boolean
‘ Links or re-links a single table.
‘ Returns True or False based on Err value.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db …
0
Hi Experts,

We have data in Caspio that we are trying to export.
When we export them to Excel or CSV they work.
However when we export them into Access, some records fail.
This is what their support team responded...
“Despite of Microsoft's declaration that record row in MS Access is limited to 4000 symbols excluding Text64000 fields, this is not exactly true. If you download attached package, import it and open table in datasheet then add at least 1 symbol into TestColumn3 field (which is Text64k) you will not be able to download table in Access format anymore. Without this symbol it will work fine. In addition metadata of table is also present in some way in the record row so if you add any new column into attached table you won't be able to download it either. There is nothing developers can do with it as it's a Microsoft bug and beyond our reach. If you move the database to SQL there will not be any issues.”

Have you had any experience like that?

What do you suggest I do in order
1- figure out what is causing those records to fail.
2- fix/avoid it in the future.

Thanks
0
I need to get Microsoft SQL Server Mgmt Studio to connect to an Oracle database. I am having issues w/ getting the Oracle data access component -    OraOLEDB.Oracle available and showing on the SQL client under Linked Servers and Providers. Ticket-Details.docx
0
there are two tables:
tblMain and DataUPdated
everything is the same, the only column Rate from tblMain should be replaces with Column NEWRATE from DataUpdated

this is select statement to get results below:

SELECT DataUpdated.Status, tblMain.Rate, DataUpdated.NEWRATE
FROM DataUpdated INNER JOIN tblMain ON (DataUpdated.AccNum = tblMain.AccNum) AND (DataUpdated.RATE = tblMain.RATE)
WHERE (((DataUpdated.Status)="Pending") AND ((tblMain.Rate) Not Like "TOU*") AND ((DataUpdated.NEWRATE) Like "TOU*"));

tblMain.Rate  DataUpdated.NEWRATE
DOMESTIC      TOU-D-B-SDP
D-SDP              TOU-D-B-SDP
DOMESTIC          TOU-D-B
DOMESTIC          TOU-D-B
DOMESTIC          TOU-D-B

how to change the select  query to update query and update the fields
0
I need to nest these queries together.

However the problem is  [Contracts].[dbo].[CONTRACT_DATA]( the botton query) is  in a different database called "Contracts"


This first query:
SELECT rcs.[CONTRACT_NUMBER] -- [program].[dbo].[rdr_contract_staging]
      ,VENDOR_CODE = CAST(rcs.VENDOR_CODE AS VARCHAR(250))
      ,rcs.[LIFE_CYCLE_MANAGER]
      ,CTS.[Sum_Of_Sales_Current]
FROM [program].[dbo].[rdr_contract_staging] rcs
LEFT OUTER JOIN (
      SELECT [SS_TRACKCODE], -- [program].[dbo].[CURRENT_TC_STC_CUST_SALES]
          Sum([sls_sum]) AS [Sum_Of_Sales_Current]
      FROM [program].[dbo].[CURRENT_TC_STC_CUST_SALES]
      GROUP BY [SS_TRACKCODE]
) AS CTS ON CTS.SS_TRACKCODE = rcs.VENDOR_CODE

to this select statement below:

[CONTRACT_NUMBER]   to     [contract_number]
(second query)
SELECT   [Freight_GIS]
        ,[CustomerName]
        ,[contract_number]
        ,[ContractName]
        ,[EffectiveDate]
        ,[ExpirationDate]
        ,[status]
        ,[Sales_Segment]
        ,[Sales_Region]
        ,[RSVP_Dir]
        ,[Sales_Manager]
[Contracts].[dbo].[CONTRACT_DATA]
WHERE (dbo.CONTRACT_DATA.status)="active/complete");

Thanks
fordraiders
0

Query Syntax

52K

Solutions

20K

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.