Ben Ben - 2 months ago 12
SQL Question

New to SQL and need some advice with queries

I have recently started learning SQL but can't seem to get my head around creating SQL statements that form relevant results from multiple tables/relations.

Given the following schema:

Account(accNumber, balance, type)
Branch(BSB, phone, streetAddress, town)
registered(accNumber*, BSB*)


I am trying to formulate some outputs:
List all the accNumber registered with a specific BSB (123) and show its listed town (Sydney).

I have tried the following statement for the first query:

SELECT accNumber, BSB, town
FROM ACCOUNT, BRANCH
WHERE BSB = 123;


However, I get every account listed even if they don't belong to the BSB, so I tried:

SELECT accNumber, BSB, town
FROM ACCOUNT, BRANCH
WHERE BSB = 123
AND Town = 'Sydney'
AND account.accNumber=registered.accNumber
AND branch.bsb=registered.bsb;


This time I get column ambiguously defined because they have the same name in the "registered" table.

I've tried making alias in the select statment i.e. accNumber AS ACCOUNT_NUMBER etc, but still getting ambiguously defined errors.

I tried just listing what was in the registered table but then I do not get the town name, just the accNumber and the BSB passed in as a foreign key.

I can't seem to understand how to pull data from other tables and display them correctly and would greatly appreciate any advice!

Answer

So this sounds like a generic SQL question. For your query here is what you're looking at:

select a.account_number
  from account a, brance b, registered r
  where a.account_number = r.account_number and
        a.bsb = b.bsb and
        b.bsb = 123;

This will get you all account numbers from the account table that are in BSB 123. When you have multiple tables that have the same column, you need to tell Oracle (and any database for that matter) which "account_number" column you're referring to (as otherwise it's ambiguous as there are multiple tables that contain the column account_number).

SQL is about tables and joins. You sometimes have to join several tables to get what you need, as above. If you don't join tables, as you did originally, you'll get a "cross product", which is not what you want.

I know this is very "light", but hopefully from the above answer to your question, you'll get some idea of how to do this.

I'd be happy to help you more if you have questions. Everyone is new to SQL at some point. Don't feel bad about that. It takes practice and then it becomes much easier.

-Jim