TM1 Forum

Hi all,

I think this is a fairly simple issue but I am not sure how to sort it out.

I have information in an SQL database which I want to obtain. I have set up a query to bring in the information from one table and this uses the code

SELECT DEAL_NO, DEALTYPE FROM DEALS

however now I want to be able to bring in info in the same query from a second table. Could an answer for how this can be done be provided please.

The new table is called PAIRRPT, the two can be linked in a relational database using DEAL_NO and the new field to be imported is THE KEY.

If anyone could help that would be great.

Cheers,

Robin

Hello,

to retrieve the data you need, you have to do a 'join' query which should look like this one:

select
DEALS.DEAL_NO, DEALTYPE, THE_KEY
from
DEALS, PAIRRPT
where
DEALS.DEAL_NO = PAIRRPT.DEAL_NO

(As this is a basic SQL query, I would suggest you to increase your SQL knowledge through some readings )

Regards,

Renaud.

Cheers Renaud,

I will have a look at SQL also - do you have any suggestions since it is not the same as Microsoft access SQL

Robin

robincollettabc123 wrote:Cheers Renaud,

I will have a look at SQL also - do you have any suggestions since it is not the same as Microsoft access SQL

Robin

It's near enough for the purposes of simple join queries. Most SQL is fairly transportable between environments until or unless you get into some of the more esoteric statements.

A good way to pick it up is to create dummy tables in a blank access databases (you don't have to replicate the whole table structure, just the fields that you're interested in), create a query using the Query By Example GUI, then switch to SQL view to see what code it has generated. I can't say that Access generates the most eficient or elegant SQL code, but it can at least get you up to speed on the syntax for some of the more common select queries, including joins.

(You can even hook an Access front end up to an SQL Server back end, though I imagine that we're talking about your production environment so that may not be a viable option here.)

Further to my previous post I have arrived at a new issue.

I am extending my query now and there are several joins required however I am only able to do the one join with my SQL knowlegde.

The query below is what I have so far and leaves.

select
DEALS.DEAL_NO, PAIRRPT.THEKEY,PAIRRPT.
MATURE_DT, PAIRRPT.L_FACE_VAL,
PAIRRPT.L_INT_RATE, PAIRRPT.S_RATE,
PAIRRPT.L_BOOK_VAL, PAIRRPT.S_BOOK_VAL,
CPARTY.THEKEY, DEALS.DEAL_DT, DEALS.TICKET_NO,
SWDEALS.START_DT, EVENTS.CASHFLOW, EVENTS.ACTION_DT,
EVENTS.AMOUNT, EVENTS.COMMENTS, BUSTRUCT.NAME
from
PAIRRPT, DEALS, CPARTY, SWDEALS, EVENTS, BUSTRUCT
where
DEALS.DEAL_NO = PAIRRPT.DEAL_NO

I need an inner join I think to say that
PAIRRPT.CPARTY = CPARTY.THEKEY

and also
PAIRRPT.BUSTRUCT = BUSTRUCT.THEKEY

Again if anyone can help that would be really useful.

Cheers

Robin

Here's an example of a multi table query which might help point you in the right direction Robin,

SELECT tblOne.RegNo, tblOne.OldRegNo, tblOne.Name, tblThree.FirstName, tblThree.MiddleName, tblThree.LastName, tblThree.Street1, tblThree.City1, tblThree.State1, tblThree.Zip1, tblThree.Country1
FROM (tblTwo INNER join tblOne ON tblTwo.RegNo = tblOne.RegNo) INNER join tblThree ON tblTwo.MemberNo = tblThree.MemberNo
WHERE (((tblOne.RegNo)=123))
ORDER BY tblOne.OldRegNo;

w3Schools is good for CSS, so might be good for SQL too.

Cheers,
Martin

Further to this point if I wanted to include two conditions I have tried doing

eg.

WHERE ((PAIRRPT.DEAL_NO)=1734) AND ((EVENTS.CASHFLOW)="Y")

but this fails to work even though the two conditions work seperately.

Cheers for all the help so far.

Robin