SQL JOIN Query (using: FOR XML AUTO)

Nov 15, 2007

I've never been able to grasp the usage of JOIN when querying the database, and would like some help building the correct SQL query for my task.
My database table looks like this:
 


tableid    
parentid    
item    

1
NULL
Fruits

2
NULL
Colours

3
NULL
Sizes

4
1
Apples

5
1
Grapes

6
1
Bananas

7
1
Peachs

8
2
Red

9
2
Blue

10
2
Green

11
3
X-Small

12
3
Small

13
3
Medium

14
3
Large

15
3
X-Large

16
4
Granny Smith

17
4
Golden Delicious

18
10
Lime Green

19
10
Dark Green

20
10
Light Green

As you can see this table uses internal foreign key linking (not sure what the correct term is). I am trying to write a query that will JOIN the correct columns and their values (...and then have it set using "for xml auto".  My expected generated xml should look like this (note that I am not outputting the XML because it will be used as a datasource, but this is for clarity):


<Categories>

    <Category ID="1" Name="Fruits" checked="false">
        <SubCategory Id="4" Name="Apples" checked="false">
            <SubSubCategory Id="16" Name="Granny Smith" checked="false" />
            <SubSubCategory Id="17" Name="Golden Delicious" checked="false" />
        </SubCategory>
        <SubCategory Id="5" Name="Grapes" checked="false" />
        <SubCategory Id="6" Name="Bananas" checked="false" />
        <SubCategory Id="7" Name="Peachs" checked="false" />
    </Category>

    <Category ID="2" Name="Colours" checked="false">
        <SubCategory Id="8" Name="Red" checked="false" />

        <SubCategory Id="9" Name="Blue" checked="false" />

        <SubCategory Id="10" Name="Green" checked="false" />
            <SubSubCategory Id="18" Name="Lime Green" checked="false">
            <SubSubCategory Id="19" Name="Dark Green" checked="false">
            <SubSubCategory Id="20" Name="Light Green" checked="false">
        </SubCategory>
    </Category>

    <Category ID="3" Name="Sizes" checked="false">
        <SubCategory Id="11" Name="X-Small" checked="false" />

        <SubCategory Id="12" Name="Small" checked="false" />
        <SubCategory Id="13 Name="Medium" checked="false" />

        <SubCategory Id="14" Name="Large" checked="false" />
        <SubCategory Id="15" Name="X-Large" checked="false" />

    </Category>

</Categories>

Can anyone help me with this query? 

View 18 Replies


ADVERTISEMENT

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'm using SQL Server 2005.



A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).



The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).

If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.



If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.



So, this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



and this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC



But this does't:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



What should I be looking for here to understand why this is happening?



Thanks,

john















View 1 Replies View Related

Problems With FOR XML AUTO Query

May 20, 2002

Hello-

I'm hoping someone can help me. I am trying to generate query results in xml. I am using FOR XML AUTO and FOR XML AUTO, elements.

The problem is that the results are returned in xml but are trunacted - the entire results set is not returned - the results cut off midway through one row...

I am using SQL 2000 - I have installed sp2 - this made no difference in the behavior

I'd be grateful for any info anyone has on this. Thanks!

View 1 Replies View Related

SQL Query Question - JOIN Or Not To JOIN

Jan 2, 2006

I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.

View 3 Replies View Related

Generating Auto Incrementing No Using SQL Query

Sep 22, 2004

Hi everyone,

I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.

Say i am executing this query

SELECT NAME,AGE,ADDRESS FROM MEMBER which gives

NAME AGE ADDRESS
HOLYMAC 13 MALACCA
HOLYCOW 25 USA
HOLYGOD 55 LONDON


Now how can i make it come out like this

SNO NAME AGE ADDRESS
1HOLYMAC 13 MALACCA
2HOLYCOW 25 USA
3HOLYGOD 55 LONDON

See the first column is a auto incrementing number 1,2,3.


How can I write a SQL QUERY that outputs a auto incrementing number.


Thank you
Have nice day

View 2 Replies View Related

SQL 2012 :: SSMS Auto-recovery / Auto-save New (unsaved) Queries

Feb 16, 2014

Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:

%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1

However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.

I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:

%userprofile%AppDataLocalTemp

View 2 Replies View Related

Query Issue (Auto Numbered Column)

Mar 15, 2008

Hello Guys!I have an important for me and maybe easy question for you...

I want to take the results of a select but i want the first column to be an auto incresement Number...

For example
aa Name Number
1 Alexander Papadopoulos 212222222
2 .... ...
3
4
5

How Can this happen?


I sell my mother in law.Is anybody interested?

View 8 Replies View Related

How To Read The Xml Returned By Select Query With For XML Auto,Elements

Dec 13, 2007

Hi all,
 I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.

View 1 Replies View Related

Auto Increment Auto Non-identity Field

