Problem With Left Join Table

May 7, 2008

View 7 Replies

Why Is Left Table In LEFT JOIN Limited By Where Clause On Right Table

Jan 25, 2015

-- Why is the left table in a LEFT JOIN limited by the where clause on the right table?eg

DECLARE @LeftTable TABLE (LeftID INT NOT NULL IDENTITY(1, 1), LeftValue INT NULL)
INSERT @LeftTable (LeftValue)
VALUES (111)
INSERT @LeftTable (LeftValue)
VALUES (222)

[code]....

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

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

Left Join Table

Jul 18, 2006

Now I have a table with the table design as following :

table cst_EmpProfile
intEmpId nvcEmpName nvcEmpAddress intEmployeeType bitActiv
1 Peter Null Null true
2 Juli 1, xxxx 2 true
3 Sam Null Null False

table cst_EmpType
intEmpType nvcEmployeeType
1 Free Enginner
2 Manager
3 Operator

To join the table but MUST follow the condition as bitActiv = TRUE:
select emp.nvcEmpName, emp.nvcEmpAddress, ety. nvcEmployeeType from cst_EmpProfile emp left join cst_EmpType on emp.intEmployeeTypee = ety.intEmpType and emp.bitActiv = 1.

But, the sql statement doesnt output the my expected result.
Because the data row return must be 1st and 2nd row as it bitActiv = true.
So, how's I going achieve what i want. tq.

View 1 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-

Faculty_Subject.Class_Id=@Class_Id

Then i don't get all subjects from subject table, how this can be achieved.

Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN

[code] ....

View 9 Replies View Related

Keep Order Of Left Table After Join

May 20, 2014

I have an problem with the order of the results after a join.

My first query works fine and the order of field Name ist correct.

Select *
FROM
(SELECT * FROM
dtree A1
WHERE
A1.Subtype=31356
AND
A1.DataID IN
(select DataID from dtreeancestors where AncestorID=9940974)) t

When I do a join the order of the left table changes

Select *
FROM
(SELECT * FROM
dtree A1
WHERE
A1.Subtype=31356
AND
A1.DataID IN
(select DataID from dtreeancestors where AncestorID=9940974)) t, llattrdata A4
WHERE
t.DataID = A4.ID

How can I do a join and keep the order of the left table?

View 4 Replies View Related

Selection At Most 1 Row On 2nd Table In LEFT JOIN

Nov 30, 2006

I have a query that include a single LEFT JOIN. I would like to be able to select at most 1 row of the second table (providing that the JOIN represents a one to many relationship).

Does anyone knows how to do that?
Thanks in advance,
Joannès

View 2 Replies View Related

SQL Server 2014 :: Left Join With A Large Partitioned Table?

Aug 3, 2015

I have a query that has a left join with a large partitioned table. The partitioned table has 10s of millions of records, and each partition has about 100,000 records.

The left join is part of an insert that gets a column from the partitioned table, if the column exists. The query contains the partition ID and all other joined columns are part of a non-clustered index.

Through the profiler, I found that there were millions of reads and the execution plan was giving me a table scan on the partitioned table.

I changed the query to do the insert followed by an update with inner join. That did the trick, but it worries me that SQL Server 2014 behaves differently from 2012 or 2008R2, which can make upgrading very time consuming.

View 3 Replies View Related

Transact SQL :: Left Join To Get Info From A Table With Foreign Keys

Sep 13, 2015

I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.

I have a table called Patient. It's primary key is pat_id.

I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.

I have a third table called Coverage_History.   It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.

I want to get the pat_id and all the coverage information that is current.   The coverage table contains specific insurance policy information.   The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:

Patient (pat_id and lname)
P123 Monto
P124 Minto
P125 Dento
P126 Donto

Coverage (pat_id, coverage_plan_id, hosp_status, policy_num)
P123     MED1   OP   A1499
P123     ACT4   OP   H39B
P124     MED1   OP   C90009
P124     RAC    OP   99KKKK
P124     RAC    OP   99KKKK
P124     MED1   OP   C90009
P125     ARP    OP   G190
P126     BCB    OP   H88

Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to)
P123   OP   1   20150102 MED1    NULL
P123   OP   2   20150102 ACT4    NULL
P124   OP   1   20150203 RAC     20150430
P124   OP   2   20150203 MED1    20150430
P124   OP   1   20150501 MED1    NULL
P124   OP   2   20150501 RAC     NULL
P125   OP   1   20150801 ARP     NULL
P126   OP   1   20150801 BCB     20160101

select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority,
       from patient p
     left join
  ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and
                                   (ch.effective_to is NULL or ch.effective_to >= getdate()
)
         ) on ch.patient_id = p.patient_id

    where ( ch.effective_to is NULL or ch.effective_to >= getdate() )

So I want to see:

P123 Monto MED1 OP   20150102    NULL       1
P123 Monto ACT4 OP   20150102    NULL       2
P124 Minto MED1 OP   20150501    NULL       1
P124 Minto RAC   OP   20150501    NULL       2
P125 Dento ARP   OP   20150801    NULL       1
P126 Donto BCB   OP   20150801    20160101    1

View 6 Replies View Related

SQL Server 2014 :: 2 Tables Combined Left Join To Primary Key Of A 3 Third Table

Sep 1, 2014

Looking to improve performance of the following code.

It basically generates future days for each dog. So there is a dog table and a day table with every day.

These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.

All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.

How to make the last query as fast as possible.

IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS

[Code] .....

View 2 Replies View Related

Three Table Join - Display Newest Comments Left On Photos Created By Members

Jul 6, 2014

I am trying to tweak some code which is used to display the newest comments left on photos created by my members.

The existing code is this:

SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC

So the latest comment left was for photo #210879 from user "Cla" (redacted user names). The 2nd newest comment would be for photo #211072 from a member named "mo". pdate is a date field

However for the script I have coded I don't want all of the photo comments to show up. This is because I use access levels based on the type of location (higher levels mean more restricted galleries). I check the access levels as I go through the recordsets.

I use this method to get the top 15 comments:

SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC

Now I have to use two other tables to determine the access level. Since PHOTOCOMMENTS is just a list of photo #'s and the people who left comments for those photos, I need to:

a) determine what location the photo is from and
b) determine the access level of that location

I use: select creator,access from locations where id=(select dir from photos where id="&pnumber&")"

This is a two step process as you can see. The first part is:

select dir from photos where id=(pnumber)

ID is the same value as pnumber seen in PHOTOCOMMENTS. That is to say PHOTOS.ID = PHOTOCOMMENTS.PNUMBER

If I haven't confused you yet, the executed code for the first example would be:

select dir from photos where id=210879

which would get me a value for DIR. DIR is the location number which would be:

select creator,access from locations where id=(dir value)

Just to simplify it a bit....

There are three tables (shown below)

PHOTOCOMMENTS
PHOTOS
LOCATIONS

I need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)

but then also

select creator,access from locations (The last table shown)
where id=(select dir from photos where id="&pnumber&")"

So the first table PHOTOCOMMENTS has to also join PHOTOS table where PHOTOS.DIR = PHOTOCOMMENTS.PNUMBER in order to get the value of "DIR" and then DIR is joined to the LOCATIONS tables where PHOTOS.DIR = LOCATIONS.ID

Here is the actual code, which I am trying to make into a single SQL command

strSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC"
set ors = oconn.Execute(strSQL)
tl = 0
do until ors.eof or tl > 15
' until we have 15 results because not every recordset will be of the proper security level

[Code] ....

Bonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2

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

SSMS Express: Creating Parent-Child Table Via LEFT OUTER JOIN - Error Message 156

May 22, 2006

Hi all,

I got an error message 156, when I executed the following code:

////--SQLQueryParent&Child.sql---////////

Use newDB

GO

----Creating dbo.Person as a Parent Table----

CREATE TABLE dbo.Person

(PersonID int PRIMARY KEY NOT NULL,

FirstName varchar(25) NOT NULL,

LastName varchar(25) NOT NULL,

City varchar(25) NOT NULL,

State varchar(25) NOT NULL,

Phone varchar(25) NOT NULL)

INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)

SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567"

UNION ALL

SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333"

UNION ALL

SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555"

GO

----Creating dbo.Book as a Child table----

CREATE TABLE dbo.Book

(BookID int PRIMARY KEY NOT NULL,

BookTitle varchar(25) NOT NULL,

AuthorID int FOREIGN KEY NOT NULL)

INSERT dbo.Book (BookID, BookTitle, AuthorID)

SELECT 1, "How to Chop a Cherry Tree", 1

UNION ALL

SELECT 2, "Valley Forge Snow Angels", 1

UNION ALL

SELECT 3, "Marsha and ME", 1

UNION ALL

SELECT 4, "Summer Job Surveying Viginia", 1

UNION ALL

SELECT 5, "Log Chopping in Illinois", 2

UNION ALL

SELECT 6, "Registry of Visitors to the White House", 2

UNION ALL

SELECT 7, "My Favorite Inventions", 3

UNION ALL

SELECT 8, "More Favorite Inventions", 3

UNION ALL

SELECT 9, "Inventions for Which the World is Not Ready", 3

UNION ALL

SELECT 10, "The Path to the White House", 2

UNION ALL

SELECT 11, "Why I Do not Believe in Polls", 2

UNION ALL

SELECT 12, "Doing the Right Thing is Hard", 2

GO

---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table

SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID

GO

////---Results---//////

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'NOT'.

////////////////////////////////////////////////////

(1) Where did I do wrong and cause the Error Message 156?

(2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'NOT'.

Can I get a Parent-Child table after the error 156 is resolved?

Please help and advise.

Thanks,

Scott Chang

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

'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

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

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

SQL LEFT JOIN Help

Jan 26, 2007

I'm trying to join 2 tables. I thought I was getting the correct results but it turns out I'm not.
My Query:
SELECT IVINVA, IVORDN, IVCSLN, IVRESR, IVCITM, CONVERT(varchar(12),CAST(IVIAMT as money),1) AS ExtPrice, CONVERT(varchar(12),CAST(IVPIVC as money),1) AS DistPrice, IVCSUM, IVQYCS, IVDESC, OIRESR, OIDPCT, CONVERT(varchar(12),CAST(IVPIVC - (OIDPCT / 100 * IVPIVC) as money),1) AS NetPrice FROM INVDET1_TBL LEFT JOIN ORDDIS_TBL ON ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN AND ORDDIS_TBL.OIRESR = INVDET1_TBL.IVRESR WHERE IVORDN = '0859919' AND IVINVA = '00324024'
Basically, my problem lies in the seonc condition of the LEFT JOIN. I needed to set the two tables equal my item number, because in some situations I need that logic to get the correct result. It most other cases, that item column in the ORDDIS_TBL is NULL, thus giving me the wrong results. In that case, I would want the JOIN to only be ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN, and not include the second part. Is there a way I can condition this with an If statement, If ORDDIS_TBL.OIRESR is Null then do this join, if not, then do this? I'm confused how to get the proper result here.

View 3 Replies View Related

Left Join Help

Jul 7, 2005

I need to make a left join from the freezefile f, to sped s, instead of having f.studentid = s.id in the where clause. Any help??

select f.studentid, f.studentname, f.sex, fs.mealstatus, s.except, s.lre, r.description, g.testid, g.scale_la, g.scale_ma, t.test_name, t.year
from freezefile f, fsapps fs, sped s, regtb_exception r, gqe_scores g, test_info t
where
f.type = 'ADM'
and
s.except = r.code
and
t.test_name = 'ISTEP'
and
t.year = 2004
and
g.testid = t.testid
and
f.studentid = fs.id
and
f.studentid = s.id
and
f.studentid = g.studentid

View 1 Replies View Related

Left/right Join

Aug 2, 2004

i'm shure it's some smal stiupid mistake bat I can't find it, PLZ help.

1)
select komorka from #plantemp
--result

komorka

09
10
I-P
II-P
III-P
SI/1
SI/2
SI/3

2)
select komorka,ustalenia from analiza_1 a where a.koniec between '20040701'and '20040731'

komorka ustalenia
SI/1788138.9300
SI/246638.4900
SI/216218.4000
08.0000

3)
select p.komorka,isnull(sum(ustalenia),0)
from #plantemp p left join analiza_1 a on p.komorka=a.komorka
where a.koniec between '20040701'and '20040731'
group by p.komorka

komorka ustalenia (sum)

08.0000
SI/1788138.9300
SI/262856.8900

I need all rows from table 1 bat right and left join gives me the same results, WHY

View 3 Replies View Related

Left Join

Jan 9, 2007

There are two tables:
tblIndices:
IndexID, Name
1index1
2index2
3index3
.
.
.

tblBasketConstituents
ID, ParentIndexID, ChildIndexID, Weight
121 20
223 80
313 50
412 50

As you can see the ParentIndexID and ChildIndexID fields refer to tblIndices.IndexID
I would like a stored procedure as follows:
show all index names and show the wights for the indexID you passed.
This is what I have so far and it is not correct yet. Not sure what the syntax should be.

alter PROCEDURE [dbo].[uspBasketIndices_Get]

@IndexIDint

AS

select
i.IndexID,
i.[Name],
bc.Weight
from
tblIndices as i left join tblBasketConstituents as bc on i.IndexID = bc.ParentIndexID
and i.IndexID = @IndexID
order by
i.[Name]

View 1 Replies View Related

How Many 'LEFT JOIN' Is Too Many?

Feb 26, 2004

Any one know any facts and figures about maximum Left Joins allowed (or recommended) in one query?

I am running a MS SQL 2000 my database is full of relational data and most of my foreign keys (INT data type) are a Clustered Indexed, Usually I will only be pulling one record from collection of about a dozen tables, but the Database is expected to grow fast and become big.

Right now I have a Stored Proc that has eight(8) LEFT JOINs in it. My worry is that this query will kill me as the database approaches 50,000 records.

Lito

View 6 Replies View Related

Left Join

Mar 6, 2007

Hi all. My query works fine, it generates reports but not my expected result.

