Identity column: if i want to join two sql tables ...

do they have to have a common primary key?

Not necessarily. You can get a CROSS JOIN of both tables but I am not sure how much help that would be.

|||

I have these two tables

id int Unchecked
CategoryID int Checked
ListTitle nvarchar(50) Checked
Blurb nvarchar(250) Checked
FileName nvarchar(50) Checked
ByLine nvarchar(50) Checked
HTMLCopy nvarchar(MAX) Checked
MainStory bit Checked
MainStoryImageFile nvarchar(50) Checked
Publish bit Checked
Date smalldatetime Checked

and

CategoryID int Unchecked
CategoryTitle nvarchar(50) Checked

and i need to get to the categoryTitle along with stuff from the first table. any idea?

|||

What does 'Checked' and 'UnChecked' mean? Also post some sample data from each of the tables and expected output, makes it easier for me to work on some data.

|||

ooops. checked and unchecked was just the allow nulls check box ticked or not in my table definition. i need to get category name and stuff from the second table shown here. Is this what you mean.

1Making It2Spending It3Staying In4Going Out5Style6Driving7Sport8Travel9Mind GamesNULLNULL

28710 WORST TENNIS PLAYERSBlurb about the 10 worst tennis players goes in here.Bravo2.jpgThey Were Very GoodNo 10. Jimmy Connors 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good
No 9. Vitas Geralitis 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good
No 8. Bjorn Borg 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very goodTrueO2.jpgTrue21/08/2007 13:43:0029710 BEST DEFENDERSBlurb about the 10 best defenders players goes in here.Yahoo.jpgCouldn't beat these guysmain list copy goes here.TrueBravo2.jpgTrue21/08/2007 17:00:0030710 BEST SQUASH PLAYERSBlurb about the 10 best squash players goes in here.Five.jpgGood raquet guysThis is were the main list copy goesTrueTWI.jpgTrue21/08/2007 17:01:0031410 FUNNIEST FILMSBlurb about the 10 funniest films goes in here.Bravo2.jpgThese were very funny filmsHere is the main copy for the funniest films here.TrueFive.jpgTrue21/08/2007 17:06:0032410 SCARIEST FILMSBlurb about the 10 scariest films goes in here.O2.jpgHold on to your seat.This is where the scary story copy goes.TrueBravo2.jpgTrue21/08/2007 17:07:0033410 BEST THEATRES IN LONDONBlurb about the 10 best theatres goes in here.BBC.jpgGreat places to see playsHere is the list copy for the theatresTrueYahoo.jpgTrue21/08/2007 17:10:0034710 BEST BADMINTON PLAYERSBlurb about the 10 best badminton players goes in here.Bravo2.jpgThese were great playersCOPY FOR THISTrueTWI.jpgTrue21/08/2007 17:12:0035510 BEST JEAN LABELBlurb about the 10 best jeans labels goes in here.Five.jpgJeans with the best fitCopy about the jeans goes in hereTrueYahoo.jpgTrue21/08/2007 22:29:0036310 BEST DVD'SBlurb about the 10 best dvd's goes in here.IMG World.jpgEnjoy these filmsworkTrueO2.jpgFalse21/08/2007 22:44:0037310 BEST BOARD GAMESBlurb about the 10 best board games goes in here.Yahoo.jpgHave playing these games at homeNo. 10 Chess 
This is the classic board game
No. 9 Backgammon 
Hope you are good with numbers.TrueYahoo.jpgTrue21/08/2007 22:49:0038710 GREATEST FOOTBALL TEAMSBlurb about the 10 greatest football teams goes in here.BBC.jpgThese teams created footballNo10. Westham United 
The greatest team of all time. The greatest team of all time. The greatest team of all time.The greatest team of all time.
No9. Arsenal 
Not a bad outfit now Henry has left. Not a bad outfit now Henry has left. Not a bad outfit now Henry has left. Not a bad outfit now Henry has left.
No 8. Liverpool 
Three times European Champions. Three times European Champions.Three times European Champions. Three times European Champions. Three times European Champions.TrueBravo2.jpgTrue21/08/2007 23:06:0039710 CLEVEREST RULES IN FOOTBALLBlurb about the 10 cleverest rules goes in here.Five.jpgThese are stupid rules<h3>No.10 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.9 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.
<h3>No.8 No coaching mid-match in tennis</h3>
Think of every sport and you'll see a ranting manager or trainer somewhere on the sidelines. Except tennis, where players have to wait until they change ends for a hurried chat with the man who takes 15 per cent. Even toilet breaks are escorted, to ensure the rule isn't broken.
<h3>No.7 Not being allowed to over-celebrate in football</h3>
In a career that spanned over 500 matches, Alan Hansen scored just 14 goals. True, he was a defender, but had the day ever come that the dour Scot popped one in for his national team (0 goals from 26 games), he'd have gone absolutely bananas. And if today, probably booked.
<h3>No.6 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.5 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.
<h3>No.4 No coaching mid-match in tennis</h3>
Think of every sport and you'll see a ranting manager or trainer somewhere on the sidelines. Except tennis, where players have to wait until they change ends for a hurried chat with the man who takes 15 per cent. Even toilet breaks are escorted, to ensure the rule isn't broken.
<h3>No.3 Not being allowed to over-celebrate in football</h3>
In a career that spanned over 500 matches, Alan Hansen scored just 14 goals. True, he was a defender, but had the day ever come that the dour Scot popped one in for his national team (0 goals from 26 games), he'd have gone absolutely bananas. And if today, probably booked.
<h3>No.2 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.1 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.TrueBravo2.jpgTrue22/08/2007 10:13:0027410 BEST HOME MADE CAKESBlurb about the 10 best home made cakes goes in here.Yahoo.jpgxxTrueBravo2.jpgFalse21/08/2007 13:38:0016710 BEST FOOTBALLERSBlurb about the 10 best football players goes in here.BBC.jpgThese were the besthtml copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes hereTrueFive.jpgTrue20/08/2007 17:44:0040710 BEST WORLD CUP GOALSBlurb about the 10 world cup goals goes in here.Bravo2.jpgThe best goals everNo 10. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 9. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 8. Zico 
Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal
No 7. Zico 
Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal
No 6. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 5. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 4. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 3. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 2. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 1. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finalsTrueBravo2.jpgTrue22/08/2007 10:32:0041710 BEST FAST BOWLERSBlurb about the 10 best fast bowlers goes in here.O2.jpgPhil Hilton10. Sir Richard Hadlee: 
Small but perfectly formed, Sir Richard’s ‘tache was like many Kiwi attempts to keep up with the cricketing powerhouses, not bad but a little short of what is required for world class. 
Sir Richard 
 
