Theta Join Or Equijoins??

Jul 23, 2005

What is the definition of a theta join? I read that all joins with the
comparison operators:

=, <,> etc. are theta joins, but joins with = are called equijoins. How can
a theta join both be equijoin and a theta join?

View 1 Replies


ADVERTISEMENT

The Price Of A Theta Join

Dec 5, 2007

Hello chapsI have a theta join on two tables, a tiddler and a bigun. The tiddler is there to recode the values of a field in my main table. It consists of mutually exclusive ranges corrollated to a new code. I want to keep the data in a table for flexibility however I would like the performance of a case statement. I suspect that SQL Server would be a lot better at this if it knew that the ranges are mutually exclusive. Although I know how to set up a check constraint to enforce this rule SQL Server still will not be able to use this information. The optimiser estimates three rows returned for the clustered index scan - actual rows is 699609 - which equals (number of rows in little table) * (number of rows in big table).BTW - I've been chucking unique constraints at the tiddly table just to see if I can affect execution - I can't.Question:Can I make this more efficient while maintining flexibility or is this just the price of a theta join? I would prefer to avoid dynamic sql please (in reality the full query is pretty complex).Ta!USE tempdbgoSET NOCOUNT ON ------------------------------ SET STUFF UP ---------------------------------IF NOT EXISTS (SELECT NULL FROM sys.schemas WHERE name = N'welovepoots') BEGIN EXEC ('CREATE SCHEMA welovepoots')ENDGOIF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'little_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.little_tableEND IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'big_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.big_tableENDCREATE TABLE welovepoots.little_table ( recode_this_lower DECIMAL(9, 3) NOT NULL , recode_this_upper DECIMAL(9, 3) NOT NULL , recode_this_recode TINYINT NOT NULL , CONSTRAINT pk_little_table PRIMARY KEY NONCLUSTERED (recode_this_lower, recode_this_upper) WITH (FILLFACTOR = 100) , CONSTRAINT ix_little_table_u_nc UNIQUE NONCLUSTERED (recode_this_recode) WITH (FILLFACTOR = 100) , CONSTRAINT ix_little_table_u_c UNIQUE CLUSTERED (recode_this_lower, recode_this_upper, recode_this_recode) WITH (FILLFACTOR = 100) ) GOINSERT INTO welovepoots.little_table (recode_this_lower, recode_this_upper, recode_this_recode)SELECT 30, 999999.999, 0UNION ALLSELECT 16, 29.999, 2UNION ALLSELECT 1, 15.999, 1CREATE TABLE welovepoots.big_table ( my_id INT NOT NULL , recode_this DECIMAL(9, 3) , CONSTRAINT pk_big_table PRIMARY KEY CLUSTERED (my_id) WITH (FILLFACTOR = 100) )GOINSERT INTO welovepoots.big_table (my_id, recode_this)SELECT a.number * b.number , CASE WHEN MIN(a.number % 50.5) = 40.5 THEN NULL ELSE MIN(a.number % 50.5) ENDFROM dbo.numbers AS aCROSS JOIN dbo.numbers AS bWHERE a.number <= 650 AND b.number BETWEEN 651 AND 1300GROUP BY a.number * b.number------------------------------ END SET STUFF UP ---------------------------------SET NOCOUNT OFFSET STATISTICS IO ONSET STATISTICS TIME ONSET STATISTICS PROFILE ON--Inefficient but flexible SELECT recode_this , recode_this_recode FROM welovepoots.big_table AS bt LEFT OUTER JOIN welovepoots.little_table AS lt ON bt.recode_this BETWEEN lt.recode_this_lower AND lt.recode_this_upper--Efficient but inflexible SELECT recode_this , recode_this_recode = CASE WHEN recode_this BETWEEN 30 AND 999999.999 THEN 0 WHEN recode_this BETWEEN 16 AND 29.999 THEN 2 WHEN recode_this BETWEEN 1 AND 15.999 THEN 1 END FROM welovepoots.big_table AS btSET STATISTICS IO OFFSET STATISTICS TIME OFFSET STATISTICS PROFILE OFFSET NOCOUNT ON------------------------------ CLEAN UP ---------------------------------IF NOT EXISTS (SELECT NULL FROM sys.schemas WHERE name = N'welovepoots') BEGIN EXEC ('CREATE SCHEMA welovepoots')ENDGOIF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'little_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.little_tableEND IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'big_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.big_tableEND------------------------------ END CLEAN UP ---------------------------------SET NOCOUNT OFF

