Neater way of joining?

Question

Neater way of joining?

Alex Thomas

Ten Centuries

Points: 1120
More actions
June 5, 2005 at 4:26 am

#66132

hiya i have a select statement which uses case and joining and am lokoing for a neater way of getting data rather than a load of join statements.. for example..

SELECT CASE
WHEN e.cust = 113
THEN
(SELECT Cust.value
FROM events e
INNER JOIN Cust ON
  e.CustID = Cust.CustID
WHERE e.id= 26
AND CustomFormFieldDefinitions.fieldid = 2540) + ' - ' +

(SELECT Cust.value
FROM events e
INNER JOIN Cust ON
  e.CustID = Cust.CustID
WHERE e.id= 26
AND CustomFormFieldDefinitions.fieldid = 2541) + ' - '

(SELECT Cust.value
FROM events e
INNER JOIN Cust ON
  e.CustID = Cust.CustID
WHERE e.id= 26
AND CustomFormFieldDefinitions.fieldid = 2542)

etc etc..

with a few case statements this is going to get ugly! i know there's a better way but it's sunday, im at work and im not exactly on the ball.

thanks!

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

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

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 1

I am a little confused. What is your primary key? Can you post a copy of your Table(s) definition and explain what you are trying to do.

The general form of a select statement is:

Select something

FROM some where

When these conditions are true

Thanks

Mike

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 2

mike, sorry i cut out the bottom bit to save space

basically i want to join onto a table using an event id (26). this event id, along with the fieldid (2542, 2541, etc) forms the composite key of the customformfielddefinitions table. i want to pull data from this table from 3 separate rows using the same event id, but different fieldids. thus, do i need 3 separate joins or can i use one?

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 3

You can use one statement if I understand what you want.

SELECT something

FROM YourTable

WHERE FieldID = 2542

OR FieldID = 2541

OR..ect

AND eventID = 26

Selects all somethings with a fieldID of 2542 or fieldID of 2541 that also have an eventID of 26

You have to be careful of the order of the items in the WHERE statement. Generally the OR conditions should preceed the AND conditions. IF you were to use this

SELECT something

FROM YourTable

WHERE EventID = 26

OR FieldID = 2542

OR FieldID = 2541

OR..ect

You would select all somethings where eventID was 26 or filedID = 2542 or fieldID = 2541 which may not be what you want.

HTH

Mike

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 4

mike thanks for the reply,

cheers- i need to be able to distinguish which results belong to which field id though.. with thati cant be sure, no?

alex

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 5

I am not sure what you need. Could you send the table def, some sample data and the expected results?

Mike

ron k SSCertifiable Points: 6103 More actions · Answer 6

Alex I think you are looking for something like this:

Select a.value, b.value, c.value

From events e

Join Cust a

On a.custId = e.custId

And CustomFormFieldDefinitions.fieldid = 2540

Join Cust b

On b.custId = e.custId

And CustomFormFieldDefinitions.fieldid = 2541

Join Cust c

On c.custId = e.custId

And CustomFormFieldDefinitions.fieldid = 2542

Where e.id = 26