Jan 23, 2004

I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?

Thanks, Justin.

View 3 Replies View Related

Trouble With An ASync Query To Insert A Record And Return The Auto Number Field.

Aug 31, 2007

I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.  conn.Open()
Dim strSql As String

strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _
& "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'"

Dim objCmd As SqlCommand
objCmd = New SqlCommand(strSql, conn)

Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader()

If aSyncResult.AsyncWaitHandle.WaitOne() = True Then


Dim sqlResults As SqlClient.SqlDataReader

sqlResults = objCmd.EndExecuteReader(aSyncResult)

Dim cid As Integer



cid = sqlResults.Item("contactId")
Me.id = cid
conn.Close()
Return cid
Else
Return "failed"


End If  

View 3 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

DELETE Query - Can't JOIN. Need Sub-query?..

Mar 30, 2007

I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?

What I initially tried to do was:

Code:

DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1


Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1

How can I do this without using JOIN. Use a sub-query? How?

Thanks

View 4 Replies View Related

Enquiry On Join 3 Query In 1 Query

Sep 29, 2007

Code:

SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total,
MAX(RecJobAds.PostedDt) as PostedDt,
MAX(RecJobAds.JobTitle) as JobTitle,
RecJobAds.JobId,
MAX (AgencyCandidate.AgencyId) as agentID,
MAX(RecJobAds.AdStatus) as status,
MAX(RecJobAds.CompanyId) as CompanyId,
MAX(RecJobAds.RecId) as RecId
FROM RecJobAds
LEFT JOIN AgencyCandidate
ON RecJobAds.JobId = AgencyCandidate.JobId
GROUP BY RecJobAds.JobId
ORDER BY Total ASC



i have the above query. but i would like to join in a third table, where my query is


Code:

select * from RecruiterMA where Activated = '1'.



anyidea in which place i can put the second query in the first query? thank you very much!

View 4 Replies View Related

Join First Query With A Query That Contains Unions

Jun 10, 2013

I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.

Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,

[Code] .....

Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND

[Code] .....

View 9 Replies View Related

T-Sql And X-Query Join

Nov 30, 2007

Hi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos>    <ContactsInfos>         <ContactID>1</ContactID>    </ContactsInfos>    <ContactsInfos>         <ContactID>2</ContactID>    </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts    ContactID : int    FirstName : varchar(200)    LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?

View 1 Replies View Related

Query Inner Join With Top

Mar 5, 2008

Hello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA.I wish to select  A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first  bot_date>B.hist_date  and after this, if we still gets multiple records then the top record will be selected (order by date)
Any ideas?

View 7 Replies View Related

Can't Join This Query

Jul 21, 2004

I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.


Code:


SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
WHERE news_id =41

SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54



my previous attempts are

trouble with query.

I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.

now when I'm editing the article I want to get the name of the author from the users table.

I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.

they both need to lookup the user_fname and user_lname fields from the users table.

my current sql is below


Code:

SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41



which gives me

Code:

news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author *
41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green



Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
every way I try doesn't work.

this is what I'm currently trying to get it with.


Code:


SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (

SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
FROM news
WHERE news.edit_author = users.user_id
)
WHERE news_id =41




Heres a different approach that I thought would work


Code:


SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (

SELECT concat( users.user_fname, users.user_lname ) AS edit
FROM users
WHERE users.user_id =54
) AS edit
WHERE news_id =41



but no i get this error

Code:


MySQL said:

#1064 - You have an error in your SQL syntax near '(
SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE' at line 4



I also tried


Code:


SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id
WHERE news_id=41



but got this error

Code:


MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE us' at line 2



can anyone help me with this query its been driving me up the wall all morning.

View 2 Replies View Related

Join Query

Jul 28, 2004

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

EmpidDatePts(DIv1) Pts(DIv2) Pts(DIv3)
V00107-24-2004NullNull25
V00207-24-200420Null25
V00307-24-2004Null30NUll
V00107-23-200415NullNUll
V00207-23-2004102525
V00107-22-2004Null1025

I'm badly in need of help. Any sort of help is appreciated.


M.L.Srinivas

View 3 Replies View Related

Join And Sum Query

May 30, 2012

how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....

Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal
FROM dbo.Cust P
INNER JOIN
(SELECT
CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
Sum(Balance) as Bal
FROM dbo.CAsh
Where Accumulator='DP'
GROUP BY CustID) A ON P.CustID = A.CustID

The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.

Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
FROM dbo.Cust P
INNER JOIN

[code]....

View 14 Replies View Related

Self Join - Query Reg

Apr 8, 2008

I've a table like

Object ObjIdParentObjId
------ ----------------
Obj1 |101 |0
Obj2 |102 |101
Obj3 |103 |0
Obj4 |104 |102


If the 'ParentObjId' is 0 it means no more parent,