9. Jack Russell: 
Eccentric wicket-keeper who could tell to within a matter of seconds how long his Weetabix had been soaked in milk for. Perhaps England’s greatest ever gloveman, now an artist, his moustache was as bedraggled as his trademark floppy hat. 
 
8. Clive Lloyd: 
A powerful batsman who, as captain from 1974 to 1985, was largely responsible for the West Indies’ extraordinary success. Also a star for Lancashire, the world’s greatest county cricket club. Great man, great ‘tache. 
 
7. Robin Smith: 
Nicknamed The Judge because of his hair, Smith combined his ‘tache with a mullet. The Judge did not wear a grill on his helmet, which meant the bowlers had a clear view of that top lip while he was hammering the fastest of bowling with supersonic cuts and hooks. He visibly enjoyed the regular snatches of chin music he received from the West Indian quicks. 
 
6. Kapil Dev: 
An Indian legend, Kapil Dev was a fine batsmen and the greatest pace bowler his country has ever produced. Competed in an era of great all-rounders, and like Botham and Hadlee he also had a decent ‘tache. 
 
5. Sir Ian Botham: 
The great man had to make an appearance. Not many men can look good with shoulder length, semi-permed hair and a moustache. But Beefy did, plus he managed to be the world’s greatest ever all-rounder at the same time. What a legend! 
 
4. Graham Gooch: 
Goochie ran the full gauntlet of facial hair during his career, from facial hair to full beard via designer stubble and his famous Zapata moustache. He was at his best though when sporting the ‘tache, as his 333 against India would testify. Interestingly, his 333 was boosted by hundreds from Allan Lamb and Robin Smith in a marvellous Test match for hairy-lips. 
 
3. David Boon: 
Tasmania’s all-time favourite cricketing son, Boonie would have held the Australian prize for the greatest cricketing ‘tache, but for the presence of the world beating Big Merv. Rumour has it that he was considered to be featured on the Aussue $5 note but they couldn’t fit his moustache on. 
 
