how to join the below queries into a single query in mysql

Hi Experts,
Table names:
BI_service_master:serviceId(p.k), serviceName,Customer_ID,ServiceType,isActive
BI_servicemaster_to_CI:serviceId(p.k),CI_ID(p.k) combination of these two columns it is a p.key ciid may have duplicate.
apid_alarms:d(p.k),operational_state,ci_id

serviceid is the relationship b/w BI_service_master and BI_servicemaster_to_CI
CI_ID is the relation ship b/w BI_servicemaster_to_CI and apid_alarms

how do i join the below queries

SELECT serviceId, serviceName FROM `BI_service_master` `t` WHERE (t.isActive = "1") AND (Customer_ID = '7' AND ServiceType='PROD') ORDER BY serviceName;

 serviceId | serviceName                 |
+-----------+-----------------------------+
|       811 | Biztalk PROD                |
|       776 | CBCA PROD                   |
|       840 | CE10 PROD                   |
|       774 | Citrix PROD                 |
|       831 | CLS Portal PROD             |



select CI_ID from BI_servicemaster_to_CI where serviceId in (811);
+-------+
| CI_ID |
+-------+
| 96044 |
| 96045 |
|


mysql> select operational_state,ci_id from apid_alarms where ci_id in(96044);
+-------------------+--------+
| operational_state | ci_id  |
+-------------------+--------+
|                 0 | 96044 |



i want expected output as:

service id    servicename   operational_state
811            Biztalk PROD     0
834            Finch PROD       2
.....
.....


Thanks
LVL 2
srikoteshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Try this code. I'm guessing the Custimer_ID is in t2. If it is not, please change to whichever table it belongs.

SELECT  t1.service_id,
        t1.servicename,
        t3.operational_state
FROM    BI_service_master t1
INNER JOIN BI_servicemaster_to_CI t2 ON t2.serviceid = t1.service_id
INNER JOIN apid_alarms t3 ON t3.ci_id = t2.CI_ID
WHERE   t1.serviceId in (811)
WHERE   t1.isActive  = "1"
AND     t2.Customer_ID = '7'
AND     t1.ServiceType = 'PROD'
ORDER BY serviceName;

Open in new window

srikoteshAuthor Commented:
that customer id from t1.

now i am getting the result with the above query

first i will execute my first query to get all service ids.
then i will all the above service ids in the above suggested query.

i have one problem.
one serviceid may have multiple ciids . so thats y i have written third query by placing in condition and passing multiple ciids in that  

mysql> select operational_state,ci_id from apid_alarms where ci_id in(96044,96045);
+-------------------+--------+
| operational_state | ci_id  |
+-------------------+--------+
|                 0 | 96044 |


can u verify the above suggested query is the correct for this scenario.
dsackerContract ERP Admin/ConsultantCommented:
You will want to add: AND t3.ci_id IN (96044, 96045)

SELECT  t1.service_id,
        t1.servicename,
        t3.operational_state
FROM    BI_service_master t1
INNER JOIN BI_servicemaster_to_CI t2 ON t2.serviceid = t1.service_id
INNER JOIN apid_alarms t3 ON t3.ci_id = t2.CI_ID
WHERE   t1.serviceId in (811)
WHERE   t1.isActive  = "1"
AND     t1.Customer_ID = '7'
AND     t1.ServiceType = 'PROD'
AND     t3.ci_id IN (96044, 96045)
ORDER BY serviceName;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.