CREATE TABLE books
(
book_id bigint,
book_title varchar(100),
CONSTRAINT books_book_id_p key PRIMARY KEY (book_id)
)WITH OIDS;
CREATE TABLE authors
(
id bigint,
book_id bigint,
author_name varchar(100),
CONSTRAINT authors_id_pkey PRIMARY KEY (id),
CONSTRAINT authors_book_id _fk FOREIGN KEY (book_id)
REFERENCES books (book_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)WITH OIDS;
INSERT INTO books (book_id,book_t itle) VALUES('1','PHP 5.X');
INSERT INTO books (book_id,book_t itle) VALUES('2','POS TGRESQL');
INSERT INTO authors (id,book_id,boo k_title) VALUES('1','1', 'Mangal Kumar');
INSERT INTO authors (id,book_id,boo k_title) VALUES('2','1', 'Pankaj Kumar');
INSERT INTO authors (id,book_id,boo k_title) VALUES('3','2', 'Brijesh Kumar');
INSERT INTO authors (id,book_id,boo k_title) VALUES('4','2', 'Mangal Kumar');
We want to select the books with their author names. We used following query which creates duplicate rows:
SELECT book_title, author_name from books JOIN authors ON (books.book_id = authors.book_id );
OUTPUT:
book_title author_name
-------------- -------------------
PHP5.X Mangal Kumar
PHP5.X Pankaj Kumar
POSTGRESQL Brijesh Kumar
POSTGRESQL Mangal Kumar
How to get the unique rows with book title and author name?
2 5288 rski 700
Recognized Expert Contributor
But the rows you put are unique?
I don't get what is your problem.
To get unique rows you can use DISTINCT, but in your example you don't need it.
How do you want the output of the query look like?
CHECK THIS QUERY AND ITS OUTPUT:
SELECT DISTINCT books.book_id, book_title, author_name from books JOIN authors ON (books.book_id = authors.book_id ) ORDER BY author_name:
OUTPUT:
book_id book_title author_name
-------------- -------------- ---------------------------
2 PHP5.X Brijesh Kumar
1 PHP5.X Mangal Kumar
2 POSTGRESQL Mangal Kumar
1 POSTGRESQL Pankaj Kumar
In the above output book_id is repeating, we want that query return unique ids only:
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: B. Fongo |
last post by:
I learned MySQL last year without putting it into action; that is why
I face trouble in formulating my queries. Were it a test, then you
would have passed it, because your queries did help me solve my problem.
I'll turn to MySQL doc after getting through this pressing project.
Thanks a lot Roger!
Babale
-----Urspr=FCngliche...
|
by: Paul Bramscher |
last post by:
Here's one for pathological SQL programmers.
I've got a table of things called elements. They're components, sort of
like amino acids, which come together to form complex web pages -- as
nodes in trees which form parent-child relationships, sort of like
newsgroups. For example, the parent_id field points to another element.
Indent_level...
|
by: DCM Fan |
last post by:
{CREATE TABLEs and INSERTs follow...}
Gents,
I have a main table that is in ONE-MANY with many other tables. For example, if
the main table is named A, there are these realtionships:
A-->B
A-->C
A-->D
|
by: Preston Landers |
last post by:
Hello all.
I am trying to write a query that "just" switches some data around so
it is shown in a slightly different format. I am already able to do
what I want in Oracle 8i, but I am having trouble making it work in
SQL Server 2000. I am not a database newbie, but I can't seem to
figure this one out so I am turning to the newsgroup. I...
|
by: Jim in Arizona |
last post by:
This doesn't make any sense to me. I'm hoping some SQL guru out there knows
the answer.
I'm making a help desk type database system. There's only two tables, one
called TTickets and the other called TNotes. The Primay Key of TTickets is a
one to Many to the Foreign Key of the TNotes table.
In my VB code, I create a relation between the...
| |
by: Steve |
last post by:
One of our clients recently upgraded their Office version to 2003. When
they tried to run our program (written in Access 2000), they ended up
with the wrong data. My coworker and I have tested this problem and
found that it ONLY occurs on Windows XP machines running Office 2003.
The problem ends up being that ONE 1-to-1 join in ONE query...
|
by: Shanimal |
last post by:
I would like to know how to join 2 queries so that the results of these
2 queries show up in the same query:
SELECT b.bios_serial_number FROM bios b:
SELECT s.system_name FROM system s;
Basically I want to create a report that includes both system name and
serial number. I'm new to this and none of the JOIn documentation was
clear to me.
|
by: JP2006 |
last post by:
I have a situation where I have 2 tables - NODES and TAGS. Nodes is the
main table and contains various columns one of which is tagID. Tags
contains a list of tag names and tagIDs. When a user adds an entry to
the Nodes table they can specify one or more tags for the entry. Then
when the form is posted if there were multiple tags in the post...
|
by: sqlservernewbie |
last post by:
Hi Everyone,
Here is a theoretical, and definition question for you.
In databases, we have:
Relation
|
by: BD |
last post by:
Hi, all.
I need to enforce a one-to-many relationship on 2 tables, with a join
table.
Say the join table contains account information. It has cust_no and
acct_no. Both cust_no and acct_no are child FKs to other tables (no
nulls will exist in these columns).
Logically, one customer can have several accounts, but one account can
|
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...
| |
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...
|
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. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| | |