2. Adam Hollioake: 
Perhaps a surprising pick for second place, Hollioake managed to do what other England captain’s have never, that is win an international ODI tournament when he led an inexperienced team to the Champions Trophy in Sharjah. His Aussie background maybe why he sported an incredible handlebar moustache for the start of the 2004 season. 
 
1. Merv Hughes: 
The clear winner by a hairy mile. According to Cricinfo, the big-hearted Australian fast bowler “was a lively character armed with an imposing run-up and delivery action, a classic fast bowlers’ glare down the pitch, a mischievous sense of humour and a moustache of incredible proportions”. Merv’s facial appendage is listed in Wikipedia as one of the all-time leading handlebar moustaches. Today’s “metrosexual” modern cricketers would do well to follow his lippy lead.TrueBravo2.jpgTrue22/08/2007 12:10:00

|||

I presume you're looking at the tables through Database Explorer - and that by Checked/Unchecked you are referring to the "Allow Nulls" column in the Table Editor. So describe your columns instead as "id int NOT NULL" or "CategoryID int NULL" and people will understand you.

To answer your question, you'll be doing an INNER JOIN on the tables by the CategoryID.

However, be careful about your first table's definition where all but the id column are nullable i.e. may be unknown. That immediately looks like poor design. Does every record in the first table have to belong to a category? Then the CategoryID field shouldn't be null in the first table, and you should define a foreign key relationship between it and the Category table (you can do this through point-and-click within the Explorer tool).

If you do so, then when you use the Query Editor in Database Explorer to graphically build your query, you'll see them joined together in exactly the way you want.

|||

Assuming your first table (with listtitle, filename etc) is called Category and second table with Title is called CategoryTitle you can write a query as:

SELECT C.id ,C.CategoryID ,C.ListTitle ,C.Blurb ,C.FileName ,C.ByLine ,C.HTMLCopy,C.MainStory ,C.MainStoryImageFile ,C.Publish ,C.Date ,C.TitleFROM Category CJOIN CategoryTitle CTon C.CategoryID = CT.CategoryID

|||

my first table is called shortlist and the second is called categories so can i write the below or do i have to put those Cs in??

SELECT id ,CategoryID ,ListTitle ,Blurb ,FileName ,ByLine ,HTMLCopy,MainStory ,MainStoryImageFile ,Publish ,Date ,CategoryTitleFROM shortlistJOIN CategoryTitleon shortlist.CategoryID = Categories.CategoryID

|||

Looking at the structure of your tables it looks like you want to select all the data for a specific category? The only value in the second table that is not in the first is CategoryTitle. So if you want to select all the data from table1 along with the CetegoryTitle Value from Table2... use something like this:

Select a.*, b.CategoryTitle from table1 a
inner join table2 b
on a.CategoryID = b.CategoryID
WHERE a.CategoryID = 'value'

I am assuming you are passing a CategoryID. Remember to replace the table1 and table2 with the table names and the value with the correct param(CategoryID). If this is not what you were trying to do, just send me what you would like to select and what value you are passing into query!

|||

Just saw that you posted your table names, so the query will look like this:

Select a.*, b.CategoryTitle from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID

OR:

Select a.*, b.CategoryTitle from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID
WHERE a.CategoryID = 'value'

If you want a specific category, hope this helps and good luck!

|||

SELECT id,S.CategoryID,ListTitle ,Blurb ,FileName ,ByLine ,HTMLCopy,MainStory ,MainStoryImageFile ,Publish ,Date ,CategoryTitleFROM shortlist SJOIN CategoryTitle Con S.CategoryID = C.CategoryID

When you have a column that is present in both tables, you will have to specify from which table you want the value retrieved from, otherwise SQL Server will complain

with an "Ambiguous column name CategoryId". So you need to put prefix the column name with the tablename or alias. If the column names are long its a lot of typing.

So we alias it with an acronym that is meaningful. Here I used S for Shortlist and C for CategoryTitle so you can prefix the CategoryId with either "S." or "C.".

|||Thank you very much. that worked. What if i want to make the CategoryTitle uppercase? :)|||

This will make the CategoryTitle upper case:

Select a.*, UPPER(b.CategoryTitle) from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID

|||

I would do it at the front end, but there is an UPPER() function if you have to do it in SQL.

Friday, March 30, 2012

if i want to join two sql tables ...

No comments:

Post a Comment

Identity column

Blog Archive

About Me