select d.fullname, p.nickname, p.birthdate, p.birthplace,
p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height,
p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken,
d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() ),
c.name, c.address, c.telno, c.email, c.occupation, ed.year1, ed.year2, ed.degree, sch.schname
from hremployees as e
inner join psdatacenter as d on e.empdcno = d.dcno
inner join pspersonaldata as p on e.empdcno = p.dcno
left join hrappempcharrefs as c on e.empdcno = c.empdcno
left join hrappempeducs as ed on e.empdcno = ed.empdcno
left join hrsetschools as sch on ed.schoolcode = sch.schcode

the above query gives a 77 records

if i ran "select * from hremployees" generates 60 records

i think the error is in the left joining.
hrappempcharrefs, hrappempeducs and hrsetschools must be left joined to hremployees.

thanks
-Ron-

View 5 Replies View Related

Left Join Using Vb 9.0 And LINQ

Dec 2, 2007

Hi Guys,
I started working with linq and vb9.0 but i have a small problem i could feagure how to solve in c# but not in vb
I wanted to make left join or right join on vb 9.0 and linq is it possible or this is only c# feature ?
Waiting to hear from u guys,
Thanks
Softy

View 2 Replies View Related

Left Outer Join-please Help!

Jan 7, 2002

Hello!
I need to write a query using left outer join and I'm having trouble with it.
I have 2 tables:customer and cust_info.
I want to pull all records from customer table and cust_info table even if there is no related data in cust_info table.
But I need one condition in this query:only records from customer table where cus_type in ("A","B","C").
I don't need all other types ("D","E").

So my query looks like this:

select customer.cus_name,customer.cus_address,customer.cu s_type,cus_info.status
from customer
left outer join cus_info ON customer.sxdat_pk = cus_info.sxdat_pk
and cus_type in ("A","B","C")
AND cus_info.cus_table = 'CUSTOMER'

The result should be like this:

cus_name cus_address cus_type status
Amoco 457 bent A new
Bingo 47 lone oak C NULL
Sears 1020 Magic dr. B exist

But my query pulls records for customers with type "D" and "E" that I'm trying to exclude from result.

Please help.
Thanks,
lena

View 4 Replies View Related

LEFT JOIN Problem

Apr 18, 2008

Hello everybody!!!

I have to left join 2 tables. The first consists of columns:id, description, descr_num.
The second table: id, descr_num, value.

I need to extract description from table1 where descr_num is in the range , say, 1-10.

LEFT JOIN

extract value from table2 ,descr_num should be in the same range. in table2 some values from (1,10) and desc_num could be not present.
BUT i want this left join to be limited as i said.

Is there any solution to this prob. without creating temp tables. Or actually can i do such a join?
Thank you

View 2 Replies View Related

Stumped - LEFT JOIN

Jan 10, 2007

Hi, I'm getting the error Syntax error (missing operator) in query expression 'dbPWork.id = dbPWorkWord.work_id LEFT JOIN words ON dbPWorkWord.word_id = words.id'." on the query below. Everything looks in place to me. Do you see anything that shouldn't work?

Query:

Code:

Code:

SELECT DISTINCT dbPWork.id, description_e as description, w_id , dateStart, dateEnd FROM dbPWork
LEFT JOIN dbPWorkW ON dbWork.id = dbPWorkW.work_id
LEFT JOIN words ON dbPWorkW.word_id = words.id

Everything works fine until I add the second LEFT JOIN statement. Any ideas? I'm stumped.

View 4 Replies View Related

Left Join Question

Sep 22, 2005

I have a left join that doesn't suit my request.

select * from A
left join B
on A.x=B.x

The problem is that for a specific record in A we can find 1 or more entries in B. What I want is to return only the 1st entry found on B.
thx

View 3 Replies View Related

Difference Between LEFT N RIGHT JOIN

Dec 6, 2005

Hi,
I have got a query for you guys. Can any one temme the difference between

"FROM Table_A LEFT JOIN Table_B" and "FROM Table_B RIGHT JOIN Table_A"

;)
Thanks,
Rahul Jha

View 2 Replies View Related

Left Join And Subquery

May 1, 2004

Hi,
Iwant to make a left join between two tables connected with a specific field
but I also want to have some criteria on the right table.
I want to display all the rows from the first(left) table and only the rows from the secod (right) table that match the join and the criteria.
However If I have criteria on the second table the join behaves like an inner join displaying only the rows that exist in both tables.
In MS Access instead of the right table I use a query in which I enter the criteria and then I connect it with a left join in a new query in order to get the right results. How can I do itin MSSQLServer without using Views?
Must I use a subquery with Exists and what is the right syntax in order to achieve this?

Best Regards,
manolis Perrakis

View 1 Replies View Related