View 14 Replies View Related

NON Equijoins

May 1, 2006



Hi there.

I just wanted to see if anyone has come across any patterns to deal with NON equijoins.

Based on this article - http://sqljunkies.com/WebLog/tpagel/archive/2005/08/31/16585.aspx - there seems to be 2 approaches:

1) encapsulate the logic in a stored procedure or

2) generate a joined dataset with potentially alot of unwanted rows which then need to be filtered out.

Are there any other patterns people have discovered?

Thanks.



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

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

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

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

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

Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used

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

'Left Outer Merge Join' Failing To Join Valid Row

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

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

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

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

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

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

View 2 Replies View Related

Select Command - Left Join Versus Inner Join

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

Merge Join (Full Outer Join) Never Finishes.

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

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

Jul 25, 2007

I'm using SQL Server 2005.



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



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

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



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



So, this works:

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



and this works:

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



But this does't:

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



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



Thanks,

john















View 1 Replies View Related

Page 2 - Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?

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

Changing From Implicit Join To Explicit Join

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

Right Join Returns Same Results As Left Join

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

How To Join 3 Tables Using Left Or Right Join Keyword?

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

Super Join - Is Merge Join The Answer?

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

How Would You Convert A Hash Join Into A Merge Join?

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

Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?

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

Inner Join To Outer Join Problem

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

SQL Query Question - JOIN Or Not To JOIN

Jan 2, 2006

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

View 3 Replies View Related

Use ANSI Join Or Old Style Join?

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

Performance Between Standard Join And Inner Join

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

Self Join Outer Join Question

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

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

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

SQL-92 Outer Join Vs T-SQL Outer Join (6.5 Or 7.0) - Test Script Included

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

Explain This JOIN Then LET JOIN

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

Join Question - If Then Or Else In A Join?

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

JOIN Question And NOT JOIN

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

Getting Count(*) From Left Side Of Join Using WHERE On Right Side Of Join

Jul 3, 2006

Hi,I have 2 tables: tblStatements and tblLines (one to many) AnytblStatements record can have many associated records in tblLines.The search criteria is against tblLines (ie tblLines.fldDateofService


Quote:

View 2 Replies View Related

LEFT OUTER JOIN Or RIGHT OUTER JOIN?

Nov 4, 2003

Hello

I've a table with these values:

Cod_Lingua - Des_Lingua
------------------------------
ITA Italian
GER German
ENG English
FRA French

and another table with product/description

ProductID - Cod_Lingua - Description
-------------------------------------------
1 ITA Mia Descrizione
1 ENG My Description

I've this SELECT:

SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description
FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua
WHERE Descrizioni_Lingua.ProductID=1

I get these results:
ITA - Mia Descrizione
ENG - My Description

I don't want this. I'd like to have this:
ITA - Mia Descrizione
ENG - My Description
GER - (null)
FRA - (null)

How can I get the second result set?

Thanks for your support.

View 3 Replies View Related

Left Join Vs Left Outer Join

Apr 7, 2008

Is there any difference between left join and left outer join in sql server 2000?please reply with example if any?
Thanks in advance

View 13 Replies View Related

Left Join Vs Left Outer Join

May 14, 2008

Hi,

Whats the diference between a left join and a left outer Join

View 5 Replies View Related







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