In order to ask my question on a SQL query I will use a simplified version of a table I was struggling with...
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMW
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦FIAT
So my question is: how can I select only SHOPs which have no CARS variety (e.g. a variety =1)?
In other words I am looking for a Query that would give me this outcome:
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
...because WEST HAMPSTEAD CARS, selling Mercedes only, has a CARS variety equal to 1 as well as Camdem Motors which sells only FIAT.
I tried with this query:
SELECT DISTINCT SHOP, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1
But it doesn't work.
In addition I also would like to know for example how to create a similar query for a different desired CARS variety (e.g. 2, 3...)
How to do the tabs or attach images in the Post New Topic Message box... I wanted to attach a screen shot of the table but I was not able to and so, in order to post my question, I prepared a simplified version of the table plus I had to do the tabs/spaces manually with the symbol (¦)
I have the following SELECT statement attached to a dropdown box: SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop] I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated! MikeD
I am trying to create a query that will pull a variety of data from three different tables. I've had to join four tables because one set of data is in a completely different table.
I am expecting one row of data but instead I get 4 rows. I suspect this is because I am joining a table indirectly. Here is the code:
There is only one applicable row in SDH, but four rows appear when I run the query. All four rows are identical except for the GL.GLCode column, which lists the three GL codes associated with the DeductionItems table. The fourth row is a duplicate of the third.
I read somewhere that market basket analysis finds rules with substitutes as likely as rules with complements due to a consumer behavior called "horizontal variety seeking". This is when customers buy more than one product in the same category even though they are subsitutes. For example, when people go to the grocery store and buy soda, they buy coke and sprite at the same time even though they are substitutes of each other. I was wondering if anyone has experience with this anomaly and how they solved it. I found a time series model called the vector autoregressive model which is used to find the elasticity of prices over a time period. Does anyone have experience working with the VAR model? I am having trouble figuring out what some of the variables in the model are.
I have a fairly basic query I want to make but I'm struggling on figuring out how to do it. Let's say I have some fields (e.g. Value1, Value2, Value3, etc). I simply want to do a Select statement that returns the highest value among those fields for every row in my db.
At first I thought of the built in Max function until I realized that is for within a column only.
The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...
The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?
I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...
But I am wondering if there is a way to do the select in one shot?
I have a really large table with many Proposal fields and corresponding approval fields.
Propose1 Approve1 Propose2 Approve2
Would it be good practice or even possible for me to select all proposal fields using a wildcard somehow within the select statement. If it is ok, how would I go about doing it?
I have: Dim queryString As String = "SELECT distinct [CSULOG5].[status] + [CSULOG5].[lmca_nbr] FROM [CSULOG5]" I even tried & instead of +. but it doesn't like it. Is it legit syntax for mssqlserver? This is a vb project aspnet
Could anyone tell me what governs whether a column is set as nullable or not nullable when creating a table using SELECT...INTO. It just seems to pick at random for me! I'm quite sure this is not the case. Is there a way to force a column to be non- nullable? I seem to be wasting a lot of time going through and altering the schema so I can use the columns in keys and indexes.
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks
I thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.
// create an open the connection OleDbConnection conn = new OleDbConnection(conString); OleDbCommand command = new OleDbCommand(); command = conn.CreateCommand();
// create the DataSet DataSet ds = new DataSet();
// run the query command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter = new OleDbDataAdapter(command); command.Parameters.Add("@P0", OleDbType.VarChar).Value = ""; adapter.Fill(ds);
// close the connection conn.Close();
bindingSource1.DataSource = ds.Tables[0];
DataGridView.DataSource = bindingSource1;
// set the size of the dataGridView Columns this.DataGridView.Columns[0].Visible = false; this.DataGridView.Columns[1].Width = 234; this.DataGridView.Columns[2].Width = 50; this.DataGridView.Columns[3].Width = 50; this.DataGridView.Columns[4].Width = 75;
//Sort on the Title Column DataGridViewColumn sortColumn = DataGridView.Columns[1]; ListSortDirection direction; direction = ListSortDirection.Ascending; DataGridView.Sort(sortColumn, direction);
//Set the Selected Property of the First Row to False DataGridView.Rows[0].Selected = false; } catch { }
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECT TXPRCL, TXALTR, TXANAM FROM dbo.PCWEBF21 WHERE (TXALTR > 0) UNION SELECT TXPRCL, TXTAXP, TXTNAM FROM dbo.PCWEBF21 WHERE TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code: SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open(); The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field. Appreciate if someone could tell me what i am doing wrong. Thanks
Is it possible to combine fields and text in a select statement? In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this: SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe FROM GE_Data WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets. If this is possible, or there's a better way to do it, I'd love some more info. Thanks! -Mathminded
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
Dear All I am trying to populate an OledbDatareader for binding to a ASP datagrid.
For this I use select statement to display combined fields in a datagrid cell. Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on. But the problem is if any of the three field is null the combined field 'Address' returns as Null. Please help me to overcome this problem.
INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1) AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)
GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR, GLF_CHART_ACCT.SELN_TYPE1_CODE
HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade' AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;
I get errors if not all the fields are included in the group by clause.
what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??
Let's say I have a table of users. Let's imagine there's two fields: username (PK), password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to (1) SELECT * FROM [User] WHERE username = 'whatever' AND password='whatever' or (2) SELECT * FROM [User] WHERE username = 'whatever' and then in my code check that the record returned matched the password?
I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
Can someone show me what I need to do to have a result set only displaying records from the specified date range. All the tables can relate on Date, shift, and ProductType (Besides UnprocessedProductTracking which can only relate on Date and Shift) I have a lot of trouble with joins...well I'm just not to experienced. Help!
I have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname; Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM (SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T WHERE T.Id_Lib IN (SELECT L.Id) GROUP BY L.LastName) AS Librarians GROUP BY Librarians.LastName ORDER BY MAX(Librarians.CountOfBooks) DESC GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt.
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.
Code Snippet INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9 SELECT Episode_Key, SecDX1 = [1], SecDX2 = [2], SecDX3 = [3], SecDX4 = [4], SecDX5 = [5], SecDX6 = [6], SecDX7 = [7], SecDX8 = [8], SecDX9 = [9], SecDX10 = [10], SecDX11 = [11], SecDX12 = [12], SecDX13 = [13], SecDX14 = [14], SecDX15 = [15] FROM (SELECT Episode_Key, DX_Key, ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code FROM srm.cdmab_dx_other WHERE Episode_key is not null ) data PIVOT ( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt ORDER BY Episode_Key
The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?
Code Snippet SELECT e.episode_key, e.medrec_no, e.account_number, Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'') + Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'') + Isnull(ltrim(rtrim(p.patient_mname)) + ' ','') + Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName, CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date, CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date, e.episode_type as VisitTypeCode, d.VisitTypeName, convert(int, pm.PatientAge) as PatientAge, pm.PatientAgeGroup, pm.patientsex, p.race FROM srm.episodes e inner join srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join srm.item_header ih on ih.item_key = e.episode_key inner join srm.patients p on p.patient_key = ih.logical_parent_key inner join ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join dbo.PtMstr pm on pm.AccountNumber = e.Account_Number
