September 25, 2007 at 3:48 pm
I have student and account table with columns id, course, number, class. As I'm novice in querying I need sample how to join these tables and find unmatched rows.
Thanks in advance
September 25, 2007 at 4:00 pm
As a novice you should start from reading manuals.
Open BOL ("help" in SQL Server), find topic "FROM clause (described)" and read how to use joins.
It has good definitions, explanations, examples, everything you need for a good start.
_____________
Code for TallyGenerator
September 25, 2007 at 9:34 pm
I agree... now's the perfect time to begin exploring Books Online... and when someone gives you a hint like what Serqiy did, don't just stop at the hint... look for other things in the Books Online pages that you find. For example, the FROM clause page is going to talk about JOINs... you should also lookup JOINs and see what might serve you...
Yeah... you could just get the answer from a forum, but then you also wouldn't be learning quite as much for the next problem...
--Jeff Moden
September 25, 2007 at 9:38 pm
wouldn't be learning quite as much for the next problem
Strike that and insert >> wouldn't be learning at all for the next problem.
September 26, 2007 at 12:52 pm
There is a BEGINNERS section for these kind of questions.
* Noel
September 26, 2007 at 1:15 pm
[p]I just felt compelled to answer this as I needed to try out various ways of getting code into the new forum, so I could alter the Simple-Talk Prettifier to do it. No dice, I can't get the IFCodes to behave themselves. Anyway, this is as far as I got! Steve and Steve have some work to do![/p]
[font="courier new"]CREATE TABLE #FirstTable
(PersonsName VARCHAR(80),
favouriteSong VARCHAR(80))
CREATE TABLE #SecondTable
(PersonsName VARCHAR(80),
favouriteSong VARCHAR(80))
INSERT INTO #firstTable(personsName, FavouriteSong)
SELECT 'Bill', 'I did it my way'
UNION SELECT 'Bob', 'Devil Woman'
UNION SELECT 'Gerhard', 'smoke gets in your eyes'
UNION SELECT 'Dave', 'That Old black magic'
UNION SELECT 'Jacob', 'abide with me'
UNION SELECT 'Gertie', 'I''m Gertie the girl with the gong'
UNION SELECT 'Anne', 'God save the queen'
UNION SELECT 'Kishor', 'Pop goes the weasel'
UNION SELECT 'Isobel', 'Anyone who had a heart'
INSERT INTO #SecondTable(personsName, FavouriteSong)
SELECT personsName, FavouriteSong FROM #firstTable
INSERT INTO #firstTable(personsName, FavouriteSong)
SELECT 'Ray', 'Waterloo sunset'
UNION SELECT 'Sacha', 'I''ts a mans mans world'
UNION SELECT 'Dave', 'I like to teach the world to sing'
INSERT INTO #SecondTable(personsName, FavouriteSong)
SELECT 'Donna', 'I am the walrus'
UNION SELECT 'Blitzen', 'boom bang a bang'
SELECT 'only In First Table',
#firstTable.personsName,
#firstTable.FavouriteSong
FROM #firstTable LEFT [/color]OUTER JOIN #SecondTable
ON #firstTable.personsName=#secondTable.personsName
AND #firstTable.FavouriteSong=#SecondTable.FavouriteSong
WHERE #SecondTable.FavouriteSong IS NULL
SELECT 'only In Second Table',
#SecondTable.personsName,
#SecondTable.FavouriteSong
FROM #firstTable RIGHT [/color]OUTER JOIN #SecondTable
ON #firstTable.personsName=#secondTable.personsName
AND #firstTable.FavouriteSong=#SecondTable.FavouriteSong
WHERE #firstTable.FavouriteSong IS NULL
SELECT 'has two favourites', personsName FROM (
SELECT personsName, favouriteSong FROM #firstTable
UNION ALL
SELECT personsName, favouriteSong FROM #SecondTable)f
GROUP BY personsname
HAVING MAX(FavouriteSong)<> MIN(FavouriteSong)[/font]
Best wishes,
Phil Factor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply