question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil
You didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil
|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.
|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliver yHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDelive ryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL
|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:

>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliver yHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDelive ryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =
CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)