Two Select Statments 1

[dvannoy]

I have a simple innerjoin as shown below

SELECT

MyTable1.Field1
MyTable2.Field1


FROM   
         
MyTable1

INNER JOIN

MyTable2 ON MyTable1.ID = MyTable2.ID

What I'm trying to do check a third table to see if a record was created that relates to any of the innerjoin records.

The results will be shown in a grid so I need all records in the join to show regardless if a record in the third table exists. If a records in the third table exists I would like show a value of '1' in the last column of my grid.

any help would be appreciated

thanks

 

[Olaf Doschke]

To not depend on the existence of the record in the third you can use an outer join.

Since you only want to check existence and not really join data, that speaks for an OUTER APPLY, though, as you can group that by the foreign key, so this APPLY - unlike a join - doesn't become a multiplier.

Setting up some sample data:

Declare @customers as table (id int identity(1,1), customername varchar(30))
Declare @orders as table (id int identity(1,1), customerid int, orderdate date default GetDate())
Declare @orderitems as table (id int identity(1,1), orderid int, product varchar(10), itemsent bit)

insert into @customers values ('Olaf D'),('D van Noy')
insert into @orders values (1, default),(1, default),(2, default)
insert into @orderitems values (1,'A',1),(1,'B',1),(2,'AA',1),(2,'BB',0),(3,'Y',0),(3,'Z',0)

Executing the usual join query showing all order item details:

Select c.customername, o.id as orderid, o.orderdate, i.product, i.itemsent from @customers c
inner join @orders o on o.customerid = c.id
left outer join @orderitems i on i.orderid = o.id

Just listing all orders with status about whether unsent items exist:

Select c.customername, o.id as orderid, o.orderdate, CAST(coalesce(items.unsent,0) as bit) as unsentitemexists from @customers c
inner join @orders o on o.customerid = c.id
outer apply (Select 1 as unsent from @orderitems i where i.orderid = o.id and itemsent=0 group by orderid) as items

Just copy all code into one SSMS query window and execute. The last query is what you need to adapt to your case.

The details in the first query show order 1 is completely processed, order 2 has one item sent, the other unsent and the third order is not at all processed.
The second query shows just one record per order and the overall status whether any unsent items exist.

To relate that to your demand to see whether records exist, that's even simpler seen by [tt]where i.orderid = o.id[/tt] alone, I'm going a step further and check whether any existing orderitem has status itemsent=0, using group by orderid ensures just one joined (or outer applied) record from orderitems. Intead of 1 this could also use Count(*) to count how many items are not yet sent. Orders with all itemsent=1 will have no join and be NULL in any items column. I turn that NULL into 0 with the COALESCE expression.

Bye, Olaf.

 

[dvannoy]

Thank you...