1 Replies - 1572 Views - Last Post: 21 April 2014 - 11:49 AM

#1 DkSnowdon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 125
  • Joined: 31-October 12

Using joins

Posted 20 April 2014 - 10:59 AM

Hi, i am new to using joins


I have a table containing payments, each payment in the Cart_Data Table has a user in the User_Data table, each user creates an entry in the address table with the address table holding the userID reference


I have been using inner join but for some reason it gives me duplicate results and misses out some witch is why i am trying to change to FULL OUTER JOIN but i cant seem to get it working

any idea, the current statement i have is
$sql = "SELECT Cart_Data.cart_id, Cart_Data.mc_gross, Cart_Data.item_name, Cart_Data.payment_Date, 
				   User_Data.first_name, User_Data.last_name, User_Data.payer_email, User_Data.phone,
				   Address_Data.address_city
				FROM Cart_Data
				FULL OUTER JOIN User_Data
					ON Cart_Data.userID=User_Data.id
				INNER JOIN Address_Data
					on Address_Data.userID=User_Data.id
				WHERE User_Data.first_name LIKE '%".$search_string."%' OR 
				User_Data.last_name LIKE '%".$search_string."%' OR 
				payer_email LIKE '%".$search_string."%';"; 



Any feedback is appreciated
Thanks
Dale

Is This A Good Question/Topic? 0
  • +

Replies To: Using joins

#2 smendoza88  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 27
  • Joined: 13-July 11

Re: Using joins

Posted 21 April 2014 - 11:49 AM

I would change the full outer join to a Inner join. You want to see users that have records in Card_Data no need to return users who don't have any orders. If you are missing records it could be because they don't have any records in the address table, This is a guess since I can't see the data. The inner join that you're using to link users_data and adress_data will only return a record if the user_date record has a record in the address_data table.
When I get weird results I typically do a select * to have it return all the data, that way it will help shed some light on where the error could be. Maybe you didn't realize you need to filter a little better eg a user can have multiple orders and you only want to return data for a specific order.

Me personally, I try not use "LIKE" in my queries. I'd use their userid if you have that instead of "LIKE".

I'd start using your fav sql IDE, enter in a userid that is giving you problems and see what query returns
select *
FROM Cart_Data INNER JOIN User_Data
  ON Cart_Data.userID=User_Data.id
LEFT JOIN Address_Data
  on Address_Data.userID=User_Data.id
WHERE User_Data.id = SomeUserID



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1