Querying a table with another set of results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querying a table with another set of results

Author

Topic

Firebrand
Starting Member

24 Posts

Posted - 2010-06-22 : 08:28:47

I'm familiar with SQL, but everything I've done up until now has been based around Querying a database with known values.

I really need to be able to return data based on values from a query that may or my not return data in another query. Like an IF Statement.

What I'd like to acheive from Queries I've laid out below is a single row from Table 2 based on the preferred languages list in Table 1, respecting the language hierachy laid down in Table1.

The way that I'd like the language hierachy from Table1 to function is based on the CountryID and languageID checking against Table 2 as a pair.

I’m thinking that I need to prepare Table1 and Table2 before checking Table1 against Table2 in a ‘Loop’ fashion before exiting when a match is made.

I hope I am making sense! Please let me know if not.

Table1 - A list of prefered languages for a country

SELECT * FROM tblLanguageOrder WHERE CountryID = 5 ORDER BY LanguageOrderID

RecordID / LanguageOrderID / CountryID / LanguageID

1 / 2 / 5 / 5
2/ 39/ 5/ 1
3/ 40 / 5 / 6

Table2 - A list of title options for a course

SELECT CourseID, CountryID, LanguageID, CourseTitle FROM tblCourseTitle WHERE CourseID = 214

RecordID / CourseID / CountryID / LanguageID / CourseTitle

214 / 1 / 1 / Alan Test UK - EN
214 / 5 / 1 / Alan test DK - EN
214 / 2 / 1 / Alan Test DE - EN
214 / 2 / 2 / Alan Test DE - DE
214 / 6 / 3 / Alan Test SE - SV
214 / 5 / 5 / Alan test DK - DA
214 / 13/ 6 / Alan test FI - FI
214 / 5 / 6 / Alan test DK - FI

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-22 : 10:40:40

isnt it a matter of simple join between two tables based on CountryID / LanguageID values? or are you looking at way to return multiple values from table2 as comma separated list?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-06-22 : 15:53:10

[code]
SELECT * FROM tblLanguageOrder as lang
JOIN tblCourseTitle as cors
on lang.CountryID = cors.CountryID
WHERE lang.CountryID = 5 ORDER BY lang.LanguageOrderID
[/code]

Firebrand
Starting Member

24 Posts

Posted - 2010-06-23 : 06:38:15

quote:
Originally posted by visakh16

isnt it a matter of simple join between two tables based on CountryID / LanguageID values? or are you looking at way to return multiple values from table2 as comma separated list?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, I didn't do a very good job of explaining myself in the first place. I'm probably going to take a different approach to this problem. Thanks again for your help.

poohbear
Starting Member

1 Post

Posted - 2010-06-23 : 06:45:24

Hi Firebrand

Just read the thread. Would I be correct in saying that you want to return data in Table 1 based on whether it exsist in Table 2? Or am I missing something....?

Regards

Londus

My Big Fat Geek Database - the SQL.

Firebrand
Starting Member

24 Posts

Posted - 2010-06-23 : 08:30:58

quote:
Originally posted by poohbear

Hi Firebrand

Just read the thread. Would I be correct in saying that you want to return data in Table 1 based on whether it exsist in Table 2? Or am I missing something....?

Regards

Londus

My Big Fat Geek Database - the SQL.

I'm looking to return data from Table2 if the CountryID and LanguageID match the CountryID and LanguageID on first row in Table1, if no match then the 2nd row in Table1, if no match then the 3rd row in Table1 and so on... Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources