-1

I have 2 tables in a large SQL Database and i need to query across them and I am struggling TBH. Here are the parameters:

Table 1 - Live Policies
Table 2 - Email Addresses

Common Pivot = Client number which is present in both tables.

From Table 1 i need to retrieve the following fields:

Client Number
Ref Number
Name 
Postcode
Inception date
Policy Type (= 'PC')

 Select Client, Ptype, Ref, Incep, [Name], Postcode from [Live
 Policies] where Ptype = 'PC'

This works fine.

From Table 2 i need to retrieve:

Webaddr

My question is how do i return the email address for the required records from the second table by referencing the client number? (client number is the same for all records) The second part of the statement is where i'm getting stuck.. I'm aware of the JOIN statement but if i try this i just get nowhere.. Help most appreciated!

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Martin Taylor
  • 1
  • 1
    You need to work a litle more in your question, Provide the complete schema for the tables involve, Include some sample data and desire output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Feb 25 '16 at 16:01
  • I'm sorry but i can't provide any sample data due to data protection issues (client data) but here are the table structures: Table 1 - Live.Policies - Column Name Column Name Occup NetR Name LtranDat LTADate IPT% Inv Incep Grp Freq Forename Fee Disc% Desc Comm% Co Client Cbran Canc Balance Addr4 Addr3 Addr2 Addr1 – Martin Taylor Feb 25 '16 at 16:07
  • The sample doesn't have to be real. See this [page from the faq](http://stackoverflow.com/help/mcve) for more on generating a good example. – David Rushton Feb 25 '16 at 16:08
  • I already give you an answer. But if you read the link I provide you will find this [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) You can make some sample data to make your question more clear. – Juan Carlos Oropeza Feb 25 '16 at 16:08
  • Table 2 - MIS_I_C26 coloumn Name Column Name WORK WEBADDR USERNAME Sect Ref PK_ID PASSWORD Line LETTER Key HOME EMAIL Date Client Bran – Martin Taylor Feb 25 '16 at 16:10
  • @MartinTaylor Dont put it on the comment, just edit your question and leave a comment like `I include the desire information` – Juan Carlos Oropeza Feb 25 '16 at 16:12
  • Why do you "get nowhere" with the JOIN? Do you get an error? Post your attempt to use JOIN, and what result you get from it. – Tab Alleman Feb 25 '16 at 16:12
  • Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) – Tab Alleman Feb 25 '16 at 16:13

1 Answers1

0

USE a JOIN

 select L.Client, L.Ptype, L.Ref, L.Incep, L.[Name], L.Postcode, E.Webaddr
 from [Live Policies] as L
 JOIN [Email Addresses] as E
    ON L.Client = E.Client
 where Ptype = 'PC'
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118