Another problem

[Zvonko]

Hello!

I'm not very good in SQL statements, so I need someone to help me.
I have three tables in database. Let's say that tables are named ONE, TWO and THREE and database is named DB.mdb. In first table I have customer info (ID, name, address...), in second is bill info (ID, CustomerID, payment...) and in third are things that are on bill with certain ID (ID, BillID, Qty, Price...). Now I need one recordset that would return all the info from three tables.
Can you help me?

[honeybee]

Can you clarify your table structure a bit?

Your customer table must have a primary key CustomerID.
Your bill table will also have a primary key BillID.
Now, what do you have a third table for? If you want to track bill-wise details, they are available from the Bills table. You can also store the CustomerID in the Bills table, so that you can know which bill was issued to which customer. In this kind of structure, the SQL statement would look like:

Select * from Bills where CustomerID = 'A0001'

or if you want multiple values of CustomerID, then:

Select * from Bills where CustomerID in ('A0001','B0003','H0123')

If you can give details of the primary keys and foreign key references in the three tables you mentioned, I can give you a more specific answer.

[Paul Warren]

Zvonko - here's an SQL statement which will pull back a list of all the customers, payments made and details of what's on the bill. The key is in the joins, these are outer joins but you could use inner if you just wanted those people who have made payments and received bills.

SELECT ONE.Name, TWO.Payment, THREE.Qty, THREE.Price
FROM (ONE LEFT JOIN TWO ON ONE.CustID = TWO.CustID) LEFT JOIN THREE ON TWO.ID = THREE.BillID;

[Zvonko]

Thanks, Paul!

I know just basics about SQL statements, nothing about JOINs. Can you explain me a little about inner and outer joins?

Zvonko,

maybe the easiest way to slowly get into SQL would be to install Access, create your tables there and produce queries with a reasonable nice wizzard interface. Then you can copy (and of course study) the SQL statements Access produced for you.

pozdrav

Sascha

[Paul Warren]

sascha is right, that's how I produced the SQL statement I posted. It's probably the easiest way because Access will do error checking for you. The basics of joins are this :

an inner join - you will only see the results from the two joined tables if the key values are the same. That's the keys you've used to join the tables. All other records in each table are ignored.

an outer join - This will include all those returned by an inner join plus all unmatched records on either the left or right of the join. Hence, these are called left outer and right outer joins. In my example "... FROM (ONE LEFT JOIN TWO ON ONE.CustID = TWO.CustID) ..." will include all records in ONE ( left side of join ) and all those records which have a macthing CustID in TWO.

There are plenty of tutorials out there on the web and SQL is not that difficult to pick up. Good luck

[Zvonko]

Thanks, guys!

One question for sascha: I noticed that you speak slovene. Where are you from?

[Zvonko]

Oh, I forgot before: Where can I get examples (tutorials) about SQL statements?

[Clunietp]

http://w3.one.net/~jhoffman/sqltut.htm

[Zvonko]

Thanks, Clunietp!

Zvonko,

I am from Vienna, and to be honest, I studied Serbo-Croatian, so my skills in Slovenian are limited. I understand it a bit, but I cannot speek (although Croatians say, that I speak Croatian with a Slovenian acceent ;) ).

best regards

Sascha