Do I have a fan trap here?

Frunkie · #1 (**permalink**) 08-20-07, 15:15

I have a small jpg of what I believe is a fan trap. I would like to get some other opinions before I start changing things around and adding another relationship when I may not to.

What I am trying to do is loop data between the "event" and "customer_Phone" tables. My loop does not give me the correct information and now that I am looking at the design, I am seeing that I possibly have a fan trap going on here. Can someone please confirm this for me?

Basically, the loop says, give me all of the customer_Phone numbers where the event_Id = something.

Thanks.

Frank

blindman · #2 (**permalink**) 08-20-07, 16:47

Your ERD looks fine to me. What exactly are you worried about happening? Also, I hope you weren't serious about using a loop...

Frunkie · #3 (**permalink**) 08-20-07, 17:13

Quote:

Originally Posted by blindman

Your ERD looks fine to me. What exactly are you worried about happening? Also, I hope you weren't serious about using a loop...

Hi Blindman, thanks for the response. Using this:

Quote:

SELECT * FROM `customer` Inner Join `event` ON `customer`.`customer_Id` = `event`.`customer_Id` Inner Join `customer_Phone` ON `customer`.`customer_Id` = `customer_Phone`.`customer_Id` WHERE `event`.`customer_Location_Id` = '1' AND `event`.`event_Number_Id` = '3'

Customer_Location_Id is a FK and even though it is not shown in the jpg, it is there.

What I need is ALL phone numbers returned from the customer_Phone table where the customer_Id = something AND the event_Number = something. I am not getting the correct results though. I have 3 records in the db as sample data but I am only getting 2 returned.

I was actually was serious about looping the data but through the web application, not sql. No good?

Thanks,

Frank

blindman · #4 (**permalink**) 08-20-07, 17:24

Check your data. The SQL appears fine to me.

You should run this through a sproc using set-based algorithms if you want any sort of efficiency.

Frunkie · #5 (**permalink**) 08-20-07, 17:26

Quote:

Originally Posted by blindman

Check your data. The SQL appears fine to me.

You should run this through a sproc using set-based algorithms if you want any sort of efficiency.

Ok Blindman, thanks again.. I will also look into using sprocs for this.

r937 · #6 (**permalink**) 08-20-07, 20:25

frank, you don't have a fan trap because of the two one-to-many relationships being returned in your query, one of them is restricted to a one-to-one by a condition in the query

may i make a recommendation? stop immediately coding those silly friggin backticks, and also, do yourself a favour, learn how to write indented code

so i would rewrite this --

Code:

SELECT * FROM `customer` Inner Join `event` ON `customer`.`customer_Id` = `event`.`customer_Id` Inner Join `customer_Phone` ON `customer`.`customer_Id` = `customer_Phone`.`customer_Id` WHERE `event`.`customer_Location_Id` = '1' AND `event`.`event_Number_Id` = '3'

like this --

Code:

SELECT * 
  FROM customer 
Inner 
  Join event 
    ON event.customer_Id = customer.customer_Id
Inner 
  Join customer_Phone 
    ON customer_Phone.customer_Id = customer.customer_Id 
 WHERE event.customer_Location_Id = '1' 
   AND event.event_Number_Id = '3'

and then i would go further and make the following changes:

Code:

SELECT event.specific 
     , event.columns 
     , customer_Phone.from
     , customer.each
     , customer.table
  FROM customer 
INNER 
  JOIN event 
    ON event.customer_Id = customer.customer_Id
   AND event.customer_Location_Id = 1 
   AND event.event_Number_Id = 3
INNER 
  JOIN customer_Phone 
    ON customer_Phone.customer_Id = customer.customer_Id

spot the changes

Frunkie · #7 (**permalink**) 08-20-07, 21:03

Rudy, thanks for the reply. I will absolutely take your advice and stop using the backticks. The sql I posted today was from a stack I was having a problem with and was written prior to you enlightening me yesterday. I will go through my code and take them out. I don’t like them either. In the code you formatted, I can see the immediate changes. Much cleaner indeed.

As far as me not getting the correct result set back, that was due to a programming error on my part. I just couldn't seem to pinpoint where the problem was until now.

Maybe I am going about this whole entire process in the wrong way, and please let me know if I am, but the joins I am writing are quite long and several stacks have several sql statements. Instead of joining 10 tables together, I have decided to do smaller chunks of sql code and break it up.

From a complete “readability” standpoint in the programming code of the UI, I have put all of the sql code on one line. I do not like this either and I am wondering if there is another way to do this. When I have an issue like I did today, I have to extract the sql and then format it so it becomes readable again so that I can troubleshoot it.

Is there a tip or trick you can offer?

Thanks,

Frank

r937 · #8 (**permalink**) 08-20-07, 21:36

Quote:

Originally Posted by fjm1967

...but the joins I am writing are quite long and several stacks have several sql statements.

stacks?

Quote:

Originally Posted by fjm1967

From a complete “readability” standpoint in the programming code of the UI, I have put all of the sql code on one line. I do not like this either and I am wondering if there is another way to do this.

yes, there is -- write it on multiple lines, and if your UI doesn't allow it, get a new UI

"doc, it hurts when i do this"

"so don't do that then!"