Help for SQL beginner

[dejanB]

Hi!

This is my problem:

In my VB app I'm using dbGrid, which uses data control (Dat)as datasource.
Dat.recordsource = "SELECT ID_buy, ID_sell, ID_produce FROM tbl1 WHERE IDNum = " & IDNumber <-- given from user

Now, this works fine, the problem is just, that values in this table are just numbers (sort of ID_numbers) of activities, that are stored in others tables, e.i.

tbl2
ID_buy Activity [text]


tbl3
ID_sell Activity [text]

tbl4
ID_produce Activity [text]

Now, what I'm asking is How to create query, that will return activities (from each tbl2, tbl3, tbl4) instead of of ID numbers of that activities.

Thanks for help!

[SirLee]

If you have MS-ACCESS the easiest thing to do is just define your query there run it to see wether the results are fine and then look at the SQL code and paste it into VB.

I am using this quite often if I have a complicated query.

At least you will get an idea of how the query should look like.

I think you will end up with several INNER JOINS in your sql.

[AKA]

Using Access is a good idea. I also use it.


Select
tbl2.Activity,
tbl3.Activity
From
tbl1,
tbl2,
tbl3
Where
tbl1.IDNum = IDNumber <-- given from user
And
tbl2.ID_buy = tbl1.ID_buy
And
tbl3.ID_sell = tbl1.ID_sell


Will give you a start of thinking.

[dejanB]

Thanks guys, I've done it in Access and it works fine.

If someone is browsing through this thread looking for solution of
similar problem, I did something like:

SELECT tbl2.Activity, tbl3.Activity,
tbl4.Activity, tbl5.Activity
FROM tbl5 RIGHT JOIN (tbl4
RIGHT JOIN (tbl3 RIGHT JOIN
(tbl2 RIGHT JOIN tbl1 ON
tbl2.ID_buy= tbl1.ID_buy) ON
tbl3.ID_sell=
tbl1.ID_sell) ON
tbl4.ID_produce=
tbl1.ID_produce) ON
tbl5.ID_other= tbl1.ID_other

** rules for spliting commands not used