Urgent Help With Query (Cross Join)

Sep 28, 2006

I have the following tables:
members
--------------
member_id
member_tpc_id ( = tpc.tpc_id)

tpc
------
tpc_id
course
------------
course_id
tpc_assignment
---------------------------
tpc_assignment_id
course_id
enrollment
-------------------
member_id
course_id
enrollment_status 
Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)
Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is 
Select
*, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status,
(Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title
From
(Select member_id As user_id, member_name, member_tpc_id
From members Where member_tpc_id> 0 And organization_id = '1'
)temp1
cross join
(Select course_id As course_id, course_title As course_title
From course Where course_id IN
(Select course_id From tpc_assignment Where tpc_requirement_id IN
(Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1')
)temp2
Order By member_name, Tpc_Title

Please help. Thank you. 

View 1 Replies


ADVERTISEMENT

Sub-Query / Cross Join / Or Something Else?

Mar 21, 2007

I have the following 2 tables:(BATCHES)BatchID [int] KEYID [int]OrderID [int]Action1DateTime [datetime]Action2DateTime [datetime]Action3DateTime [datetime]Action4DateTime [datetime]Action5DateTime [datetime]Action6DateTime [datetime]Action7DateTime [datetime]Action8DateTime [datetime](ORDERS)OrderID [int] KEYProductionLineID [int]RecipeID [int]OrderAmount [int]Batches.Action1DateTime to Batches.Action8DateTime can have several entrieseach day.I need a query to count all Batches.Action1DateTime to allBatches.Action8DateTime for each day in a specified period.I also need to specifically use where clauses for Orders.OrderID and/orOrders.RecipeID.I need the data to draw a graph for each ActionXDateTime as a function ofdate.Any help appreciated./Henrik

View 3 Replies View Related

Cross-Join Query

May 21, 2008





Hi folks, this is my first time here... i hope that you can help me with this query

I have two query and one table

Queries
Q1-Training - List of training
Q2-Group - Groups that must take the training
Table
T1 - Training_Group

Here a query example using SELECT * FROM

Q1
Code Name
-----------------------------------
AD0041 Advance SQL
BE0012 Beginner SQL
NE0001 Newbie SQL
...

Q2
Group_Name Group_Code
-----------------------------------
Administration ADMIN
Directors 456
John Smith 555666
...

T1
Code Level Group_Code
-----------------------------------
AD0041 1 ADMIN
AD0041 2 456
AD0041 3 555666
BE0012 2 456
NE0001 1 ADMIN

Here's my problem :

I need to make a report that look like that

(as training) (Group_Code) --> dynamic column
^^^^^^ ^^^^^^
Training ADMIN 456 555666
-------------------------------------
AD0041 1 1 1
BE0012 0 1 0
NE0001 1 0 0

I did it in ACCESS with TRANSFORM and PIVOT.

But how i do that in SQL Server 8.0 ?^

Thanks in advance for your solution or hints... i'll be back tomorrow morning

Best regards
David

View 5 Replies View Related

Cross Join/? Query In A View Help

Feb 4, 2004

Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|

#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1

How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).

Thanks

robbied111

View 2 Replies View Related

Help With (Pivot/Cross-Join???) Query To Select A Result Set

Jan 20, 2005

I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.

Current structure is:

Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000

I want to produce this result set:

Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000

There could be any number of sizes or colours.

Is this possible? Can anyone give me any pointers?

Thanks in advance

greg

View 8 Replies View Related

SQL Server 2014 :: Writing A Cross Join Query With One Table?

Jul 19, 2015

writing a cross join query with one table:

Cities(City_name, X_coordinate, Y_coordinate)

the result should be all combinations without reverse column returns

SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???

for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)

View 8 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

SQL Server 2014 :: Column Store Query Reverting To Row Mode With CROSS JOIN

May 20, 2015

I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.

-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.

-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;

[Code] .....

View 6 Replies View Related

JOIN Query Help(urgent)

Nov 2, 2004

i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below

TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM



TOTALESCA shows NuMbers of escalated calls out of totalcalls

table 1

TOTALCALL
AGENTID
DATE


TABLE 2

TOTALESCA
AGENTID
DATE

SAMPLE DATA ON TABLE 1

TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM

SAMPLE DATA ON TABLE 2


TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PM

View 14 Replies View Related

Cross Join

Dec 25, 2007



good morning
I wanna to put a sub query in the cross join like that



SELECT *

FROM table1

CROSS JOIN (select field

from table2

cross join table3)


but i got this error


Msg 102, Level 15, State 1, Line 7

Incorrect syntax near ')'.

can anyone help me!!!!

View 1 Replies View Related

Challenging: Cross Tab In SQL 2000 (Urgent)

Oct 8, 2007



I have the following table dbo.Test

Name Week Type Hours
Steve 1 Section_1 1

Steve 1 Section_1 2

Steve 2 Section_2 5

Sarah 1 Section_1 1

Sarah 2 Section_2 2

Sarah 2 Section_3 3

Mike 3 Section_3 1

Mike 1 Section_3 1


I know for a fact i am going to have no more than 3 sections

here is the output i would like to see


Name Week1 Section_1 Section_2 Section_3 Total

Steve 1 3 3

Steve 2 5 5

Sarah 1 1 1

Sarah 2 2 3 5

Mike 1 1 1

Mike 3 1 1


I read some articles but i am still confused.

thanks in advance for your help

View 6 Replies View Related

Cross Join Without Table?

Feb 1, 2005

I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:

tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk

I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end

But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:

event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown

Thanks for any help,
jb

View 2 Replies View Related

Help With Cross Join - 3 Tables

Feb 26, 2008

I am wanting to show Total Quantity of Presentations2 for each EmpVol and TypeofPresentation, even if there are no Presentations done in TypeofPresentation or if an Employee did not do any Presentations.

I am close with the query below - but not getting back exactly what I want - Ideas.

SELECT ISNULL(Presentations2.Quantity, 0) AS Quantity, TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER
FROM Presentations2 INNER JOIN
EmpVol ON Presentations2.ID = EmpVol.ID RIGHT OUTER JOIN
TypeofPresentation ON Presentations2.TypePresINT = TypeofPresentation.TypePresINT CROSS JOIN
EmpVol AS EmpVol_1 CROSS JOIN
EmpVol AS EmpVol_2
GROUP BY TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER, ISNULL(Presentations2.Quantity, 0)
HAVING (EmpVol_2.FSSTVOLUNTEER = N'FSST')

Thanks,

View 2 Replies View Related

TOP 2 In A Cross Join (Matrix)

Oct 30, 2006

Hello

I have to retrieve a cross join between languages and language-skills for more person in a matrix (grid) report.

E.g. i like to ritrieve the TOP 2 people in each "cell" of the grid (order by name).
There are 3 people in cell "ITALIAN / very good".
So i have to retrieve only the top 2.

In table tEmployee i have the people names (john, anna, michael)
In table tLanguage i have all language (english, italian, german)
And in table tLanSkill i have the language rating (bad, good, very good)

bad
good
very good


Englisch
Michael
John
Anna


Italian




John

Michael

Anna


German




Anna






THX for your help.

ciao, michele

View 5 Replies View Related

Same Effect As Cross Join?

Jul 7, 2006

How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?

Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.

I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...

I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.

Thanks in advance.

View 13 Replies View Related

It Seems Cross Join Is Usefulless At All?

May 31, 2008



StaffTable
StaffID (int ,PK)
StaffFullName(vnchar(50))

DepartmentTable
DepartmentID (int,PK)
StaffID (int,FK)
DepartmentName(vnchar(50))

it seems when I use SELECT * FROM StaffTable,DepartmentTable

it returns a lot of wrong data.(I guess, the right data is just in 10%, 90% is wrong)

So , is this mean, in a real industry software project, no one would use cross join?

View 8 Replies View Related

Querying On A Nvarchar(MAX) With Cross Join?

Nov 6, 2006

Hello,
I have a query that returned much more results than it should.  I believe this is because it was a cross join instead of inner join (because I forgot to map a temporary relationship in the designer of SQL Server Express; I think this is what the problem may have been, but I can't remember), but could it be because the join criteria is because it is an nvarchar(MAX)?
I did the join on two nvarchar(MAX) fields.  The table has 31102 rows in both tables, and it would pull back the first entry in the first table, and all 31102 rows in the second table, and so it would pull back 31102 rows for each entry in the first table.
Is that only because of the cross join, or maybe because of nvarchar(max)?

View 1 Replies View Related

Cross Join Iteration Problem...

Feb 5, 2004

I am having a mental fart...

I have two tables:

DECLARE @store_options TABLE(store_option_id INT IDENTITY(1,1), store_id INT)

DECLARE @vendor_options TABLE(vendor_option_id INT IDENTITY(1,1), store_option_id INT, item_id INT, vendor_id INT, price NUMERIC(18,4))

I populate the first table with a litst of stores that offer all desired items.

I populate the second table with a list of vendors, the item is, and cost avaiable at each of the stores in the first table.

What I would like is to output all possible the store and vendor combos ordered by combined price.

So, for instance, I have 3 products, A B and C. Store X has A and B by vendor G, and A B and C by vendor H. I want the output to have all iterations of (Store, Product, Vendor, Price) grouped in order of total price. So...

X A G
X B G
X C H

X A G
X B H
X C H

X A H
X B G
X C H

X A H
X B H
X C H

ordered by each group's combined price.

For some reason, I can't get this straight in my head. Must need more coffee.

View 1 Replies View Related

Cross Join Tables And Nulls

Jan 31, 2015

I have a small doubt regarding cross join and NULL values,

Table A has four rows (1,2,3,NULL)..Table B has 4 rows(1,2,3,NULL)..if i cross join table A and B..

How many rows will be there in result

View 5 Replies View Related

Pickup Tax Rate Without Using CROSS JOIN

Aug 29, 2005

I need to pickup a tax rate, that is stored on a 1 record file. I would like to avoid using the CROSS JOIN. Is there a way to SELECT the record and set a Variable = to the tax rate so I can pickup the rate in another SELECT statement on each record?

View 10 Replies View Related

Cross Join Inside Grouo

Aug 20, 2007

table a
id group value
1 g1 2
2 g1 3
3 g2 4
4 g2 5
5 g2 10

table b
gourp name
g1 n1
g1 n2
g2 n4
g2 n5

I want ret table look like
1 g1 2 n1
1 g1 2 n2
2 g1 3 n1
2 g1 3 n2
3 g2 4 n4
3 g2 4 n5
4 g2 5 n4
4 g2 5 n5
5 g2 6 n4
5 g2 6 n4

How can I fulfill this task, thanks.
Jeff

View 5 Replies View Related

What Does Just &#39;JOIN&#39; Statement Do W/o Inner Or Outer Or Cross In Front

Jul 26, 2002

There is some code that I am looking at. It is like this

SELECT a.Name, p.Name
FROM applicant a
JOIN Pending p
ON a.ID = p.ID

Thanks list.

View 2 Replies View Related

Cross Database Join In A Data Source View

Jun 21, 2007

Hi,

Is it possible to do a cross database join in a report services data source view? It doesn't look like it.

If not I was thinking of linking the table into the other database.

TIA,
Darren

View 3 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix


test a

text b








text c








text d

text e

text f








text g










This is a matrix with all the same row-height.



test a

text b

.








text c

.
.









text d

text e

text f








text g

.

.









Thx you a lot

View 3 Replies View Related

Cross Tab Query

Sep 7, 2007

 Hi!
I m using sql 2005.I have a table as showing below.










year
Region
loan_amt
pur_id
purpose


1981
Andhra pradesh
$20,000.00
     1
Animal Husbandary

1981
Arunachal Pradesh
$110,000.00
     1
Animal Husbandary

1981
Assam
$240,000.00
     1
Animal Husbandary

1981
Bihar
$75,000.00
     1
Animal Husbandary

1981
Chhattisgarh
$55,500.00
     1
Animal Husbandary

1981
Gujarat
$77,500.00
     1
Animal Husbandary

1982
Goa
$44,888.00
     1
Animal Husbandary

1982
Himachal pradesh
$4,000.00
     1
Animal Husbandary

1982
Himachal pradesh
$20,000.00
     1
Animal Husbandary

1982
Jammu and kashmir
$30,000.00
     1
Animal Husbandary

1882
Jharkhand
$35,000.00
     1
Animal Husbandary

1982
Karnataka
$40,000.00
     1
Animal Husbandary

1982
Kerala
   $20,000.00   
     1
Animal Husbandary

1982
Madhya pradesh
$5,000.00
     1
Animal Husbandary
 
 
I want to produce report as  by using crosstab query as showing bellow
 
Year      Asam     Hyadrabad   goa  arunachal pardesh etc............
1981      1000.00    2000.00       8000.2  00000        000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.
 
Thanx in advance.
Abhishek
 
 
 
 
 

View 6 Replies View Related

Hi Need Help In Cross Tab Query

Apr 14, 2008

I wanna develope a report using cross  tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test                   45          50            25          10            11
 
Please can any one help me how to do this this is very urgent ?

View 2 Replies View Related

Cross-Tab Vs SQL Query

Sep 15, 2006

Hi,
I have a report like this....

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
-------
-------


02/12/2006 1 1 1 3 1
------------------------------------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)


So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!

View 2 Replies View Related

Cross-tab Query

Jan 2, 2007

Hi all.

we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

Thanks so much.
ODaniels

View 6 Replies View Related

Cross Query

Oct 11, 2007

I would like to make 3x3 table

as following
john | mike | wendy
< 100€ | 3 0 0
100-200€ | 0 2 0
> 200€ | 1 0 1

table:
name (john, mike, wendy)
money (numer)


how should i do it?

i tried with inner join and subquery and a case but it doesn't work.

thank you

View 4 Replies View Related

Cross Tab Query

Jul 20, 2005

Hi - I have what I think is a "simple problem".We currently have a database table that stores ItemProperties byItemID, PropertyID and Value. (The PropertyID references another tablefor property names and types.)This ItemProperties table is indexed and provides a flexible way ofstoring our item metadata. However, I would now like to returnrecordsets to the application layer that list these properties incolumn fashion, grouped by ItemIDI have seen that most cross-tab queries examples assume numerical dataand are based around using SUM and the GROUP BY phrase. But our datais a mixture of string and numbers (of various formats) and so GROUPBY is not an obvious solution. I have tried using CASE in the selectlist but this returns one row for each property with one column havingthe correct value, and all the other colums are NULL. I cannot thinkof how to combine these into one full record!!I could achieve the desired resultset by using a SELECT sub-statementfor every column, but I was hoping there was a more efficient method.Can anyone offer advice on this? It would be most appreciated.Best,Bill

View 1 Replies View Related

Cross Tab Query

Aug 7, 2006

Is there any simple cross tab query template, which can used by general user?

I need it so that I can provide it to our Power users, so that they can use it themselves, instead of coming back to the IT Department.

(something similar like the one found in the Access)

Tomal

View 6 Replies View Related

Cross-database Query From ASP.NET

Feb 7, 2005

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?

View 1 Replies View Related

Complicated Cross-Tab Query

Feb 15, 2006

I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2,  SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?

View 3 Replies View Related







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