SQL Query Question- Querying The Middle Table In A M To M

May 17, 2000

I need help with a query, and I haven't seen anything like this in a SQL book.

I will use the Northwind database for this example. The tables I am using are Products, Order_Detail, and Orders. There is a many to many relationship between Products and Orders, that is resolved by the Order_Detail table. Suppose I want to find out which products are related to each order. How would I query the tables?

Thanks everyone,
Nathan

View 1 Replies


ADVERTISEMENT

Query A SQL Server Compact Table While Querying A SQL 2005 Server Table

May 21, 2008

Hi there,

I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.



SELECT * from RemoteDB.TESTDB.dbo.Objects

WHERE Last_Updated > '2008-05-21 10:51:00'

AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)


Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.

Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.

Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?


Thanks in advance

View 10 Replies View Related

Reporting Services :: Add Data In The Middle Of Table In Ssrs?

Sep 18, 2015

 In SSRS report genertion using store proc based on years, retrieving the data of previous and current year details. In the middle of data, I have to add sub tax total and sub total amount from previous and current year.

I have added sub total tax and sub total in database for  previous and current year totals.

Below are the current results

Expected results:

View 18 Replies View Related

Transact SQL :: Querying Data From Master Table Into Transaction Table?

Oct 13, 2015

I am stuck in the following scenario.

Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01

[code]....

View 5 Replies View Related

Querying Result Set Of Another Query

Oct 3, 2007



Hi,

I have a query that returns the result set as follows

Querey
-----------------

select Code ,Date from

(

select *

from MasterTable

where ID= 1 OR ID = 3

)as data

unpivot

(

Date for Code in ( COD1,COD2,COD3 )

) as UPVT)

Result

CODE DATE
------------------------------------------------------------
COD1 2007-09-11 02:00:00.000
COD2 2007-09-21 02:00:00.000
COD1 2007-10-11 02:00:00.000
COD2 2007-09-12 02:00:00.000
COD3 2007-09-11 02:00:00.000




I just wanted to write the query using the result set of the above unpivot query and expecting the result as foolows

CODE DATE
------------------------------------------------------------
COD2 2007-09-21 02:00:00.000
COD1 2007-10-11 02:00:00.000
COD3 2007-09-11 02:00:00.000

The condition is that i wanted to have the code that has latest date.

Can any one please let me know the query??

~Mohan

View 3 Replies View Related

Querying Using Another Table

Apr 30, 2008

Hello all,

I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.

what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.

does anyone have an idea?
thanks in advance.

View 2 Replies View Related

Querying Table More Than Once

Jun 12, 2008

Hi All

Thx in advanced for any help anyone can offer me.

I have 2 tables Staff and Class which are as follows

Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .

Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID

LessonTemplateID and TermID link to other tables which i don't need right now.

The Class.TeacherID and Class.AssistantID both link to the Staff.ID

I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.

If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.

Im really stuck on this and just can't figure it out (is it even possible?).

Thx for any help Scott.

View 2 Replies View Related

Querying Between Table Adapters ?

Sep 12, 2006

Hi all, I am currently moving from access queries to SQL and am using the query Builder In Visual web developerto help me with the SQL Code..This has been easy while just querying the one table adapter but Now I am stuck..I have a table adapter that links to an SQL Server database through a connection in web.config.I have another table adapter that links to DB2 through a different Connetion in web.config.My question is ..How do you query from one table adapter to another.Each table adpater can only query based on the 1 connection ? Thx,Ray..

View 1 Replies View Related

Log Users Querying Table

May 17, 2000

We have been asked to keep a log of users querying a particular table.
User's can query any odbc compliant tool, not just one application.

Can we do this w/ sql server 7?

View 1 Replies View Related

I Thought I Posted This One But Don't See It. Problem With Querying A Query

Jul 20, 2005

It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.

View 3 Replies View Related

Querying The Size Of Table Fields?

Jun 5, 2008

Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
 for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
 
I would like to do something like this:
 
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
 
Thanks for the help! 

View 4 Replies View Related

How To Keep Original Order When Querying A Table?

Sep 10, 2004

Hello, everyone:

I have a table like:

ColName
b
b
b
d
d
d
a
a
c
c
c

I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c

However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d

Can any one have the idea to handle that? Thanks

ZYT

View 4 Replies View Related

Querying Table In Arbitrary Database

May 1, 2006

Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.

What I've tried:

DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'

SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1

Result:
Invalid object name '@DBName.dbo.UPR00100'.

I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.

Thanks for the help
Wes

View 2 Replies View Related

Querying A Really Badly Designed Table

Mar 28, 2006

