Query two tables to get results without a JOIN

Question

question

Noonies asked Jan 5, '11

Query two tables to get results without a JOIN

Here I am again to pick everyones brain: I have the following query - SELECT CONVERT(date, o.shippeddate, 102) as Date , customers.shipadr1 as Customer, customers.ShipCity As City, customers.ShipState AS State, customers.ShipZip As [Zip Code], o.TotalGrosslbs AS Weight , o.PackNote FROM (( Orders o INNER JOIN Customers "Customers" ON o."ToCusNid"="Customers"."CusNid") INNER JOIN OrderLines ol ON ol.OrderTicketNumber = o.TicketNumber ) WHERE CONVERT(Date, o.ShippedDate, 102) >= '2010-07-01' AND CONVERT(Date, o.ShippedDate, 102) query joins

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-01-05T11:20:36Z

Tim answered Jan 5, '11

You should still be able to use the join, just use an outer join.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Noonies commented · Jan 05, 2011 at 11:27 AM

Thank you Trad! :) I'm new so I'm used to constantly using INNER JOIN and neglect to look at outer join. Thanks again. This worked!

0 ·

Tim commented · Jan 05, 2011 at 11:36 AM

Thanks, glad to help out. Do you mind marking my response as the correct answer. Simple click the check box to mark it.

0 ·

Answer 2 · 2011-01-05T11:25:38Z

Grant Fritchey answered Jan 5, '11

You should use an OUTER JOIN in the query to join to the table in question, and then use an ISNULL statement in the SELECT list to put in $0 when the value is NULL. That should do everything you need.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question