If I filter the Object value as Obj4, it should show the following rows


Obj4 |104 |102
Obj2 |102 |101
Obj1 |101 |0
Guide to me write a SQL for this situvation?

View 17 Replies View Related

Query JOIN

Jun 10, 2008

i have two tables field1 is the primary key:
table1
--------------------
field1|field2|field3
---------------------

table2
---------------------
field1|field4|field5
---------------------
what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.

is that possible, i used inner, right joins but all in vain.

what I need is a output like this

output
---------------------------------------
field1|field2|field3|field4|field5
---------------------------------------

View 5 Replies View Related

Join Query

May 17, 2006

i hav 2 tables which contain a common field called ID.
Table A Table B
ID ID
a a
b b
c
d

Now i want results from Table A in such way that ID's Which are in table B do not get selected in table A.
how do i use join query for this

View 2 Replies View Related

Inner Join Query

Mar 15, 2007

Hi

I have two tables and one linking table they look something like this

<<<<<<<Application Table>>>>>>

AppID AppName
1 MS Word
2 Excel
3 SoftGrid

<<<<<<System Table>>>>>>>>

SysID SysName
1 System 1
2 System 2
3 Server 1

<<<<<<Link Table>>>>>>>>>

SysID AppID
1 1
1 3

How can i retirve the data so that it picks up and displays all the applications that are not on the the server as well as those that are

for example the output im trying to get is as follows from the data above.......

SysName AppName Y/N
System 1 MS Word Y
System 1 Excel N
System 1 SoftGrid Y
System 2 MS Word N
System 2 Excel N
System 2 SoftGrid N
Server 1 MS Word N
Server 1 Excel N
Server 1 SoftGrid N

i want to able to get a dynamically generated Y/N colunm

Im ok with doing innerjoin queries but the problem is how do i get the rest of the results that have no links to show that there it does not exist.

Thanks

View 3 Replies View Related

Sub Query In Inner Join

Jun 4, 2007

Hi,
I need to write a query to get the data in hierarchy from a table.
Like emp table where each employee(EMPID) has an manager and manager is also an employee(EMPID).
There could be 2 level of managers. Mean an employee who is a manager can also have manager.
So i need to write query which shows three columns with column1=toplevel manager, column2=secondlevel manager and column3=team member.

Any help will be appreciated.

Thanks,
Nadeem

View 7 Replies View Related

Join Query

Jul 12, 2007

I use the following query

select distinct safetyvideo2007.user_id,safetyvideoview.name
from safetyvideo2007,safetyvideoview where safetyvideo2007.user_id=safetyvideoview.user_id
order by safetyvideoview.name asc

this retrive records only where first table user_id= second table user_id

user_id......name
---------------------
p0088421Khan
p0038040Peter
p0031344Jones
....

table1 = safetyvideo2007 user_id colum
table2 = safetyvideoview user_id and name column

I want the it retreive records from safetyvideo2007 all records
if first table only have user_id column no name column available.

ie. show table safetyvideo2007 all user_id if second table safetyvideoview user_id does not found ? ie.
it found user_id in second table then ok otherwise it retreive
all records from safetyvideo2007.

like this.
user_id.......name
---------------------
p0088421Khan
p0038040
p0031344Jones
p0031345Martin
p0031347Wagner
p0031357
....
...

display all safetyvideo2007 user_id if second table safetyvideoview
user_id not found ?

regards
Martin

View 2 Replies View Related

Join Query Help

Jul 25, 2007

Hi,

I have a table with following fields..

tblEmployee
-----------

EmpID int,
EmpName char(20),
MgrID int


I want the name of all employees and names of correspoding managers..

Please help me as soon as possible..

Thanks in Advance..

Regards,
Asha Sivan

View 1 Replies View Related

Join Query Help

Sep 20, 2007

Hello everyone,

I am stuck with a query, i cannot make it work.

I have 2 tables:

NEWS
ID | TITLE | TEXT
-------------------
1 | title1 | text1
2 | title2 | text2
3 | title3 | text3

IMAGES
ID | IDNEW | PATH
------------------
1 | 1 | path1
2 | 1 | path2
3 | 1 | path3
4 | 2 | path4
5 | 2 | path5
6 | 3 | path6


I need to get all the news, and for each new i need the id of only 1 of the possible images.

I thought it could be an inner join but it does not work:

SELECT NEWS.*, IMAGES.ID AS IDIMAGE
FROM NEWS
INNER JOIN IMAGES
ON NEWS.ID = IMAGES.IDNEW

Can someone please help me with this query?

Thanks in advance.

View 3 Replies View Related

Join Query

Jan 4, 2008

I have tab1 as:

Col1 Col2 Col3
1 2 Null

Then the Master as:

Col Name

1 One
2 Two
3 Three


I want the output as

Col1 Col2 Col3
One Two Null

