let say i got such condition
INSERT INTO TABLE
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
UNION
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
do you think that mssql will produce error or problem?
from what i heard it will.
Hi all
I'm new to SQL Server 2005 and its queries. I've created a DB that stores bonusses. For example, a person goes out with usually 4 other people, and does their work. The first person is the engineer, the second is the team leader, and the third and forth is the helpers. According to the number of notes they capture for that day, they get a bonus. if 3 notes has been done for the day, the enigeer gets 100 bucks per note, aka 300 bucks - 3 * 100 = 300. the team leader gets half of it, 150, and the helpers get each half of that, 75 and 75.
in a select query i did the match/calculation
now i want to add everything up to get a total, 300+150+75+75 - how does one do it, and write it to a table? Note: The amount of people can change, the role of the person can change, so a helper can be a team leader or engineer as well, and the amount also changes depending on performance.DECLARE @BonusID int;
SET @BonusID = '1';
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, b.Notes * a.Amount AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Surveyor')
UNION
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, (b.Notes * a.Amount)/2 AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Team Leader')
UNION
SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, ((b.Notes * a.Amount)/2)/2 AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Helper')
ORDER BY Total DESC
BonusDetailID
BonusID
Name
Surname
Role
Total
DateModified
-------------
-----------
------------------------
-----------------------
--------------------------
-------------
-----------------------
1
1
Riaan
de Lange
Surveyor
300
2008/03/21 14:17
2
1
Kobus
Vermaak
Team Leader
150
2008/03/21 14:17
3
1
Johan
Bester
Helper
75
2008/03/21 14:17
4
1
Pieter
Koen
Helper
75
2008/03/21 14:17
How do i get the total for the bonusid = 1? which should be 600
I want to include product added date and time in my querry but getting this error "The number of columns in the two selected tables or queries of a union query do not match".
Code:
SELECT Products.*,ProdPics.* FROM Products INNER JOIN ProdPics ON Products.ItemID=ProdPics.ItemID WHERE
Products.ItemID = 4 UNION SELECT Date, Time FROM History WHERE ProdID = 4
What am i doing wrong ?
Using SQL 2000...tblCustomer:CustomerID intCompanyName varchar(20)HasRetailStores bitHasWholesaleStores bitHasOtherStores bittblInvoiceMessages:MessageID intMessageText varchar(100)CustomerID intAllRetailStores bitAllWholesaleStores bitAllOtherStores bitAllStores bitIsActive bitThe Invoice Messages are text blocks which will be added to invoicesgoing out to customers. A customer can have Retail stores, Wholesalestores, and/or Other Stores. The messages can go to only thosecustomers with a specific type of store, or all customers, or to aspecific customer. It is important to note that a customer can have 1,2 or all 3 types of stores. Here are a couple of sample entries in theinvoice messages table:tblInovoiceMessages1,For Customers with Retail and Wholesale Stores,0,1,1,0,02,Only For Customer # 10,10,0,0,0,0....Attempt #1 (IF SELECT UNION SELECT)IF (SELECT TC.HasRetailDestinationsFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)Attempt #1 checks if the Customer has retail stores, and if it does,returns all messages for Retail Stores. The second Select statementchecks for all messages designated for that particular Customer. I useUnion to combine the tables (which have identical structures) and itworks great.Attempt #2 (IF SELECT UNION SELECT UNION IF SELECT)IF (SELECT TC.HasRetailStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)UNIONIF (SELECT TC.HasWholesaleStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllWholesaleStores = 1)Attempt #2 is the same as Attempt#1 except that I attempt to Unionanother If Select query to the first two queries. This attemptgenerates:Server: Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'IF'.I have tested each individual If Select statement, and they all returnproper results. However, anytime I attempt to Union more than 1 IfSelect statement together, I get the Msg 156 error. Is there somelimitation that I am not aware of?
View 4 Replies View RelatedCan you implement the same type of feature in SQL as MS Access uses with it's "immediate if"
or iif() function? In other words can you look at a specific row and change the contents
of one field based on the contents of another field in the same row?
I am trying to create a view like the the following:
CREATE VIEW AS Test
SELECT
name,
city ,
IF city = 'NY' state ELSE country
FROM address_table
The view's 3rd field will be either [state] or [country] depending on the contents of [city]
Thanks in advance!
..Marisa
Hi guys
Im not even sure how to word this right, but how do I compare one element(unsure word number 1) namely Filename, from a list of results, and use that value as a variable for another condition in the same select... I think?!
So if I get a list of 50 filenames (from a log table), some might be double.
If the file was successfully processed, it will log that filename and the status as OK and if not then BAD.
The file will be modified and then reprocessed until OK. Each time the file was processed, it will log the filename and status.
I then run a statement that shows all the files that were BAD (from the log table) and it will bring up then filename and BAD status.
What I want to do is check the status' and if the file is BAD, check if there is another logged entry with that filename and status is OK, if found then not produce output as Im only looking for the files that status' are BAD with no OK status for any of the logged entries for that filename.
Is that understandable? If not then Ill try reword it.
The help is much appreciated!
Justin
Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.
SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,
tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROMtblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId
Thanks,
Rahul Jha
I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other
IDTimestamp
1NULL
209/17/2012 11:04:32
309/17/2012 11:05:44
409/17/2012 11:05:45
So I need a query to return
IDTimestamp
409/17/2012 11:05:45
as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.
create function stzipcustinfo
( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )
RETURNS TABLE
AS
RETURN
(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,
t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,
t.Audience_type
from
(
select distinct
v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,
v.Phone, v.totalquantity as totalquantity,
case @audience_type
when v.custclas then v.custclas
end as audience_type
from vwstzipcustaudienceinfo v
Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)
) as t
where t.audience_type is not null
group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,
t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type
)
--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows
--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row
problem have a “-“ in them..i m not getting result..so what condition should be in red color one..
thanks
Hi All,
I want get the result in a single SELECT statement
SELECT
SUM (PAID_LOSS),
SUM (PAID_EXP)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE Accounting_Period GE 200701 and LE 200712
-************************************
SELECT
SUM (MEDICAL_RESERVE)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE Accounting_Period LE 200712
col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B 100
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
and also wants the sum of result above conditions when column 3 has two values(i.e N,B) for single col2 value
here col2 values are unique
take an example for col2=212
if col3=B
then result is 100
if col3=N
then result is 18022.54
for a single col 2 value ,if col3= N and B then we want sum of above 2 conditions i.e (100+18022.54)
but not the sum of (100+18022.54+111.07+0.0) .
Can any one reply for this..............
I tried following code and it return no data after query run successfully. I m testing it on msaccess .
SELECT Book.ID, Book.Title, Book.Arthor, Book.Publisher, Book.Year, Book.Price, Inventory.B_ID, Inventory.Quantity
FROM Book INNER JOIN Inventory ON Book.ID=Inventory.B_ID
WHERE Book.ID=Inventory.B_ID And ("Book.Arthor" Like '%es%');
-------
I have test and test2 in Arthor column ... Thanks all !!
can someone help me how can i access datas using union? or show my data in the gridview.....pls.....
coz i have 3 tables...
i need to output the datas of the 3 tables in 1 gridview.
tables are: TT0001,TM0011,TM0001
TT0001 has syain_id(PK),time_in,time_out,year(PK),month(PK),day(PK)
TM0011 has office_name,office_id(PK)
TM0001 has syain_id(PK) office_id(PK),empl_date
the scenario is:
i have a combo box for office_name, and a textbox for imputting the date( 2007/01/23).when i click the button OK.
the time_in,time_out and syain_name of the person who is present in the choosen date(ex. 2007/01/23) will be shown in my gridview_info.
i already have some codes but it still confuse me and has many errors....
my code:
GridView_info.Visible = True
'//for odbc Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft® Visual Studio® 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes" Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)
'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
MyConn.Open()
'//parsing 2 Dim MyString As String = TextBox_date.Text Dim MyDateTime As DateTime = DateTime.Parse(MyString) Console.WriteLine(MyDateTime)
Dim stringQuery2 As String = "SELECT * from tempo_db"
Dim SQLcommand2 As New Odbc.OdbcCommand("Create table tempo_db ( syain_name char(40),year char(4), month char(2), day char(2), in_hh int, in_mi int, out_hh int, out_mi int )", MyConn) SQLcommand2.ExecuteNonQuery()
Dim SQLcommand1 As New Odbc.OdbcCommand("Drop table tempo_db", MyConn) SQLcommand1.ExecuteNonQuery()
Dim da As New Odbc.OdbcDataAdapter("Select TT0001.Year,TT0001.Month,TT0001.Day,TT0001.in_hh,TT0001.in_mi,TT0001.out_hh,TT0001.out_min where TT0001.Year =" + MyString + " and TT0001.Month =" + MyString + " and TT0001.Day =" + MyString + " and TT0001.syain_id =" + TM0001.syain_id + " Union Select TM0001.syain_name where TM0001.syain_id =" + TT0001.syain_id + " Union Select TM0011.office_name where TM0011.office_id =" + TM0001.office_id + " Dim ds As New DataSet() Dim foundrow As DataRow Dim ds2 As New DataSet 'Dim temp_data_table As New DataTable
GridView_info.DataSource = ds da.Fill(ds, "TT0001")
Dim sqldataadapter2 As New Odbc.OdbcDataAdapter(stringQuery2, MyConn) sqldataadapter2.Fill(ds2, "tempo_db") Dim date_ctr As Integer date_ctr = 1
While date_ctr <= Date.DaysInMonth(Now.Year, Now.Month)
ds.Tables(0).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Year")} 'ds.Tables(1).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Month")} 'ds.Tables(2).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Day")}
foundrow = ds.Tables(0).Rows.Find(date_ctr) 'foundrow = ds.Tables(1).Rows.Find(date_ctr) 'foundrow = ds.Tables(2).Rows.Find(date_ctr)
Dim in_hh, in_mi, out_hh, out_mi As String
If foundrow IsNot Nothing Then in_hh = foundrow.Item("in_hh") in_mi = foundrow.Item("in_mi") out_hh = foundrow.Item("out_hh") out_mi = foundrow.Item("out_mi") End If
End While
GridView_info.DataSource = ds2 GridView_info.DataBind() MyConn.Close()
End Sub
/// ps: im having problem with my select statements.....
can someone help me analyze what i had written... and what is my mistake...
any help is greatly appreciated....
thanks
nat!
Hi,
I have discovered something weird.
I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions.
In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc.
And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)
hi all....im having problem with union selection query....this is the situation...i have a details view with data source from two table in the sql server...both select query has same query string parameter,the thing is i dont know how to display the data from both table in the column in details view...here i paste the code.... 1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
2
3 <script runat="server">
4
5
6 </script>
7
8 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
9 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
10 CellPadding="4" DataKeyNames="programID" DataSourceID="programdetailsSqlDataSource"
11 ForeColor="#333333" GridLines="None" Height="50px" Width="272px">
12 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
13 <CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />
14 <EditRowStyle BackColor="#2461BF" />
15 <RowStyle BackColor="#EFF3FB" />
16 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
17 <Fields>
18 <asp:BoundField DataField="namaprogram" HeaderText="Nama Program" SortExpression="namaprogram" />
19 <asp:BoundField DataField="lokasi" HeaderText="Lokasi" SortExpression="lokasi" />
20 <asp:BoundField DataField="tarikh" DataFormatString="{0:dd/mm/yyyy}" HeaderText="Tarikh"
21 HtmlEncode="False" SortExpression="tarikh" />
22 <asp:BoundField DataField="kapasiti" HeaderText="Kapasiti" SortExpression="kapasiti" />
23 <asp:HyperLinkField DataTextField="nama" HeaderText="Nama Staff" NavigateUrl="~/staffdetail.aspx?staffID={0}" DataNavigateUrlFields="staffID" />
24 </Fields>
25 <FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />
26 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
27 <AlternatingRowStyle BackColor="White" />
28 </asp:DetailsView>
29
30 <asp:SqlDataSource ID="programdetailsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:blksConnectionString %>"
31 SelectCommand="SELECT * FROM [program] UNION SELECT staffID,nama,programID FROM [staff] WHERE ([programID] = @programID)">
32 <SelectParameters>
33 <asp:QueryStringParameter Name="programID" QueryStringField="programID" Type="Int32" />
34 </SelectParameters>
35 </asp:SqlDataSource>
36 <br />
37 </asp:Content> and here is the errors:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
View 23 Replies View RelatedThat's about it (subject line), I have used UNION and now I want to select DISCTINCT from that resultset
Code:
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2
That code gives me half of what I want.
I would like a list of the unique results.
I know I could use a TempTable and do the DISTINCT on that, but I'm hoping there is a more elegant way.
EDIT: The following code gives me the result I want:
Code:
CREATE TABLE #TempTable (
TempCol VARCHAR (20) collate database_default,
)
INSERT INTO #TempTable
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2
SELECT * FROM #TempTable
DROP TABLE #TempTable
. . . but, can it be done without a TempTable??
What am I missing?
I have three tables "UNIONED" and I want the this inserted into a table.
INSERT INTO mytable (A, B, C, D, E)
SELECT A, B, C, D, E
FROM
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
GROUP BY A,B,C,D,E
This part alone works just like I want it:
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
I just want it inserted inte stated columns in my table.
I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...
I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed
This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);
This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;
I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;
Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.
NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID
DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932
DIEBOLD, INCOHUPS #88X08X18932
MY POINT: Is there a way to select a over all DISTINCT order ID.
Thank you for any help hope this make sense!
Hi,I'm trying to get the count of rows from the union of several tables.My code is:select count(*) from (select * from #AdvSearch_Mainunionselect * from #AdvSearch_Atty)This will not get past the syntax check saying that the error occurs onthe final closing ")".Can someone tell me how to correctly write this?Thanks,Glen--------------------------Numbers 6:24-26----------------------------------------------------Numbers 6:24-26--------------------------*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _
"UNION ALL" & _
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"
This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:
Invalid column name 'Towing'.
Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.
IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable
How to display in select statment starting from the word 'basic'
table name:version
Table values:
versionidVersiondescription
53666445SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
53666446SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
53666447SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
53666448SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY
Output should be:
versiondescription
Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY
I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true my select query should select the number column. if the variable is false , only the first two columns are selected.
e.g @displaynum = true
@displaynum = false
Firstname | Lastname | number Firstname | Lastnam
blah | blah | 12345
blah | blah
i am not sure if this is possible as a case still selects the column but just changes the values.
Hi, in SQL Management Studio, we just click 'Process' to train the data, how could I conditionally select a subset of Data Source (View) to train?
Thanks in advance.
Ricky.
Given the following tables:
[Members]
-memberID (PK)
-memberName
[Questions]
-questionID (PK)
-questionText
[Surveys]
-surveyID (PK)
-surveyName
-surveyDescription
-surveyType (FK)
[SurveyQuestions]
-surveyID (PK/FK)
-questionID (PK/FK)
[SurveyQuestionMemberResponse]
-surveyID (PK/FK)
-memberID (PK/FK)
-questionID (PK/FK)
-yesResponse(bit)
-noResponse(bit)
-undecidedResponse(bit)
How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.
In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.
I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.
When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.
The error indicates:
The text data type cannot be selected as DISTINCT because it is not comparable.
Can someone please point me in the right direction. I suspect I am going about this all wrong.
[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.
Thanks,
Zoop
if there are 2 insert statement
insert ... select * from table1 union all select * from table2
or
insert ... select * from table1
insert ... select * from table2
pls advise which 1 is faster ...
I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases db1, db2, db3, db4 which all have the same table (Table1)with identical column names (Surname, GivenNames).CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick
View 1 Replies View RelatedI'm trying to get a UNION of UserIDs from multiple subselects. Theoriginal query--which puts all the found UserIDs into separatecolumns, looks like this:--------------------------------------------------------------------------------SELECTtmp_MY_TABLE_2_3_4.LAST_UPDATED_USER_ID AS UserID_1MY_DB_1.MY_TABLE_5.LAST_UPDATED_USER_ID AS UserID_2MY_DB_1.MY_TABLE_6.LAST_UPDATED_USER_ID AS UserID_3FROM(SELECT MY_DB_1.MY_TABLE_1.PRIMARY_KEY_IDFROM MY_DB_1.MY_TABLE_1WHERE MY_DB_1.MY_TABLE_1.PRIMARY_KEY_ID = 12345) AS tmpMY_TABLE_1LEFT JOIN(SELECT MY_DB_2.MY_TABLE_2.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_2.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_2UNIONSELECT MY_DB_2.MY_TABLE_3.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_3.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_3UNIONSELECT MY_DB_2.MY_TABLE_4.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_4.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_4) AS tmp_MY_TABLE_2_3_4ON tmpMY_TABLE_1.PRIMARY_KEY_ID = tmp_MY_TABLE_2_3_4.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_5 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_5.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_6 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_6.PRIMARY_KEY_ID--------------------------------------------------------------------------------As you can see, I'm getting "LAST_UPDATED_USER_ID" from all tables/aliases--but those tables/aliases need to LEFT JOIN to"tmpMY_TABLE_1.PRIMARY_KEY_ID", because I only care about"LAST_UPDATED_USER_ID"'s that are related to my "tmpMY_TABLE_1"records.I tried putting, parenthesis around the whole SQL (aliasing as"tmpAllTables") and putting in the front:--------------------------------------------------------------------------------SELECT tmpAllTables.UserID_1 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_2 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_3 AS UserID FROM tmpAllTables--------------------------------------------------------------------------------But that didn't work. Any thoughts?
View 1 Replies View RelatedI have a select such as this:
select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2
Now, I want to order the result set by COLUMNS. When I try the following query, the SQL fails.
select * from
(
select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2
) as T
order by COLUMNS
Any idea how this can be done?
Thanks
Hi all,
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.
I have table with filed
EmpProject -Table
Empno, Name, Project NO, cost center
I need to restrict the results by project No by user input. In case if user did not provide any value for the projectNO, then need to fetch all rows.
Select empno, name, projecno, cost_center from empproject where projectno=nvl(:pno, :deptno)
below is my original query
select Value = count(*) from dbo.test
I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.