SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query help please...

    Hey guys...

    I've got this fairly complex query (which I wrote myself, thanks Rudy!):
    Code:
    SELECT
    	p.prod_id, p.prod_name, prod_thimg, 
    	m.mfr_id, m.mfr_name,
    	me.myeq_name, me.myeq_dailyrate, me.myeq_weeklyrate,
    	me.myeq_fourweekrate, me.myeq_deliverycharge, me.fk_prod_id
    FROM tblproducts p
    INNER JOIN tblmanufacturers m
    	ON m.mfr_id = p.fk_mfr_id
    INNER JOIN tblmyequipment me
    	ON me.fk_prod_id = p.prod_id
    WHERE me.fk_company_id = #session.usercompany#
    ORDER BY p.prod_name
    It selects all product details for products which are currently "owned" by my company. There are two tables which contain rates information. The "my equipment" table which lists default rates, and the "rates" table which shows the actual rate for that piece. I tie them together with a JOIN between the two. Now, this query, when run, works properly. It's when I try to find out if a piece of equipment is rented already. I thought it would be fairly simple to add another INNER JOIN. The query itself works just fine, but it returns 0 rows. Here's what it looks like:
    Code:
    SELECT
    	p.prod_id, p.prod_name, prod_thimg, 
    	m.mfr_id, m.mfr_name,
    	me.myeq_name, me.myeq_dailyrate, me.myeq_weeklyrate,
    	me.myeq_fourweekrate, me.myeq_deliverycharge, me.fk_prod_id,
    	r.actual_id
    FROM tblproducts p
    INNER JOIN tblmanufacturers m
    	ON m.mfr_id = p.fk_mfr_id
    INNER JOIN tblmyequipment me
    	ON me.fk_prod_id = p.prod_id
    INNER JOIN tblrates r
    	ON me.myeq_id = r.fk_myeq_id
    WHERE me.fk_company_id = #session.usercompany#
    ORDER BY p.prod_name
    What would be the best way to find out if a piece of equipment has been rented? I could add a toggle in the myequipment table for "rented". That would be very easy.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    change your last INNER JOIN to LEFT OUTER JOIN and see what that does



    p.s. nice job on the query so far!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That "seems" to have done it Rudy. Thanks for the input. I have to say that I've REALLY expanded my understanding of SQL thanks almost solely to you.

    I wouldn't have been able to do some of this stuff without your help.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thanks

    glad to help
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by creole
    That "seems" to have done it Rudy. Thanks for the input. I have to say that I've REALLY expanded my understanding of SQL thanks almost solely to you.
    Ah yes my freind, make no mistake...The SQL is definitely strong in that one.


Bookmarks

Posting Permissions

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