Any help? I have been struggling since yesterday.

View 3 Replies View Related

Query Using Self Join

Jan 23, 2008

Table EMP
EMP_IDEMP_NAME
1A
2B

Table MgrID
EMP_IDMgr_ID
12

How to have the following result
EmpNameMgr_Name
AB


-Senthil

View 2 Replies View Related

Query Inner Join

Feb 20, 2008

Hi i have a query which i cant get to work,

i need to display categories and sub categories

Category 1
SubCat1
SubCat2

Category 2
SubCat1
SubCat2

but i am actually geting

Category 1
SubCat1
Category 1
SubCat2

Category 2
SubCat1
Category 2
SubCat2

any ideas what i need to do to this query?

select CT.Cat_ID, CT.Cat_Name, ST.SubCat_Name, ST.SubCat_ID
from dbo.Category_Table CT

LEFT JOIN dbo.SubCategory_Table ST ON ST.SubCat_Cat_ID = CT.Cat_ID

View 4 Replies View Related

Sql Query Help Join

Feb 17, 2006

kind of a tough one i think but any help is appreciated. please try tostay away from T-SQL...I have a table with products and closing dates for each of 4 quartersand annual for several years back. (Earning_Dates)I have another table that has those products with dates and closingprices. (undPrices)i have a third table which has the id for all the products.I need a query that will look at earning_dates and return the latest 6records for each product.ALSOa query which will do that as well as return the closing_price at eachof those 6 dates for each of the products in earning_dates.parts of the tables:select top 10 *from WTGlobal.dbo.Earnings_Datesselect top 10 *from [wtchi-sqldb].pnl.dbo.undpricesorder by Trade_date descselect top 10 *from [wtchi-sqldb].pnl.dbo.productsProduct BB_exch Expected_Report_DtExpected_Report_Period-------------------------------- -------------------------------------------------------------------------------------------------A 3 2005-11-14 00:00:00.0002005:AA 3 2005-11-14 00:00:00.0002005:Q4A 3 2005-08-15 00:00:00.0002005:Q3A 3 2005-05-16 00:00:00.0002005:Q2A 3 2005-02-14 00:00:00.0002005:Q1A 3 2004-11-11 00:00:00.0002004:AA 3 2004-11-11 00:00:00.0002004:Q4A 3 2004-08-12 00:00:00.0002004:Q3A 3 2004-05-17 00:00:00.0002004:Q2A 3 2004-02-17 00:00:00.0002004:Q1(10 row(s) affected)Product_id Price_Open Price_Close Trade_DateTrade_Date_PrevTrade_Date_Nextumi----------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------7968 4.38000 4.43000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017963 23.32000 23.84000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017961 6.77000 7.20000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017960 10.05000 10.12000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017959 16.97000 15.99000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017958 15.72000 15.98000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017957 66.62000 70.59000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017956 31.35000 31.62000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017955 5.15000 5.09000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017953 5.25000 5.34000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.0001(10 row(s) affected)Product_id Product Currency_id Locale_idumi update_date----------- -------------------------------- ----------- ---------------------- ------------------------------------------------------3594 .DJX 1 40 12006-02-16 08:55:39.8103595 .MNX 1 40 12006-02-16 08:55:39.8103596 .MOX 1 40 12006-02-16 08:55:39.8103597 .NDX 1 40 12006-02-16 08:55:39.8103598 .OEX 1 40 12006-02-16 08:55:39.8103599 .OSX 1 40 12006-02-16 08:55:39.8103600 .RLG 1 40 12006-02-16 08:55:39.8103601 .RLV 1 40 12006-02-16 08:55:39.8103602 .RUI 1 40 12006-02-16 08:55:39.8103603 .RUT 1 40 12006-02-16 08:55:39.810(10 row(s) affected)

View 1 Replies View Related

Pls Help With JOIN Query...

Nov 23, 2006

I'm trying to write a stored proc...Basically, I have a tblItems table which contains a list of every itemavailable. One of the columns in this table is the brand... for testpurposes, I hardcoded the BrandID=1...tblItems also contains a category column (int) which contains a categoryIDof 0..3...I then have a category table which has CategoryID, Name, and DisplayOrder...So basically what I'm trying to do is return a list of Category NAMES thathave items in them for a specifc brand... but I want to sort the returnedcategories by the DisplayOrder column...this is what I have now:select DISTINCT tblCategories.Name, tblCategories.DisplayOrder fromtblCategoriesINNER JOIN tblItemson tblCategories.CategoryID = tblItems.CategoryIDwhere BrandID=1 order by tblCategories.DisplayOrderthis does what I want it to do, but its returning TWO columns... Name ANDDisplayOrder... I only want to return Name, but if I take the DisplayOrderout of the select portion, it errors out because it can't order by that...Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies ofthe same category name.

View 5 Replies View Related







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