October 9, 2007 at 1:37 pm
I have a the following query
SELECT vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.reg_cst_key, vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.reg_key,
vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.reg_evt_key, vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.[Reg Type],
vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.Name, vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.Badge, vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.Company,
vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.[Reg Date], vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.Invoice, vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.cst_id,
vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.reg_reg_key, vw_client_gcsaa_2008_golf_classic_invoice_sc.Inv#,
vw_client_gcsaa_2008_golf_classic_invoice_sc.Product, vw_client_gcsaa_2008_golf_classic_invoice_sc.code, vw_client_gcsaa_2008_golf_classic_invoice_sc.Price,
vw_client_gcsaa_2008_golf_classic_invoice_sc.[Qty.], vw_client_gcsaa_2008_golf_classic_invoice_sc.Total,
vw_client_gcsaa_2008_golf_classic_invoice_sc.Balance, vw_client_gcsaa_2008_golf_classic_invoice_sc.[Paid/Closed],
vw_client_gcsaa_2008_golf_classic_invoice_sc.Shipped, vw_client_gcsaa_2008_golf_classic_invoice_sc.Notes
FROM vw_client_gcsaa_2008_golf_classic_invoice_sc INNER JOIN
vw_client_gcsaa_GIS2008_SAME_AS_NF_SC ON
vw_client_gcsaa_2008_golf_classic_invoice_sc.net_cst_key = vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.reg_cst_key
where vw_client_gcsaa_GIS2008_SAME_AS_NF_SC.Name like 'Blak%'
what I need is 3 things, I just pulled there were clause to get data to someone. this is a list of registrants with Guest that I need to pull invoices out on and get onto a report (Visual Studio). I want the everything that's in the query where the guest is attached to the registrant and a registrant that has no guest with them. I have tried 6 way's from sunday to get this working and it's not working.
The only 2 things I see is on the query there is a reg_key and a reg_reg_key that the Reg_key is the Parent and the reg_reg_key is the guest. and all of the registrants have null reg_reg_key's and the only persons that have reg_reg_key that is not null are the guest. This is for an invoice and if anyone has a clue on this please let me know.:ermm:
Sorry the formatting on this sucks it should be 5 records
14350EF1-490F-4A75-929D-589C59D70CE6E6AB2518-A617-4AFC-B4C3-52125AF4BB1EDCF05A23-CDD1-482F-8C8D-2F30FAEAA705RegistrantBlaker Joel SJoelOld Greenwood Golf Course09/20/200753026000016595INULL16889Golf Course Management Member Benefit SubscriptionGCMMBRBEN0.0010.000.0000YNNULL
14350EF1-490F-4A75-929D-589C59D70CE6E6AB2518-A617-4AFC-B4C3-52125AF4BB1EDCF05A23-CDD1-482F-8C8D-2F30FAEAA705RegistrantBlaker Joel SJoelOld Greenwood Golf Course09/20/200753026000016595INULL53026Golf ClassicM_9201450.001450.000.0000YNNULL
14350EF1-490F-4A75-929D-589C59D70CE6E6AB2518-A617-4AFC-B4C3-52125AF4BB1EDCF05A23-CDD1-482F-8C8D-2F30FAEAA705RegistrantBlaker Joel SJoelOld Greenwood Golf Course09/20/200753026000016595INULL53026Member Registration - GCSAA National Championship or Golf Classic 10010.0010.000.0000YNNULL
2A9AC754-A9B9-47C0-A53E-C0C703E607C6D504EADF-FD21-4327-B5B1-12510F54EC5CDCF05A23-CDD1-482F-8C8D-2F30FAEAA705GuestBlaker JulieJulieNULL09/20/200753027000589338IE6AB2518-A617-4AFC-B4C3-52125AF4BB1E53027Guest - GCSAA National Championship or Golf Classic10020.0010.000.0000YNNULL
14350EF1-490F-4A75-929D-589C59D70CE6E6AB2518-A617-4AFC-B4C3-52125AF4BB1EDCF05A23-CDD1-482F-8C8D-2F30FAEAA705RegistrantBlaker Joel SJoelOld Greenwood Golf Course09/20/200753026000016595INULL53026Four-BallM_9202150.001150.000.0000YNNULL
October 9, 2007 at 2:01 pm
in this case, you need to join the main table on itself.
in order to do that, you need to use at least one alias; here's an example based on your data; you'll be able to use it as a model to flesh out the rest of the columns you need;
SELECT
Member.reg_key,
Member.Name As MemberName,
Guest.reg_key,
Guest.Name As GuestName,
Member.reg_cst_key,
Member.reg_key,
Member.reg_evt_key,
Member.[Reg Type],
Member.Name,
Member.Badge,
Member.Company,
Member.[Reg Date],
Member.Invoice,
Member.cst_id,
Member.reg_reg_key,
Invoice.Inv#,
Invoice.Product,
Invoice.code,
Invoice.Price,
Invoice.[Qty.],
Invoice.Total,
Invoice.Balance,
Invoice.[Paid/Closed],
Invoice.Shipped,
Invoice.Notes
FROM vw_client_gcsaa_2008_golf_classic_invoice_sc Invoice
INNER JOIN vw_client_gcsaa_GIS2008_SAME_AS_NF_SC Member
ON Invoice.net_cst_key = Member.reg_cst_key
INNER JOIN vw_client_gcsaa_GIS2008_SAME_AS_NF_SC Guest
ON Member.reg_key = Guest.reg_reg_key And Member.reg_reg_key is Null
where Member.Name like 'Blak%'
Lowell
October 9, 2007 at 2:04 pm
Yeah, I have already tried that one. It doesn't show the 5th record but rather combines the user to the Parent, I need to have the fifth record there for the quest because that person has a different invoice number and different line amounts.
October 9, 2007 at 2:12 pm
if you could post the DDL for the table, and change the sample records to insert statements, we could help better; there's just no way to sort thru that copy paste to make meaningful inserts to a sample table.
Lowell
October 9, 2007 at 2:18 pm
it's a view that's created on 4 tables in the database
October 9, 2007 at 3:43 pm
Let me understand the problem.
1. You want list of all registrants with guests (and their invoices)
2. You want list of all registrants with no guest (and their Invoices).
The first query is inner join between registrants and guests (tables ) - ViewA
The second query is left join of registrant with guest (where guest id is null). - ViewB
Select * from
(select cols... from ViewA
Union select cols... from ViewB) Registrants
Inner join
Invoices on Keys ..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply