How to retrive the items based on joins

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I have two table one is item table which contains the itemid,itemname,itemprice and another for userpermission which contains productid,userid . I want to listed down the items which are in the item table but not in the userpermission table for a particular user. Means for userid 1 i want to list the items which are in item table but not in userpermission .

where Userid is stored in session

Posted 19-Oct-11 19:15pm

Vimalrashamra

Add a Solution

Comments

kiran dangar 20-Oct-11 1:26am

Hi, your question is confusing.. please elaborate.
where is the relation between UserID & your Item Table ???

kumar_k508 20-Oct-11 1:38am

Give more detail explanation about your query with relation between two tables..

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

janwel · Answer 1 · 2011-10-19T20:31:00

SQL

Select * from table1 Inner join table 2 on 
table1.FirstID = table2.FirstID

That is the syntax regarding on how to use Inner Join must have a primary key or a foreign key
Basically im proposing something to you.,,
If your using SQL server it is better to put in your SQL statement and try to execute it in new query of your database. In that way you can easily see what is the error of your SQL command.

Happy coding

AmarSinghRawat · Answer 2 · 2011-10-19T20:34:00

Solution 2

SQL

Select * from Item where ItemID not in (select ItemID from UserPermission where UserID=ID)

Posted 19-Oct-11 20:34pm

AmarSinghRawat

Updated 19-Oct-11 23:51pm

v2

sachin10d · Answer 3 · 2011-10-19T21:58:00

Solution 3

Try this

SQL

declare @Id int
set @Id= 1
select * from Item I left outer join UserPermission U on I.ItemId = U.ItemId
where ItemId is null and userId = @Id

Posted 19-Oct-11 21:58pm

sachin10d

Zubair Alie · Answer 4 · 2011-10-19T23:48:00

you are mixing product_id and item_id. what i have understood is as follows
you have an item table and userPermission table. userPermissiontable contains the user_id and item_id. now you want to display those items which are in user's permission. if right, here is query

SQL

Declare @userID INT
set @userID = 1

Select * from UserPermission up
where 
up.userid = @userID
up.item_id IN (Select i.item_id from item i)

How to retrive the items based on joins

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0