Simple SQL script...

Question

Post reply

Simple SQL script...

Zaza

SSC-Addicted

Points: 493
More actions
January 17, 2006 at 12:45 pm

#90444

Hi,
I need some help writing this SQL script.. Looks simple but I cannot figure it out..
I am writing a 'sale printout' report in Crystal Report for our reservation system. Here is the structure (simplified) and sample data of the 2 tables where I get the data from
SALE (as S)
ID (identity)       Name
174                      A School
175                     B School
176                      C School
MEMO (as M)
ID (identity)   RecordID       Table           Memo
10                    174                     Sale              Sale memo for sale 174
11                     174                     Event           Event memo for event 174
12                     175                    Sale              Sale memo for sale 175
13                     175                     Facility        Facility memo for facility 175
The problem is that all memos (event memo, sale memo, facility memo) are stored in the same table (differentiated by the Table field) and the identity columns in the SALE, EVENT and FACILITY tables are using the same numbers.. (a range was not specified so I can have a sale and an event with the same ID )
With the sample data , I need to get a report listing the 3 sales with their corresponding memo... Here are the records I should get
S.ID       S.Name         M.Table     M.Memo
174        A School        Sale            Sale memo for event 174
175        B School      Sale            Sale memo for event 175
176       C School        NULL          NULL (because the memo is a facility memo)
With the script I write, I either get a resultset with 4 records (which will print the same sale twice ie 174) or with 2 records.. How do I write this Select statement to get the 3 rows I need..
Any advice is appreciated...   many thanks...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

Would somthing like this work:

SELECT a.ID,

a.NAME,

b.TABLE,

b.MEMO

FROM SALE a LEFT OUTER JOIN MEMO b

ON a.SID = b.RECORDID AND MTABLE LIKE 'SALE'

Zaza SSC-Addicted Points: 493 More actions · Answer 2

Unfortunately no since it would not select my sale.id 175 which has no memo.

Thanks..

Zaza SSC-Addicted Points: 493 More actions · Answer 3

sorry I meant it would not select my sale 176 which has no memo

allen davidson SSCertifiable Points: 7042 More actions · Answer 4

I think Zaza's script works fine with some small amends

SELECT a.ID,

a.NAME,

b.

, -- Bracket reseerved word

b.MEMO

FROM SALE a LEFT OUTER JOIN MEMO b

ON a.ID = b.RECORDID AND

LIKE 'SALE' -- TABLE not MTABLE

The LEFT OUTER he gave you is what you want. The results I get are:

ID NAME TABLE MEMO

----------- ------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

174 School A Sale Sale memo for sale 174

175 School B Sale Sale memo for sale 175

176 School C NULL NULL

(3 row(s) affected)

Zaza SSC-Addicted Points: 493 More actions · Answer 5

Hi Grasshopper,

Yes, your little modification to the script makes the script work.. Can you explain the logic behind using the square brackets around the field name to make it work??

Thank you very much..

allen davidson SSCertifiable Points: 7042 More actions · Answer 6

Hi Zaza - your script was fine - I think table is a reserved word so it needs square brackets around it. Also column names with spaces in them like 'first name' need square brackets. However I try not to have any spaces in my names.

Zaza SSC-Addicted Points: 493 More actions · Answer 7

Zaza

SSC-Addicted

Points: 493

January 18, 2006 at 7:51 am

#615487

Thanks...