TSQL query

For a TSQL statement

select [fields] from A
inner jon B on a.field1 = b.field1
right join c on c.field1 = b.field1
and c.field2 = "Yes"

As the TSQL using right join c, will the final result contain all records (ie c.field2 = "Yes" or "No) from table c ?
AXISHKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,
you need to use WHERE

select [fields] from A
inner jon B on a.field1 = b.field1
right join c on c.field1 = b.field1
WHERE c.field2 = "Yes"
AXISHKAuthor Commented:
This is the original statement. Should it return all records for ORDER d even though d.ISINVOICE='Yes' as it uses right join here ? Tks

select  d.*,InvoiceId,a.PreInvoiceDate into HUA_UE120RECAPTOexcel from DynamicsAX_PRO.dbo.WMSShipment a
 inner join  DynamicsAX_PRO.dbo.WMSOrderTrans b on a.SHIPMENTID =b.SHIPMENTID
 inner join DynamicsAX_PRO.dbo.INVENTDIM c on b.INVENTDIMID=c.INVENTDIMID
 right join ORDER d on c.INVENTBATCHID=d.[Lot No] and d.ISINVOICE='Yes' and a.INVOICEID<>'' and c.INVENTBATCHID<>''  order by d.OrderbyId,d.PO,d.PD#
Lokesh B RDeveloperCommented:
Yes,

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match

http://www.w3schools.com/sql/sql_join_right.asp
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

PortletPaulEE Topic AdvisorCommented:
when in doubt run a test.

This result:
|  AFLD1 |  BFLD1 | FIELD1 | FIELD2 |
|--------|--------|--------|--------|
| (null) | (null) |      1 |     No |
|      2 |      2 |      2 |    Yes |
|      3 |      3 |      3 |    Yes |
| (null) | (null) |      4 |     No |

Open in new window


From this data:
CREATE TABLE c
	([field1] int, [field2] varchar(3))
;
	
INSERT INTO c
	([field1], [field2])
VALUES
	(1, 'No'),
	(2, 'Yes'),
	(3, 'Yes'),
	(4, 'No')
;



CREATE TABLE b
	([field1] int)
;
	
INSERT INTO b
	([field1])
VALUES
	(1),
	(2),
	(3)
;

CREATE TABLE a
	([field1] int)
;
	
INSERT INTO a
	([field1])
VALUES
	(1),
	(2),
	(3),
	(4),
	(5)
;

Open in new window


Using the original query, slightly modified:
select a.field1 afld1, b.field1 bfld1, c.field1, c.field2
from A 
inner join B on a.field1 = b.field1
right join c on c.field1 = b.field1 and c.field2 = 'Yes'
;

Open in new window

AXISHKAuthor Commented:
Thanks PortletPaul.

So, is it meaningless to add criteria and d.ISINVOICE='Yes' , correcdt ?
PortletPaulEE Topic AdvisorCommented:
Don't use BOTH right and left joins in a query, that's very messy and extremely hard to maintain.

I don't know your data or your requirements, but if I had to guess I would say that your "from table" should be D

I would re-think the query, get rid of the right join. Perhaps this will help
SELECT
    d.*
  , InvoiceId
  , a.PreInvoiceDate
INTO HUA_UE120RECAPTOexcel
FROM [ORDER] d
INNER JOIN DynamicsAX_PRO.dbo.INVENTDIM c  ON d.[Lot No] = c.INVENTBATCHID
INNER JOIN DynamicsAX_PRO.dbo.WMSOrderTrans b ON c.INVENTDIMID = b.INVENTDIMID
INNER JOIN DynamicsAX_PRO.dbo.WMSShipment a ON b.SHIPMENTID = a.SHIPMENTID
WHERE d.ISINVOICE = 'Yes'
    AND a.INVOICEID <> ''
    AND c.INVENTBATCHID <> ''
ORDER BY
    d.OrderbyId
  , d.PO
  , d.PD#

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.