How to write on two tables so that --7 colums form one table and Value of a Column distributed in N Rows MAtch

Question

0.00/5 (No votes)

See more:

How to write on two tables so that --7 colums form one table and Value of a single Column distributed in N Rows MAtch and retun Matched Data

My First table has -- columns
Keyword1
Keyword2
Keyword3
Keyword4
Keyword5
Keyword6
Keyword7

and my second table contains two coulmns User_Id and Choice
as he can select as much items so his respected choice's Id is stored in second table like--

User_Id--Choice
2 ---121
2 ---122
2 ---123
2 ---124
2 ---125

so can i MAtch data through
Table1 (Keywods 1 to Keywords7 ) and Table2(Choice)

create Script of Table1 is--

SQL

CREATE TABLE [dbo].[JobForm](
    [Job_Id] [int] NOT NULL,
    [Recruiter_id] [int] NULL,
    [JobTitle] [varchar](100) NULL,
    [Location] [varchar](50) NULL,
    [Category] [varchar](50) NULL,
    [MinExp] [varchar](20) NULL,
    [MaxExp] [varchar](20) NULL,
    [Body] [varchar](2000) NULL,
    [Keyword1] [varchar](50) NULL,
    [Keyword2] [varchar](50) NULL,
    [Keyword3] [varchar](50) NULL,
    [Keyword4] [varchar](50) NULL,
    [Keyword5] [varchar](50) NULL,
    [Keyword6] [varchar](50) NULL,
    [Keyword7] [varchar](50) NULL,
    [Investor1] [varchar](50) NULL,
    [Investor2] [varchar](50) NULL,
    [Investor3] [varchar](50) NULL,
    [Investor4] [varchar](50) NULL,
    [Investor5] [varchar](50) NULL,
    [CreatedDate] [datetime] NULL
) ON [PRIMARY]

and create Script of Second Script is---

SQL

CREATE TABLE [dbo].[UserFeedSelection](
    [User_Id] [int] NOT NULL,
    [JobFeed] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [User_Id] ASC,
    [JobFeed] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

i have to match

VB

Keyword1
Keyword2
Keyword3
Keyword4
Keyword5
Keyword6
Keyword7

with
Choice column below as having N number of choices for a particular USer_Id

VB

User_Id--Choice
2 ---121
2 ---122
2 ---123
2 ---124
2 ---125

Pls Help ..Thanks in Advance

Posted 28-Feb-13 22:38pm

sr_24

Updated 1-Mar-13 0:29am

v4

Add a Solution

Comments

gvprabu 1-Mar-13 6:10am

Hi ,

Give some sample data... like from table 1 , table 2 what are the values u need to list.?
Both the tables I need some sample data...

sr_24 1-Mar-13 6:30am

I have Update my Question with Table scripts in it !!have a Look gvprabhu

Karthik Harve 1-Mar-13 6:50am

You mean you want to match the choice column with columns keyword 1 - 7 ?

gvprabu 1-Mar-13 7:02am

Hi,

See Keyword1...keyword7 is VARCHAR Datatype and Choice Datatype is INT...
Okie give the sample data for both the table

sr_24 3-Mar-13 10:50am

yes u r right , i hv resolved this mismatch ,the below solution worked

SELECT DISTINCT A.USER_ID, A.CHoice FROM
UserFeedSelection A
INNER JOIN JobForm B ON
A.choice = B.Keyword1 OR A.Choice = B.Keyword2 OR
A.choice = B.Keyword3 OR A.Choice = B.Keyword4 OR
A.choice = B.Keyword5 OR A.Choice = B.Keyword6 OR
A.choice = B.Keyword7
WHERE A.User_ID = 2

but even it has some problem like--
if 4 keywords match with 4 choices
it REPEAT a SAME RECORD 4 Times ...so what should i do Karthik Harve ???

1 solution

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)

*Karthik Harve* · Accepted Answer · 2013-03-01T00:56:00

Solution 1

Hi,

try below query.

SQL

SELECT DISTINCT A.USER_ID, A.CHoice FROM
UserFeedSelection A 
INNER JOIN JobForm B ON 
A.choice = B.Keyword1 OR A.Choice = B.Keyword2 OR 
A.choice = B.Keyword3 OR A.Choice = B.Keyword4 OR
A.choice = B.Keyword5 OR A.Choice = B.Keyword6 OR
A.choice = B.Keyword7
WHERE A.User_ID = 2

hope it helps

Posted 1-Mar-13 0:56am

Karthik Harve

Comments

sr_24 3-Mar-13 10:47am

it worked but it has a PROBLEM like that---

if 4 keywords match with 4 choices
it REPEAT a SAME RECORD 4 Times ...so what should i do Karthik Harve ???