CODEWITHSUNDEEP
asp.netsqlgridview
user2224962
user2224962

Reputation: 3

How to retrieve data using where clause from 2 different tables

I have two tables [basic] & [info] both have a primary key email_id. Now I want to retrieve info from the basic table but the where clause should have searching condition from both the tables

Example:

SELECT DISTINCT 
    basic.f_name, 
    basic.l_name 
FROM 
    basic 
CROSS JOIN 
    info 
WHERE 
    (basic.email_id = basic.email_id) 
    AND (basic.caste LIKE '%' + @caste + '%') 
    AND (info.diet =  @diet )

(this query is written in asp for gridview but its not working)

Upvotes: 0

Views: 318

Answers (3)

Zo Has
Zo Has

Reputation: 13018

Use an INNER JOIN

SELECT DISTINCT 
    basic.f_name, 
    basic.l_name 
FROM 
    basic as basic
INNER JOIN 
    info as info 
    ON basic.email_id = info.email_id
WHERE 
    (basic.caste LIKE '%' + @caste + '%') 
    AND (info.diet =  @diet )

Upvotes: 0

Kyle Copeland
Kyle Copeland

Reputation: 539

When you do an inner join you are actually using a cross join with a where clause. In an inner join all the data is combined (cartesian product) and then filtered through with a where clause. Therefore, I think your code would be cleaner using an inner join. 

SELECT DISTINCT basic.f_name, basic.l_name 
FROM basic 
CROSS JOIN info 
WHERE (basic.email_id = basic.email_id)

is equivalent to 

SELECT basic.f_name, basic.l_name 
FROM basic INNER JOIN info ON basic.email_id = info.email_id

I think this solves your problem.

Upvotes: 1

atw13
atw13

Reputation: 719

SELECT basic.f_name, basic.l_name 
FROM basic INNER JOIN info ON basic.email_id = info.email_id 
WHERE (basic.caste LIKE '%' + @caste + '%') AND (info.diet = @diet )

Upvotes: 6

Related Questions