What I want is this:

if

there are records in table A that match the company id

then

run a select against table A joined with table B on company id

else

run a select against table A on its own

Is it possible in one single query?

no idea but if you don’t call them ‘rows’ instead of ‘records’ it shows you’re an old school IT bod and grew up on Cobol etc.

One way would be…

UNIONs with where clauses including counts to include or exclude them (i.e. where (select count(*) from….)>0)

Are you trying to return all rows from table a optionally joined to table b?

The syntax may vary depending on database but have a look for how to do a ‘Left Outer Join’

This sounds like a left join to me (see this for a good description)

select a.*, b.*
from a left join b
on a.companyId = b.companyId

Will return all from [a] and everything from where there’s a match

or have i missed something?

for MS SQL

select *
from Table_A A
left outer join Table_B B
on A.CompanyId = B.CompanyId

Oracle:

select *
from tablea,tableb
where tableb.id = tablea.id(+)

Snot a left join.

I’ll explain it better.

There’s two tables A and C, and a link table B between them – many to many relationship.

However the link is not implemented for all companies. So if there are NO rows in the link table matching the company id then I want to select all rows in A *

I think 5E’s idea is a good one.

* not all in fact, there are other criteria but that’s not important here.

Have a look at NOT EXISTS it can work really nicely in this sort of situation. Using aggregates (counts etc) in the criteria can be slow if your tables are big.

Outer join A to B and then outer join the result set to C

case when (company exist in table b) then
begin
query both tables
end
else
begin
query one table
end
end

you might want to check the syntax of the case statement. This is for sql server, though may work on others.

No stored procs allowed. And it’s MySQL.

There’s two tables A and C, and a link table B between them – many to many relationship.

However the link is not implemented for all companies. So if there are NO rows in the link table matching the company id then I want to select all rows in A *

This still sounds like an outer join to me – would something like the following not work?

select
a.*,
c.<column name>
from
a
left outer join
b
on
a.<linky column> = b.<linky column>
left outer join
c
on
b.<linky column> = c.<linky column>

Not outer join, since this would always return all of A wouldn’t it?

The result of a left outer join (or simply left join) for table A and B always contains all records of the “left” table (A)

It’s filtering results for a specific company. However the filter does not exist for all companies so if the filtered query returns no results then I want to show everything.

I’m really confused – you said you wanted to select all rows from a – I think you need to provide some examples.

Or are you saying that you want to filter records from table A for a particular company, in which case you just specify the company ID in the WHERE clause against table a.

select
a.*,
c.<column name>
from
a
left outer join
b
on
a.<linky column> = b.<linky column>
left outer join
c
on
b.<linky column> = c.<linky column>
where
a.company_id = <required company_id>

<ignore> Don’t fully understand question, examples would help.

Most important question:

How many records are you dealing with and how many do you need it to scale to?

All rows in A that don’t have a matching id?

create table matches as
(
select a.id
, case when a.id is null then ‘no_match’ else ‘match’ end matches_col
from
table_a a
, table_b b
where a.id = b.id(+)
group by a.id, matches_b
)

Select a.* from table_a a, matches m
where a.id = m.id
and m.matches_col =’no_match?’

(select a.companyid from tableA
minus
select b.companyid from tableB)
union
(select b.companyid from
tableB)

??????

…jeez. I come on here to get away from the day job!

Ok.

Let’s say A is company, and C is template. It’s a many to many relationship. Companies and templates are both in the low thousands.

Previously, all companies got all templates, there was no link table. Now I am introducing a filter so companies get the templates appropriate to them. However this is only being implemented for some companies at the moment.

So if there are records in the link table with a specified company ID I want only those templates that apply to the company. If there are NO records in the link table with that ID then I want all the templates.

I could write a script to populate the link table with everything for all the companies that aren’t being filtered but that’ll result in millions of rows.

It has to be done every time a user hits a screen, and there are quite a few users. Performance is not critical but I don’t want to be creating temp tables and so on.

However the link is not implemented for all companies. So if there are NO rows in the link table matching the company id then I want to select all rows in A *

