Cross Join Tables And Nulls
Jan 31, 2015I 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
ADVERTISEMENT
Help With Cross Join - 3 Tables
Feb 26, 2008I 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,
Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language
Apr 30, 2008Hello
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
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!!!!
Cross Join Without Table?
Feb 1, 2005I 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
Sub-Query / Cross Join / Or Something Else?
Mar 21, 2007I 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 RelatedTOP 2 In A Cross Join (Matrix)
Oct 30, 2006Hello
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
Same Effect As Cross Join?
Jul 7, 2006How 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.
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?
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
Indexes, Nulls And A Join Problem
Dec 11, 1998I am getting VERY poor performance on a join even though I have indexes. Instead of using the indexes, SQL Server does a table scan on one of the tables in the join. After I force the index in the query, I can see why. The index doesn't give any better results than a table scan. I suspect it may be because the table has a huge number of rows with null values for one column of a two-column key used in the join. However, the same tables use the indexes and give good performance when queried independently. It seems the problem is specific to join processing. I notice that the index I created appears to have pages referencing the null rows. I know that Oracle disregards nulls when creating indexes, but does SQL Server not do so? Is there any way to create a better index? And if I can't solve the join problem, any suggestions about workarounds?
The details:
-I have TABLE A with columns X, Y, and Z. In TABLE A there are 380,000 rows, and X and Y yield a unique key. The index is on X and Y. This table has 240,000 rows where X is null but there is a non-null value for Y.
-I have TABLE B (60,000 rows) with columns X,Y, and Q, where X,Y, and Q yield the unique key and have an index.
-Here is my query:
SELECT A.Z
FROM A, B
WHERE B.Q=1
AND B.X = A.X AND B.Y = A.Y
Problem With A Join Due To Multiple Nulls
Mar 29, 2006I want to join 2 tables by a unique ID field, but the ID field also hasmultiple NULLS which I do not want to ignore and I fear they will causeduplication.Using TableA and TableB below i will demonstrate the problem.TableATableA.ID Field1 Field21 Paul 1Null John 12 John 1TableBTableB.ID Field3 Field41 25 1Null 32 1Null 23 12 26 1The Table I want isTableA.ID TableB.ID Field1 Field2 Field3 Field41 1 Paul 1 25 12 2 John 1 26 1Null Null John 1 Null NullNull Null Null Null 32 1Null Null Null Null 26 1I think a select distcinct statement with a full outer join may do whatI want, but I'm not certain so want to check.Regards,Ciarán
View 10 Replies View RelatedUrgent Help With Query (Cross Join)
Sep 28, 2006I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_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_TitlePlease help. Thank you.
Querying On A Nvarchar(MAX) With Cross Join?
Nov 6, 2006Hello,
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)?
Cross Join/? Query In A View Help
Feb 4, 2004Hey 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
Cross Join Iteration Problem...
Feb 5, 2004I 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.
Pickup Tax Rate Without Using CROSS JOIN
Aug 29, 2005I 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 RelatedCross Join Inside Grouo
Aug 20, 2007table 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
SQL 2012 :: Join Removing NULLS That Need To Be Displayed?
Apr 22, 2014I have a table named item that looks like this:
PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014
And a second table named orders that looks like this:
OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL
What Does Just 'JOIN' Statement Do W/o Inner Or Outer Or Cross In Front
Jul 26, 2002There 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.
Left Join Returns Values Where I Was Was Expecting Nulls
Nov 16, 2006I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'
In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
Help With (Pivot/Cross-Join???) Query To Select A Result Set
Jan 20, 2005I 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
Cross Database Join In A Data Source View
Jun 21, 2007Hi,
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
SQL Server 2014 :: Writing A Cross Join Query With One Table?
Jul 19, 2015writing 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)
SQL Server 2014 :: Column Store Query Reverting To Row Mode With CROSS JOIN
May 20, 2015I 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] .....
Transact SQL :: Cross Reference 2 Tables
Jul 28, 2015I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).
I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.
How do I go about doing this..???
Cross Validation With Predictable Nested Tables
Dec 24, 2007How does cross-validation work in the case of models with predictable nested tables? Is it supported? For classification and regression with a flat structure, during the testing phase (that is, validation phase) of cross-validation I can think of the inputs being presented and comparing the predicted value with the real value. But in the case of nested tables, the input is not a subset of the attributes (a subset of the input vector), but whole input vectors. (For instance, complete itemsets in the case of association rules). Can you please explain some more how the validation phase works in the case of the association rules and decision trees with predictable nested tables?
thanks,
Gustavo Frederico
Fact Table With 3 Keys From Dimension Tables - Avoid Populating NULLs
Jun 10, 2014I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
View 3 Replies View RelatedHOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell
Nov 2, 2006Hello
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
How To Join 3 Tables Using Left Or Right Join Keyword?
Aug 17, 2007Hi guys,
I'll appreciate any help with the following problem:
I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.
1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02
2. Master table TBCODE contain 5 records:
rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5
3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR
rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR
I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.
Right Join method:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE
RIGHT JOIN TBLOC C ON A.LOC = C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
When I use Non-ASNI method it work:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C
WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
Result:
LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL
Please Help.
Thanks.
Column Allows Nulls I Want To Change No Nulls Allowed
May 16, 2006When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
Testing Permutations Of Nulls And Not Nulls
Feb 17, 2008is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies View RelatedDo Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS
May 15, 2008I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.