Single Table Query &<blush&>

Oct 20, 2006

I usually write (very simple) queries and export to Excel, then work my magic there. But there's got to be a better way.

sampletable
CUSTNO PARTNO
ABC001 A1
ABC002 A1
ABC002 A2
ABC003 A2

I need to find all CUSTNO that have both PARTNO A1 and A2.

select * from sampletable
where PARTNO in('A1','A2')
returns 2 rows.

I really only want a list of unique CUSTNO ... "Only 1 customer has both partno A1 & A2".

Thank you,
--mark

View 11 Replies


ADVERTISEMENT

SQL Single Query For Multiple Table

Apr 15, 2008

Hi friends,
I have three table named as Eventsmgmt,blogmgmt,forummgmt..
Each table contain the common column named as CreatedDateTime..

I want to get the most recent CreationDateTime from these three table in single query..

Plzz help me its urgent

Thanks

View 20 Replies View Related

Query On Multiple Rows In Single Table?

Aug 1, 2014

I have a table named LEDGER

LEDGER has two columns named PATID and CODE

Example data:

PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092

I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.

View 5 Replies View Related

Query To Create A Countable List Using A Single Value From Table

Mar 15, 2004

Hi,

from a single numeric value, say 'n', in a field in a table, i'd like to create a SELECT query that produces a list from 1 to n, e.g

if the value was 6, I would like selected back:
1
2
3
4
5
6

Any pointers?
Greg

View 5 Replies View Related

Single Query - Update Same Column In Diff Table

Sep 25, 2014

I have two tables table1 and table2 and having a common column "col1"

When i ran the following query

UPDATE table1, table2 SET col1=FALSE WHERE id = 1;

getting the following error

Error Code: 1052
Column 'col1' in field list is ambiguous

id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.

View 3 Replies View Related

Assistance With Insertering Query Results Into A Single Table Row.

Feb 14, 2008

I have a table that was created as follows
create table call_summary (

row_date smalldatetime,
[700] int,
[730] int,
[800] int,
[900] int)


I have a query that returns the following
date interval (int) calls (int)
2008-01-07 00:00:00 700 0
2008-01-07 00:00:00 730 0
2008-01-07 00:00:00 800 0
2008-01-07 00:00:00 830 9
2008-01-07 00:00:00 900 8

I am looking for a way to get my table mentioned above to look like this
row_date [700] [730] [800] [830] [900]
2008-1-7 0 0 0 9 8

does anyone have any slick ideas on how I can accomplish this task?

View 3 Replies View Related

Regarding The Query To Save The Ids Of The Edited Names From Different Tables Into A Single Table.

Mar 8, 2008

Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
Groups Gr,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Gr.G_ID_PK =Aud.Audit_Group and
Ad_ID_PK in (select Ad_ID_PK from Audits)
Now i want to edit these names.
when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table.
For example:
i have this data in my audits table:




Commercial83312

2
2
2
1
Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs.
when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table.
so the data may be changed in audits table as :



Commercial83312

4
2
2
1

 




so here the industry ID is changed
I need the stored procedure for this.
please help me,its very urgent...

View 4 Replies View Related

How To Perform SELECT Query With Multiple Parameters In A Single Field In A Table

Sep 13, 2006

i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields:       SalesID | RegionID | Date | Amount   This is how the interface looks like.Thank You.

View 1 Replies View Related

SQL 2012 :: Single Column In Staging Table Deems Retrieval Query Unresponsive

Jul 17, 2015

where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.

View 3 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date


Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear


Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

Wants To Insert Into Multiple Table By A Single Insert Query

Apr 11, 2008

Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks

View 3 Replies View Related

Insert Data Into A Table From Two Tables Into A Single Table Along With A Hard Coded Value?

Feb 9, 2012

I'm trying to insert data into a table from two tables into a single table along with a hard coded value.

insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)

I get the following-

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Do I need to use a cursor?

View 5 Replies View Related

Transact SQL :: Avoid Same Table Multiple Times Rather Than Put Records In Single Table And Use It Throughout

Nov 19, 2015

There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule

SELECT  
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
    [PSDCode]
, PV.ValuationEffectiveDate
    [EffectiveDate]
