question

Noonies avatar image
Noonies asked

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) queryjoins
10 |1200

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

Tim avatar image
Tim answered
You should still be able to use the join, just use an outer join.
2 comments
10 |1200

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

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 Likes 0 ·
Thanks, glad to help out. Do you mind marking my response as the correct answer. Simple click the check box to mark it.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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