Home > Archive > PHP SQL > August 2006 > Anoying SQL problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Anoying SQL problem
|
|
| b@a.com 2006-08-13, 6:58 pm |
| I have a problem with the below SQL I want to return hirers and their
related details for accounts they have.
But in my several WHERE clauses that I use, I check fields in the
arrangements table, sooo, if a client
Has many Arrangement set for their account, it returns that clients
details x amount of times based on how many arrangements records
relate to there account. So my problem is, I need to be able to query
the arrangements table for a client's account but not return duplicate
results for every arrangement relating to that account. So it ignores
the arrangements table with regards to the amount of records entered
for a account and just queries it if records exists, but doesn’t
return duplicate hirers for the amount of arrangements for that hirers
account.
THANK You so much!!!
SELECT Hirer.Hirer_ID, Hirer.Hirer_First_Name, Hirer.Hirer_Surname,
Hirer.Hirer_Address, Hirer.Hirer_DOB,
Accounts.Account_number, Account_Type.Account_Type,
account_status.Account_Status, Clients.Client_Name,
Agents.Agents_Name, Vehicle.Vehicle_Registration
From hirer
left outer join Accounts on (hirer.hirer_ID =
Accounts.hirer_ID)
left outer join account_status on (Accounts.account_status_ID =
account_status.account_status_ID)
left outer join Account_Type on (Accounts.Account_Type_ID =
Account_Type.Account_Type_ID)
left outer join Vehicle on (Accounts.Account_ID =
Vehicle.Account_ID)
left outer join Clients on (Accounts.Client_ID =
Clients.Client_ID)
left outer join Agents on (Accounts.Agents_id =
Agents.Agents_id)
left outer join Arrangement on (Accounts.Account_ID =
Arrangement.Account_ID)
Where <condition removed>
| |
| ZeldorBlat 2006-08-14, 6:58 pm |
|
b@a.com wrote:
> I have a problem with the below SQL I want to return hirers and their
> related details for accounts they have.
> But in my several WHERE clauses that I use, I check fields in the
> arrangements table, sooo, if a client
> Has many Arrangement set for their account, it returns that clients
> details x amount of times based on how many arrangements records
> relate to there account. So my problem is, I need to be able to query
> the arrangements table for a client's account but not return duplicate
> results for every arrangement relating to that account. So it ignores
> the arrangements table with regards to the amount of records entered
> for a account and just queries it if records exists, but doesn't
> return duplicate hirers for the amount of arrangements for that hirers
> account.
>
> THANK You so much!!!
>
>
> SELECT Hirer.Hirer_ID, Hirer.Hirer_First_Name, Hirer.Hirer_Surname,
> Hirer.Hirer_Address, Hirer.Hirer_DOB,
> Accounts.Account_number, Account_Type.Account_Type,
> account_status.Account_Status, Clients.Client_Name,
> Agents.Agents_Name, Vehicle.Vehicle_Registration
> From hirer
> left outer join Accounts on (hirer.hirer_ID =
> Accounts.hirer_ID)
> left outer join account_status on (Accounts.account_status_ID =
> account_status.account_status_ID)
> left outer join Account_Type on (Accounts.Account_Type_ID =
> Account_Type.Account_Type_ID)
> left outer join Vehicle on (Accounts.Account_ID =
> Vehicle.Account_ID)
> left outer join Clients on (Accounts.Client_ID =
> Clients.Client_ID)
> left outer join Agents on (Accounts.Agents_id =
> Agents.Agents_id)
> left outer join Arrangement on (Accounts.Account_ID =
> Arrangement.Account_ID)
> Where <condition removed>
The easy solution would be to add the word "distinct" after the select.
|
|
|
|
|