, PV.PropertyListAlterationDate
    [ListAlterationDate]

[code]....

Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple times 

View 11 Replies View Related

Create Single Table By Linking All The Table

Sep 29, 2014

i have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like

Column A and Column B = Column C
e.g
A B C
Atul Jadhav Atuljadhav
Vijay vijayvijay

in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic

View 5 Replies View Related

Need A Single Query

Jun 14, 2007

Hi,
I have received a text file in the following format:
MthYear                  Customer                     Quantity           Price           Total
Apr2003                 Allan                              100                 5                500
 ---------                  Austin                             25                  2                  50
 ---------                 George                           1500              1               1500
 ----------                Jessy                                200               2                 400
Apr2004                Jerry                             600                 3                 1800
 ---------                 Stella                            250                 2                   500
June2005            XXXX                               50                  5                    250
 
I am exporting this text file in Sql Server database table. After exporting I need to Update the MtnYear field marked as ------- to appropriate year. ie
The MthYear field for Austin, George,Jessy should be updated with Apr2003,  
The MthYear field for Stella should be updated with Apr2004
iiily,  for other records. Let me inform if this is possible with the help of a single query or any better way of doing it. The records that i need to update is more than 2 lakhs. I am sorry if u feel this questionis not apropriate.

View 3 Replies View Related

Single Query

Jan 7, 2007

Hello

I have a sql query that shows the top N numbers of records and also wanted to show the total numbers of this records.


Code:


.
.
.
rs_1.Open "SELECT top 5 msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3

rs_2.Open "SELECT msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3 “

Top number to show: <%=rs_1.recordcount%>
Total number of records in database: <%=rs_2.recordcount%>
.
.
.



As you can see, it is only on the same database that I am querying. How do I make a single sql query out of this?

View 3 Replies View Related

Single Query

Jul 6, 2004

i hav a table of the form


table EMP
(
EMPNAME varchar(50)
)

and the data in the table is

record #1 abc
record #2 abc
record #3 abc
record #4 abc
record #5 abc
record #6 abc

can i use a single query so as to retain a single record in the table and remove the rest ?

View 14 Replies View Related

Can A Single Query Do This?

Mar 13, 2008

I have the following table:

id geounit parentgeounit

Here's some sample data:
1 Northeast null
2 Southeast null
3 New Jersey 1
4 Florida 2

Basically I need to return the parentgeounit's name, for instance for record #4 I'd want to return

4 Florida Southeast
Instead of:
4 Florida 2

Can this be accomplished?

View 3 Replies View Related

How To Take Single Value From Sql Query For Repeater ?

Oct 29, 2006

So I have code to fill my repeater with a data: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        Dim cnn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("db_ConnStr").ConnectionString)        Dim cmd1 As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter("SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date FROM aspnet_Users INNER JOIN forum_posts ON aspnet_Users.uID = forum_posts.user_id INNER JOIN forum_posts AS forum_posts_1 ON aspnet_Users.uID = forum_posts_1.user_id GROUP BY aspnet_Users.UserName, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date HAVING (forum_posts_1.topic_id = @topic_id) ORDER BY forum_posts_1.post_date ASC", cnn)        cmd1.SelectCommand.Parameters.AddWithValue("@topic_id", Request.QueryString("ID"))        Dim ds As Data.DataSet = New Data.DataSet        cnn.Open()        cmd1.Fill(ds, "posts")        Repeater1.DataSource = ds.Tables("posts")        Page.DataBind()        cnn.Close()    End Sub And I want to take the value from column which I bolded - IlePost. I want take this out at compare with some condition. For example:Dim label as label = Repeater1.Findcontrol("Label1")If COLUMN < 10 thenlabel.text = "Less than 10"elselabel.text = "More than 10"I hope you understand me :-) 

View 1 Replies View Related

Single Update Query

Apr 29, 2008

Table Name:-emp
table structure:-  Name           Gender
                         A                  Male
                         B                  Male
                         C                  FeMale
                         D                  FeMale
I want only one update query to update this table like this
table structure:- Name           Gender
                         A                  FeMale
                         B                  FeMale
                         C                  Male
                         D                  Male
   please help me?                  

View 1 Replies View Related