This looks relevant.
http://stackoverflow.com/questions/915643/select-where-not-exists

think you need to publish your table structures…

A combination of NOTEXISTS a join and an IF ELSE will sort it for you.

select * from company left outer join filter on ( company.company_id = filter.company_id),template
where company.company_id = template.company_id
and (filter.company_id is null
or filter.template_criteria = template.template_criteria )

?

You may have to do convert the (+) left join for MS

select X.id,
case when x.matches_col = ‘no_match’ then c.template_col
, when x.matches_col = ‘match ‘then d.template_col
else ‘summatandknowt’ end template_col
from company_table c, template_table d
(
select a.id
, case when a.id is null then ‘no_match’ else ‘match’ end matches_col
from
(select distinct id from company_table a
, (select distinct id from template_table b
where a.id = b.id(+)
) X
where x.id = a.id
and x.id = b.id
and c.company_name =’company_name’

So if there are records in the link table with a specified company ID I want only those templates that apply to the company. If there are NO records in the link table with that ID then I want all the templates.

This is somewhat different to what you asked for earlier – you want everything from c if no match is found between a, b and c. Right?

Er yes. That’s what I was attempting to ask for earlier.

Can’t use if then else or case, afaik. No stored procs/functions allowed.

If there are NO records in the link table with that ID then I want all the templates.

This bit does a cartesian join to get every template for each company not appearing in the link tableB.

(SELECT c.template_id, a.company_id
FROM tableA a, tableC c
WHERE NOT EXISTS
(SELECT 1
FROM tableB b
WHERE b.company_id = a.company_id)
)

if there are records in the link table with a specified company ID I want only those templates that apply to the company

UNION ALL
(SELECT b.template_id, b.company_id
FROM tableB)

join the statements together.
if you don’t publish table structures it is near on impossible to get an exact and precise answer.

FFS is this not fixed yet – Mol, get a bluddy grip. How much are they paying for you to be able to do this??

if you don’t publish table structures it is near on impossible to get an exact and precise answer

I don’t want the exact SQL to cut and paste. And you’ll appreciate that I can’t post details of my clients systems online.

That is what I was thinking of based on 5E’s post.

Geoff, I haven’t been working on it all day 🙂

Why do you have to do it in one go?
If you did the lookup in the join table first, you could then do the other bits as required.

Doing it in a oner sounds like more trouble than its worth.

but ur not even posting details of primary and foreign keys belonging to which tables – that’s the bare minimum required.

Doing it in a oner sounds like more trouble than its worth.

Believe me, it’s not.

Big stupid long method with loads of rambling code fannying about with the PreparedStatement and the result set, all of which would have to be duplicated if I did more than one query, making the method even longer and harder to read. This way all I have to do is change the query and blam.

Company id and template id are the keys. I know SQL well enough to work with what I now have ta 🙂

What about something like this – may need to do a group by to incorporate the count on the second part of the union:

–gives you matching records

select
c.*
from
a
inner join
b
on
a.company_id = b.company_id
inner join
c
on
b.template_id = c.template_id
where
a.company_id = <required company_id>

–joins the results of c where there is no link to company ID in a via b

union select
c.*
from c
left outer join
b
on
c.template_id = b.template_id
left outer join
a
on
b.company_id = a.company_id
and
b.company_id = <required company id>
having
count(a.company_id) = 0

The gist of it is to do a union of two selects including mutually exclusive conditions. Et voila, instant if-then-else in a single query.

I think that’s what I was trying to do above… not sure if it’s right, but I think its on the right track

More generic version of my query above:-

SELECT <whatever you need>
FROM
template, company LEFT OUTER JOIN filter ON
( company.company_id = filter.company_id )
WHERE company.company_id = template.company_id
AND (filter.company_id is null
OR filter.<criteria> = <whatever your filter criteria are> )

Big stupid long method with loads of rambling code fannying about with the PreparedStatement and the result set, all of which would have to be duplicated if I did more than one query, making the method even longer and harder to read. This way all I have to do is change the query and blam.

Int that what OO programming is supposed to knock on the head? 👿

It is, geoff, yes. If you do it right. Which many people don’t 🙂