I want to make a list box that displays results from 2 columns, how?

[LibreLyrae]

I am using LO 4.0 to create an invoice database. I want the invoice entry form to be able to have a drop down list where the employee can choose from either the name or the business name that the invoice will be billed to. The Name is a concatenated result of joining First Name and Last Name fields from the Contacts table, and Business Name is another field on the contacts table, both have associated ContactID’s. I have tried several different ways using queries and views and combining queries and no luck. My two most promising so far have been:

1. A Union ALL:
   SELECT “NAME”, “ContactID” FROM “vwFirstNameLastNameJoin” UNION ALL SELECT “NAME”, “ContactID” FROM “vwBusinessNameFromContacts”

This displayed the concatenated “NAME” from the first-last name join I did and the contact ID’s only (shows ALL contact IDs), it shows no Business Names.

2. I tried a LEFT OUTER JOIN
   SELECT * FROM { OJ “vwFirstNameLastNameJoin” LEFT OUTER JOIN “vwBusinessNameFromContacts” ON “vwFirstNameLastNameJoin”.“ContactID” = “vwBusinessNameFromContacts”.“ContactID” }

and it produced 4 columns instead of 2.

How do I get it to produce a column of all the ContactID’s along with the corresponding ‘NAME’ (First and Last combined) AND OR the Business Name associated with that ID? OR does anyone have any other suggestion? ContactID is a required field and a PK, If I put 2 boxes on the form, one for name and one for business name it won’t let me bypass the Name box to get to Business Name because ContactID is required. I’d rather not have to have 2 separate but identical forms, one for customers and one for business customers.

I am developing business databases that I plan to offer to the Open Source community when complete to help more businesses make the switch to Linux. Any help would be greatly appreciated.

[LibreLyrae]

NOTE: The reason I had tried the above UNION and JOIN was because the double pipe ( || ) concatenations I was using were excluding all of my columns with NULL values.I am still learning my SQL and I have learned that sometimes the best way to figure it out is try it all and see what works :o)

[EasyTrieve]

@LibreLyrae, || only works with HSQLDB, but not with MySQL. So better to use CONCAT(…) which works with both.

[LibreLyrae]

I figured it out myself and thought I would post my solution: The answer was the CONCAT function which allows the combining of columns that contain NULL values. I did 2 CONCAT queries, one to first combine first and last name:

SELECT CONCAT( CONCAT( “LastName”, ’ ’ ), “FirstName” ) AS “NAME”, “ContactID” FROM “Contacts”

Then a second one to combine “NAME” with “BusinessName”

SELECT CONCAT( CONCAT( “Contacts”.“BusinessName”, ’ ’ ), “qryFirstLastNameConcat”.“NAME” ) AS “Full Name”, “ContactID” FROM “Contacts”, “qryFirstLastNameConcat” WHERE “qryFirstLastNameConcat”.“ContactID” = “Contacts”.“ContactID”

And this gave me the result I wanted, I was then able to order this list and use the query to populate my list box so that we can look up by any combination of name (First only, Last only, Business only, or some combination of the 3).

My only wish… that I was able to use a comma to separate the Business Name and the person’s name. When I did:
SELECT CONCAT( CONCAT( “Contacts”.“BusinessName”, ', ’ ), “qryFirstLastNameConcat”.“NAME” ) AS “Full Name”, “ContactID” FROM “Contacts”, “qryFirstLastNameConcat” WHERE “qryFirstLastNameConcat”.“ContactID” = “Contacts”.“ContactID”

It put a comma before the beginning of every “NAME” whether they had a business or not, because it is a CONCAT which allows NULL values… but I can live with it for now, if anyone has a suggestion I’d love to hear it. Cheers.