2. PHPRunner General questions
  3. SQL Using Where Clause
This topic is locked

SQL Using Where Clause

 		5/31/2007 12:35:04 PM
PHPRunner General questions
H
 hlewis author

Hi,

Looking for some guidance here ....
I have 3 tables which we will call Table A, B & C. Table A has a primary field which we will call 'field 1' and others, 'field 2', 'field 3', etc. Tables B and C have the foreign key 'field 1' as well as others e.g field2, field 3, etc. So in my query I have select field 2, field 3, etc. FROM Tables A, B and C, but I only want the records from Tables B & C that match 'field 1' e.g Table A.field 1 = Table B.field 1 and the same for Table C. Field 1 in Tables B and C might be empty so there needs to be some independence. I have tried using AND and OR conditions in the statement, but not getting the output I'm expecting.
Any suggestions for what the Where clause should look like?
Sergey Kornilov admin 5/31/2007
select * from A

inner join B on A.Field1=B.Field1

inner join C on A.Field1=C.Field1


Check Using SQL Joins topic in PHPRunner manual for more info.
H
 		hlewis author 5/31/2007


select * from A

inner join B on A.Field1=B.Field1

inner join C on A.Field1=C.Field1


Check Using SQL Joins topic in PHPRunner manual for more info.


Thanks for that. Have used format above but am getting an error message saying syntax error (missing operator) in query expression?
Sergey Kornilov admin 5/31/2007

Why don't you just post your query here along with exact error message?
H
 		hlewis author 5/31/2007

Ok, query listed below:
select account.account_id,

[account.account_id] as [account_id1],

[customer_listing.account_id] as [account_id2],

[partner_listing.account_id] as [account_id3],

[subscriptions_current.account_id] as [account_id4],

[subscription_start_date],

[subscription_end_date],

[subscription_type],

[account.user_name],

[password],

[account.title] as [title1],

[account.first_name] as [first_name1],

[account.surname] as [surname1],

[user_email] as [user_email1],

[account.last_update] as [last_update1],

[customer_listing.phone_number] as [phone_number2],

[customer_listing.mobile_number] as [mobile_number2],

[customer_listing.fax] as [fax2],

[organisation_name],

[organisation_email],

[organisation_url],

[user_group],

[admin_user],

[admin_setup_name],

[admin_setup],

[add_to_mail_list],

[customer_listing.address_line1] as [address_line12],

[customer_listing.address_line2] as [address_line22],

[customer_listing.town] as [town2],

[customer_listing.county] as [county2],

[customer_listing.postcode] as [postcode2],

[customer_listing.creation_date] as [creation_date2],

[customer_listing.last_update] as [last_update2],

[partner_listing.first_name] as [first_name3],

[partner_listing.surname] as [surname3],

[partner_listing.email] as [email3],

[partner_listing.url] as [url3],

[partner_listing.phone_number] as [phone_number3],

[partner_listing.mobile_number] as [mobile_number3],

[partner_listing.fax] as [fax3],

[partner_listing.address_line1] as [address_line13],

[partner_listing.address_line2] as [address_line23],

[partner_listing.town] as [town_p],

[partner_listing.county] as [county3],

[partner_listing.postcode] as [postcode3],

[partner_listing.postcode_last_3_digits] as [postcode_last_3_digits3],

[partner_listing.creation_date] as [creation_date3],

[partner_listing.last_update] as [last_update3],

[account.creation_date] as [creation_date1]

From [account], [subscriptions_current], [customer_listing], [partner_listing]

inner join customer_listing on account.account_id = customer_listing.account_id

inner join subscriptions_current on account.account_id = subscriptions_current.account_id

inner join partner_listing on account.account_id = partner_listing.account_id
Error message as follow:
SQL Error State:4200, Native Error Code: FFFFF3E4, ODBC Error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'account.account_id = customer_listing.account_id

inner join subscriptions_current on account.account_id = subscriptions_current.account_id

inner join partner_listing on account.account_id = partner_listing.account_id'
J
 		Jane 6/1/2007

Hi,
try to use following query:

...

[account.creation_date]as [creation_date1]

From [account]

inner join [customer_listing] on account.account_id = customer_listing.account_id

inner join [subscriptions_current] on account.account_id = subscriptions_current.account_id

inner join [partner_listing] on account.account_id = partner_listing.account_id
H
 		hlewis author 6/1/2007

Hi,
Tried that, but it still doesn't like it and returns the same error message???
Sergey Kornilov admin 6/1/2007

Two things:

  1. Every time you add square bracket wrappers make sure you wrap both table name and field name:
    [account.account_id] - incorrect

    [account].[account_id] - correct
  2. Take baby steps. Test this query without joining tables first. Add first joined table etc.

    Also you can use Query Designed in MS Access which will build correct SQL query for you.
H
 		hlewis author 6/1/2007

Thanks Sergey. Took the later MS Access route and worked fine. Think the square brackets were getting me confused. The resulting query syntax didn't seem to different to what I had tried? Nevertheless, I understand the MS Access querying far better so will stick with that from now on.
Regards and thanks once again.