I Am Really Confusing Please Help Me About A Single Query

Sep 1, 2005

Hi Guys,This is my Problem.A table contain following desing for handling different level of categories, bu it is dynamic int_categoryid,int_parent_categoryid,int_categorylevel,str_categoryname,bit_activethatall.I want to list data from table as following ordercategorry_parent11               category_child12                        category_child13                        category_child23               category_child22categorry_parent21.............................................................................................like this..ie we can insert parent category and sub category to n level dynamically without adding a new tableplease mail me for another clarification....please help meregardsAbdul

View 2 Replies View Related

Query On A Single Word

Aug 30, 2005

Hi i am using Access as my database (i know its an MS SQL forum but that's the closest i could find, and maybe its more of an sql question).

I am trying to select all rows that contain i.e. the word "Art"

SELECT * FROM table1 WHERE column1 LIKE %Art%

as you can see column1 contain some words and not just "ART"
however i am getting also the following:

CART , ARTI, DARTS - since all has arts.

( i can use '=' since the column consist of other words)
The query should also load : Art. Art, Art! Art? and "ART"

Is there an easy solution or i need to create a huge condition ?

View 2 Replies View Related

Get Data From Query In Single Row

Jun 23, 2008

Hello All,

I have following type of table with data.


Year id value
01-01-2008 123 10
01-01-2008 131 11
01-01-2008 139 132
01-01-2007 153 112
01-01-2007 154 134
01-01-2007 155 164

I want to write query such that I should get value of particular year in single row as follows


Date Col 1 Col 2 Col 3
01-01-2008 10 11 132
01-01-2007 112 134 164

How can I write this type of query ? Please do let me know if anyone know how to write it ?

Thanks in advance.



--kneel

View 5 Replies View Related

Need Single Select Query

Feb 8, 2007

Hello Everyone,

Consider the below table

DocNbr Cheques
00001 101010
00002 101010
00002 102020
00003 103030
00004 103030
00004 104040
00005 105050

I just need the single select to result the docnbr which is repeating the values. In the above case I want my result like below where the DocNbr 00002 and 00004 repeated their values.

DocNbr Cheques
00002 101010
00002 102020
00004 103030
00004 104040

Thanks in advance,

Senthil .G

Note: I do not consider if the cheque contain repeated values

View 3 Replies View Related

AND Query In Single Column

Jul 23, 2005

Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply

View 14 Replies View Related

2 Group By In A Single Query

Jul 20, 2005

Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai

View 2 Replies View Related

Select 1-20 In Single Query.

Oct 2, 2007


Hello,

I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table.
If any one have solution, then please help me.

View 18 Replies View Related

I It Possible To Get This Result By Single Query ?

Oct 13, 2007

hello
i am a beginner
i it possible to get this result by single query ?

i got a int column and a datetime column
is it possible to get the sum(int column) for all the year 2007 and by the april2007 and by the month n year by the person name Tony

so the end result will look like this

year 2007 | current_month | tonys_group
-----------------------------------------------
$ 222 $22 $2

View 2 Replies View Related

Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table

Nov 1, 2007

I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
 
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' 
 
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return.  Same goes for the last_name field.   Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..

View 7 Replies View Related

BCP A Single Table

Sep 28, 1999

I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why.

The table only had a few rows of data and only had a few small dependancy tables.

The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long.

Here is the BCP command line that I ran :-

bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n

Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one.

Failing that, is there any way of scripting the transfer of a table from one server to another.

Many thanks.

Dave

View 2 Replies View Related

Can I Update Two Tables In A Single Query

Jun 14, 2008

hi friends

in my sqlserver 2005 i want to update two tables in a single query.

is it possible? or is it possible by creating a view?

thanks in advance.

shweta

View 3 Replies View Related

Concatenate Query From Single Column

Feb 22, 2007

I have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.

I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.

I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.

For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.

So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).

I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:

"54","ABC","apple, orange, banana, pear, mango, peach"

Does that make sense?

View 4 Replies View Related

Query Single Col Into Several Cols Result Set

Oct 24, 2004

Hello,

I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:

L M H
-----------------------
3 2 1

(L are values below 4, M between 4 and 5, H is 6 and above)

Any suggestions?

TIA

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved