Joining two tables 1

[kev510]

Hello everyone.
Let me first say that I'm very new with SQL, and I believe there is an easy way to go about the problem I'm having, but it's just not clicking into my head.

I have Table1 and Table44.

Both table contain data that I would like to display as one table in a webpage. The tables would be matched by values in the columns called "SampleNo". Table1 has MOST of the data I need (SampleNo, SampleTitle) to display, but I have to bring over a column named "SamplePG" from Table44.

Problem I'm having is that ONLY records that are SamplePG = 1 exist in Table44. So when I match SampleNo in Table1 and Table44, my query only displays 4 records because there are only 4 (SamplePG = 1) records in Table44.

Here's what I need it to do - I have to display ALL RECORDS from Table1, but whatever DOES exist in Table44 (matching by SampleNo in Table1/Table44), I have to display SamplePG = 1, otherwise display SamplePG = 0.

Sorry if my explaination is hard to understand, but I tried my best. If you have questions, please let me know as I'll be standing by the computer.

Thanks for your time!

 

[r937]

your explanation was very good

what you're looking for is a LEFT OUTER JOIN

every basic SQL tutorial will cover this

the only twist is displaying 0 when there is an unmatched row, and you would use the COALESCE function for that purpose

give it a try and we'll help you if you get stuck

r937.com | rudy.ca

 

[kev510]

Hi R937!
Thanks for your response. I guess re-writing my question 4-5 times helped.

I got as far as using inner/outer join functions, but not COALESCE function. I will try that and let you know of the results soon! Thanks again!

 

[kev510]

Ok, I got my tables to join up correctly, but I've researched and researched about the COALESCE function, and I can't seem to figure out how it can help me. All I have left now to do is to display the NULLs as 0...

 

[r937]

could you please show your query?

r937.com | rudy.ca

 

[kev510]

Here's the query i'm using

select a.SampleNo, a.SampleTitle, b.PG from Table1 a LEFT OUTER JOIN Table44 b ON a.SampleNo = b.SampleNo

 

[r937]

select a.SampleNo
     , a.SampleTitle
     , [b]coalesce(b.PG,0)[/b] as PG
  from Table1 a 
left outer
  join Table44 b 
    on b.SampleNo = a.SampleNo

simple, eh?

r937.com | rudy.ca

 

[kev510]

WOW

the "Books Online" help guide completely misdirected me (so complicated) about the coalesce function...

all i got left to do is to practice its pronounciation...

THANK YOU SO MUCH for your help!

 

[gmmastros]

[blue]>> all i got left to do is to practice its pronounciation...[/blue]

I can't speak for everyone else, but I pronounce it:

co-uh-less




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

co-ah-less

r937.com | rudy.ca