Help with joins and where clause

Transact-SQL
1

Hi Guys,

I hope you can help, I am new to SQL language and I’m learning as I go along. I need to write some SQL queries using joins and where clause, but I keep getting some errors.

I have 3 tables – billing, circuit and service - see below the column names for each table

billing
BILL_ID
circuit_ID
PRODUCTNAME
MONTHLY_RENTAL
PRODUCT_LAST_BILLDATE

Circuit
CIRCUIT_ID
MONTHLY_RENTAL

Service
SERVICE_ID
PROVISIOING_STATUS
CIRCUIT_ID

I want to run a query joining the three tables WHERE the MONTHLY_RENTAL in the billing table is greater than 0 and the provisioning status in the service table is = Dead.

I have written the query like this, but it is not working. Can someone point me to the error I am making, please?

SELECT * FROM billing b
INNER JOIN circuit c
ON b.circuit_ID = c.circuit_ID
INNER JOIN service s ON b. circuit_ID = c.circuit_ID
WHERE MONTHLY_RENTAL b > 0 AND provisioning_status s = Dead

Additionally, What I also want to do is using the same joins, I want to show where the MONTHLY_RENTAL in the billing table is greater than the monthly rental in the circuit table and the provisioning status in the service table is = live

your support is much appreciated

MHersi

2

I would like to mention that I use trial and error much. So please start small with your query and add each time an extra step untill the result is unexpected and try to understand why.

When you join the table service s you don't join it at all. You should use s.circuit_id.

If you provide some example data we can easily use then we can help you further.

3 
SELECT * 
FROM billing b
INNER JOIN circuit c ON c.circuit_ID = b.circuit_ID
INNER JOIN service s ON s.circuit_ID = b.circuit_ID
WHERE b.MONTHLY_RENTAL > 0 AND s.provisioning_status = 'Dead'
4

Hi Rogier/Scott,

Thanks for the support. Scot your query worked excellent. I had to change 'Dead' to "Dead" to make it work. I think it's because I'm using SQLite that is the reason.

Many Thanks
MHersi