0

I have two queries that work but I want the 2nd query to be done based on the results from the first query and join all results together.

First query was 

select c.contribution_amount, c.contribution_dt,
c.contribution_descr,c.report_info_id,
p.name_last,p.name_first,p.name_organization,
p.employer,p.occupation,p.job_title,
a.city, a.state_cd, a.postal_code,
r.report_info_ident, r.filer_info_id
from contribution_info c
inner join contribution_persent cp on  c.contribution_info_id=cp.contribution_info_id
inner join persent_info p on cp.persent_info_id=p.persent_info_id
inner join persent_address pa on p.persent_info_id=pa.persent_info_id
inner join address_info a on pa.address_info_id=a.address_info_id
inner join report_info r on c.report_info_id=r.report_info_id
where lower(p.name_last) like '%somebody%' or lower(p.name_organization) like '%somebody%'

and the second query was 

select f1.filer_ident, fp1.filer_persent_kind_cd,
p1.name_last, p1.name_first,p1.name_organization, p1.persent_type_cd
from filer_info f1 
inner join filer_persent fp1 on f1.filer_info_id=fp1.filer_info_id
inner join persent_info p1 on fp1.persent_info_id=p1.persent_info_id
where f1.filer_info_id=000000000
and fp1.filer_persent_kind_cd='FILER'

the second query's 00000000 needs to be equal to the first query's c.report_info_id (also represented as r.report_info_id)

and the row result needs to be a combination of all the r, p, c, a, r1, fp1 results

jpw
  • 44,361
  • 6
  • 66
  • 86
th3louvre
  • 55
  • 1
  • 7
  • Are you using a case-sensitive collation? Either way, I thought LIKE ignored case. ?!?! And what's a 00000000? – Strawberry Aug 13 '15 at 16:13

2 Answers2

1

Seems to me you're after simply joining the two result sets together... This can be accomplished by aliasing both queries as inline views and then joining them on the necessary criteria...on A.report_info_ID = B.filer_info_id based on your question phrasing...

Select * 
from (
select c.contribution_amount, c.contribution_dt,
c.contribution_descr,c.report_info_id,
p.name_last,p.name_first,p.name_organization,
p.employer,p.occupation,p.job_title,
a.city, a.state_cd, a.postal_code,
r.report_info_ident, r.filer_info_id
from contribution_info c
inner join contribution_persent cp on  c.contribution_info_id=cp.contribution_info_id
inner join persent_info p on cp.persent_info_id=p.persent_info_id
inner join persent_address pa on p.persent_info_id=pa.persent_info_id
inner join address_info a on pa.address_info_id=a.address_info_id
inner join report_info r on c.report_info_id=r.report_info_id
where lower(p.name_last) like '%somebody%' or lower(p.name_organization) like '%somebody%') A
INNER JOIN (
select f1.filer_ident, fp1.filer_persent_kind_cd,
p1.name_last, p1.name_first,p1.name_organization, p1.persent_type_cd
from filer_info f1 
inner join filer_persent fp1 on f1.filer_info_id=fp1.filer_info_id
inner join persent_info p1 on fp1.persent_info_id=p1.persent_info_id
where and fp1.filer_persent_kind_cd='FILER') B
 on A.report_info_ID = B.filer_info_id
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • There may be better approaches from a performance standpoint but without heavy thought this is the simplest extension of what you already had. – xQbert Aug 13 '15 at 18:34
1

I would tell you to use "WITH" clause, but MySQL doesn't support it, but still you can handle your query like "TABLES" or "VIEWS". You can read and see example here, how to do it with MySQL: MySQL "WITH" clause

In your case it would be like this:

select * from 
(select c.contribution_amount, c.contribution_dt,
c.contribution_descr,c.report_info_id,
p.name_last,p.name_first,p.name_organization,
p.employer,p.occupation,p.job_title,
a.city, a.state_cd, a.postal_code,
r.report_info_ident, r.filer_info_id
from contribution_info c
inner join contribution_persent cp on     c.contribution_info_id=cp.contribution_info_id
inner join persent_info p on cp.persent_info_id=p.persent_info_id
inner join persent_address pa on p.persent_info_id=pa.persent_info_id
inner join address_info a on pa.address_info_id=a.address_info_id
inner join report_info r on c.report_info_id=r.report_info_id
where lower(p.name_last) like '%somebody%' or lower(p.name_organization) like '%somebody%') a
inner join
(select f1.filer_ident, fp1.filer_persent_kind_cd,
p1.name_last, p1.name_first,p1.name_organization, p1.persent_type_cd
from filer_info f1 
inner join filer_persent fp1 on f1.filer_info_id=fp1.filer_info_id
inner join persent_info p1 on fp1.persent_info_id=p1.persent_info_id
where fp1.filer_persent_kind_cd='FILER') b
on b.filer_info_id = a.report_info_id 
where b.filer_persent_kind_cd='FILER'

I am not sure if you want only the first value of c.report_info_id, but if you are, you can easily use this condition to the WHERE clause:

and a.report_info_id in (SELECT TOP 1 report_info_id from contribution_info)
Community
  • 1
  • 1
user5224389
  • 81
  • 4