Hi,
I have the following table:
Column 1 Column 2
A A
A B
A C
B F
B F
C G
C A
I need a query to return the following:
A null
B F
C null
How can I do that?
Thanks,
ALex 3 1325
Why did you return an "F" for one row and NULL for the others? I'm
guessing that you only want to return a value in the second column
where that value is the same for every instance of the value in the
first column. I'm also guessing that both columns are not nullable in
your table (because you didn't specify any other columns that could be
a key). If that's correct, try this:
SELECT col1,
CASE WHEN MIN(col2)=MAX(c ol2) THEN MIN(col2) END AS col2
FROM your_table
GROUP BY col1;
(untested)
--
David Portas
SQL Server MVP
--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
After that ABSOLUTELY BASIC PIECE OF NETIQUETTE, did you read what you
posted? What the @!$% specs for this?? Wait, wait, let me read your
mind and guess that you want to see only those rows whose col_1 groups
have one and only one value for col_2 !!
SELECT F1.col1, F2.col2
FROM Foobar AS F1
LEFT OUTER JOIN
(SELECT col1, MIN(col2)
FROM Foobar
GROUP BY col1
HAVING MIN(col2) = MAX(col2))
AS F2 (col1, col2)
ON F1.col1 = F2.col1;
If you plan to stay in this trade, please learn minimal Netiquette and
how to write a minimal specification.
--CELKO-- (jc*******@eart hlink.net) writes: After that ABSOLUTELY BASIC PIECE OF NETIQUETTE, did you read what you posted? What the @!$% specs for this?? Wait, wait, let me read your mind and guess that you want to see only those rows whose col_1 groups have one and only one value for col_2 !! ... If you plan to stay in this trade, please learn minimal Netiquette and how to write a minimal specification.
No, this is not what minimal nettiquette is about. Although, including
CREATE TABLE is nice, it's mainly a good idea, because it helps to
get a good answer.
But minimal and basic nettiquette is about something else: that is about
being polite and friendly. Something you are miserable poor in.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: James |
last post by:
My form and results are on one page.
If I use :
if ($Company) {
$query = "Select Company, Contact
From tblworking
Where ID = $Company
Order By Company ASC";
}
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use SUBSTRING(ProductName,
1, CHARINDEX('(', ProductName)-2).
I can get this result, but I had to use several views (totally
inefficient). I think this can be done in one efficient/fast query,
but I can't think of one.
In the case that one query is not...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: Steve Patrick |
last post by:
Hi All
You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.
I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4....
| |
by: Alan Lane |
last post by:
Hello world:
I'm including both code and examples of query output. I appologize if
that makes this message longer than it should be.
Anyway, I need to change the query below into a pivot table query. I'm
having trouble doing it. Help!
Here is my code so far:
Sub OldRegionQuery()
|
by: Takeadoe |
last post by:
Dear NG,
Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?
Thank you very much!
Mike
|
by: mcmahonb |
last post by:
Hey people...
I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my side.
I am trying to learn how to manipulate Dynamic Queries by forms via the example database: QrySampl.MDB, offered by Microsoft (as a learning tool, I suppose.) In particular, I am working with code from the example: "Query By Form (QBF) Using...
|
by: n |
last post by:
Hello! Here is a problem I hope you can point me to a solution. It
Problem: A teacher needs to know which lesson to teach.
A school has a curriculum with 26 lessons, A-Z.
For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.
|
by: Jo |
last post by:
Hi there,
I'm Jo and it's the first time I've posted here. I'm in process of creating
a database at work and have come a little unstuck.....I'm a bit of a novice
and wondered if anyone could help. I work in a library and send out dual
language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers ordered
and books sent out to individuals. I am trying to work out a way of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: 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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |