Simple JOIN Question

Mar 28, 2007

I feel like this is an easy question, but I can't describe it well enough to find the answer I need by searching. Anyway, in my spare time (I'm definitely not a SQL Query pro) I'm putting together a small app for our local Little League to help with scheduling.

I have 2 tables I need to join:

T1 = Schedule
has the following fields:
ID
hTeamID (ID of Home team)
vTeamID (ID of Vistor team)
Time
Date

T2 = Teams
ID
Name
Other info...

I want to be able to do a SELECT statement on the schedule table and JOIN the team names for both home and visitor. I've tried a bunch of different ways but keep getting errors. I can think of 100's of reasons to join the same table more than once, but I still don't know how to and can't find the answer online.

Thanks in advance.

View 2 Replies


ADVERTISEMENT

Simple Join Not So Simple

Feb 21, 2007

I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.

result set

colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e

So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.

Any help is appreciated.

View 7 Replies View Related

Simple Join Not So Simple

Aug 19, 2006

Hi,

I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)

I have:


Master table Hotel

table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)


table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')


foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)

however not all hotels have thumbnail image

hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media


the problem is that if there is no entry in Media or mediaXref, I don't get any results

i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned


any clue?

thanks

View 5 Replies View Related

Help With Simple Join?

Sep 3, 2004

I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:

I have 3 tables I want to join:

Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250

Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB

ShipTo Table:

Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston


A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.

I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).

What I came up with is this, but is this correct?:


FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no

View 12 Replies View Related

JOIN Simple Problem

Sep 20, 2005

Hi Guys,

I have the following table called VMailMessages:


PHP Code:




 MessageNum  MailboxNum  State
========================
1                 100             1
2                 101             1
3                 101             1
4                 102             0 






Which is of messages in a mailbox system, the MessageNum is my primary key, MailboxNum indicates which mailbox it is for and State indicates whether it is 'New' (value = 1) or 'Saved' (value = 0).

What I want to do is write a query to obtain a list of mailboxes, along with how many New and how many Saved messages they have, producing a result table like this:


PHP Code:




 MailboxNum  NewCount  SavedCount
===========================
100             1             0
101             2             0
102             0             1 






My problem is I cannot seem to get my SQL right, so far I've got:


PHP Code:




 SELECT NewQuery.MailboxNum, NewQuery.NewCount, SavedQuery.SavedCount 
FROM (SELECT MailboxNum, COUNT(1) AS NewCount
          FROM VMailMessages
          WHERE (State = '1')
          GROUP BY MailboxNum) NewQuery 
FULL OUTER JOIN
         (SELECT MailboxNum, COUNT(1) AS SavedCount
           FROM VMailMessages
           WHERE (State = '0')
           GROUP BY MailboxNum) SavedQuery 
ON NewQuery.MailboxNum = SavedQuery.MailboxNum 






WHich works only if the mailbox has New messages as well as Saved messages. For mailboxes with only Saved messages, the count appears but, the MailboxNum is NULL. The opposite occurs if I change my SELECT clause to ask for SavedQuery.MailboxNum, but I really want both.

Can anyone help me?

Thanks

Richard

View 1 Replies View Related

T-SQL (SS2K8) :: Simple Self Join

Aug 6, 2014

I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating

View 1 Replies View Related

Best Way To Do A Simple Join Query

Feb 28, 2007

ive seen so many ways to do this, including some using cursors (strange i know)

but i have tableA and tableB i want to show fields from tableA which don't apear in tableB

what is the MOST efficient way to do this

View 2 Replies View Related

Simple Query, INNER Join Problem

Sep 14, 2006

In a single table I have 2 columns. Date | Number2006/09/01 - 2352006/09/03   - 2452009/08/01 - 230 I want to write a query that will return the AVG number between two dates.  I am assuming this will require a JOIN but I'm having problems implementing my solution.  I think about it, it's probably not a join but a sub query...I was trying the following: SELECT Date, AVG(Number) as AVERAGE_NUMBER
FROM test.Table
WHERE ( Date>='09/01/2006' AND DATE<='09/04/2006' )  But I keep getting date is invalid in the select list because it is not contained in either an aggregate funtion or a group by clause.  Thanks in advance for your help. 

View 2 Replies View Related

Join In A View - Simple Question

Jan 19, 1999

I've got a simple ( I think) question on views. I've got a view that has a table join in it.
With this view, we want to be able to perform updates, inserts, and deletes. At this time
we can do the updates and inserts, but not deletes. I've checked the permissions and
the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are
deletes just not possible in a view with a join?

CREATE VIEW update_bd_view
AS select
D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID
FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID
GO

