how to write a query with subquery

  • i want to query for consumer, phone and address, SSN information

    consumer table is Consumer

    Phone are in table ConsumerPhone with FK to PhoneType and consumer table

    Address are in table ConsumerAddress with FK to AddressType and Consumer tables

    Identifier are in table ConsumerIdentifier with FK to IndentifierType and Consumer table

    I want

    consumer firstname, lastname from consumer

    primary phone from phone where PhoneType.phonetypeid=1

    primary address from address where AddressType.addresstypeid=1

    SSN from consumeridentifier where IndentifierType.identifiertypeid=2

    Medicaid from consumeridentifier where IndentifierType.identifiertypeid=1

    query should always return consumer firstname and last even when address, phone are NUL

  • This article on OUTER JOINs should be helpful to you. Even though it is for SQL Server 2000 it still applies to 2014.

    Essentially you want something like this:

    SELECT

    C.firstName,

    C.lastName,

    CP.phone,

    CA.ADDRESS,

    CI.SSN,

    CI.Medicaid

    FROM

    dbo.consumer AS C

    LEFT JOIN dbo.consumerPhone AS CP

    ON C.consumerID = CP.consumerID

    LEFT JOIN consumerAddress AS CA

    ON C.consumerID = CA.consumerID

    LEFT JOIN consumerIdentifier AS CI

    ON C.consumerID = CI.consumerID;

    If there will always be a row in ConsumerIdentifier you can make that an INNER JOIN

  • thank you.

    I want only a particular phone for the consumer.

    Each consumer can have 0 phone or more than one phone number,i want only primary phone.

    similarly address, consumer can have more than one address or 0 address, i want only primary address.

    From consumerphone where phoneTypeID=1

    From ConsumerAddress where AddressTypeID=1

    For ConsumerIdentifier is a table with SSN and medicaid.

    So for a particular consumer, ConsumerIdentifier table will have

    ID=1 value=xxxx - where id=1 means SSN

    ID=2 value=yyyy - where ID=2 means medicaid

  • Saujib (9/12/2013)


    thank you.

    I want only a particular phone for the consumer.

    Each consumer can have 0 phone or more than one phone number,i want only primary phone.

    similarly address, consumer can have more than one address or 0 address, i want only primary address.

    From consumerphone where phoneTypeID=1

    From ConsumerAddress where AddressTypeID=1

    For ConsumerIdentifier is a table with SSN and medicaid.

    So for a particular consumer, ConsumerIdentifier table will have

    ID=1 value=xxxx - where id=1 means SSN

    ID=2 value=yyyy - where ID=2 means medicaid

    OUTER JOIN's still apply. To limit to a particular phone type you can put "AND CP.phoneTypeID = 1" in the ON clause of the JOIN to customerPhone. To get what you want out of CustomerIdentifier you will have to JOIN to it twice with the desired TYPE in the ON CLAUSE so your query could look like this:

    SELECT

    C.firstName,

    C.lastName,

    CP.phone,

    CA.ADDRESS,

    SSN.value AS SSN,

    MC.value AS Medicaid

    FROM

    dbo.consumer AS C

    LEFT JOIN dbo.consumerPhone AS CP

    ON C.consumerID = CP.consumerID AND

    CP.phoneTypeID = 1

    LEFT JOIN consumerAddress AS CA

    ON C.consumerID = CA.consumerID AND

    CA.addressTypeID = 1

    LEFT JOIN consumerIdentifier AS SSN

    ON C.consumerID = SSN.consumerID AND

    SSN.identifierTypeID = 1

    LEFT JOIN consumerIdentifier AS MC

    ON C.consumerID = MC.consumerID AND

    MC.identifierTypeID = 2;

    Or you can LEFT JOIN to the "type" tables and specify a name or description in the ON clause. I'll leave that part for you to figure out.

  • thank you very much this helps.

    i have another query.

    OrderHistory table, has list of values for order.

    I have to query for last row from the orderhistory table, get addressID, query address information from address table.

    I was able to write the query.

    I also has to get user who submitted the order from OrderHistory table.

    Can i include this in the same query? if so, how to join the query?

    OrderHistory table

    OrderHistoryID

    OrderID

    AddressID

    OrderStatusID

    UserID

    select top 1 addressID from OrderHistory where OrderID=1 order by OrderHistoryID

    I want userid from OrderHistory where OrderStatusID=2.

    can i combine both queries ?

  • To get the data for the most recent row, use row_number:

    LEFT JOIN (SELECT UserID, AdressID,

    rowno = row_number() OVER (PARTITION BY AdressID ORDER BY OrderID DESC)

    FROM Orderhistory) AS OH ON A.AdressID = OH.AddressID

    AND OH.rowno = 1

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply