Best practice for comparing 2 tables

[Paigey]

Hi all,

Having a bit of a headache trying to compare 2 tables in my MS SQL db.

Basically I have

Table 1 - dbo.teams (Contains a list of teams under field name team_NAME)
Table 2 - dbo.user-preferences (Keeps a record of Teams chosen from Table 1)

At the moment I may have

Table 1 - teams:
Column in db - team_NAME:
teamA
teamB
teamC
teamD
teamE etc...

Table 2 - user_preferences:
Column in db - prefTEAM:
teamC
teamE

What I'm trying to do is construct a select statement and condition to display a distinct list of results of all teams in Table 1 but highlight/bold any that are also in Table 2

so in the above tables example the results I would need would be...

teamA
teamB
<bold>teamC<bold>
teamD
<bold>teamE<bold>

Any ideas on the best way to do this?

If possible I need the condition that would highlight duplicates seperate from the select statement as rather than bold, I may change these to check or uncheck boxes on a form.

Hope it all makes sense.

Regards

Paigey

[ganeshkumar08]

Hi SQL Friend,

I understood that you need a result that you must able to identify the user_pref record. i.e as below result.
-------------------------------------------------------
Team_Name AvailableInUserPref
---------------------------------------------------------
TeamA 0
TeamB 0
TeamC 1
TeamD 0
TeamE 1
-------------------------------------------------------
I didnt tried by running it, but observe the logic what i did,

Declare @User_Preffered Table(Team_Name varchar(50), AvailableInUserPref bit Default 0)
Insert @User_Preffered(Team_Name,AvailableInUserPref)
Select Team_Name,
(Case When Team_Name IN (Select Distinct prefTeam from dbo.userPreferences ) Then 1 End) from dbo.Teams

Try it,
If u get result means its okay otherwise i try it in system and send u query

Ganesh Kumar V

[ck9663]

Hi all,

Having a bit of a headache trying to compare 2 tables in my MS SQL db.

Basically I have

Table 1 - dbo.teams (Contains a list of teams under field name team_NAME)
Table 2 - dbo.user-preferences (Keeps a record of Teams chosen from Table 1)

At the moment I may have

Table 1 - teams:
Column in db - team_NAME:
teamA
teamB
teamC
teamD
teamE etc...

Table 2 - user_preferences:
Column in db - prefTEAM:
teamC
teamE

What I'm trying to do is construct a select statement and condition to display a distinct list of results of all teams in Table 1 but highlight/bold any that are also in Table 2

so in the above tables example the results I would need would be...

teamA
teamB
<bold>teamC<bold>
teamD
<bold>teamE<bold>

Any ideas on the best way to do this?

If possible I need the condition that would highlight duplicates seperate from the select statement as rather than bold, I may change these to check or uncheck boxes on a form.

Hope it all makes sense.

Regards

Paigey

name is really not recommended primary key...but if this all you have to work with, try:

select team_name, case when user_preferences.prefTEAM is null then 0 else 1 end as preferred
from teams left join user_preferences on team_name = prefTEAM

the generated column preferred will be your flag if it exist on both table. you then have to adjust on your GUI that if it's 1 you have to tag it in BOLD

-- ck

[Paigey]

Hi Ck,

Thanks for the help. Seems to be along the right lines.

I've updated the script and now have

prefPREM = "SELECT team_NAME, CASE WHEN user_preferences.prefTEAM is null then 0 else 1 end as preferred " _
& "FROM football_teams LEFT JOIN user_preferences on team_NAME = prefTEAM "


Set rsPREM = conn.Execute (prefPREM)

Do Until rsPREM.EOF

' Conditional statement

rsPREM.MoveNext
Loop


I need to add a couple of things

also need to have the user_preferences.prefTEAM as a distinct list
need to select all prefTEAM from user_preferences where prefEMAIL = '"&currUSER&"'

'"&currUSER&"' is a session of the users email address that gets called in when they login.

Also how would I write the conditional statement so as you say the 0 and 1 gets flagged as bold or not bold?

Thanks for your help...

Paigey

[ck9663]

Hi Ck,

Thanks for the help. Seems to be along the right lines.

I've updated the script and now have

prefPREM = "SELECT team_NAME, CASE WHEN user_preferences.prefTEAM is null then 0 else 1 end as preferred " _
& "FROM football_teams LEFT JOIN user_preferences on team_NAME = prefTEAM "


Set rsPREM = conn.Execute (prefPREM)

Do Until rsPREM.EOF

' Conditional statement

rsPREM.MoveNext
Loop


I need to add a couple of things

also need to have the user_preferences.prefTEAM as a distinct list
need to select all prefTEAM from user_preferences where prefEMAIL = '"&currUSER&"'

'"&currUSER&"' is a session of the users email address that gets called in when they login.

Also how would I write the conditional statement so as you say the 0 and 1 gets flagged as bold or not bold?

Thanks for your help...

Paigey

try

select distinct prefTeam from user_preferences where where prefEMAIL = currUSER -- > you have to convert this into sql string. similar to the one you created above.

the conditional i am talking about will be in your GUI/apps side, not in sql server. so the syntax would depend on your front-end tool

-- ck