thank you for your time!
Toni Eibner

View 1 Replies View Related

Simple Question Regarding Outer Join (was SQL Help)

Sep 4, 2006

Hi Everyone,

I have a simple question regarding outer join.

Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.

View 3 Replies View Related

Simple Outer Join Question

May 31, 2007

Ok here is the situation. I have 2 tables.

Movies
MovieID Title Year
21 A Beautiful Mind 2002
22 Forrest Gump 1994
23 The English Patient 1999


Actors
ActorID MovieID Name
1 22 Tom Hanks
2 21 Russell Crowe
3 23 Ralph Fiennes
4 NULL Nachiket Mehta

Here is the SQL Query.

SELECT ActorID, Name, Title, Year FROM Actors LEFT OUTER JOIN Movies ON Actors.MovieID = Movies.MovieID

Now, I only want to show movies made in 1990's and display all 4 actors. If I put

WHERE Year < 2000

it won't show the fourth actor because he doesn't have any movies. I need to show all 4 actors here and NULL for movies if they don't have any.

Hope this makes sense. Thank you.



Nachiket

View 3 Replies View Related

Simple JOIN, INTERSECT Query

Apr 25, 2006

Hi,We are in the process of buying a new server to run mssql. Howeverbefore this as a tempory fix to using a msaccess backend i believethrough odbc i need to address the following issue:SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai, leads l, recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign ANDlast_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#ORDER BY ai.agent_login, ai.entry_dateI want to make the recordings entry optional so the same results comeout whether it matches a recording or not. If it does i want it topopulate the AgreementRecordingFile column above, if not just put a ''as you would with '' as AgreementRecordFile.Does anyone know how you can do this, in a access based database systemusing SQL through i believe ODBC?ThanksDavid

View 1 Replies View Related

Simple OUTER JOIN (I Thought)

Sep 11, 2007

Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...

View 2 Replies View Related

Strange Result From A Simple JOIN

Sep 18, 2007



I am currently studying Transact SQL and playing around with queries from a sample database. Recently I created the following query.


USE MemtrackSQL

SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth

FROM tblMember m1 JOIN tblMember m2

ON m1.FirstName = m2.FirstName

WHERE m1.MemberID <> m2.MemberID


This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID


2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
5 O'Grady Patrick 1975-09-23 00:00:00.000
7 Greenfield Lynne 1955-07-26 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000


Any help in explaining where I have gone wrong here would be greatly appreciated.

Cheers

View 3 Replies View Related

Error With A Simple JOIN Query....

Apr 12, 2007

Hi!



I've a big problem by using the following query :






Code Snippet

public SqlCeResultSet selectRSQuery(String query)

{

SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

return resultSet;

}



SqlCeResultSet resultSet = sgb.selectRSQuery(

"SELECT p.pId, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +

"WHERE g.gId = pg.tpGroupId " +

"AND p.pId = pg.tpProfileId " +

"AND g.gProfileID = '" + app.Settings.Default.id + "'");



It return me this error :

Cannot generate an updatable cursor for the query because there is a non-standard join expression.



What can I do??



Thxx

View 9 Replies View Related

Deceptively Simple Join / Select Question

Mar 24, 2004

Ok, I have two tables with a child/parent or one -> many relationship:

parent_table:
pid int primary key
pname varchar

child_table:
cid int primary key
pid int
cname varchar

Say the contents of these two tables are:

parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael

child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3

Now what I would like to be able to do is:

select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid

Except! Instead of getting the results in the form of:

Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...

I would like them in

Ben ben_Child1 ben_Child2

Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?

View 6 Replies View Related

Simple Group By And Count With Join Not Matching Between Sql Server And Sql CE

Apr 2, 2007

In Sql Server




Code Snippet

CREATE TABLE t_contact

(

Id uniqueidentifier,

FirstName nvarchar(50),

LastName nvarchar(50),

TaskId uniqueidentifier

)

GO

CREATE TABLE t_task

(

Id uniqueidentifier,

Start datetime

)

GO



INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');

INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')

INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')

GO

SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start

GO





Result (Expected)

2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1



Result In Sql CE (UnExpected)

2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3



Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.



Thanks Adam

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

Simple Simple Linking Tables & Perform Calculation

Mar 22, 2007

found it

View 3 Replies View Related

Simple Question (Hope Simple Answer Too)

May 26, 2004

Hey,

I have MS SQL database.
I have procedure:



code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId

begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------


I got error when using variable with tables names.
How to do this?

Ps. Number is send to this function and it must drop table with name Reg_[That number]

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







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