CODEWITHSUNDEEP
phpmysqlsql
user2866631
user2866631

Reputation: 73

Joining and querying two mysql tables

I am pretty new to php and MySql and required some help.

I have two tables 1) a Wishlist containing three columns - an emailId, itemId and alert

2) an ItemList containing several columns - itemId, itemName, itemPrice, itemUrl etc.

When a user is logged in with a particular emailId, i need to display the list of items in his WishList (from the itemList) along with the "alert" column from his wishlist. Since both the tables are different, and the emailId is the only input I will have, I need to find an optimal method to query this.

WishList

----------------------------------
emailId      |  itemId  | alert
----------------------------------
[email protected]     01        true
[email protected]     02        false
[email protected]     03        false
[email protected]     03        false
[email protected]     03        false
[email protected]     04        false

ItemList
-------------------------------------------------
itemId | itemName | itemPrice | itemUrl | ..
-------------------------------------------------
 01       abc         129         
 02       cde         99
 03       def         981
 04       efg         29
 05       fgh         200

So, given the emailId, how can I optimally query all the items corresponding to that? example : if emailId is [email protected] , then required output:

Output - [email protected]
----------------------------------------------------------
 alert |  itemId | itemName | itemPrice | itemUrl | ..
---------------------------------------------------------
 true       01       abc         129         
 false      03       def         981
 false      04       efg         29

Thankyou

edited the question..sorry about that.

Upvotes: 0

Views: 80

Answers (7)

Nishu Tayal
Nishu Tayal

Reputation: 20820

Use LeftJoin 

Select I.* from ItemList I Left join WishList W 
    on I.itemId = W.itemId where W.emailId = "<email_address>"

Upvotes: 0

Oki Erie Rinaldi
Oki Erie Rinaldi

Reputation: 1863

You can use JOIN or RIGHT JOIN or LEFT JOIN method.
try this :

SELECT WL.emailId, WL.itemId, IL.itemName, IL.itemPrice 
FROM WishList WL
LEFT JOIN ItemList IL
on WL.ItemId = IL.itemId
//filter here, you can add WHERE CLAUSE

Upvotes: 1

R R
R R

Reputation: 2956

u can try the following query .

select *
from wishlist w,itemlist i
where w.itemid=i.itemid
and i.emailid="emailaddress"

Upvotes: 0

B.Praveen kumar
B.Praveen kumar

Reputation: 81

Try this

select * from ItemList i where i.itemId=(select itemID from WishList where emailid="emailaddress")

Upvotes: 1

Ankit Sharma
Ankit Sharma

Reputation: 4071

Try This

select b.* from WishList as a join ItemList as b on a.itemId = b.itemId 
where a.emailId = 'email address'

It will give you the whole item list for the desired emailid.

Upvotes: 0

Deniyal Tandel
Deniyal Tandel

Reputation: 632

You can use left join for that types of result. 

Select * 
FROM ItemList I 
Left join WatchList W On I.ItemId=W.ItemId 
where your_require_condition ;

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

Try This

select * from WishList join ItemList ON WishList.itemId = ItemList.itemId 
where emailId = "emailaddress"

Upvotes: 0

Related Questions