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
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
Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.
Scott
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!!!!
There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.
The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?
Thanks..
Code:
SELECT TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType,
dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State,
dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID,
dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite,
dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage,
dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style,
dbo.tblPurchaseRaw.StockDate
FROM dbo.tblPurchaseRaw INNER JOIN
dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
WHERE (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND
(dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
ORDER BY dbo.tblvZipCodes.ZIPCode
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
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,
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 RelatedHello
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
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.
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?
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
I 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.
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)?
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
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.
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
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 RelatedThere 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.
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
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
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)
Is it possible to do something like this:
Select a.address from person p
join Addresses a on p.AddressPointer =
Case when @UseNewAddress=1 then a.NewAddress else a.OldAddress end
It gives a syntax error but I'm wondering if there's another way to do it.
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] .....
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
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
I should've posted this thread here....
http://www.dbforums.com/t1005070.html
I need to format the following tables:
tbEmployees
EmployeeID | fName | lName
-------------------------------------------
jdoe | Joe | Doe
bsmith | Blake | Smith
tbDepartments
DepartmentID | Department
-------------------------------
ENG | Engineering
DET | Detailing
tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
------------------------------
ENG | jdoe
DET | bsmith
tbProjects
ProjectID |
---------------
1001
tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
-------------------------------------------
1001 | jdoe | ENG
1001 | bsmith | DET
To the following view :
vProjects
ProjectID | Engineer | Detailer
-------------------------------------
1001 | Joe Doe | Blake Smith
Any Ideas?
Mike B
Is it possible change to cross tab using code?
Before code:
NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198
After code
NAMEHEIGHTWEIGHT
AAA66198