dlite922 1,584
Recognized Expert Top Contributor
I've heard this may not be possible, but never hurts to ask.
I've got three tables, Case, Violator, Alias
Case is a court police case
Violator is the person
Alias is different names (aliases) that person has.
Here's my Query: -
-
SELECT
-
c.id ,
-
c.caseNumber ,
-
v.lastName ,
-
v.firstName ,
-
a.firstName AS aliasFirstName,
-
a.lastName AS aliasLastName,
-
v.driverLicenseNumber ,
-
v.dateOfBirth ,
-
t.courtDate ,
-
c.status
-
-
FROM
-
`case` as c LEFT JOIN trial as t ON c.id = t.caseID,
-
violator as v LEFT JOIN alias as a ON v.id = a.violatorID
-
-
WHERE
-
c.violatorID = v.id AND
-
( c.status IN ('CONT','ARR','DE','FTAP','OJW','PRO','STAY','WRNT'))
-
-
ORDER BY 1 DESC;
-
-
-
//Here's some test results from fake data, some fields are cut off because of no room.
-
-
+-----+------------+-----------+-------------+----------------+---------------+
-
| id | caseNumber | lastName | firstName | aliasFirstName | aliasLastName
-
+-----+------------+-----------+-------------+----------------+---------------+
-
| 117 | A001 | DOE | JOHN | NULL | NULL
-
| 116 | 07PA36503 | AVENT | CHRISTOPHER | NULL
-
| 115 | 07PA33856 | APPLEBY | KENT | NULL
-
| 114 | 07PA36620 | ANDERSON | JOSHUA | Josh
-
| 114 | 07PA36620 | ANDERSON | JOSHUA | Joshi
-
| 113 | 07PA35300 | ADNDERSON | DONALD | NULL | NULL
-
| 112 | 07PA36487 | AKHTAR | NASER | NULL | NULL
-
| 111 | 07PA36488 | AKHTAR | NASER | NULL | NULL
-
| 110 | CA0003 | ARANJUEZ | ALAN | NULL | NULL
-
| 109 | CA0001 | CARINOS | JOHNY | Jimmy | Car
-
| 106 | CA0002 | MILLS | CORRINE | NULL | NULL
-
+-----+------------+-----------+-------------+----------------+---------------+
-
-
//expect result is for the second caseID 114 to not be displayed.
-
-
As you can see CaseID 114 is listed twice because Joshua Anderson has 2 alias records (Josh and Joshi)
How can I change my query so that it only displays the first alias, and each case only ONCE, but i need it to search all the aliases.
That is, If i type in Joshi, it will still pull up that record, but the listing will display Josh, because that's the first alias.
Complicated I know.
Anybody up for the challenge if even possible?
Thanks,
Dan
2 1920 dlite922 1,584
Recognized Expert Top Contributor
Okay, I feel stupid.
Just add GROUP BY c.id !!
Tada!!
Problem Solved.
Dan
Just in case you are not aware of this option, if you wish to also retrieve a delineated list of all the aliases as one string variable (which you can display or manipulate after retrieving from the database), you can use the GROUP_CONCAT function when you have a GROUP BY clause.
The default deliniator in group_concat is a comma, but you can specify instead what should be used. For direct display purposes, I usually set the delineator to ', ' which is a comma followed by a space, as this looks better.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Steve |
last post by:
I've run in to a problem with a query I'm trying to write. I have attached a
sample SQL script at the end of this post to show an overview of what I'm
working with.
I want to be able to use LIMIT to control how many rows from one table are
returned, independent of how many rows there are in a second table that is
joined to the first.
When I execute the following SQL script:
|
by: Haisoo Shin |
last post by:
Hello..
I am working on a PHP/PEAR web page that shows statistics of how many
people read a certain article during given period of time.
I have, say, two tables called 'books' and 'logs'. The 'books' table
has fields named 'bookNo', 'bookName', and so on, and 'logs' table has
fields named 'bookNo', and 'time'. Whenever somebody accesses a book,
I insert a row to the 'logs' table with 'INSERT INTO logs VALUES ($no,
NOW());'
|
by: Edward |
last post by:
SQL Server 7.0
If I run the following in Query Analyzer I get no records returned:
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'
If, however, I run either :
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'
|
by: AG |
last post by:
I have a very big table with 20 million records DistinctProjectionKey
which i join several times to different tables in this query.
select distinct distinctprojectionkeyid,dpk.MarketID,
dpk.Classificationid,
dpk.DistributorID,
dpk.ManufacturerID,
dpk.LocationID,
dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR as
factor3,P1.FACTOR as factor4
|
by: Dom |
last post by:
I need to run a query based on a query in Access. The second query has
a number of conditions which all work well, but there is one more
contition I need to set to make it run properly.
the first query returns a number of data items, consisting, among other
fields, of a Company_ID and a Rating_Date. For most companies, the
latest value of Rating_Date is equal to a value in a seperate table
(tblVariables) which logs the last time a...
| |
by: deko |
last post by:
Is there any way to limit the number of records loaded into a ListBox?
I looked at qdf.MaxRecords (to apply to the query that is the RowSource of
the ListBox) but that only applies to ODBC data sources. I also looked at
Tools > Options > Edit/Find and tried setting the "Don't display lists where
more than this number of records read:" property, but that doesn't help.
The List Box in question is supposed to allow scrolling/browsing of...
|
by: aaron.reese |
last post by:
Guys,
this is my problem:-
I have records which are linked by two fields on a left inner join
(there may be 0,1 or more records in the right hand table)
The relationship is not unique (it's a map reference so it can't be...)
so I am getting a cartesian join on the right hand table - look, it's
not my database OK, I don't have any choice!!!
|
by: mmitchell_houston |
last post by:
I'm working on a .NET project and I need a single query to return a
result set from three related tables in Access 2003, and I'm having
trouble getting the results I want.
The details:
Question ------------
QuestionID
QuestionText
Question_MediaTypeID
|
by: Ivor Somerset |
last post by:
Hi,
I've an Access DB table ("Groups") where data are as follow:
Id Group Rank Item
1 1 1 7364
2 1 2 283
3 1 3 34888
4 2 1 277
5 2 2 8233
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |