Inner Join Weirdness In DTS
Nov 23, 2005
I am trying to import data from Access 2000 in SQL Server 2000 using
DTS. One of the tasks requires a multi-table join but I am getting
syntax errors if I generate the query with Build Query.
With just a single join like this it works fine:
FROM Tracker INNER JOIN
bdmanager ON Tracker.bdmanager = bdmanager.name,
country
But as soon as I get it to generate an extra join, e.g.
FROM Tracker INNER JOIN
bdmanager ON Tracker.bdmanager = bdmanager.name
INNER JOIN
country ON Tracker.country = country.country
.... I get "sytax error (missing operator)". The weird thing is that it
generated the syntax itself!
I can paste the query into Access and it works fine.
Why is this happening and what's the best workaround?
Thanks
Andy
View 3 Replies
ADVERTISEMENT
Mar 4, 2008
Hi All: I'm having problems creating a data connection between a windows 2000 server running .net 2.0 and a sql2005 server (server2003). I'm trying to create a connection in VWD2005 express, and when I use the visual wizard, I get a connection error with something about "named pipes" when I'm trying to connect using an alias on the server that uses tcp/ip (the alias is created in cliconfg).
If I click on "advanced properties" in the connection, and I select "TCP/IP" for the Network Library, what's displayed is "TCP/IP (DBMSGNET)" ...but shouldn't it say "DMBSSOCN"?
I'm wondering if this is correct, or a cosmetic bug in the .net sqlclient GUI, or indicative of any underlying problem that's preventing me from creating the connection?
If I manually override the connection string in web.config by entering "Network Library: DBMSSOCN", I still can't connect, getting a timeout error that the sql2005 server is not responding. But I know the server is working properly, with other .NET DB connections working within the same application...so I'm thinking this must be a problem with VWD and/or the .net framework on my local computer (not the server)?
I don't know where to begin to look to debug this problem?
View 2 Replies
View Related
Mar 30, 2001
Hey folks,
Today's been way too much fun...
Someone changed the name of a machine in our department and now Win2k thinks the machine's name is (for example) "Fred", but if I do a select @@servername, SQL returns (again, for example) "Barney".
The machine's name started out as "Barney".
The last time this happened, it was on an NT4 box, and we just reinstalled sql and away we went.
This time, they reinstalled sql and released the box to the team, only to find out later that reinstalling sql didn't do squat this time.
Which brings me to the question: How do I convince SQL that it's really, truly supposed to be named "Fred"?
Besides that, what do I tell Wilma, Betty, Pebbles and Bamm Bamm?
Any assistance would be greatly appreciated.
: )
Thanks,
Tom
View 3 Replies
View Related
Jan 7, 2006
If I alter the SqlDataSource select command in code and then bind to a gridview, I run into problems. When I do a sort, next page (basically any postback), the datasource goes back to the original state. It is like the SqlDataSource is not maintained in the state. I end up having to re-alter the SqlDataSource select command on every page_load. Is this by design or is this a bug?
Is the SqlDataSource any "smarter" than doing it the old fashion way by populating a Dataset on (!IsPostBack) ? For example, if I have a bunch of data in a paged gridview, is the SqlDataSource smart enough not to bother filling the entire dataset if I don't need it for that page's display? I know the SqlDataSource provides for update/insert/delete, but I am not doing that in this application, it is just a query/report page.
Thanks in advance
View 4 Replies
View Related
Apr 15, 2008
I've been writing stored procedures for a while, but right now I'm stumped on something. I've got this one error when I try to use OPENROWSET in my stored procedure that tells me I need to set my ANSI_NULLS and ANSI_WARNINGS, so I put SET ANSI_NULLS ON GO SET ANSI_WARNINGS ON GO into my stored procedure, I clicked check syntax (this is all in enterprise manager), it was okay, I clicked okay, it told me I needed to set those things again, so I played around some more, deleted the SET ANSI's, adding them, deleting them, moving them around, and eventually it worked after I deleted them again.
Anyone know why this happens? Where exactly are my SET ANSI_NULLS supposed to go? As a workaround I've been simply running them in the query analyzer.
View 1 Replies
View Related
Mar 4, 2008
Hi,
I have an Excel source > Data Conversion task > OLE DB Destination.
In the Data Conversion task I rename all the outputs to match the column names in my destination table.
However, when I go to map the columns in the OLE DB Destination mapping tab, it's a mess. Some fields are prefaced by "Data Conversion" as in "Data Conversion.column1", others are prefaced by "Excel Source" and others have no prefix at all.
What's confusing is, since all the columns are going through the Data Conversion task, how come I don't have ALL fields prefaced by "Data Conversion" ?
That is, only SOME of the fields have a "Data Conversion" prefix, and some don't. The ones that aren't prefaced by "Data Conversion" have no corresponding "Excel Source" so I'm assuming that the ones without the prefix are from the Data Conversion task.
It's inconsistent. Any ideas.
Thanks
View 3 Replies
View Related
Jul 20, 2005
There is something very strange going on here. Tested with ADO 2.7 andMSDE/2000. At first, things look quite sensible.You have a simple SQL query, let's sayselect * from mytab where col1 = 1234Now, let's write a simple VB program to do this query back to anMSDE/2000 database on our local machine. Effectively, we'llrs.open sSQLrs.closeand do that 1,000 times. We wont bother fetching the result set, itisn't important in this example.No problem. On my machine this takes around 1.6 seconds and modifyingthe code so that the column value in the where clause changes eachtime (i.e col1 = nnnn), doesn't make a substantial difference to thistime. Well, that all seems reasonable, so moving right along...Now we do it with a stored procedurecreate procedure proctest(@id int)asselect * from mytab where col1 = @idand we now find that executingproctest nnnn1,000 times takes around 1.6 seconds whether or not the argumentchanges. So far so good. No obvious saving, but then we wouldn'texpect any. The query is very simple, after all.Well, get to the point!Now create a table-returning UDFcreate function functest(@id int) returns table asreturn(select * from mytab where col1 = @id)try calling that 1,000 times asselect * from functest(nnnn)and we get around 5.5 seconds on my machine if the argument changes,otherwise 1.6 seconds if it remains the same for each call.Hmm, looks like the query plan is discarded if the argument changes.Well, that's fair enough I guess. UDFs might well be more expensive...gotta be careful about using them. It's odd that discarding the queryplan seems to be SO expensive, but hey, waddya expect?. (perhaps theUDF is completely rebuilt, who knows)last test, then. Create an SP that calls the UDFcreate procedure proctest1(@id int)asselect * from functest(@id)Ok, here's the $64,000 question. How long will this take if @idchanges each time. The raw UDF took 5.5 seconds, remember, so thisshould be slightly slower.But... IT IS NOT.. It takes 1.6 seconds whether or not @id changes.Somehow, the UDF becomes FOUR TIMES more efficient when wrapped in anSP.My theory, which I stress is not entirely scientific, goes somethinglike this:-I deduce that SQL Server decides to reuse the query plan in thiscircumstance but does NOT when the UDF is called directly. This iscounter-intuitive but it may be because SQL Server's query parser istuned for conventional SQL i.e it can saywell, I've gotselect * from mytab WHERE [something or other]and now I've gotselect * from mytab WHERE [something else]so I can probably re-use the query plan from last time. (I don't knowif it is this clever, but it does seem to know when twotextually-different queries have some degree of commonality)Whereas withselect * from UDF(arg1)andselect * from UDF(arg2)it goes... hmm, mebbe not.... I better not risk it.But withsp_something arg1andsp_something arg2it goes... yup, i'll just go call it... and because the SP was alreadycompiled, the internal call to the UDF already has a query plan.Anyway, that's the theory. For more complex UDFs, by the way, theperformance increase can be a lot more substantial. On a big complexUDF with a bunch of joins, I measured a tenfold increase inperformance just by wrapping it in an SP, as above.Obviously, wrapping a UDF in an SP isn't generally a good thing; theidea of UDFs is to allow the column list and where clause to filterthe rowset of the UDF, but if you are repeatedly calling the UDF withthe same where clause and column list, this will make it a *lot*faster.
View 3 Replies
View Related
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
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 Replies
View Related
Oct 8, 2015
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.
View 5 Replies
View Related
Dec 23, 2014
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
View 9 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
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
Aug 9, 2013
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies
View Related
Jun 5, 2006
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies
View Related
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
Apr 24, 2007
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
View 2 Replies
View Related
Dec 24, 2013
We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.
Below is my Implict join query
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow
[Code] ....
and below is explict join query
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow
ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
[Code] ....
the count returned from both the queries is different.
I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.
View 7 Replies
View Related
Feb 5, 2015
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]
View 3 Replies
View Related
Aug 17, 2007
Hi 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.
View 3 Replies
View Related
Nov 7, 2006
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
View 2 Replies
View Related
May 6, 2008
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS
View 3 Replies
View Related
Apr 17, 2007
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
View 14 Replies
View Related
Mar 1, 2008
hello, i am running mysql server 5 and i have sql syntax like this:
select
sales.customerid as cid,
name,
count(saleid)
from
sales
inner join
customers
on
customers.customerid=sales.customerid
group by
sales.customerid
order by
sales.customerid;
it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales.
what can be the problem?
thanks,
View 10 Replies
View Related
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
May 20, 2006
Hi,Just curious. Would you use ANSI style table joining or the 'oldfashion' table joining; especially if performance is the main concern?What I meant is illustrated below:ANSI Styleselect * from a join b on a.id = b.idOld Styleselect * from a, b where a.id = b.idI noticed that in some SQL, the ANSI is much faster but sometimes, theold style looks much better.It's ridiculous to try out both styles to see which is better wheneverwe want to write an SQL statement.Please comment.Thanks in advance.
View 1 Replies
View Related
Jun 28, 2007
Hello, everyoneI have one question about the standard join and inner join, which oneis faster and more reliable? Can you recommend me to use? Please,explain me...ThanksChamnap
View 12 Replies
View Related
Oct 10, 2007
Given a table of building components e.g. floors, walls, etc, etc:
create table component_multiplier_table
(
system_code char(4),
system_component_code char(3),
function_code char(4),
component_multiplier dec(7,6)
)
Where function_code is the function of the area e.g. Auditorium, Classrom, etc, etc. And not all components are available for all functions e.g. Carpeting is available for Classrooms but not Power Plants or Warehouses.
I need to self join the above table to itself on system_code and system_component_code and find out which rows are missing from each side.
A query that I've been banging away at with no success is:
SELECT c1.*, c2.*
FROM [dbo].[component_multiplier_table] c1 FULL OUTER JOIN [dbo].[component_multiplier_table] c2
ON (c1.system_component_code = c2.system_component_code) AND (c1.[system_code] = c2.[system_code])
WHERE c1.function_code = '2120' AND c2.[function_code] = '2750' AND (c1.[system_code] IS NULL OR c2.system_code IS NULL);
I added the is null conditions, no joy. I've tried every flavor of outer join w/o success.
Could any T-SQL gurus out there help me figure out how to do this in a set before I start coding
DECLARE crsr CURSOR
Thanks.
View 7 Replies
View Related
Apr 16, 2008
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
View 5 Replies
View Related
Apr 26, 2002
Take the following scenario:
We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.
Thanks in advance for any help.
-David Edelman
Test script below, followed by results
===========================================
create table parent (p_id int NOT NULL)
go
create table child (p_id int NOT NULL, c_type varchar(6) NULL)
go
insert parent values (1)
insert parent values (2)
insert parent values (3)
insert parent values (4)
insert parent values (5)
insert parent values (6)
insert parent values (7)
insert parent values (8)
insert parent values (9)
insert parent values (10)
go
insert child values (1, 'AAA')
insert child values (1, 'BBB')
insert child values (1, 'CCC')
insert child values (2, 'AAA')
insert child values (4, 'AAA')
insert child values (4, 'DEF')
insert child values (4, 'AAA')
insert child values (4, 'BBB')
insert child values (5, 'AAA')
insert child values (5, 'AAA')
insert child values (6, 'AAA')
insert child values (7, 'AAA')
insert child values (7, 'BBB')
insert child values (7, 'CCC')
insert child values (7, 'DDD')
insert child values (10, 'AAA')
insert child values (10, 'CCC')
go
select p.p_id, count(c.p_id) as num_rows
from parent p, child c
where p.p_id *= c.p_id
and c.c_type in ('AAA', 'BBB')
group by p.p_id
select p.p_id, count(c.p_id) as num_rows
from parent p left outer join child c on p.p_id = c.p_id
where c.c_type in ('AAA', 'BBB')
group by p.p_id
=========================================
Results:
(T-SQL *= outer join)
p_id num_rows
----------- -----------
1 2
2 1
3 0
4 3
5 2
6 1
7 2
8 0
9 0
10 1
(SQL-92 outer join)
Warning: Null value eliminated from aggregate.
p_id num_rows
----------- -----------
1 2
2 1
4 3
5 2
6 1
7 2
10 1
View 1 Replies
View Related
Sep 30, 2005
Hello,
Could someone please explain the following sel statement to me please:
SELECT * FROM userdata UD
join useroffice UO on UO.USER_ID = UD.PK
join officedata OD on OD.pk = UO.OFFICE_ID
left join usertitle1 UT on UT.PK = UD.fk_user_title1
WHERE UD.PK = 451
ORDER BY UD.pk
I understand that the joins are just brining data that is related the a particular filed but why the LEFT JOIN???
Thanks
View 2 Replies
View Related
Nov 2, 2007
Hello,
I have this table called "Playlist_Items". It has different "media types" in it. An MP3 is "1", a video is "2", etc.
I want to make a query that gets all the items in the playlist and gets common properties between them, like the name, user, etc.
The only problem is that the tables aren't normalized, so I have to do something like, if the "MediaType" is 1 then look in the MP3 table and get this column, otherwise if its 2 then look in the video table. Etc.
I have no idea how to do this in SQL... :/
Here is a diagram of my tables:
Thanks very much!
View 3 Replies
View Related
Jun 25, 2007
Hi,
This is a sample database table
TableA
============================
aID int identity(1,1) primary key
aName varchar(30)
TableB
===========================
bID int identity(1,1) primary key
bTitle varchar(30)
aID int references TableA(aID)
TableC
===========================
cID int indentity(1,1) primary key
cCategory varchar(30)
bID int references TableB(bID)
Here I got two query, are them the same?
Select A.aName, B.bTitle, C.cCategory
From TableA A, TableB B, TableC C
Where A.aID = B.aID And B.bID = C.cID
and
Select A.aName, B.bTitle, C.cCategory
From TableA A Join TableB B On A.aID=B.aID
Join TableC On B.bID=C.cID
Are those two the same?
And what is the different of JOIN and LEFT OUTER JOIN? Any other JOIN?
Millions Thanks!
View 6 Replies
View Related