I am working on an application with a number of tables (which I can't
change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3,
c3... The tables contain 5 to 10 sets of data in each row.



I want to create a stored procedure to return a cursor with a row for
each set of data for a row of the table. I know I can use unions:



select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk

union

select a2, b2, c2... from sometable where pk=@pk

union

select a3...



Is there a way that is more efficient (eliminates requerying the table and all the unions)?

View 4 Replies View Related

Querying Single Table For Multiple Summaries - How?

Jul 20, 2005

Folks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve

View 2 Replies View Related

Transact SQL :: Is Table Will Be Extracted When Querying To DB Views

Sep 15, 2015

I wonder how the query will be executed when querying to views.Is database engine will be extract the tables that are used to create the view and query for the result ?

View 5 Replies View Related

Data Warehousing :: Querying In Fact Table

May 2, 2015

I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.

Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?

1. XML
2. Spatial
3. Clustered
4. Non-Clustered

View 2 Replies View Related

SQL Server 2012 :: Querying Table With Several Date Type Columns

Oct 30, 2014

I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.

I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.

I started with a normal select statement selecting each column with a join to the project and then a where clause using

(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...

The rest of the columns(all with OR between them).

The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.

Obviously because I have all the columns in the select statement... So I need something like

Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))

View 2 Replies View Related

SQL Server 2012 :: Querying A Supersession Two Column Table With Multiple Supersessions In Both Columns

Jan 29, 2014

I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

Supersessions Table
----------------------

RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191

[code]...

The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

View 9 Replies View Related

SQL Server Admin 2014 :: How To Read And Load Extended Events Into A Table For Querying

Jun 19, 2015

I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?

I've got the files configured to be 20MB before rolling over, the XE is running all the time.

So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?

I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.

View 0 Replies View Related

Average Of Middle 90%

Sep 21, 2007

Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

View 9 Replies View Related

How Can I Select Middle .. From?

Oct 25, 2007

I have a table Customer with 200000 records.

When I run query

DECLARE @posTable TABLE(idkey INT IDENTITY(1,1), value VARCHAR(128))
INSERT INTO @posTable (value) SELECT CustID FROM Customer ORDER BY CustID

At the first time it takes 1 minute 42 seconds, at the second time it only takes 3 seconds.

When i run the query

SELECT IDENTITY(INT, 1, 1) AS idkey, CustID INTO #posTable FROM Customer ORDER BY CustID
The order in column idkey is not exacts


Pls. tell me why? and How can i rewrite it.

View 8 Replies View Related

Returning Middle Records

Dec 7, 2006

I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
 "SELECT TOP 20 to 40 * FROM Northwind"
 Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ

View 2 Replies View Related

Only Put A Period . If There Is A Middle Intial.

Dec 29, 2003

I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)

I need my resultant field data to look like the following:
"Doe, John P."

I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".

I have tried the following CASE statement:

CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END

However, I get an error indicating that the CASE statement is not supported in the Query Designer.

How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?

View 4 Replies View Related

Extract Value From Middle Of String

Sep 9, 2005

Lets say I have a column of type varchar and need to extract an integer value from the middle of it.  The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?

View 2 Replies View Related

Can You Trim In The Middle Of A Join?

Dec 12, 2001

Is it possible to use a TRIM(){or similar} function when JOINing 2 tables via a NVARCHAR field in T-SQL?

I need to trim the field first because some of the data contains trailing spaces (GRRR) and so might future data...

View 2 Replies View Related

Selecting A Middle Date

May 3, 2006

So I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.

Thanks in advance for any help.

View 3 Replies View Related

Retreive The Middle Of A String

Apr 5, 2007

Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???

Thanks :)

View 7 Replies View Related

Get Middle Initial From A Name Field

Nov 15, 2013

I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.

Example data:

Jane,Smith Ron
John,Dow L
Mary Jane,Dow Welsh

The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:

R
L
W

View 5 Replies View Related

Getting The Middle Initial From A Name Column

Jan 7, 2004

-- I have a first name field that sometimes contains only the first name,
-- sometimes contains the first name and the middle initial, and
-- sometimes contains the first name, a space, followed by NMI (for no middle initial)
-- how do I correctly grab the first letter of the middle initial in all cases?
-- I have been playing with patindex but its harder than I thought. guess I need a case
-- statement in addition to this. Any idea how I can do this?
-- thanks!

create table UHS_t1 (c1 varchar(20))
insert UHS_t1 select 'john a'
insert UHS_t1 select 'jeff b'
insert UHS_t1 select 'sue z'
insert UHS_t1 select 'joe nmi'
insert UHS_t1 select 'jamie'


select *, substring(c1, patindex('%[ ]%', c1)+1, 1) as middle_name
from UHS_t1
go
drop table UHS_t1

View 10 Replies View Related

How To Stop A Restore In The Middle

Aug 13, 2012

How do I stop the restore in the middle?

restore database blah
from disk='mybackups hismonthbackup1.BAK'
with replace, recovery, stats=5, maxtransfersize=1048576

View 3 Replies View Related

Trim Spaces In Middle

Jun 20, 2006

In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?

View 6 Replies View Related

How To Get String Value In A Middle Position?

Feb 26, 2007

khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."

View 3 Replies View Related







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