Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Do I have a fan trap here?

    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
    Attached Thumbnails Attached Thumbnails export.jpg  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Your ERD looks fine to me. What exactly are you worried about happening? Also, I hope you weren't serious about using a loop...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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:

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    stacks?
    it is just a page of data in the UI where you can put your database output.
    Quote Originally Posted by r937
    yes, there is -- write it on multiple lines, and if your UI doesn't allow it, get a new UI
    LOL.. That is really sound advice Rudy. Ok.. between your advice and the link, I got the point and I agree...

    Thank you..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •